Peter barasa
Blogs, products & Use cases

Built for heavyweight engineering posts. Super opinionated.

Incident write-ups. System design. Performance notes. Security checklists. A little ranting.

Featured Latest View a post template

Latest

Cards that look like a publication, not a list.

Page 1 of 8
Deep Dive Updated: Feb 13, 2026 • 18 min read • Difficulty: Intermediate

Postgres indexing in the real world: when “add an index” becomes a lie

A technical post layout designed for serious content: TOC, callouts, copyable code blocks, side rail, and structure that can handle long-form engineering writing without turning into a wall of text.

PB
Peter Barasa
Backend • DevOps • Cloud
databases performance systems postgres
On this page
1) Why indexes fail in production 2) Recognize the query pattern 3) Read EXPLAIN like a human 4) Partial indexes 5) Bloat & maintenance 6) Shipping checklist

1) Why indexes fail in production

Most “add an index” advice stops at syntax. Production doesn’t. Production adds: shifting data distribution, hot partitions, cache behavior, write amplification, and the quiet villain: bad assumptions.

Rule of thumb
If your index makes reads fast but writes expensive, you must prove the trade-off with metrics.

2) Recognize the query pattern

Indexing is pattern-matching. Before you build, name the query shape:

  • Lookup (point queries)
  • Range (time-series scans)
  • Order + limit (feed queries)
  • Join-heavy (OLTP graph edges)

3) Read EXPLAIN like a human

Don’t worship the plan. Interpret it. Your job is to answer: “Where is time spent and why?”

SQL
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email
FROM users
WHERE tenant_id = $1
  AND status = 'active'
ORDER BY created_at DESC
LIMIT 20;
The best plan is the one that stays fast when the dataset doubles and nobody warns you.

4) Partial indexes

If your query filters on a small subset, partial indexes can be a cheat code.

Example

Index only active users instead of indexing everything.

5) Bloat & maintenance

Indexes are not “set and forget”. They age. They bloat. They drift. Maintenance is part of design, not an afterthought.

6) Shipping checklist

  • Compare query latency p50/p95 before vs after
  • Monitor write amplification (TPS impact)
  • Track index size, bloat signals, vacuum cadence
  • Have a rollback plan (drop index is not always instant)
Related
Keep the reader in your world.
Back to feed
Query latency: the 5 metrics that matter
10 min • databases
Designing schemas for real workloads
15 min • systems
Newsletter
One email when something worth reading ships.
N
Unsubscribe anytime.