BRIN improvements and new opclasses
summary
BRIN indexes chunk the heap into ~1MB ranges and store compact summaries so a 3.5GB table yields a BRIN under 1MB versus a B-tree ~50–65MB; on 10M-row tests BRIN eliminates ~95% of blocks then bitmap-scans the rest, giving big wins over sequential scans but B-tree still beats BRIN for point lookups (milliseconds vs ~1s). Postgres 14 adds minmax_multi (multiple intervals per range) and a bloom opclass for random keys, plus operator-class parameters and a parallel CREATE INDEX patch; randomized concurrent stress tests expose long-standing bugs and guide fixes.
AI-written summary · about
chapters (9)
- 0:00Start
- 3:15BTREE - traditional tree-like index
- 6:08BRIN - block range index
- 17:08Problems (minmax)
- 21:05minmax-multi opclass
- 24:50bloom opclass parameters
- 28:20HOT updates (PG15)
- 30:33Future improvements (?)
- 36:05Stress testing
related talks
49mFlexible Indexing with Postgres
01h 00mFlexible Indexing With Postgres
46mA Deep Dive Into PostgreSQL Indexing
53mAdvanced Indexing Techniques in PostgreSQL
34mUnconventional ways to index UUIDs in PostgreSQL
37mRules of Indexing in PostgreSQL
45mA Deep Dive into PostgreSQL Indexing
29mPostgres Index types and where to find them