4 ms·

Super fast aggregations in PostgreSQL 19

226 points by jnord 11mo ago

pgelephant2025 11mo ago
[flagged]
- isoprophlex 11mo ago
  [flagged]
  - tomhow 11mo ago
    Please don't do this here. If a comment seems unfit for HN, please flag it and email us at hn@ycombinator.com so we can have a look.
    - isoprophlex 11mo ago
      Got it. I didn't know mailing you was an option / something you'd prefer in such a case.
aidos 11mo ago
The key idea here seems to be that if you’re grouping on a column on a related table you can do your main aggregation by grouping on the foreign key id on the primary table and use that as a proxy for the data on the related table that you’re actually grouping by. In the examples given, it’s much faster, but is that mostly due to the missing indexes? I’d have thought that an optimal approach in the colour example would be to look at the product.color_id index, get the counts directly from there and you’re pretty much done. I have a feeling that Postgres doesn’t make that optimisation (I’ve looked before, but it was older Postgres). And I guess depending on the aggregation maybe it’s not useful in the general case. Maybe in this new world it _can_ make that optimisation? Anyway, as ever, pg just getting faster is always good.
- yfontana 11mo ago
  > In the examples given, it’s much faster, but is that mostly due to the missing indexes? I’d have thought that an optimal approach in the colour example would be to look at the product.color_id index, get the counts directly from there and you’re pretty much done. So I tried to test this (my intuition being that indexes wouldn't change much, at best you could just do an index scan instead of a seq scan), and I couldn't understand the plans I was getting, until I realized that the query in the blog post has a small error: > AND c1.category_id = c1.category_id should really be > AND p.category_id = c1.category_id otherwise we're doing a cross-product on the category. Probably doesn't really change much, but still a bit of an oopsie. Anyway, even with the right join condition an index only reduces execution time by about 20% in my tests, through an index scan.
- Sesse__ 11mo ago
  > In the examples given, it’s much faster, but is that mostly due to the missing indexes? You're saying “the missing indexes” as if you could add indexes for every join you're ever doing and that this would be faster than a hash join. For many systems, that's not feasible nor very performant; and depending on selectivity, hash join would often be better than an index lookup anyway. The biggest win from early aggregation is that you can reduce the number of rows significantly before you go join in other things (which would be a win even in nested-loop index lookup joins; smaller joins are nearly always better along every axis).
- sgarland 11mo ago
  So you’re saying, do something like this? 1. Index-only scans on t_product.{category,color} indices, summing each value 2. Lookup the names of those values in their parent tables, generate output rows If so, I suspect there are two reasons why it might not do that: Given the relatively small size of the t_product table (23 bytes overhead + 1 byte padding + int4 + int4 + 16 bytes text + [I think] 1 byte varlena = 49 bytes/row), it will be fairly well bin-packed into pages on the heap, consuming roughly 170 pages, assuming 8 KiB default, and default fillfactor of 100%). That trivially fits into a single segment file on-disk, and is a very easy sequential scan. If it does a sequential scan on the heap, it doesn’t have to check the Visibility Map, because it already has that information in the heap itself, which avoids a second (albeit small) lookup. Happy for someone who knows more about Postgres to correct me if I’m wrong, though!
yfontana 11mo ago
Interestingly, "aggregate first, join later" has been the standard way of joining fact tables in BI tools for a long time. Since fact tables are typically big and also share common dimensions, multi-fact joins for drill-across are best done by first aggregating on those common dimensions, then joining on them. Makes you wonder how many cases there are out there of optimizations that feel almost second nature in one domain, but have never been applied to other domains because no one thought of it.
- Netcob 11mo ago
  Probably quite a lot, being a specialist in multiple domains is getting more difficult.
  - pgaddict 11mo ago
    It's not about not knowing about an optimization. The challenge is to know when to apply it, so that it does not cause regressions for cases that can't benefit from it. It may be less risky in specialized systems, like BI systems typically don't need to worry about regressing OLTP workloads. Postgres absolutely needs to be careful of that. I believe that's one of the reasons why it took about ~8 years (the original patch was proposed in 2017).
- Sesse__ 11mo ago
  It's not that nobody thought of it. Group pushdown has been a thing in papers for ~10 years at least, but it's hard to plan; your search space (which was already large) explodes, and it's always hard to know exactly how many rows come out of a given grouping. I have no idea how Postgres deals with these. Hopefully, they're doing something good (enough) :-) Next up would hopefully be groupjoin, where you combine grouping and hash join into one operation if they are on the same or compatible keys (which is surprisingly often).
  - SigmundA 11mo ago
    I wonder if PG will ever implement plan caching like MSSQL so that the speed of the optimizer is less of a concern and it can take more time finding better plans rather than replanning on every execution of the same statement.
yxhuvud 11mo ago
Neat, I see how this can prevent a lot of frustration when it comes to making certain queries stay quick as complexity grows. I wonder if this means I can forget the trick to chose LATERAL queries all over the place for performance reasons.
anentropic 11mo ago
Is this "super fast" as in "faster than previous Postgres" or as in comparable to duckdb etc?
- mritchie712 11mo ago
  it's faster than previous Postgres. e.g. the gender_name example would already be optimized in duckdb via columnar execution and “aggregate first, join later” planning.
- thenaturalist 11mo ago
  DuckDB and other specialized DBs benefit from much more optimized math, data structures and data storage/ memory lookups I'd assume. But a 5x increase simply by optimizing the planner is nothing to be ashamed of.
- trueno 11mo ago
  toyed with pg_lake against our absolute dump of iceberg files (the nerds in my field call it a data lakehouse but engineering already has too many abstractions). it's pretty insane having postgres & the power of duckdb for mega aggregation, i threw a lot of wild windowed queries and aggregations at it and it seemed to really intuitively switch to using the duckdb jujutsu very well. looking at migrating the rest of our catalog to iceberg now just to have the pg_lake option in our back pocket for future application development. it's so damn cool, as far as dbs go i haven't personally been involved in anything that needed more power than what postgres could deliver with writes. to be able to tack on bigboi analytics on top of it really consolidates a lot for us. im generally pretty cynical of these big saas peoples acquiring cool stuff but snowflake nabbing crunchydata here (crunchydata = guys who work on some pretty interesting postgres extensions) and helping them push this one to the a proverbial finish line and then open sourcing it was really great to see. i was worried when the acquisition went down because this was the major postgres thing i was really hoping someone would deliver, and crunchydata imo seemed to have the best plan outlined that understood the need.
  - anentropic 11mo ago
    that's good to hear, I'm planning to try pg_lake soon would love to see more docs about operationalising it so far it looks like it may be possible to use the Crunchy PGO k8s and have the duckdb part as a sidecar
throw0101c 11mo ago
Running list of some of the changes/commits that have been done on the 19/dev branch (?): * https://pgpedia.info/postgresql-versions/postgresql-19.html https://pgpedia.info/postgresql-versions/postgresql-19.html
fourseventy 11mo ago
"Let us assume that we only store a handful of genders but millions of people" Weird example because you will get assholes like me pointing out that you can store gender with a boolean.
- bhaak 11mo ago
  The nicest word I would use to describe you is uninformed. There are various standards (e.g. ISO/IEC 5218) to encode gender and they are never boolean.
listenallyall 11mo ago
The example uses the syntax FROM person AS p, gender AS j WHERE p.gender_id = j.gender_id Isn't it preferable to be explicit? Does some of the inefficiency come from lack of explicitness? FROM person as p INNER JOIN gender as j ON p.gender_id = j.gender_id
- pgaddict 11mo ago
  AFAIK these two joins are exactly the same once you get past the parsing. It's just a different way to write an inner join. It's translated into the same AST and so there's no difference in planning/execution.
  - listenallyall 11mo ago
    Perhaps in this very basic case they are exactly the same but is that still true if you add secondary WHERE conditions that apply to just one table, or if one "table" is actually a view with a complex query definition, or many other ways in which a very simple "example" can quickly get complicated?
    - Sesse__ 11mo ago
      In general, you split up the WHERE condition at every top-level AND. Then you do either pullup or pushdown or both (it depends a bit on how your planner looks on the inside). In the end, you end up with the two cases being exactly the same before you start planning. For outer joins (left/right/full), it's different, and there you absolutely need the explicit join syntax (for correctness). And semijoins are not expressed the same way at all, partially for weird historical reasons.
- spprashant 11mo ago
  I personally prefer being explicit in this case. Seeing the word INNER triggers the right way to visualize the query in my head.

226 points by jnord 11mo ago

pgelephant2025 11mo ago

[flagged]

isoprophlex 11mo ago
[flagged]
- tomhow 11mo ago
  Please don't do this here. If a comment seems unfit for HN, please flag it and email us at hn@ycombinator.com so we can have a look.
  - isoprophlex 11mo ago
    Got it. I didn't know mailing you was an option / something you'd prefer in such a case.

aidos 11mo ago

The key idea here seems to be that if you’re grouping on a column on a related table you can do your main aggregation by grouping on the foreign key id on the primary table and use that as a proxy for the data on the related table that you’re actually grouping by. In the examples given, it’s much faster, but is that mostly due to the missing indexes? I’d have thought that an optimal approach in the colour example would be to look at the product.color_id index, get the counts directly from there and you’re pretty much done. I have a feeling that Postgres doesn’t make that optimisation (I’ve looked before, but it was older Postgres). And I guess depending on the aggregation maybe it’s not useful in the general case. Maybe in this new world it _can_ make that optimisation? Anyway, as ever, pg just getting faster is always good.

yfontana 11mo ago
> In the examples given, it’s much faster, but is that mostly due to the missing indexes? I’d have thought that an optimal approach in the colour example would be to look at the product.color_id index, get the counts directly from there and you’re pretty much done. So I tried to test this (my intuition being that indexes wouldn't change much, at best you could just do an index scan instead of a seq scan), and I couldn't understand the plans I was getting, until I realized that the query in the blog post has a small error: > AND c1.category_id = c1.category_id should really be > AND p.category_id = c1.category_id otherwise we're doing a cross-product on the category. Probably doesn't really change much, but still a bit of an oopsie. Anyway, even with the right join condition an index only reduces execution time by about 20% in my tests, through an index scan.
Sesse__ 11mo ago
> In the examples given, it’s much faster, but is that mostly due to the missing indexes? You're saying “the missing indexes” as if you could add indexes for every join you're ever doing and that this would be faster than a hash join. For many systems, that's not feasible nor very performant; and depending on selectivity, hash join would often be better than an index lookup anyway. The biggest win from early aggregation is that you can reduce the number of rows significantly before you go join in other things (which would be a win even in nested-loop index lookup joins; smaller joins are nearly always better along every axis).
sgarland 11mo ago
So you’re saying, do something like this? 1. Index-only scans on t_product.{category,color} indices, summing each value 2. Lookup the names of those values in their parent tables, generate output rows If so, I suspect there are two reasons why it might not do that: Given the relatively small size of the t_product table (23 bytes overhead + 1 byte padding + int4 + int4 + 16 bytes text + [I think] 1 byte varlena = 49 bytes/row), it will be fairly well bin-packed into pages on the heap, consuming roughly 170 pages, assuming 8 KiB default, and default fillfactor of 100%). That trivially fits into a single segment file on-disk, and is a very easy sequential scan. If it does a sequential scan on the heap, it doesn’t have to check the Visibility Map, because it already has that information in the heap itself, which avoids a second (albeit small) lookup. Happy for someone who knows more about Postgres to correct me if I’m wrong, though!

yfontana 11mo ago

Interestingly, "aggregate first, join later" has been the standard way of joining fact tables in BI tools for a long time. Since fact tables are typically big and also share common dimensions, multi-fact joins for drill-across are best done by first aggregating on those common dimensions, then joining on them. Makes you wonder how many cases there are out there of optimizations that feel almost second nature in one domain, but have never been applied to other domains because no one thought of it.

Netcob 11mo ago
Probably quite a lot, being a specialist in multiple domains is getting more difficult.
- pgaddict 11mo ago
  It's not about not knowing about an optimization. The challenge is to know when to apply it, so that it does not cause regressions for cases that can't benefit from it. It may be less risky in specialized systems, like BI systems typically don't need to worry about regressing OLTP workloads. Postgres absolutely needs to be careful of that. I believe that's one of the reasons why it took about ~8 years (the original patch was proposed in 2017).
Sesse__ 11mo ago
It's not that nobody thought of it. Group pushdown has been a thing in papers for ~10 years at least, but it's hard to plan; your search space (which was already large) explodes, and it's always hard to know exactly how many rows come out of a given grouping. I have no idea how Postgres deals with these. Hopefully, they're doing something good (enough) :-) Next up would hopefully be groupjoin, where you combine grouping and hash join into one operation if they are on the same or compatible keys (which is surprisingly often).
- SigmundA 11mo ago
  I wonder if PG will ever implement plan caching like MSSQL so that the speed of the optimizer is less of a concern and it can take more time finding better plans rather than replanning on every execution of the same statement.

yxhuvud 11mo ago

Neat, I see how this can prevent a lot of frustration when it comes to making certain queries stay quick as complexity grows. I wonder if this means I can forget the trick to chose LATERAL queries all over the place for performance reasons.

anentropic 11mo ago

Is this "super fast" as in "faster than previous Postgres" or as in comparable to duckdb etc?

mritchie712 11mo ago
it's faster than previous Postgres. e.g. the gender_name example would already be optimized in duckdb via columnar execution and “aggregate first, join later” planning.
thenaturalist 11mo ago
DuckDB and other specialized DBs benefit from much more optimized math, data structures and data storage/ memory lookups I'd assume. But a 5x increase simply by optimizing the planner is nothing to be ashamed of.
trueno 11mo ago
toyed with pg_lake against our absolute dump of iceberg files (the nerds in my field call it a data lakehouse but engineering already has too many abstractions). it's pretty insane having postgres & the power of duckdb for mega aggregation, i threw a lot of wild windowed queries and aggregations at it and it seemed to really intuitively switch to using the duckdb jujutsu very well. looking at migrating the rest of our catalog to iceberg now just to have the pg_lake option in our back pocket for future application development. it's so damn cool, as far as dbs go i haven't personally been involved in anything that needed more power than what postgres could deliver with writes. to be able to tack on bigboi analytics on top of it really consolidates a lot for us. im generally pretty cynical of these big saas peoples acquiring cool stuff but snowflake nabbing crunchydata here (crunchydata = guys who work on some pretty interesting postgres extensions) and helping them push this one to the a proverbial finish line and then open sourcing it was really great to see. i was worried when the acquisition went down because this was the major postgres thing i was really hoping someone would deliver, and crunchydata imo seemed to have the best plan outlined that understood the need.
- anentropic 11mo ago
  that's good to hear, I'm planning to try pg_lake soon would love to see more docs about operationalising it so far it looks like it may be possible to use the Crunchy PGO k8s and have the duckdb part as a sidecar

throw0101c 11mo ago

Running list of some of the changes/commits that have been done on the 19/dev branch (?): * https://pgpedia.info/postgresql-versions/postgresql-19.html https://pgpedia.info/postgresql-versions/postgresql-19.html

fourseventy 11mo ago

"Let us assume that we only store a handful of genders but millions of people" Weird example because you will get assholes like me pointing out that you can store gender with a boolean.

bhaak 11mo ago
The nicest word I would use to describe you is uninformed. There are various standards (e.g. ISO/IEC 5218) to encode gender and they are never boolean.

listenallyall 11mo ago

The example uses the syntax FROM person AS p, gender AS j WHERE p.gender_id = j.gender_id Isn't it preferable to be explicit? Does some of the inefficiency come from lack of explicitness? FROM person as p INNER JOIN gender as j ON p.gender_id = j.gender_id

pgaddict 11mo ago
AFAIK these two joins are exactly the same once you get past the parsing. It's just a different way to write an inner join. It's translated into the same AST and so there's no difference in planning/execution.
- listenallyall 11mo ago
  Perhaps in this very basic case they are exactly the same but is that still true if you add secondary WHERE conditions that apply to just one table, or if one "table" is actually a view with a complex query definition, or many other ways in which a very simple "example" can quickly get complicated?
  - Sesse__ 11mo ago
    In general, you split up the WHERE condition at every top-level AND. Then you do either pullup or pushdown or both (it depends a bit on how your planner looks on the inside). In the end, you end up with the two cases being exactly the same before you start planning. For outer joins (left/right/full), it's different, and there you absolutely need the explicit join syntax (for correctness). And semijoins are not expressed the same way at all, partially for weird historical reasons.
spprashant 11mo ago
I personally prefer being explicit in this case. Seeing the word INNER triggers the right way to visualize the query in my head.