Postgres at Scale: The Index Bloat Problem Nobody Warns You About

Dead tuples from MVCC accumulate in indexes differently than in tables. We hit a production stall from index bloat on a 400 GB table — queries that ran 200 ms went to 45 s before anyone said "vacuum" out loud.

Answer first

Monitor index bloat separately from table bloat. On Postgres 14/15 at scale, pg_stat_user_indexes + pgstattuple or check_btree extensions tell the story. Fix: REINDEX CONCURRENTLY on the bloated index (or pg_repack if downtime impossible), then fix autovacuum so it doesn't recur — see vacuum tuning.

Incident timeline

• T0: API p99 latency alert — read path on events table by device_id, created_at
• T+20m: Confirmed not app deploy — DB CPU 40%, disk read 800 MB/s sustained
• T+45m: EXPLAIN ANALYZE — Index Scan still chosen but buffer reads exploded
• T+90m: pg_relation_size('events_device_id_created_at_idx') → 180 GB; table heap 210 GB — index nearly size of table
• T+4h: REINDEX INDEX CONCURRENTLY started; completed in 6 h maintenance window extension
• T+next day: p99 back to 220 ms

Customer impact: elevated latency, no data loss. SEV2. Postmortem tagged postgres-bloat.

Why indexes bloat worse than heaps

Updates to indexed columns create new index tuples; dead entries linger until index vacuum reclaims pages. Heavy UPDATE rate on status column we indexed "temporarily for one report" — report became permanent, updates never stopped.

Table autovacuum ran — heap dead tuple ratio healthy 2%. Index bloat invisible to naive monitoring.

Postgres 13+ improved index vacuum (deduplicate B-tree) — we were 14.9 — still hit wall at 400 GB scale.

Diagnosis queries

-- Rough size compare
SELECT relname,
       pg_size_pretty(pg_relation_size(oid)) AS rel,
       pg_index.indrelid::regclass AS table
FROM pg_class
JOIN pg_index ON pg_index.indexrelid = pg_class.oid
WHERE relname = 'events_device_id_created_at_idx';

-- pgstattuple extension (install on primary, run off-peak)
SELECT * FROM pgstatindex('events_device_id_created_at_idx');
-- leaf_fragmentation high → bloat

amcheck (bt_index_check) — corruption check, not bloat — run anyway if suspicious.

For fleet analytics without extension:

SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'events';

Low idx_scan + huge size → candidate to drop, not reindex — we had opposite: high scan + bloat.

Fix options

Method	Locking	Speed	Notes
REINDEX INDEX CONCURRENTLY	non-blocking reads/writes	slow on 180 GB	our choice PG14+
REINDEX (non-concurrent)	exclusive	faster	downtime
pg_repack	minimal	medium	needs extension install
Drop + recreate index	exclusive on create	varies	OK if definitional

REINDEX CONCURRENTLY failed once — duplicate key during build — transient; retry succeeded. Document retry in runbook.

Prevention

1. Don't index high-churn columns without need — our status index should have been partial: WHERE status = 'open'
2. autovacuum_scale_factor too lazy at large table — per-table settings:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

1. Monitor index bloat — nightly job pgstattuple sample on top 20 indexes by size
2. Fillfactor — ALTER INDEX ... SET (fillfactor = 90) on append-mostly indexes only; wrong on random updates

Cross-link cloud egress — REINDEX read 1.2 TB from EBS in 6 h — showed up in bill next month; factor into maintenance planning.

Interaction with incidents

During stall, on-call primary debated VACUUM FULL — don't on 400 GB prod without exec sign-off. VACUUM (INDEX) helps but didn't solve 180 GB index alone.

Incident IC runbook rollback ruled out — no deploy correlation.

Postgres version notes

• PG14: REINDEX CONCURRENTLY for indexes — game changer for us
• PG16: btree vacuum improvements — evaluating upgrade partly for this
• Aurora Postgres: check REINDEX CONCURRENTLY support and I/O credit impact — different ops story

What I'd do next

Add index_bloat_pct custom metric to Datadog via cron — alert >40% on indexes >10 GB.

Schema review gate: new indexes on columns with UPDATE rate > N/day require staff sign-off.

Index bloat is silent until it isn't — at 400 GB, it's a capacity incident wearing a query planner costume.

Query plan before and after

Before reindex — Index Scan still chosen but:

Buffers: shared hit=890234 read=412033
Execution Time: 45231 ms

After — same plan node, different buffers:

Buffers: shared hit=1204 read=89
Execution Time: 218 ms

Planner wasn't wrong — index pages were wrong.

Partial index we should have used

CREATE INDEX CONCURRENTLY events_open_device_created_idx
ON events (device_id, created_at)
WHERE status = 'open';

Update rate on closed rows stopped polluting index — lesson for schema review.

pg_repack note

We evaluated pg_repack for online rebuild — extension install on RDS required parameter group change + maintenance window for first deploy. Chose REINDEX CONCURRENTLY first; pg_repack reserved if heap also bloated beyond vacuum reclaim.

What I'd do next