grammr

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!

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!

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

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!

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.

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

One thing I will mention here is that we do have plans to add support for persistent streams [0] after version 1.0.0 is released. We've learned a lot over the years about how our users/customers interact with streams in production and persistent streams will be built atop that foundation of understanding.

Please feel free to comment on that issue with your use case, requirements, etc. and we'll see what we can do!