Unconventional ways to index UUIDs in PostgreSQL
summary
Benchmarking shows no-index point lookups at ~179 ms, while a 30 MB B-tree gives ~0.02 ms and a 32 MB hash index is slightly faster for single-row UUID lookups; BRIN using default minmax is tiny (~3.5 MB) but slow on random UUIDs (~530 ms), switching to ULID (time-sorted) makes BRIN ~12 ms, and BRIN with bloom_ops cuts random-UUID queries to ~15 ms; recommendation: B-tree for uniqueness and ranges, hash for compact fast point lookups, BRIN variants to save space when correlation exists.
AI-written summary · about
chapters (8)
- 0:00Introduction
- 2:13What are UUIDs good for?
- 5:22Sortable UUIDs, specifically ULIDs
- 8:19Indexing UUIDs in PostgreSQL
- 11:08B-Tree is the king of all Postgres indexes
- 17:37Hash indexes, the ugly duckling of index types
- 21:12Using block range indexes (BRIN)
- 26:34BRIN Index and Bloom filters
