← Writing

Diagnosing and Fixing PostgreSQL Index Bloat in Production

PostgreSQL, Performance, Database

The Problem

After months of heavy write traffic, your PostgreSQL query times start creeping up. EXPLAIN ANALYZE shows the planner is still choosing your indexes, but execution time is climbing. Welcome to index bloat.

Why It Happens

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to maintain transaction isolation. When you UPDATE or DELETE a row, the old version isn't immediately removed — it's marked dead and remains until VACUUM processes it. Indexes point to the physical location of every row version, not just the current one.

This means:

  • 100 updates to a row create 100 index entries (one per version)
  • Dead index entries waste space and CPU cycles during index scans
  • The index file grows without bound unless VACUUM can clean it up
  • When VACUUM can't keep up with write traffic, index bloat accelerates

Detection

Check index bloat with the pgstattuple extension:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple_approx('public.users_email_idx');

Key metrics:

  • live_tuples: number of live index entries
  • dead_tuples: number of dead entries
  • dead_tuples / (live_tuples + dead_tuples): bloat ratio

A ratio above 20% is worth investigating. Above 50% means action is needed.

Solutions

1. REINDEX (Offline)

Full rebuild, takes an exclusive lock:

REINDEX INDEX CONCURRENTLY users_email_idx;

CONCURRENTLY doesn't lock writes, but takes 2–3× as long.

2. CREATE INDEX + DROP (Zero-Downtime)

Create a new index, swap application queries, drop the old one:

-- 1. Build new index concurrently (queries still use old index)
CREATE INDEX users_email_idx_new ON users (email);

-- 2. Swap in application config or run:
ALTER INDEX users_email_idx RENAME TO users_email_idx_old;
ALTER INDEX users_email_idx_new RENAME TO users_email_idx;

-- 3. Drop old index
DROP INDEX users_email_idx_old;

3. Preventive Maintenance

Tune autovacuum for index-heavy tables:

ALTER TABLE users SET (
  autovacuum_vacuum_scale_factor = 0.01,   -- vacuum at 1% changes
  autovacuum_analyze_scale_factor = 0.005  -- analyze at 0.5% changes
);

Monitor bloat with regular pgstattuple scans and alert at 30%.

← Back to all writing