Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PipelineDB 1.0 – High-Performance Time-Series Aggregation for PostgreSQL

pipelinedb.com

Post reply on HN
Print view View original

31–40 of 64 posts

Re: PipelineDB 1.0 – High-Performance Time-Series Aggregation for PostgreSQL

#31

Post by wenc »

skunkworker wrote: post #7

Interesting, this seems to be the other side of the postgres time series extension coin. TimescaleDB for writes, PipelineDB for reads.

If 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.

Re: PipelineDB 1.0 – High-Performance Time-Series Aggregation for PostgreSQL

#32

Post by grammr »

alakin wrote: post #30
grammr wrote: post #29

Earlier quoted context omitted.

I'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 .

That'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 ). Can you comment on when you think this feature cold land or is it still a ways off?

Sure! 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

Re: PipelineDB 1.0 – High-Performance Time-Series Aggregation for PostgreSQL

#33

Post by [deleted] »

alakin wrote: post #30
grammr wrote: post #29

Earlier quoted context omitted.

I'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 .

That'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 ). Can you comment on when you think this feature cold land or is it still a ways off?

[deleted]

Re: PipelineDB 1.0 – High-Performance Time-Series Aggregation for PostgreSQL

#34

Post by Fergi »

alakin wrote: post #30
grammr wrote: post #29

Earlier quoted context omitted.

I'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 .

That'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 ). Can you comment on when you think this feature cold land or is it still a ways off?

Just out of curiosity, do you have a specific use case that necessitates stream-stream JOINs, or were you just exploring the docs and wondering about this?

Re: PipelineDB 1.0 – High-Performance Time-Series Aggregation for PostgreSQL

#35

Post by grammr »

wenc wrote: post #31
skunkworker wrote: post #7

Interesting, this seems to be the other side of the postgres time series extension coin. TimescaleDB for writes, PipelineDB for reads.

If 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. Time…

PipelineDB 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

Re: PipelineDB 1.0 – High-Performance Time-Series Aggregation for PostgreSQL

#36

Post by manigandham »

PipelineDB = 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.

Re: PipelineDB 1.0 – High-Performance Time-Series Aggregation for PostgreSQL

#37

Post by alakin »

Fergi wrote: post #34
alakin wrote: post #30

Earlier quoted context omitted.

That'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 ). Can you comment on when you think this feature cold land or is it still a ways off?

Just out of curiosity, do you have a specific use case that necessitates stream-stream JOINs, or were you just exploring the docs and wondering about this?

My use case is pretty much parallel time series alignment with several layers of aggregation. I guess I perceive stream-stream joins as an easy way for me to wrap my head around how to structure my compute graph, but it seems doable with the method mentioned by @grammr. I'd hope for an interface roughly like "CREATE join_stream from (SELECT slow_str.key AS key, sum(slow_str.val, fast_str.val) AS val FROM slow_str, fast_str INNER JOIN ON slow_str.key = fast_str.key)". I do realize there are some tough design decisions for a system like this, but I'd also like to drop my wacky zmq infrastructure ;)

Re: PipelineDB 1.0 – High-Performance Time-Series Aggregation for PostgreSQL

#38

Post by mfreed »

manigandham wrote: post #36

PipelineDB = 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.…

Thanks 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.

Re: PipelineDB 1.0 – High-Performance Time-Series Aggregation for PostgreSQL

#39

Post by dominotw »

mfreed wrote: post #38
manigandham wrote: post #36

PipelineDB = 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.…

Thanks 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.

I 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.

Re: PipelineDB 1.0 – High-Performance Time-Series Aggregation for PostgreSQL

#40

Post by akulkarni »

skunkworker wrote: post #7

Interesting, this seems to be the other side of the postgres time series extension coin. TimescaleDB for writes, PipelineDB for reads.

(Timescale founder) As someone points out elsewhere, the difference between TimescaleDB and PipelineDB is more akin to raw data and materialized aggregates (Timescale) vs. streaming summary data (Pipeline).

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)

Post reply on HN