Skip to content
← NOTES

PERFORMANCE · 5 MIN · 2026-07-15

Postgres Indexing: Field Notes

The index you need is usually the one matching your WHERE, not your imagination.

Checklist before adding an index

  1. Read `EXPLAIN ANALYZE` first.
  2. Match the index to the actual query pattern.
  3. Watch write amplification — every index taxes INSERT/UPDATE.

My default stack

  • B-tree for equality + range.
  • Partial index for soft-deleted tables (`WHERE deleted_at IS NULL`).
  • GIN only when JSONB search is proven slow.

Performance is a feature. Guessing is not a strategy.

I spent two hours debugging this site's CSS once. The bug was a missing semicolon. I am not proud.

← ALL NOTES