Runbook: Rebuild a Bloated Index Without Blocking

1 · Prerequisites

Confirm every item is in place before any state change.

• The index name, its size, and a measurement showing it is actually bloated rather than merely large
• Disk space for a second copy of the index, because a concurrent rebuild builds the new one alongside the old
• Knowledge of any long-running transactions, because REINDEX CONCURRENTLY does not block but does wait for them
• The pgstattuple extension available, or agreement to install it
• A window that is not strictly required but is helpful, since the rebuild competes for I/O
• Agreement that the index is still wanted at all, checked against idx_scan before any work is done

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

• · Confirm the index is used before rebuilding it. SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes WHERE indexrelname = :index; An index with idx_scan = 0 may be a candidate for removal rather than a rebuild — but check stats_reset first, because statistics reset when the cluster does.
• · Measure the bloat rather than assuming it. CREATE EXTENSION IF NOT EXISTS pgstattuple; SELECT version, tree_level, index_size, leaf_pages, avg_leaf_density, leaf_fragmentation FROM pgstatindex(:index); A healthy freshly built B-tree has an avg_leaf_density near 90 percent; a bloated one is much lower.
• · Check for invalid indexes on the table already. SELECT c.relname, i.indisvalid, i.indisready, pg_size_pretty(pg_relation_size(c.oid)) FROM pg_class c JOIN pg_index i ON i.indexrelid = c.oid WHERE i.indrelid = :table::regclass AND NOT i.indisvalid; A previous cancelled build may already be costing you writes.
• · Check for long-running transactions. SELECT pid, state, now() - xact_start AS xact_age, left(query,60) FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start; REINDEX CONCURRENTLY never blocks them, but it cannot finish until older transactions end.
• · Confirm free space for a second copy. The concurrent rebuild holds both indexes simultaneously. pg_size_pretty(pg_relation_size(:index)) against df -h.
• · Confirm the index is not a constraint's index in a way that matters. REINDEX INDEX CONCURRENTLY handles primary key and unique constraint indexes, but a DROP and recreate does not, so know which you are doing.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1. 1Decide whether a rebuild is the right instrument. Index bloat is the majority of visible bloat on many workloads and REINDEX CONCURRENTLY is cheap. Heap bloat is a different problem with a different answer, and pgstattuple on the table tells you which you have.
2. 2Run the rebuild concurrently. REINDEX INDEX CONCURRENTLY events_created_at_idx; This builds a new index alongside the old, swaps them, and drops the old — without ever taking ACCESS EXCLUSIVE for the build.
3. 3**Expect it to take longer than a plain REINDEX.** A concurrent rebuild makes two passes over the table and waits for transactions older than each pass. Measured on a comparable index with an active reader holding a transaction open for 25 seconds: 23 seconds to complete, without ever blocking that reader.
4. 4Watch progress from a second session. SELECT pid, phase, blocks_total, blocks_done, tuples_total, tuples_done, current_locker_pid FROM pg_stat_progress_create_index; A non-zero current_locker_pid names the transaction it is waiting for.
5. 5Do not cancel it unless you have to. A cancelled REINDEX CONCURRENTLY leaves an index behind, and depending on when it was cancelled that index may be maintained by every write while being usable by nothing.
6. 6Confirm the rebuild produced a valid index. SELECT c.relname, i.indisvalid, i.indisready, pg_size_pretty(pg_relation_size(c.oid)) FROM pg_class c JOIN pg_index i ON i.indexrelid = c.oid WHERE c.relname = :index;
7. 7Re-measure the density. SELECT avg_leaf_density, leaf_fragmentation, index_size FROM pgstatindex(:index); A rebuilt index should show density near 90 percent and fragmentation near zero.
8. 8Confirm the planner still uses it. EXPLAIN (COSTS OFF) <a query that should use this index>; A rebuilt index the planner ignores is a different problem from a bloated one, and only the plan distinguishes them.
9. 9**Run ANALYZE on the table.** A rebuild does not update statistics, and a plan chosen on stale statistics will not reflect the new index size.
10. 10If the build was cancelled, clean up before leaving. REINDEX INDEX CONCURRENTLY finishes an interrupted build in place; DROP INDEX CONCURRENTLY removes it. Use CONCURRENTLY on the drop as well — a plain DROP INDEX takes ACCESS EXCLUSIVE and forms a lock queue.
11. 11Consider whether this will recur. An index that bloats back within weeks under a steady write workload is telling you about the table's churn rate and its autovacuum settings, not about the index.
12. 12Record the before and after densities, the duration, and whether the query timings improved.

4 · Verification

Confirm the procedure actually fixed the problem.

• ✓SELECT indisvalid, indisready FROM pg_index WHERE indexrelid = :index::regclass; returns true for both.
• ✓avg_leaf_density from pgstatindex is near 90 percent and leaf_fragmentation near zero. Measured on a rebuilt index: 89.95 and 0, against 36.17 and 39.97 before.
• ✓Index size has fallen. Measured on the same index: 11 MB to 4408 kB.
• ✓The planner uses the index for a query that should use it, confirmed with EXPLAIN.
• ✓idx_scan for the index is climbing after the rebuild. Zero scans on a valid index means the rebuild was wasted work even though it succeeded.
• ✓No invalid index exists on the table: SELECT count(*) FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid WHERE i.indrelid = :table::regclass AND NOT i.indisvalid; returns zero.
• ✓Write latency on the table has not regressed. A rebuild should not change it, and measuring confirms nothing else changed at the same time.

5 · Rollback

If verification fails, undo the procedure in reverse order.

• ↶A completed REINDEX INDEX CONCURRENTLY leaves one valid index with the same name and definition. There is nothing to roll back and no window in which the index was absent.
• ↶If the rebuild was cancelled, check what it left: SELECT c.relname, i.indisvalid, i.indisready FROM pg_class c JOIN pg_index i ON i.indexrelid = c.oid WHERE NOT i.indisvalid;
• ↶An entry with indisvalid = false and indisready = true holds real pages and is maintained by every write while being usable by nothing. REINDEX INDEX CONCURRENTLY finishes it, or DROP INDEX CONCURRENTLY removes it.
• ↶An entry with indisvalid = false and indisready = false occupies no pages and costs nothing. Drop it when convenient.
• ↶If a plain DROP INDEX was issued and a lock queue formed, cancel it: SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE query LIKE 'DROP INDEX%';. The queue drains immediately.
• ↶If the index was dropped rather than rebuilt and queries have regressed, recreate it with CREATE INDEX CONCURRENTLY using the definition from pg_get_indexdef, which you should have recorded before starting.

6 · Escalation

When the runbook isn't enough, contact:

• · REINDEX CONCURRENTLY does not complete because a long-running transaction never ends: escalate to its owner. The rebuild is waiting, not stuck, and killing it wastes the work already done.
• · The index bloats back to the same state within weeks: escalate to whoever owns the table's maintenance. Repeating the rebuild quarterly is a treadmill; the autovacuum settings are the actual question.
• · The rebuild would need more free space than the volume has: escalate to the platform owner. A concurrent rebuild holds both copies, and running out of space partway leaves an invalid index behind.
• · The index is on a partitioned table and the rebuild must cover every partition: escalate to plan it. REINDEX INDEX CONCURRENTLY on a partitioned index is not supported in the same way, and the partitions must be handled individually.
• · An invalid index has been present for some time and nobody knows why: escalate rather than dropping it. If indisready is true it has been costing writes for that whole period, and that is worth recording before it disappears.
• · The index turns out to be unused and somebody proposes dropping it: escalate to the application owner with idx_scan and stats_reset. An index supporting a quarterly job reads as unused for most of the quarter.

References

1. PostgreSQL 18 documentation, REINDEX
2. PostgreSQL 18 documentation, pgstattuple
3. PostgreSQL 18 documentation, Building Indexes Concurrently
4. PostgreSQL 18 documentation, CREATE INDEX Progress Reporting