How PostgreSQL aggregation works and how it inspired our hyperfunctions’ design • Hacker News Board

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

#11

Post by codeismath » Thu, Aug 05, 2021, 7:00 PM UTC

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 to have in-depth (and sometimes rather arcane) knowledge about each aggregate’s internals to know which ones meet the above criteria – and therefore, which ones you can re-aggregate."

I think Giarrusso addresses the internal requirements in a precise mathematical manner (though I may be wrong). Giarrusso Section 2.1 is the specific area I'm thinking, particularly Definition 2.1 and then later the discussion of the abelian group where certain binary operations must be commutative and associative. Giarrusso Equation 1 in the introducton defines externally what it means to be logically consistent.

Also, Giarrusso talks about "Self-Maintainability" where updating the continuous result only needs to look at the changes to the input. What was obvious to me from reading Giarrusso was that something simple like "AVG" is not self-maintainable unless you keep the intermediate state (sum and count) seperately. Kohn's distinction of Transition Function, Combine Function, and Final Function - together with Giarrusso's abelian group and change structure - is kind of a big deal in making arbitrary functions "continuous"-capable while being "self-maintainable".

I can see designing a [data] structure that adheres to the specific algabraic laws defined by Giarrusso's abelian group along with the 3 functions from Kohn (missing from Giarrusso). You can then feed this structure to a function that spits out two new functions for you: a normal function that can be used to calculate a result, and it's "continuous" version which only needs change to the inputs. For example, "avg()" and "avg_continuous()" can be automatically derived from a data structure of algebraically well-behaved operations.

Plus this would have a really cool Category Theory diagram for it!

David: absolutely love the animated gifs and pictures. Very well written indeed.

[0] Giarrusso et. al. https://www.researchgate.net/publication/269126515_A_theory_...

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

#12

Post by djk447 » Thu, Aug 05, 2021, 7:02 PM UTC

sgtfrankieboy wrote: post #9

djk447 wrote: post #8

Earlier quoted context omitted.

(NB: Post author here) Yes! You can use any of the hyperfunction aggregates to build a continuous agg. Including sketches like the percentile approximation stuff. With hyperloglog, it's still experimental, so you have to jump through some hoops to get it into a continuous agg as it could be dropped on next release, but once that's been stabilized it'll be very easy to use in continuous aggs. Feel free to test now, ju…

Interesting article! I was wondering if there plans for supporting Continuous Aggregates on Distributed hypertables? Its currently listed as not supported under the limitations docs page. I think the two would fit together perfectly as it will allow users to aggregate very large tables.

Yes! They do fit together very well.

There are plans to do that, I'm not sure the exact timeline, I think it's in the design phase now.

We do already support partitionwise aggregation, and this meshes very well with that, so that you're sending much, much less data back over the network between the data node and access node when you're trying to do things like percentiles. But I agree that continuous aggregate support will be an even bigger win!

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

#13

Post by nixpulvis » Thu, Aug 05, 2021, 7:20 PM UTC

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…

I'll have to read this paper, it seems interesting.

This problem also reminds me of this: https://jon.thesquareplanet.com/papers/phd-thesis.pdf

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

#14

Post by mfreed » Thu, Aug 05, 2021, 7:36 PM UTC

sgtfrankieboy wrote: post #9

djk447 wrote: post #8

Earlier quoted context omitted.

(NB: Post author here) Yes! You can use any of the hyperfunction aggregates to build a continuous agg. Including sketches like the percentile approximation stuff. With hyperloglog, it's still experimental, so you have to jump through some hoops to get it into a continuous agg as it could be dropped on next release, but once that's been stabilized it'll be very easy to use in continuous aggs. Feel free to test now, ju…

Interesting article! I was wondering if there plans for supporting Continuous Aggregates on Distributed hypertables? Its currently listed as not supported under the limitations docs page. I think the two would fit together perfectly as it will allow users to aggregate very large tables.

We plan to release support for continuous aggregates on distributed hypertables in the next month or two.

Initially version will support case where the raw data is a distributed hypertable, while the cagg table is a hypertable on the access node. Then follow-up with support where the cagg table can be distributed as well.

We are simultaneously working on support for compressed continuous aggregates as well, which we are targeting the same timeframe.

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

#15

Post by thom » Thu, Aug 05, 2021, 7:49 PM UTC

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…

Thanks for this comment and link, I think this area is one of the most interesting frontiers in computer science today. We're starting to see products like here in TimescaleDB and elsewhere in Materialize that build on these ideas, but in five or ten year's time the developer story here is going to be absolutely incredible. There is so much potential in being able to write declarative specifications for very complex stateful dataflows, that are then just maintained quickly and automagically, especially if we can do it without arbitrary windows and watermarks. Very exciting!

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

#16

Post by paulddraper » Thu, Aug 05, 2021, 8:09 PM UTC

> 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/rivethealth/denorm

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

#17

Post by ecnahc515 » Thu, Aug 05, 2021, 8:11 PM UTC

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 sounds a lot like how Prometheus works.

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

#18

Post by djk447 » Thu, Aug 05, 2021, 8:21 PM UTC

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…

(NB: Post author here)

Glad you liked the GIFs! Will have to take a look at this. Thanks for sharing!

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

#19

Post by pvorb » Thu, Aug 05, 2021, 9:05 PM UTC

GordonS wrote: post #6

oconnor663 wrote: post #2

Noob question: Can you have time-series tables and regular Postgres tables in the same database? Is this distinction something that you decide on when you create a table?

Yes! It's a nice benefit of TimescaleDB being built on top of Postgres.

And it's the feature that sets Timescale apart from other, NoSQL, time-series databases. With Timescale you can have all your data in the same database and join regular tables against hypertables or do other useful stuff directly in the database.

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

#20

Post by miohtama » Thu, Aug 05, 2021, 9:08 PM UTC

oconnor663 wrote: post #2

Noob question: Can you have time-series tables and regular Postgres tables in the same database? Is this distinction something that you decide on when you create a table?

I have been using TimescaleDB with Python, SQLAlchemy for a mixed timeseries and normal relational workload. The only issue I have encountered so far is that pq_restore needs some special, easy, wrapping. TimescaleDB is just an extension to PSQL.