How PostgreSQL aggregates work and how to create and use custom...
summary
Explains how PostgreSQL aggregates maintain state with transition, combine, and final functions, how parallel aggregation and serialization work, and why custom aggregates can be marked parallel safe. Shows building aggregates in SQL and in PGX the Rust-based framework used to generate and test code, with examples (approximate percentiles using UDD sketch), a two-step aggregate pattern with rollup/rolling accessors, and a toolkit managing 600+ custom types and functions to make these approaches practical and performant.
AI-written summary · about
chapters (14)
- 0:00Introduction
- 1:20Aggregates vs Functions
- 2:48How aggregates work
- 3:37State transition function
- 3:58Complex aggregates
- 5:45Parallelization
- 8:42Create Aggregate Statement
- 10:15SQL Aggregate Example
- 16:52Two Step Aggregate Design
- 17:52Two Step Aggregate Functions
- 20:32Deduplication
- 22:42Aggregate vs Accessor
- 25:08Rollup
- 29:00Questions
related talks
46mUse PostgreSQL to Step Up Your Game
49mSELECT 'amazing features' FROM "postgresql"
42mPostgreSQL Tips & Tricks For App Devs
45mSELECT 'amazing_features' FROM "postgresql"
07mPostgreSQL in ever more critical environments
52mBoosting Typical Query Patterns: PostgreSQL 18's Performance Enhancements
49mCan PostgreSQL Compete with Analytics Specialists?
34mPostgres 10, Performance, and You