PostgreSQL Index Strategy Guide

on YouTube ↗52m

" summary

Shows concrete measurements from a 26 GB test table with 12 GB shared_buffers and explains when indexes reduce IO versus when planner prefers a sequential scan — e.g. a 1% selective column yields big wins, 5% often does not. Demonstrates partial indexes and function-based MD5 index that make queries index-only and cut IO, and shows write cost and bloat: adding many includes or 18 indexes makes index size approach the table and cripples writes. Gives commands, pg_stat views, and test scripts to reproduce.

AI-written summary · about

" chapters (15)

1. 0:00PostgreSQL index strategy
2. 1:06Indexing goals and trade-offs
3. 5:46What indexes are and costs
4. 7:16PostgreSQL data pages
5. 10:47Statistics and query planning
6. 15:57Data distribution and index efficiency
7. 20:16Partial indexes benefits
8. 23:49Index-only access and covering indexes
9. 27:43Index size impact on performance
10. 29:06Function-based indexes cost
11. 37:32Write costs and index overhead
12. 39:42Table bloat in PostgreSQL
13. 43:32Testing index strategies
14. 44:14Summary
15. 45:22Q&A

" related talks

• 45mA Deep Dive into PostgreSQL IndexingIbrar Ahmed · PGConf NYC 2021
• 52mA Deep Dive into PostgreSQL IndexingIbrar Ahmed · PGConf NYC 2022
• 46mA Deep Dive Into PostgreSQL IndexingIbrar Ahmad · Percona Live 2022
• 50mImproving Postgres' EfficiencyAndres Freund · PostgresOpen Silicon Valley 2017
• 26mBeyond Joins and IndexesBruce Momjian · POSETTE: An Event for Postgres 2024
• 29mExplaining the Postgres Query OptimizerBruce Momjian · Citus Con: An Event for Postgres 2022
• 29mPostgres Index types and where to find themLouise Grandjonc · DjangoCon US 2019
• 40mExplaining the Postgres Query OptimizerBruce Momjian · HighLoad++ 2012