Postgres Index Cleanup: Find Unused and Duplicate Indexes

Postgres index cleanup is a write-performance and storage review, not a quick count of zero scans. An index can be unused, duplicated, bloated, required by a constraint, or only chosen by the planner for rare but important queries.

The useful output is an index cleanup migration plan with stats evidence, definition comparison, constraint check, query-plan review, and rollback notes. Keep the review concrete: Mark candidate indexes for review after checking stats age and constraint ownership, then make the next action visible to the team that owns the risk. That matters because the cleanup can still go wrong when dropping an index used by rare queries.

Key takeaways

Treat each cleanup candidate as an owned system with dependencies, not anonymous clutter.
Use a full workload cycle that includes reporting, maintenance, and customer-support query patterns before deciding that “quiet” means “unused.”
Prefer reversible changes first when dropping an index used by rare queries is still plausible.
Leave behind an index cleanup migration plan with stats evidence, definition comparison, constraint check, query-plan review, and rollback notes so the next review starts with context.
Measure the result as lower spend, lower risk, less operational drag, or clearer ownership.

Map Index Purpose

Start with one Postgres database or schema where index statistics, constraints, query plans, table size, and release windows can be reviewed together. The best cleanup scope is small enough that owners can answer quickly but wide enough to include the attachments that make removal risky.

Field	Why it matters
Owner	Cleanup needs a person or team that can accept the decision
Current purpose	A short reason to keep the item, written in present tense
Last meaningful use	read/write activity, size, query plans, job dependencies, and retention rules
Dependency evidence	database metrics, query logs, application references, and reporting schedules
Risk if wrong	The outage, data loss, access failure, or rollback gap the review must avoid
Next action	Keep, reduce, archive, disable, remove, or investigate

Do not make the inventory larger than the decision. A short list with owners and evidence beats a perfect spreadsheet that nobody is willing to act on.

Index Evidence to Collect

The useful question is not “how old is it?” It is “what would break, become harder to recover, or lose accountability if this disappeared?” For Postgres index cleanup, collect enough evidence to answer that without relying on naming conventions.

Check	What to look for	Cleanup signal
Usage statistics	pg_stat_user_indexes scans, last scan where available, tuples read, and stats reset time	The index has little or no observed use after a meaningful window
Definition overlap	pg_indexes definitions, indexed columns, predicates, uniqueness, and included columns	Another index covers the same access pattern
Constraint role	Primary keys, unique constraints, foreign key support, and application invariants	The index is not enforcing correctness
Query-plan risk	EXPLAIN output for slow, rare, and reporting queries	Dropping the index will not remove a critical plan option

Use several signals together. Activity can miss monthly jobs and incident-only paths. Ownership can be stale. Cost can distract from security or recovery risk. The strongest case combines runtime data, dependency checks, owner review, and a rollback plan.

If the evidence conflicts, label the item “investigate” with a named owner and review date. That is still progress because the next review starts with a narrower question.

Example Evidence Check

PostgreSQL documents pg_stat_all_indexes/pg_stat_user_indexes for index access stats and pg_indexes for index definitions; combine both before choosing candidates.

SELECT
  s.schemaname, s.relname AS table_name, s.indexrelname AS index_name,
  s.idx_scan, s.idx_tup_read, i.indexdef
FROM pg_stat_user_indexes s
JOIN pg_indexes i
  ON i.schemaname = s.schemaname
 AND i.indexname = s.indexrelname
WHERE s.idx_scan = 0
ORDER BY s.schemaname, s.relname, s.indexrelname;

Treat the output as a candidate list. Do not pipe these checks into delete commands; add owner review, dependency checks, and a rollback path first.

Drop Only After Plan Review

Use the least permanent move that proves the decision. In Postgres index cleanup, removal is only one possible outcome; reducing size, narrowing permission, shortening retention, archiving, or disabling a trigger may produce the same benefit with less risk.

Mark candidate indexes for review after checking stats age and constraint ownership.
Test representative queries before dropping an index that appears unused.
Drop one index at a time during a monitored window and watch write latency and slow queries.

Track the cleanup candidate with a simple priority score:

Score	Good sign	Bad sign
Impact	Meaningful spend, risk, toil, noise, or confusion disappears	The item is cheap and low-risk but politically distracting
Confidence	Owner, purpose, and dependency path are understood	The team is guessing from age or name
Reversibility	Restore, recreate, re-enable, or rollback path exists	Deletion would be the first real test
Prevention	A rule can stop recurrence	The same pattern will return next month

Start with high-impact, high-confidence, reversible candidates. Defer confusing items only if they get an owner and a date; otherwise “defer” becomes another word for keeping waste permanently.

Indexes That Need Patience

Some cleanup candidates are supposed to look quiet. Do not rush these cases:

Indexes used by month-end reports, rare customer searches, or incident investigations.
Partial, expression, unique, and constraint-backed indexes whose purpose is not obvious from scan count.
Statistics that reset recently or do not cover the workload cycle.

For these cases, use a longer observation window, explicit owner approval, and a staged reduction. The point is not to avoid cleanup; it is to avoid making the first proof of dependency an outage.

Run the Index Cleanup

Run Postgres index cleanup as a decision review, not an open-ended hygiene project.

Pick the narrow scope and export the candidate list.
Add owner, current purpose, last-use evidence, dependency checks, and risk if wrong.
Remove obvious false positives, then ask owners to choose keep, reduce, archive, disable, remove, or investigate.
Apply the least permanent useful change first.
Watch the signals that would reveal a bad decision.
Complete the final removal only after the review window closes.
Save an index cleanup migration plan with stats evidence, definition comparison, constraint check, query-plan review, and rollback notes.

For broader cleanup planning, use the cleanup library to pair this guide with related notes. If the cleanup has infrastructure impact, pair it with a visible owner, a rollback path, and a measurable business case. For infrastructure cleanup, the main cloud cost optimization checklist is a useful companion.

Make New Indexes Explain Themselves

Prevention should change the creation path, not just the cleanup path. For Postgres index cleanup, the useful prevention fields are data owner, retention policy, recreate path, and review date. Make those fields part of normal creation and review.

Require new indexes to include the query pattern, owner, and review trigger in the migration note.
Review duplicate and zero-scan indexes after major product or reporting changes.
Pair index creation with query-plan evidence so future cleanup has context.

The recurring review should be short: sort by impact, pick the unclear items, assign owners, and close the loop on anything nobody claims. If the review keeps producing the same class of candidate, fix the creation path instead of celebrating repeated cleanup.

Example Decision Record

Use a compact record so the cleanup can be reviewed later without reconstructing the whole investigation.

Field	Example entry for this cleanup
Candidate	Unused Postgres indexes in Postgres databases
Why it looked stale	Low recent activity, unclear owner, or no current consumer after the first review
Evidence checked	Usage statistics, Definition overlap, and owner confirmation
First reversible move	Mark candidate indexes for review after checking stats age and constraint ownership
Watch signal	The metric, alert, job, route, query, or owner complaint that would show the cleanup was wrong
Final action	Keep, reduce, archive, disable, or remove after a full workload cycle that includes reporting, maintenance, and customer-support query patterns
Prevention rule	Require new indexes to include the query pattern, owner, and review trigger in the migration note

This record is intentionally small. If the decision needs a long narrative, the candidate is probably not ready for removal yet. Keep investigating until the owner, evidence, reversible move, and prevention rule are clear.

FAQ

How often should teams do Postgres index cleanup?

Use a full workload cycle that includes reporting, maintenance, and customer-support query patterns for the first decision, then set a recurring cadence based on change rate. Fast-moving non-production systems may need monthly review; slower systems can be quarterly if every unclear item has an owner and a review date.

What is the safest first action?

The safest first action is usually ownership repair plus evidence collection. After that, mark candidate indexes for review after checking stats age and constraint ownership. That creates a visible test before permanent deletion.

What should not be removed quickly?

Do not rush anything connected to indexes used by month-end reports, rare customer searches, or incident investigations. Also slow down when the cleanup affects recovery, compliance, customer-specific behavior, rare schedules, or security response.

How do you make the decision useful later?

Write the decision as a small operational record: candidate, owner, evidence, chosen action, watch signals, rollback path, final date, and prevention rule. That format helps future engineers, search engines, and AI assistants understand the cleanup without guessing.