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

How PostgreSQL aggregation works and how it inspired our hyperfunctions’ design

blog.timescale.com

Post reply on HN
Print view View original

21–30 of 33 posts

Re: How PostgreSQL aggregation works and how it inspired our hyperfunctions’ design

#21

Post by takeda »

clarkbw wrote: post #10
noctarius wrote: post #4

Earlier quoted context omitted.

Yes you can have both in the same database. We actually use TimescaleDB exactly that way, well at least for now. We'll in the process of separating certain tables out, so make it possible to select a pg database based on async or sync replication. In terms of creating a Hypertable (TimescaleDB table) you create the normal table first and then transform it into a Hypertable.

> ... make it possible to select a pg database based on async or sync replication Can you explain why you doing this? Is this application data vs. time-series?

Not op, but choosing async vs sync replication is typically done around guarantees. Async (the default mode) is generally preferred in most cases as it adds relatively small overhead. The problem with it, is that if something happens to master, the standby node might be behind (i.e. some data might be lost).

Sync on the other hand won't return from the transaction until the change is replicated.

For time series, generally async is acceptable, and even for regular data most people might also be fine with async (that's why it is the default). Sync is used if you absolutely aren't allowing any data loss. But because it waits for all nodes to confirm they wrote data to the disk writing will always be slower.

The problem is that if you use physical replication it applies to all data in the database (the logical resource not the physical server), so my guess is that some of the non-time series data has higher durability requirements.

Re: How PostgreSQL aggregation works and how it inspired our hyperfunctions’ design

#22

Post by nlehuen »

You can see the same pattern of two-step aggregation with the HLL_COUNT family of functions in BigQuery: https://cloud.google.com/bigquery/docs/reference/standard-sq...

This is really useful for this kind of metric (distinct count) that can't be trivially aggregated. It allows generating pre-aggregated tables containing the intermediate aggregation state. Now you can compute your DAUs, WAUs and MAUs from the same daily pre-aggregates.

I wish this design was generalized in SQL. For instance, getting the same family of function for variance computation would be super useful for efficient & correct computation of confidence intervals at different levels of aggregation.

Re: How PostgreSQL aggregation works and how it inspired our hyperfunctions’ design

#23

Post by djk447 »

nlehuen wrote: post #22

You can see the same pattern of two-step aggregation with the HLL_COUNT family of functions in BigQuery: https://cloud.google.com/bigquery/docs/reference/standard-sq... This is really useful for this kind of metric (distinct count) that can't be trivially aggregated. It allows generating pre-aggregated tables containing the intermediate aggregation state. Now you can compute your DAUs, WAUs and MAUs from the same dai…

Absolutely! We're actually developing a lot of that: https://github.com/timescale/timescaledb-toolkit/tree/main/d...

A number of the things you're looking for we've done experimentally and we'll be stabilizing over the next few releases. So we'd love some feedback while we're still able to futz with the API without making breaking changes.

But the two you're asking about are, I think, going to be covered by hyperloglog (we just reimplemented the internals with HLL++) and stats_agg family of functions, which have both 1D (which will give you avg, stddev, variance, etc) and 2D (co-variance, slope, intercept, x-intercept etc as well as all the 1D functions).

Would also love issues if you think we're missing other stuff, going to be generalizing this and want to make it useful for folks.

(NB: Post author here.)

Re: How PostgreSQL aggregation works and how it inspired our hyperfunctions’ design

#25

Post by djk447 »

infogulch wrote: post #24

This seems similar to differential datalog (DDlog) https://hexgolems.com/2020/10/getting-started-with-ddlog/ https://github.com/vmware/differential-datalog

Hadn't heard of this, but really interesting, thanks for posting! Will have to see if we can use that somehow...

(NB: Post author here.)

Re: How PostgreSQL aggregation works and how it inspired our hyperfunctions’ design

#27

Post by nextaccountic »

paulddraper wrote: post #16

> Continuous aggregation I found this to be a very common need, so I created denorm [1] for doing this in vanilla PostgreSQL. (It also does incrementally updated joins as well.) It's a real help to instant aggregation results for large data sets. (I expect the performance of a native implementation like Timescale to be superior. Unfortunately, I use managed database services, like RDS.) [1] https://github.com/rivethe…

Hey, that seems very cool!

I saw it is written in Python. Does the Python code run only when creating the sql query? Am I supposed to add the generated sql code to git?

Re: How PostgreSQL aggregation works and how it inspired our hyperfunctions’ design

#28

Post by sa1 »

codeismath wrote: post #11

The continuous aggregates portion of this blog (along with the breakdown of Transition, Combine, and Final Functions) reminded me of "A Theory of Changes for Higher-Order Languages: Incrementalizing Lambda-Calculi by Static Differentiation" (Giarrusso et. al.) [0]. Particularly the part of getting logically-consistent results via different routes of computation. David Kohn says this in the blog post: "But, you have t…

This is the most interesting paper I’ve been pointed to in a while.

Re: How PostgreSQL aggregation works and how it inspired our hyperfunctions’ design

#29

Post by paulddraper »

nextaccountic wrote: post #27
paulddraper wrote: post #16

> Continuous aggregation I found this to be a very common need, so I created denorm [1] for doing this in vanilla PostgreSQL. (It also does incrementally updated joins as well.) It's a real help to instant aggregation results for large data sets. (I expect the performance of a native implementation like Timescale to be superior. Unfortunately, I use managed database services, like RDS.) [1] https://github.com/rivethe…

Hey, that seems very cool! I saw it is written in Python. Does the Python code run only when creating the sql query? Am I supposed to add the generated sql code to git?

Correct. The Python code generates the triggers and functions to keep the rollup table up-to-date.

Add the SQL DDL to whatever migration control system you use.

Re: How PostgreSQL aggregation works and how it inspired our hyperfunctions’ design

#30

Post by djk447 »

throwdbaaway wrote: post #26

I don't quite understand how is it possible to rollup percentile_agg from 15 minutes buckets to 1 day buckets. Won't the resulting values be off by a lot?

Under the hood we use the uddsketch percentile approximation algorithm [1], which is able to both be combined and also provides relative error guarantees. I'll also be writing a post about our percentile approximation stuff in the future that will go into more detail on this, though I'm not going to go through all the math. The basic gist is it uses a histogram with logrithmically sized buckets to give some relative error guarantees.

(NB: Post author here)

[1]: https://docs.timescale.com/api/latest/hyperfunctions/percent...

Post reply on HN