PipelineDB 1.0 – High-Performance Time-Series Aggregation for PostgreSQL
pipelinedb.com
pipelinedb.com
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.
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.
Really looking forward to multi node version. Good luck.
There's a question of what you actually want to do with the time-series data. If you don't expect to have much data or just want to store it and maybe occassionally query it, then a single server with partitioning (e.g. through pg_partman, Timescale) might be enough. If you want to build an analytical dashboard that needs to remain fast even if you're dealing with many users and terabytes of data per day, then you probably need Citus.
Citus can load, aggregate and query the data in parallel using all the cores in the cluster. It also supports Postgres' native partitioning and pg_partman: https://www.citusdata.com/blog/2018/01/24/citus-and-pg-partm...
If you're going to create roll-up tables and power your dashboard using those tables, you're fine with both options IMO. Cloudflare was also using Citus exactly for this use-case before they switched to Clickhouse.
If you have ad-hoc use-cases for time-series data, Timescale might be a better option because it's built exactly for this use-case and it knows the semantics of the data so it can partition the data in an optimized way and perform some optimizations such as parallelized operations and re-sizing chunks. In that sense, it's comparable to Influxdb, not Citus.
Timescale is limited to time-based partitioning on one node while Citus uses a master node with partitioning across multiple worker nodes. I don't see many situations why you would run both.
If you're just doing single-node time-series then use Timescale. If you have Citus then you can use the native partitions in 11 or pg_partman to add a secondary partitioning dimension.
TimescaleDB for writes, PipelineDB for reads.
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!
The time-series database of a project I'm on uses timescale and it's been great for the quick inserts and the `time_bucket` function has been very useful for aggregate queries.. But moving from aggregations generated on-the-fly to ones updated continuously on data change sounds like it could be awesome for us, so I am v happy to see this article today :-)
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.
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).
So we are big fans of what the PipelineDB team are building and see value in using both.
(And if you are interested in how TimescaleDB's hypertable/chunk architecture plus other optimizations (e.g. at the query planner level) lead to both higher inserts and faster queries compared to Postgres: https://blog.timescale.com/timescaledb-vs-6a696248104e)
Are they solving the same problem in different ways or are they complementary projects? If it's the latter, what would that look like?
http://docs.pipelinedb.com/installation.html#install-postgre...
Can't wait for Postgres 11 support.
INSERT INTO events_stream (ts, value) VALUES (now(), '0ef346ac');
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?
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-aggre...
> So you create a table, insert into it, and it's always empty. Is that right?
That is correct. Streams can only be read by continuous queries (e.g. you can't even run a SELECT on them).
> Does this work for any table in pg? How does pg know that the insert should NOT actually insert a row?
PipelineDB streams are represented as a specific kind of PostgreSQL foreign table [0], so only foreign tables created in a specific way will be considered streams. You can use triggers to write table rows and updates out to streams if you want to though.
[0] https://www.postgresql.org/docs/current/static/sql-createfor...
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.
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!
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.
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!
That being said, millions of events per second should absolutely be doable, especially if your continuous queries are relatively straightforward as you've suggested. If the output of your continuous queries fits in memory, then it's extremely likely you'd be able to achieve the throughput you need relatively easily.
Many of our users use our Kafka connector [0] to consume messages into PipelineDB, although given that you're using protobufs I'm guessing your messages require a bit more processing/unpacking to get them into a format that can be written to PipelineDB (basically something you can INSERT or COPY into a stream). In that case what most users do is write a consumer that simply transforms messages into INSERT or COPY statements. These writes can be parallelized heavily and are primarily limited by CPU capacity.
Please feel free to reach out to me (I'm Derek) if you'd like to discuss your workload and use case further, or set up a proof-of-concept--we're always happy to help!
How both products achieve this is however different.
Also, how's stride.io doing?
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!