PGConf NYC 2021 - How PostgreSQL aggregates work and how to create and use custom... by David Kohn

How PostgreSQL aggregates work (through pictures) and how to create and use custom aggregates effectively by David Kohn In order to truly get the most out of a system like PostgreSQL, it’s helpful to understand how some of the work happens “under-the-hood”. Aggregates (functions applied over groups of rows like sum() or avg()) are one of the most commonly used features of PostgreSQL, but many people don’t understand how they work internally and therefore don’t always use them efficiently. Additionally, many people don’t even know that custom aggregates exist and how powerful they can be, (especially when combined with a good understanding of how all aggregates work!). This talk will provide a brief overview of how aggregates work in PostgreSQL through pictures! The overview will cover the following items: Transition, final, and combine functions Parallel aggregation Aggregate de-duplication and optimization After the overview of aggregates in general, we’ll cover custom aggregates, including the command, how to create them (in a couple languages) and how they fit into the framework we discussed before. Finally, we will understand why and how you can design efficient custom aggregates for use in your applications, with examples of how to design custom aggregates to fill specific needs and work synergistically with the optimizations PostgreSQL provides. We’ll also provide an example of how we’ve used custom aggregates to create TimescaleDB hyperfunctions and the two-step design pattern we’ve used.