How PostgreSQL Aggregates Work and How to Create and Use Custom Aggregates

Explore how PostgreSQL aggregates function internally and learn to create and utilize custom aggregates effectively in this conference talk from PGConf NYC 2021. Gain insights into transition, final, and combine functions, parallel aggregation, and aggregate de-duplication and optimization through visual explanations. Discover the power of custom aggregates and their implementation in various programming languages. Understand the design principles for efficient custom aggregates, including the two-step design pattern used in TimescaleDB hyperfunctions. Learn how to leverage these concepts to optimize your PostgreSQL applications and enhance database performance.

Introduction
Aggregates vs Functions
How aggregates work
State transition function
Complex aggregates
Parallelization
Create Aggregate Statement
SQL Aggregate Example
Two Step Aggregate Design
Two Step Aggregate Functions
Deduplication
Aggregate vs Accessor
Rollup
Questions