Index maintenance · PostgreSQL

Indexes accumulate. Someone adds one for a report that was retired two years ago; someone else adds a composite index that makes an older one redundant; a migration creates one that was never used. Each carries the write cost measured in lesson X-05, and nothing ever removes them.

Finding unused indexes

Read-only / Safethe query, and the context it needs— Captured on 18.6. Note the second result: without it the first is uninterpretable.

$ psql -U postgres -c "SELECT s.relname AS table_name, s.indexrelname AS index_name, s.idx_scan, pg_size_pretty(pg_relation_size(s.indexrelid)) AS size, i.indisunique, i.indisprimary FROM pg_stat_user_indexes s JOIN pg_index i ON i.indexrelid=s.indexrelid ORDER BY s.idx_scan, pg_relation_size(s.indexrelid) DESC" -c "SELECT stats_reset FROM pg_stat_database WHERE datname=current_database()"

 table_name |  index_name  | idx_scan |  size   | indisunique | indisprimary
------------+--------------+----------+---------+-------------+--------------
idx6       | idx6_c_idx   |        0 | 26 MB   | f           | f
idx6       | idx6_e_idx   |        0 | 10 MB   | f           | f
idx6       | idx6_a_b_idx |        0 | 5184 kB | f           | f
idx6       | idx6_a_idx   |        0 | 4824 kB | f           | f
...

        stats_reset
-------------------------------
2026-08-27 19:38:36.13558+00

An idx_scan of zero is meaningless without stats_reset

In the capture above the statistics were reset minutes before the query ran. Every zero is an artefact of that, and dropping indexes on the strength of it would be vandalism.

idx_scan counts since the counter last started, and it is reset by:

pg_stat_reset() or pg_stat_reset_single_table_counters();
a crash, which discards the statistics file;
pg_upgrade, since statistics do not carry across a major version upgrade.

Always report the two together. A zero is evidence only when stats_reset predates a full business cycle — which must include month-end, quarter-end and the annual report that runs once and is the only thing that has ever used that index.

Two further cautions before dropping anything:

Check the replicas. idx_scan is per node. An index used only by read-only reporting queries on a standby shows zero on the primary.

Never drop an index backing a constraint. indisunique or indisprimary being true means the index is enforcing something. Dropping it is a schema change, not a maintenance action.

Finding redundant indexes

Exact duplicates first:

SELECT indrelid::regclass AS table_name,
       array_agg(indexrelid::regclass) AS duplicate_indexes,
       pg_size_pretty(sum(pg_relation_size(indexrelid))) AS total_size
  FROM pg_index
 GROUP BY indrelid, indkey, indclass, indexprs, indpred
HAVING count(*) > 1;

That query misses the commonest kind of redundancy

It returned no rows on the test schema, which had both idx6_a_idx on (a) and idx6_a_b_idx on (a, b).

Those definitions genuinely differ, so grouping does not find them — but a single-column index whose column is the leading column of a composite index is nearly always removable, because any seek the narrow index can serve, the composite one can serve too.

-- indexes whose columns are a prefix of another index on the same table
SELECT a.indrelid::regclass                AS table_name,
       a.indexrelid::regclass              AS possibly_redundant,
       b.indexrelid::regclass              AS covered_by,
       pg_size_pretty(pg_relation_size(a.indexrelid)) AS reclaimable
  FROM pg_index a
  JOIN pg_index b
    ON a.indrelid = b.indrelid
   AND a.indexrelid <> b.indexrelid
   AND b.indkey::text LIKE a.indkey::text || '%'
 WHERE NOT a.indisunique
   AND NOT a.indisprimary
   AND a.indpred IS NULL
   AND b.indpred IS NULL;

Two reasons to keep such an index anyway, both worth checking:

Size on a hot path. A scan through the narrow index reads fewer pages than through the wide one. On a very high-frequency query that can matter.

A partial index is not covered by a full one with the same leading columns, which is why the query above excludes indexes with predicates on either side.

Dropping safely

-- 1. save the definition, so there is a way back
SELECT indexdef FROM pg_indexes WHERE indexname = 'orders_old_idx';

-- 2. make it invisible to the planner without dropping it
BEGIN;
UPDATE pg_index SET indisvalid = false
 WHERE indexrelid = 'orders_old_idx'::regclass;
COMMIT;

Setting indisvalid = false is a catalogue write, and it has a real cost

This trick is widely circulated as a safe way to trial an index removal. It works — the planner ignores an invalid index — and it comes with consequences that are usually omitted.

It is a direct catalogue update. PostgreSQL does not offer a supported command for it, and modifying pg_index by hand is outside what the documentation covers.

The index is still maintained on every write. You keep the entire write cost measured in lesson X-05 and get none of the read benefit. This is a trial, not a saving.

It affects every session immediately, including on standbys once the change replicates.

Used for a bounded trial — a day, a week — it is a genuinely useful way to confirm nothing breaks before dropping. Left in place, it is the worst of both worlds. Set a reminder to finish the job:

DROP INDEX CONCURRENTLY orders_old_idx;

CONCURRENTLY avoids the ACCESS EXCLUSIVE lock that a plain DROP INDEX takes, and cannot run inside a transaction block.

Rebuilding a bloated index

Lesson VIII-06 established that a B-tree page is reclaimed only when completely empty, so scattered deletes leave sparse pages that vacuum cannot remove. Measure before acting:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('orders_pkey');
-- avg_leaf_density is the figure; a healthy B-tree sits near 90

Then:

REINDEX INDEX CONCURRENTLY orders_pkey;
REINDEX TABLE CONCURRENTLY orders;         -- every index on the table

Always check for invalid indexes afterwards

REINDEX CONCURRENTLY builds a replacement alongside the original and swaps it in. If it fails partway — a lock timeout, a cancelled session, a deadlock — it leaves the partial index behind, named with a _ccnew suffix.

That index is ignored by the planner and maintained on every write. It is pure cost, and it is invisible unless you look:

SELECT indexrelid::regclass AS index_name,
       indrelid::regclass   AS table_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
  FROM pg_index
 WHERE NOT indisvalid;

Run that after every concurrent build or rebuild, and put it in routine monitoring. An invalid index left from a failed build eighteen months ago is a common, entirely silent drag on write throughput — and it is the kind of finding that explains a write-latency regression nobody could account for.

Drop what it finds with DROP INDEX CONCURRENTLY and rebuild.

A maintenance routine

Monthly. Review unused indexes, with stats_reset shown alongside. Review redundant ones with the prefix query. Check for invalid indexes.

Quarterly. pgstatindex on the largest indexes; rebuild any whose avg_leaf_density has fallen well below 90.

On every schema change. Ask whether the new index makes an existing one redundant, and whether the column it covers is frequently updated — which, from lesson VII-02, costs the whole table its HOT updates.

After every pg_upgrade. Statistics reset, so the unused-index clock starts again. Note the date; the next review is a full business cycle later.

Why a REINDEX can make queries slower, and the correlation it destroys

Rebuilding an index produces a compact, well-ordered structure. It also changes the relationship between index order and heap order, and that relationship is an input to the planner’s cost model.

pg_stats.correlation for an indexed column measures how well the physical row order matches the value order. High correlation makes an index scan cheap, because consecutive index entries point at consecutive heap pages. Low correlation makes it expensive, because each entry may require a different page.

A REINDEX does not move heap rows, so it does not change correlation directly. But two adjacent operations do:

CLUSTER rewrites the heap in index order, setting correlation to 1.0 for that index — and destroying it for every other index on the table. Clustering on created_at makes range scans on it excellent and range scans on customer_id worse.

VACUUM FULL rewrites the heap in its existing physical order, which usually preserves correlation, but does not restore it if it was already poor.

Neither is maintained afterwards. Correlation degrades again as rows are updated and inserted, which is why CLUSTER is a one-off treatment rather than a setting.

The practical points: check correlation before assuming an index scan should be cheap, and be aware that a table clustered for one query is de-clustered for every other.

SELECT tablename, attname, correlation
  FROM pg_stats
 WHERE tablename = 'orders'
 ORDER BY abs(correlation) DESC;

What to take from this

Report idx_scan with stats_reset. A zero without it is not evidence.
Check standbys, and never drop an index backing a constraint.
Exact-duplicate detection misses the prefix case, which is the commonest redundancy.
indisvalid = false is a bounded trial, not a saving — the write cost remains.
Check for invalid _ccnew indexes after every concurrent build. They are pure cost and entirely silent.
CLUSTER improves one index’s correlation at the expense of every other, and is not maintained.

Cross-course references

Ansible for Production Sysadmins — Part XLVIII (Maintenance windows and rollback) covers running a concurrent rebuild across an estate, and Part XXXI (Serial execution) covers doing it one host at a time so a failure is bounded.
Observability for Production Sysadmins — Part LIX (Database observability) covers alerting on an invalid index, which is the state a cancelled concurrent build leaves behind and nothing else reports.