PipelineDB 1.0 – High-Performance Time-Series Aggregation for PostgreSQL • Hacker News Board

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

#61

Post by buremba » Sat, Oct 27, 2018, 11:33 PM UTC

mslot wrote: post #55

buremba wrote: post #54

Earlier quoted context omitted.

Use TimescaleDB if you have time-series data, if you want to scale out your OLTP workload then Citus is what you're looking for.

Citus is also used for large time-series / analytics use cases e.g. https://www.citusdata.com/customers/heap 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 dashboar…

AFAIK Heap uses Citus but also has an internal partitioning scheduler for their customer event data so I don't think that they're a good example. Timescale doesn't support scaling out yet but it's in their roadmap so let's wait for them to implement for a fair conclusion.

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.

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

#62

Post by grammr » Mon, Oct 29, 2018, 3:50 AM UTC

jmort wrote: post #58

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 .

Can you say a bit more about "performant" or point me to some information? I haven't found any yet. I'm processing millions of protobufs per second and would love to get away from batch jobs to do some incredibly basic counting -- this seems like a fit conceptually...If its a fit, any recommendations on the best way to get those protobufs off a kafka stream and into pipelinedb would be great, too!

Performance depends heavily on the complexity of your continuous queries, which is why we don't really publish benchmarks. PipelineDB is different from more traditional systems in that not all writes are all created equal, given that continuous queries are applied to them as they're received. This makes generic benchmarking less useful, so we always encourage users to roughly benchmark their workloads to really understand performance.

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!

[0] https://github.com/pipelinedb/pipeline_kafka

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

#63

Post by nethi » Tue, Oct 30, 2018, 9:04 AM UTC

grammr wrote: post #11

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.

I'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 iss…

It seems like if you combine pipelinedb with timescaledb, you get continuous query capability of influx ?

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

#64

Post by ishikawa » Tue, Oct 30, 2018, 12:44 PM UTC

grammr wrote: post #60

ishikawa wrote: post #59

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

How 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…

Thank you, yes, it was helpful for me to understand the possibilities. I'll dig more into that.