Index Maintenance and Bloat

Analogy categoryAnalogies

Why Indexes Bloat

PostgreSQL's MVCC model means an UPDATE never modifies a row in place; it writes a new heap tuple and, unless the update qualifies for HOT (Heap-Only Tuple) optimization, inserts new entries into every index on that table while the old index entries become dead once no active transaction can see the old row version. VACUUM later reclaims that dead space, but on tables with high update or delete churn and infrequent or delayed autovacuum runs, dead index entries accumulate faster than they are cleaned, leaving indexes physically larger than the live data they describe and forcing more page reads per scan than a compact index would need.

🏏

Cricket analogy: It is like a scoreboard operator crossing out a wrong score and writing the correction next to it rather than erasing, leaving crossed-out entries cluttering the board until someone tidies it up at the innings break, just as dead index entries pile up until VACUUM runs.

Detecting and Measuring Bloat

There is no single built-in 'bloat percentage' function, but pgstattuple (a contrib extension) gives an accurate, direct measurement by scanning the actual index and reporting free space and dead tuple percentage, at the cost of scanning the whole index under a lock that can briefly compete with other readers. Lighter-weight approaches include comparing an index's actual disk size from pg_relation_size() against an estimate of its 'ideal' size for the current row count, or simply watching pg_stat_user_tables for n_dead_tup trending upward alongside last_autovacuum timestamps that are falling behind, which signals autovacuum isn't keeping pace with the table's write rate.

🏏

Cricket analogy: It is like a groundskeeper measuring pitch wear directly with a pace gun and moisture meter (pgstattuple's precise but intrusive scan) versus just eyeballing how many overs have been bowled since the last relaying (the lighter proxy metrics).

sql

-- Measure actual index bloat directly (requires the pgstattuple extension)
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('idx_orders_customer_id');

-- Cheap proxy: dead tuples and last autovacuum timing
SELECT relname, n_dead_tup, n_live_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- Rebuild a bloated index without blocking writers
REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

Plain REINDEX takes an ACCESS EXCLUSIVE lock on the table for the duration of the rebuild, blocking all reads and writes. On production systems, always use REINDEX INDEX CONCURRENTLY (or REINDEX TABLE CONCURRENTLY) instead; it takes longer and briefly needs roughly double the disk space for the index being rebuilt, but it never blocks concurrent queries.

Autovacuum Tuning as Prevention

The most durable fix for chronic index bloat is not a recurring REINDEX schedule but tuning autovacuum to actually keep pace with the table's write volume, since a well-vacuumed table sheds dead tuples continuously instead of accumulating them until a manual rebuild is needed. For high-churn tables, lowering autovacuum_vacuum_scale_factor (and its cost-based throttling parameters like autovacuum_vacuum_cost_limit) as a per-table storage parameter via ALTER TABLE ... SET lets autovacuum trigger more frequently and run with less throttling specifically on the tables that need it, without changing the global default that governs every other table in the cluster.

🏏

Cricket analogy: It is like a groundskeeper switching a heavily used practice pitch to nightly light rolling instead of waiting for the seasonal full relay, preventing wear from ever accumulating to a crisis point.

MVCC means updates create new row and index versions; old entries become dead space until VACUUM cleans them.
HOT updates avoid touching indexes at all when the update doesn't change indexed columns and the page has free space.
pgstattuple's pgstatindex() gives a precise, direct bloat measurement at the cost of scanning the index under load.
n_dead_tup and last_autovacuum in pg_stat_user_tables are lightweight proxies for whether vacuum is keeping pace.
REINDEX INDEX CONCURRENTLY rebuilds a bloated index without blocking reads/writes, unlike plain REINDEX.
Per-table autovacuum tuning (lower scale_factor, adjusted cost limits) prevents bloat proactively on high-churn tables.
Preventing bloat via well-tuned autovacuum is more sustainable than relying on periodic manual REINDEX rebuilds.