Advanced Indexing Techniques in PostgreSQL
summary
Covers concrete index types (B‑Tree/B+ tree, hash = O(1), BRIN, GIN, GiST) and when each actually speeds queries; shows BRIN suits append‑only time‑series, GIN suits JSONB/arrays, GiST suits geospatial and vector data (PGVector). Builds a custom composite type plus operator/class so B‑Tree can order that type. Benchmarks: a SELECT runs ~9–10s, a rewritten query ~5s, with an index ~500ms; a customer drops ~37 JSONB B‑Tree indexes and replaced 37 B-tree indexes with one GIN to fix terabyte‑scale slowness. Emphasizes index maintenance cost, vacuum and up‑to‑date stats.
AI-written summary · about
chapters (8)
- 0:00Advanced indexing intro
- 3:55Why indexes boost performance
- 11:45PostgreSQL index types overview
- 26:35Indexing demo without indexes
- 32:14B-tree internals
- 43:10Custom types and indexing
- 46:42Index health monitoring
- 47:04Q&A
related talks
46mA Deep Dive Into PostgreSQL Indexing
49mFlexible Indexing with Postgres
52mA Deep Dive into PostgreSQL Indexing
29mPostgres Index types and where to find them
45mA Deep Dive into PostgreSQL Indexing
01h 00mFlexible Indexing With Postgres
47mThe Whole Truth About Indexes in PostgreSQL
48mB-tree Indexes in Databases Illustrated by IndexedDB and PostgreSQL