Index Maintenance and Bloat
Two indexes on the same table. One has never been scanned once. The other is real, measured, 33% fragmented. Find both, and fix only the one that matters.
An index that nobody queries through is not neutral — it still has to be updated on every insert, update, and delete against its table, paying real write cost for zero read benefit. And an index that IS queried can still degrade over time: as rows are updated and deleted, a B-tree accumulates dead space inside its pages, the same bloat process VACUUM handles for table heaps, except an index needs its own accounting to detect. Both problems are measurable directly rather than guessed at: pg_stat_user_indexes.idx_scan is a genuine, cumulative counter of how many times an index has actually been used, and pgstattuple's pgstatindex gives a genuine bloat percentage for any index, not an estimate from table-level statistics.\n\nFixing bloat safely matters as much as detecting it. A plain REINDEX takes an exclusive lock for its duration, blocking every read and write against the table — unacceptable on a live production table. REINDEX CONCURRENTLY avoids that lock by building a brand new index alongside the old one and swapping them once ready, at the cost of taking noticeably longer and being interruptible — and being interrupted has a real, specific consequence this lab reproduces directly rather than just describing.
Finding Indexes Nobody Uses
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'idx_maint';
indexrelname | idx_scan
------------------+----------
idx_maint_pkey | 0
idx_maint_unused | 0
idx_maint_used | 2
idx_scan is a real, cumulative counter — it only increments when a query actually uses that specific index. Two of these three indexes show 0: genuinely never used since the counter was last reset, paying full write-maintenance cost for zero read benefit.
Measuring Real Bloat, Not Guessing
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('idx_maint_unused');
index_size | leaf_pages | avg_leaf_density | leaf_fragmentation
------------+------------+------------------+---------------------
2342912 | 284 | 64.64 | 38.38
2.2 MB, with leaf_fragmentation at 38.38% — real, page-level bloat measured directly from the index's own internal structure, not inferred from table-level row-count changes.
Rebuilding Online, With No Table Lock
REINDEX INDEX CONCURRENTLY idx_maint_unused;
SELECT * FROM pgstatindex('idx_maint_unused');
index_size | leaf_pages | avg_leaf_density | leaf_fragmentation
------------+------------+------------------+---------------------
917504 | 110 | 89.41 | 0
896 kB, 0% fragmentation — a genuine rebuild, and CONCURRENTLY means it happened without ever taking the exclusive lock a plain REINDEX would have needed, at the cost of a longer runtime.
What a Cancelled REINDEX CONCURRENTLY Actually Leaves Behind
Cancelling a REINDEX INDEX CONCURRENTLY partway through — reproduced here with pg_cancel_backend from a second session — does not clean up after itself:
SELECT relname, indisvalid FROM pg_class JOIN pg_index ON pg_class.oid = pg_index.indexrelid
WHERE relname LIKE 'idx_maint2%';
relname | indisvalid
----------------------+------------
idx_maint2_val | t
idx_maint2_val_ccnew | f
The original index is untouched and still valid. A new, half-built index named with a _ccnew suffix is left behind, permanently marked invalid — it will never be considered by the planner, but it still occupies disk space and needs to be dropped explicitly:
DROP INDEX CONCURRENTLY idx_maint2_val_ccnew;
idx_scan
A column in pg_stat_user_indexes counting how many times PostgreSQL has used a specific index to answer a query, since statistics were last reset. A persistent 0 across a realistic observation window is strong direct evidence an index is not earning back its write-maintenance cost — far more reliable than guessing from the index's definition alone.
REINDEX CONCURRENTLY
Rebuilds an index without taking the exclusive lock a plain REINDEX requires, by building an entirely new index alongside the old one while normal reads and writes continue, then swapping them atomically once the new one is ready and valid. It takes longer than a plain REINDEX and cannot run inside a transaction block; if interrupted before completing, it leaves a permanently invalid, unused index behind that must be dropped manually — the original index is never harmed.
🔍 Find the Indexes Nobody Queries Through
Query pg_stat_user_indexes to see the real, cumulative scan count for every index on this table.
psql -U postgres -d beer_db -c "SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'idx_maint';"student@lab:~$ psql -U postgres -d beer_db -c "SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE relname = 'idx_maint';" SET indexrelname | idx_scan ------------------+---------- idx_maint_pkey | 0 idx_maint_unused | 0 idx_maint_used | 2 (3 rows)
📐 Measure Real Bloat With pgstatindex
Use pgstattuple's pgstatindex function to get a genuine, page-level bloat measurement for one of the unused indexes.
psql -U postgres -d beer_db -c "SELECT * FROM pgstatindex('idx_maint_unused');"student@lab:~$ psql -U postgres -d beer_db -c "SELECT * FROM pgstatindex('idx_maint_unused');" SET version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation ---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------- 4 | 1 | 2342912 | 3 | 1 | 284 | 0 | 0 | 64.64 | 38.38 (1 row)
♻️ Rebuild Online and Confirm the Real Improvement
Run REINDEX CONCURRENTLY and measure the same index again to confirm the rebuild actually helped.
psql -U postgres -d beer_db -c "REINDEX INDEX CONCURRENTLY idx_maint_unused;"psql -U postgres -d beer_db -c "SELECT * FROM pgstatindex('idx_maint_unused');"student@lab:~$ psql -U postgres -d beer_db -c "REINDEX INDEX CONCURRENTLY idx_maint_unused;" SET REINDEX student@lab:~$ psql -U postgres -d beer_db -c "SELECT * FROM pgstatindex('idx_maint_unused');" SET version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation ---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+-------------------- 4 | 1 | 917504 | 3 | 1 | 110 | 0 | 0 | 89.41 | 0 (1 row)
💥 Reproduce What a Cancelled Rebuild Actually Leaves Behind
Start a REINDEX CONCURRENTLY on a second table, cancel it mid-flight from another session, and see exactly what gets left behind — then clean it up.
psql -U postgres -d beer_db -c "CREATE TABLE idx_maint2 (id int PRIMARY KEY, val int);" -c "INSERT INTO idx_maint2 SELECT g, g FROM generate_series(1,300000) g;" -c "CREATE INDEX idx_maint2_val ON idx_maint2(val);" -c "ANALYZE idx_maint2;"(psql -U postgres -d beer_db -c "REINDEX INDEX CONCURRENTLY idx_maint2_val;" > /tmp/reindex_out.txt 2>&1 &) ; sleep 0.5; psql -U postgres -d beer_db -c "SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND query ILIKE 'REINDEX INDEX CONCURRENTLY idx_maint2_val%';"sleep 1; psql -U postgres -d beer_db -c "SELECT relname, indisvalid FROM pg_class JOIN pg_index ON pg_class.oid = pg_index.indexrelid WHERE relname LIKE 'idx_maint2%';"psql -U postgres -d beer_db -c "DROP INDEX CONCURRENTLY idx_maint2_val_ccnew;"student@lab:~$ psql -U postgres -d beer_db -c "CREATE TABLE idx_maint2 (id int PRIMARY KEY, val int);" -c "INSERT INTO idx_maint2 SELECT g, g FROM generate_series(1,300000) g;" -c "CREATE INDEX idx_maint2_val ON idx_maint2(val);" -c "ANALYZE idx_maint2;" SET CREATE TABLE INSERT 0 300000 CREATE INDEX ANALYZE student@lab:~$ (psql -U postgres -d beer_db -c "REINDEX INDEX CONCURRENTLY idx_maint2_val;" > /tmp/reindex_out.txt 2>&1 &) ; sleep 0.5; psql -U postgres -d beer_db -c "SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND query ILIKE 'REINDEX INDEX CONCURRENTLY idx_maint2_val%';" SET ERROR: canceling statement due to user request student@lab:~$ sleep 1; psql -U postgres -d beer_db -c "SELECT relname, indisvalid FROM pg_class JOIN pg_index ON pg_class.oid = pg_index.indexrelid WHERE relname LIKE 'idx_maint2%';" SET relname | indisvalid ----------------------+------------ idx_maint2_pkey | t idx_maint2_val | t idx_maint2_val_ccnew | f (3 rows) student@lab:~$ psql -U postgres -d beer_db -c "DROP INDEX CONCURRENTLY idx_maint2_val_ccnew;" SET DROP INDEX
Lab 3.2.6 complete. Index maintenance and bloat, proven rather than assumed:\n\n\n Unused indexes found : ✅ idx_scan = 0 on 2 of 3 indexes\n Real bloat measured : ✅ 2.2 MB, 38.38% fragmented, via pgstatindex\n Online rebuild confirmed : ✅ 896 kB, 0% fragmented, no table lock\n Cancelled rebuild reproduced : ✅ real invalid _ccnew index, cleaned up\n
Enable JavaScript to run the live terminal and track your progress.