Flexible Indexing With Postgres
summary
Covers which Postgres index to pick and why: expression indexes on lower(name) let function-based WHERE clauses use an index; partial indexes shrink index size and write overhead by indexing only hot rows; bitmap index scans merge multiple indexes in-memory for AND/OR queries; BRIN shines for chronological or warehouse data — on a 100M-row example the B‑tree index is roughly two‑thirds the table size while BRIN stays tiny; GIN handles jsonb/full‑text with a pending‑update area for batched writes.
AI-written summary · about
chapters (15)
- 0:00Start
- 4:54Postgres Does Not Have Index Organized Tables
- 5:44The Postgres Optimizer
- 10:43Partial Indexes
- 20:41Generalized Inverted Index
- 23:52Hash Indexes
- 24:48Create Your Own Index Methods of Postgres
- 28:24Examples
- 36:31Quad Point Ops
- 40:18How Long Does It Take To Build the Index from Scratch
- 40:33What Is the Storage Size
- 40:56Insert Update Overhead
- 41:32Access Speeds
- 42:03Operator Lookup Flexibility
- 43:45Thank You
related talks
49mFlexible Indexing with Postgres
46mA Deep Dive Into PostgreSQL Indexing
53mAdvanced Indexing Techniques in PostgreSQL
29mPostgres Index types and where to find them
52mA Deep Dive into PostgreSQL Indexing
45mA Deep Dive into PostgreSQL Indexing
37mRules of Indexing in PostgreSQL
44mBRIN improvements and new opclasses