PostgreSQL Indexing Field Guide: The 5 Patterns That Fix 90% of Slow Queries
Covering indexes, partial indexes, BRIN for time-series, and why EXPLAIN ANALYZE should be your homepage. Real query plans included.
-- covering index kills the sort + heap fetch CREATE INDEX CONCURRENTLY events_tenant_time_cov ON events (tenant_id, created_at DESC) INCLUDE (type, payload_id) WHERE deleted_at IS NULL;
Start with the query plan
Slow query? Run EXPLAIN (ANALYZE, BUFFERS) before touching code. In my audits, 70% of fixes are a missing or wrong index, 20% are N+1s, 10% need a rewrite.
Look for Seq Scan on large tables, high Buffers counts, and Sort nodes spilling to disk. Those three tell you exactly which index family to reach for.
The five I use weekly
1. Composite B-tree with correct column order (equality first, range last). 2. Covering INCLUDE indexes to make index-only scans possible. 3. Partial indexes for soft-deletes and active-only queries. 4. BRIN for append-only time-series — 100x smaller than B-tree. 5. GIN trigram for fuzzy search before reaching for Elasticsearch.
verifiedKey takeaways
- arrow_rightEXPLAIN ANALYZE first, index second.
- arrow_rightPartial indexes shrink size and speed writes.
- arrow_rightBRIN is magic for event/log tables.