15 ms·
PipelineDB 1.0 – High-Performance Time-Series Aggregation for PostgreSQL
- chucky_z 8y agoI've been following Pipeline since the beginning and it's so fricking cool. Please, if you can't think of a good use of Pipeline, use it instead of a count(*)! :D
- Fergi 8y ago(Jeff, PipelineDB Co-Founder here) - thanks, Chucky! We appreciate your support!
- temuze 8y agoCongrats! Also, how's stride.io doing?
- grammr 8y agoI'm Derek, one of the co-founders--thank you! We're super happy with where Stride is at! We've continued to onboard customers in a few AWS regions and the infrastructure is rock solid at this point. Most users are ingesting 10k+ events/s and their analytics frontends are retrieving results in well under 100ms. We've gotten it to the point where it "just works" which has made Stride users' lives a lot easier at that scale. And since the hard parts of Stride are powered by PipelineDB, an added benefit for us is that we now get a ton of super detailed instrumentation data about PipelineDB performance and behavior, which has helped make the open-source product quite a bit better. We'll be moving Stride into self-service/GA next year--stay tuned!
- the-alchemist 8y agoAnd it supports Postgres 10.x! http://docs.pipelinedb.com/installation.html#install-postgresql http://docs.pipelinedb.com/installation.html#install-postgre... Can't wait for Postgres 11 support.
- Fergi 8y agoPostgreSQL 11 support is imminent =)
- skunkworker 8y agoInteresting, this seems to be the other side of the postgres time series extension coin. TimescaleDB for writes, PipelineDB for reads.
- wenc 8y agoIf I understand correctly, they're not really solving the same problem on the read/write sides of the coin. In fact, they seem to be on different tracks. PipelineDB seems to do continuous aggregation, so the type of data it deals with is essentially summary data. If you know your summary function a priori, this can lead to very compact and efficient storage. The use case for this is reporting, dashboarding, etc. TimescaleDB on the other hand deals with raw data. This is useful if you have multiple parties needed different types of aggregation from the same raw data. Also, if you want to do any kind of machine learning, raw unaggregated data would typically be more useful. They serve different use-cases it seems.
- grammr 8y agoPipelineDB co-founder here--I think this is a pretty fair take! I would also like to point out that the aggregate data stored in PipelineDB can still be further aggregated, processed, JOINed on etc. on demand as well. Since a continuous view's output is simply stored as a regular table, you are free to run arbitrary SELECT queries on it to further distill and filter your results. PipelineDB's special combine [0] aggregate allows you to combine aggregate values with no loss of information for this very purpose. The most common pattern among our user base is to aggregate time-series data into continuous views at some base level of granularity (e.g. by minute) and then aggregate over that for final results (e.g. aggregate down to hour-level rows for the date range my frontend has selected). [0] http://docs.pipelinedb.com/aggregates.html#combine http://docs.pipelinedb.com/aggregates.html#combine
- grammr 8y agoI'm Derek, one of the co-founders--that's an interesting way to frame it, I think that makes a lot of sense at a high level. We're in contact with the TSDB founders (awesome and super smart guys!) and are in the early stages of figuring out an integration that makes sense. That's most likely going to happen. To anyone interested: we'd love to hear and consider your ideas re: TSDB integration. Feel free to open an issue in either repo (or add to an existing one) and tell us more!
- allan_s 8y agoHas anyone tried to mix pipelinedb with timescale[1] , I think both are working on different side of playing with timeseries data ? [1]https://www.timescale.com/how-it-works https://www.timescale.com/how-it-works
- qaq 8y agotheoretically sounds like a perfect match
- crescentfresh 8y agoLooking over this cursorily, looks super cool. INSERT INTO events_stream (ts, value) VALUES (now(), '0ef346ac'); > As soon as the continuous view reads new incoming events and the distinct count is updated the reflect new information, the raw events will be discarded. So you create a table, insert into it, and it's always empty. Is that right? Does this work for any table in pg? How does pg know that the insert should NOT actually insert a row?
- Fergi 8y agoThis only applies to continuous views, not all PG tables. Think of continuous views in PipelineDB as very high throughput, incrementally updated materialized views. Raw data hits continuous queries in PipelineDB (continuous views) and only the output of the continuous queries is stored. So 1 billion events ingested could be distilled down into a single row that incrementally counts up from 1 => 1 billion as each data point arrives, instead of storing all of the 1 billion raw data points and counting them up later.
- ohnoesjmr 8y agoYou can't really do that with distinct, as if you have 1 billion distint entries, you essentially have to store all of them to dedup.
- deleted 8y ago[deleted]
- grammr 8y agoThis is precisely why PipelineDB has rich support for data structures such as HyperLogLog [0]. HLL's allow you to track distincts information using fixed-size HLLs that only grow to about 14KB while encoding uniques counts for billions of distinct values. The tradeoff is about a ~0.8% margin of error, which users generally find acceptable. Furthermore, PipelineDB has a special combine [1] aggregate that allows you to combine data structures such as HLL across multiple rows with no loss of information. A simpler example would be average: to get the actual average of multiple averages you obviously can't simply take the average of all the averages. Their weights must be taken into account, and combine handles that. The capability to combine aggregate values in this way generalizes to all aggregates in PipelineDB. [0] http://docs.pipelinedb.com/aggregates.html#hyperloglog-aggregates http://docs.pipelinedb.com/aggregates.html#hyperloglog-aggre... [1] http://docs.pipelinedb.com/aggregates.html#combine http://docs.pipelinedb.com/aggregates.html#combine
- jadbox 8y agoHow does this compare to Citus?
- Fergi 8y agoFundamentally different approaches. Citus focuses on horizontal scaling for PostgreSQL and PipelineDB focuses on continuous aggregation for large volumes of streaming time-series data.
- nwmcsween 8y agoThey are completely different products? Citus deals with scaling pipelinedb deals with continuous queries.
- Mayzie 8y agoCitus advertises itself as an excellent way to achieve real-time analytics across billions of rows and tonnes of data, which this product also does. How both products achieve this is however different.
- dkulchenko 8y agoHow does this compare to TimescaleDB? Are they solving the same problem in different ways or are they complementary projects? If it's the latter, what would that look like?
- akulkarni 8y ago(Timescale founder) I'd say they are quite complementary. More here: https://news.ycombinator.com/item?id=18298004 https://news.ycombinator.com/item?id=18298004
- tracker1 8y agoHoping this gains some traction as a defacto extension for cloud hosted postgresql. I think this is probably as useful as plv8 for a lot of use cases.
- alakin 8y agoIs most of the intermediate processing done in memory, or is it limited by hd write speed?
- grammr 8y agoI'm Derek, one of the co-founders--excellent question! The former. PipelineDB performs aggregations in memory on microbatches of events, and only merges the aggregate output of each microbatch with what's on disk. This is really the core idea behind why PipelineDB is so performant for continuous time-series aggregation. Microbatch size is configurable: http://docs.pipelinedb.com/conf.html http://docs.pipelinedb.com/conf.html.
- alakin 8y agoThat's awesome! If you don't mind - one more q.. I see that stream-stream joins are not yet supported (http://docs.pipelinedb.com/joins.html#stream-stream-joins http://docs.pipelinedb.com/joins.html#stream-stream-joins). Can you comment on when you think this feature cold land or is it still a ways off?
- grammr 8y agoSure! So stream-stream JOINs actually haven't been requested by users as much as you'd think. Users have generally been able to get what they need by using topologies of transforms [0], output streams, and stream-table JOINs. Continuous queries can be chained together into arbitrary DAGs of computation, which turns out to be a very powerful concept when mapping out a path from raw input events to the desired output for your use case. The primary issue in implementing stream-stream JOINs is that we'd essentially need to preemptively store every single raw event that could be matched on at some point in the future. Conceptually this is straightforward, but on a technical level we just haven't seen the demand to optimize for it. That being said, you could just use a regular table as one of the "streams" you wanted to JOIN on and then use an stream-table JOIN. As long as the table side of the JOIN is indexed on the JOIN condition, an STJ would probably be performant enough for a lot of use cases. With PostgreSQL's increasingly excellent partitioning support this is becoming especially practical. I also suspect that this is an area where integration with TimescaleDB could be really interesting! [0] http://docs.pipelinedb.com/continuous-transforms.html http://docs.pipelinedb.com/continuous-transforms.html
- manigandham 8y agoPipelineDB = Insert data with time component to be aggregated on the fly into always up-to-date summary tables using a variety of aggregation functions. Raw data is not persisted. TimescaleDB = Store data with time component into "hypertable" that is automatically partitioned by time, for faster queries when limited by time range. Single node and has helper methods to make time based bucketing and aggregation easier. Citus = Store data in distributed tables automatically partitioned and spread across multiple nodes, by any single column. Join across nodes with non-distributed tables. Can definitely use PipelineDB for real-time summaries and TimescaleDB or Citus for raw long-term storage in the same database. Side note: It would be nice if Postgres had package manager for extensions.
- mfreed 8y agoThanks for the great summary, manigandham. We're actively working on the scale-out version of TimescaleDB that will allow you to transparently shard hypertables across many servers. Hope to announce more specifics in the next several months.
- dominotw 8y agoI loved timescaledb but single node restriction made us go with Kafka streams. It was just too much hassle to maintain mapping between data and which node it's located at. Really looking forward to multi node version. Good luck.
- nwmcsween 8y agoWhat is wrong with citus? Why reimplement it?
- usgroup 8y agoFantastic guys, thank you! I’ve been looking forward to it becoming an extension for half a year. This is great news. This basically means Postgres now has continuous views and a toolkbox of functions for running calculations. Combined with PG11 partitioning features and better parallel gusty execution, PG is an even more formidable choice for medium sized data.
- Arqu 8y agoI work closely in the space of providing time series databases as managed solutions. I can say that I am very happy to see this recent development of new tsd's and this with timescale is a huge bump to the industry/segment. Everybody currently measures some analytics and mostly user data and there is so much abuse with it, yet there is so much more you can measure and do and it is still very early stage. Farms, industrial applications, IoT and so much more. I'd love to just measure temperature and wind speed at unprecedented resolution.
- tnolet 8y agoBig question for me: does it work on Heroku postgres?
- Fergi 8y agoNo, not currently; however, we offer hosted deployments of PipelineDB as well as a SaaS product called Stride (stride.io), which is based on PipelineDB.
- Rapzid 8y agoRunning functions on top of the transaction log(in transaction order) is a really powerful thing.
- ishikawa 8y agoVery interesting. Does it aggregate per day? If so, I wonder how it handles time-zone, I mean when to create a new day when you have agents on different time-zones.
- grammr 8y agoHow aggregations are performed are determined entirely by your own continuous view definitions [0]. In this case I'm guessing you'd want to include a time-based column in the aggregation GROUP BY clause. And since PipelineDB is a PostgreSQL extension, you can use the timestamptz type (which includes timezone support), and in general you could pretty easily simply normalize your event timezones in your continuous view definitions. When you're reading aggregate data back out, you could cast the time-based column using whatever timezone the client prefers. Thanks for the question--I hope that was helpful! [0] http://docs.pipelinedb.com/continuous-views.html http://docs.pipelinedb.com/continuous-views.html
- ishikawa 8y agoThank you, yes, it was helpful for me to understand the possibilities. I'll dig more into that.