C06 — Index Health

Indexes that are costing more than they're worth: unused indexes (write overhead with no read benefit), duplicate indexes (redundant write overhead), invalid indexes left behind by a failed CREATE INDEX CONCURRENTLY, and bloat that's grown an index's on-disk size well past what its actual data needs.

10 checks, PGHF06-001 through PGHF06-010. Every check here is PGHF-namespace and built-in — see Metadata Columns Explained for what each field below actually means, and Resetting to Defaults for why these definitions can't be hand-edited in place.

PGHF06-001 — Unused indexes

An index that has never been scanned provides zero query benefit while still costing storage and slowing down every INSERT/UPDATE/DELETE that has to maintain it — pure overhead with no offsetting upside.

How to fix

1. Confirm stats are actually representative first — check PGHF06-009 for a recent pg_stat_reset(), and make sure a full business cycle (including any monthly/quarterly reports) has run since.
2. Drop it, CONCURRENTLY to avoid locking out writes:

DROP INDEX CONCURRENTLY schema.the_unused_index;

1. Keep the exact index definition somewhere before dropping (\d the_table or pg_get_indexdef()) in case it needs to come back — an unused index today can become necessary after a query pattern change.

PGHF06-002 — Duplicate indexes

Two indexes with identical columns, expressions, and predicates provide no additional query optimization over one — the second is pure duplicated disk space and write-time maintenance cost.

How to fix

Drop whichever one is newer/less-referenced (check for FK constraints or unique constraints depending on a specific index name before picking):

DROP INDEX CONCURRENTLY schema.the_duplicate_index;

PGHF06-003 — Invalid indexes

An invalid index (typically left behind by a failed CREATE INDEX CONCURRENTLY) is invisible to the query planner but still consumes space and write overhead — it needs to be rebuilt or dropped, not left in limbo.

How to fix

Either rebuild it in place:

REINDEX INDEX CONCURRENTLY schema.the_invalid_index;

or drop it and re-create cleanly if the original CREATE INDEX CONCURRENTLY command is known:

DROP INDEX CONCURRENTLY schema.the_invalid_index;
CREATE INDEX CONCURRENTLY the_invalid_index ON schema.the_table (...);

Investigate why the original build failed (often a lock timeout or a concurrent constraint violation) before assuming a plain retry will succeed.

PGHF06-004 — Index size vs row count

An index consuming far more bytes per row than its data type would suggest is a bloat signal, just like a bloated table — the top-20-by-size list surfaces exactly the candidates worth checking with a REINDEX.

How to fix

For a candidate from the largest_indexes/top-20 list that looks disproportionately large:

REINDEX INDEX CONCURRENTLY schema.the_bloated_index;

If it recurs quickly after a rebuild, the underlying cause is usually a high-churn column (frequent updates to an indexed value) — that's a write-pattern issue REINDEX alone won't permanently fix.

PGHF06-005 — FK columns without index

A foreign key column with no supporting index forces a sequential scan every time the referencing side is checked on a parent-row delete or update — often invisible until the parent table grows large enough for cascading operations to become painfully slow.

How to fix

CREATE INDEX CONCURRENTLY ON schema.child_table (fk_column);

CONCURRENTLY avoids taking a lock that would block writes to the table while the index builds.

PGHF06-006 — Prefix-redundant indexes

When one index's columns are a strict prefix of another's, the shorter index is almost always redundant — the longer one can serve the same queries, making the shorter one wasted space and maintenance cost.

How to fix

Drop the shorter (prefix) index, provided nothing depends on its specific name (a unique constraint, a FK target):

DROP INDEX CONCURRENTLY schema.the_shorter_index;

Exception: if the shorter index is genuinely used far more often and index-only scans matter, a narrower index can still be cheaper to scan — check actual usage (PGHF06-001) before assuming the longer one always wins.

PGHF06-007 — Low-cardinality indexed columns

A B-tree index on a column with only a handful of distinct values (a boolean, a status flag) rarely narrows a scan enough to be worth its maintenance cost — the planner usually ignores it in favor of a sequential scan anyway.

How to fix

Check real-world usage first (PGHF06-001) — if it's genuinely unused, drop it. If it is used, it's likely serving a query that filters on the low-cardinality column plus something more selective; consider a partial index instead:

-- instead of indexing the whole low-cardinality column:
CREATE INDEX CONCURRENTLY ON schema.orders (customer_id) WHERE status = 'pending';

PGHF06-008 — BRIN index correlation

BRIN indexes only work well when the indexed column is physically well-ordered on disk — on a poorly-correlated column they degrade to scanning most of the table anyway, and a B-tree would have served the query better.

How to fix

Either restore the physical ordering the BRIN index depends on:

CLUSTER schema.the_table USING some_btree_index;  -- reorders the table on disk; takes an ACCESS EXCLUSIVE lock

or, if the column genuinely isn't well-ordered and can't be, replace the BRIN index with a B-tree one instead:

DROP INDEX CONCURRENTLY schema.the_brin_index;
CREATE INDEX CONCURRENTLY ON schema.the_table (the_column);

PGHF06-009 — Statistics reset date

Unused-index findings (PGHF06-001) are only trustworthy if usage stats have accumulated over a representative workload period — a recent stats reset means "unused" might just mean "not exercised yet since the reset."

How to fix

No fix needed by itself — this is context for interpreting PGHF06-001, not a problem on its own. If the reset was recent, wait for a full representative period (including periodic/monthly jobs) to pass before trusting an "unused index" finding. If stats keep getting reset unexpectedly, check for a monitoring tool or script calling pg_stat_reset() on a schedule.

PGHF06-010 — Tables without primary key

A table without a primary key can't participate in logical replication (or Spock/pgEdge multi-master replication) without REPLICA IDENTITY FULL, which is dramatically more expensive per-row — this quietly blocks replication or makes it far slower than it needs to be.

How to fix

ALTER TABLE schema.the_table ADD PRIMARY KEY (id);

If the table lacks an obvious natural or surrogate key, add one first:

ALTER TABLE schema.the_table ADD COLUMN id bigint GENERATED ALWAYS AS IDENTITY;
ALTER TABLE schema.the_table ADD PRIMARY KEY (id);

Adding a primary key builds a unique index under the hood — on a large table, do this during a maintenance window or expect it to hold a lock for the duration.

Continue to C07 — TOAST Table & Corruption Detection.