An index needs a measured beneficiary and an explicit lifecycle budget.
Database · SQL
PostgreSQL Index Admission Review
An index earns admission only after its recurring write cost is counted.
SELECT i.indexrelname, i.idx_scan,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS size,
t.n_tup_upd, t.n_tup_hot_upd,
round(100.0*t.n_tup_hot_upd/nullif(t.n_tup_upd,0),1) AS hot_pct
FROM pg_stat_user_indexes i
JOIN pg_stat_user_tables t ON t.relid = i.relid
WHERE i.relname = 'orders'
ORDER BY pg_relation_size(i.indexrelid) DESC;
-- Pair with EXPLAIN (ANALYZE, BUFFERS) for the beneficiary query.
-- Note statistics resets and replica-only usage.Invariant: Every index has a measured beneficiary, observed write cost, owner, and removal condition.
Use when: A proposed index must prove read value and expose recurring write cost.
Why this boundary matters
A query speedup can add WAL, cache pressure, vacuum work, and replication lag to every write. Admission makes that exchange explicit.
Failure policy
| Boundary | Action |
|---|---|
| Critical query improves | Admit with size and write-cost evidence |
| Existing prefix overlaps | Prove the distinct plan |
| Indexed column changes frequently | Estimate lost HOT updates |
| Usage disappears | Schedule reversible removal |
| Concurrent build fails | Remove or rebuild the invalid index |
Trade-offs
Indexes exchange reads for persistent write, storage, cache, vacuum, and replication work. Covering indexes reduce heap access but widen the structure.
Decision rule: Create an index only when a named workload benefits enough to pay its measured lifecycle cost.