Finding unused indexes, table bloat and missing primary keys with Postgres catalog queries

When we take over a PostgreSQL database, from another team or after an acquisition, the first week always uncovers the same three problems. Indexes that no query uses but every write still pays for. Tables and indexes several times larger than their live data. And a few tables without a primary key, which break logical replication and make duplicate rows easy.

None of this needs extra tooling to find. The statistics views and system catalogs already know. This guide is the checklist of catalog queries we run on day one, written for PostgreSQL 16 through 18, how to interpret each result without fooling ourselves, and how to clean up without blocking production.

Before you trust any statistic: check when it was reset

The usage counters in pg_stat_user_indexes and pg_stat_user_tables accumulate since the last statistics reset. An index with zero scans since yesterday tells you nothing. Check first:

SELECT datname, stats_reset, now() - stats_reset AS age
FROM pg_stat_database
WHERE datname = current_database();

We want at least a full business cycle, usually a month and ideally one that includes month-end jobs, before calling anything unused. If stats_reset is NULL, the counters have never been reset since the cluster was created, which is good.

A second trap: statistics are per server. A replica serving read traffic has its own counters, and an index with zero scans on the primary may be the most important index on the replica. Run the usage queries on every node that serves queries.

Unused indexes

SELECT s.schemaname, s.relname AS table_name, s.indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       s.idx_scan, s.last_idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
  AND NOT EXISTS (
      SELECT 1 FROM pg_constraint c WHERE c.conindid = s.indexrelid
  )
ORDER BY pg_relation_size(s.indexrelid) DESC;

The filters matter. Unique and primary key indexes enforce constraints even when no query reads them, so they are never "unused". The pg_constraint check also excludes indexes backing exclusion constraints and foreign key lookups declared as constraints. The last_idx_scan column, available since PostgreSQL 16, gives the timestamp of the last scan, which is more useful than a counter: an index last used eight months ago is a stronger candidate than one with a low count.

What this query cannot see:

• Indexes used only by a quarterly or yearly job that has not run since the reset.
• Indexes used on a replica (run it there too).
• Indexes that exist to speed up foreign key checks on deletes from the parent table. A missing one makes a DELETE on the parent scan the child table; those lookups do count as scans, but only when deletes happen.

In our last inherited database, the query listed 41 indexes totalling 63 GB. After checking replicas and the batch job calendar, we dropped 29 of them.

Duplicate and overlapping indexes

Two indexes on the same columns are pure waste, and an index on (a) is usually redundant next to one on (a, b), because the second can serve lookups on a alone.

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

This finds exact duplicates: same columns, same operator classes, same expressions and same partial predicate. For prefix overlaps, compare indkey arrays:

SELECT a.indrelid::regclass AS table_name,
       a.indexrelid::regclass AS shorter_index,
       b.indexrelid::regclass AS covered_by
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
               AND a.indexrelid <> b.indexrelid
               AND a.indnatts < b.indnatts
               AND (b.indkey::int2[])[0:a.indnatts - 1] = (a.indkey::int2[])[0:a.indnatts - 1]
WHERE NOT a.indisunique
  AND a.indexprs IS NULL AND a.indpred IS NULL;

Review every hit by hand. A shorter index can still be worth keeping if it is much smaller and serves a very hot query, and different index types (B-tree versus GIN) on the same column are not duplicates.

Invalid indexes

A CREATE INDEX CONCURRENTLY that fails, or is cancelled, leaves an invalid index behind. It is never used for reads, but it is still updated on every write.

SELECT indexrelid::regclass AS index_name, indrelid::regclass AS table_name
FROM pg_index
WHERE NOT indisvalid;

Drop and recreate these, or run REINDEX INDEX CONCURRENTLY on them.

Table bloat

Updates and deletes leave dead row versions. Vacuum makes their space reusable but rarely gives it back to the operating system, so a table that once had a big purge stays large. First a quick view of dead tuples and vacuum activity:

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, last_autoanalyze,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

A high dead_pct with an old last_autovacuum points at autovacuum not keeping up, often because of a long-running transaction holding back cleanup. Check for those:

SELECT pid, state, now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

Dead tuples only measure what vacuum has not yet processed. Free space that vacuum already reclaimed, but which stays inside the file, is the real bloat. For an accurate number use the pgstattuple extension. pgstattuple_approx is much cheaper on large tables because it skips pages the visibility map marks as all-visible:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT pg_size_pretty(table_len) AS size,
       round(approx_free_percent::numeric, 1) AS free_pct,
       round(dead_tuple_percent::numeric, 1) AS dead_pct
FROM pgstattuple_approx('public.orders');

For indexes, pgstatindex('index_name') reports avg_leaf_density. A B-tree built fresh sits near 90 percent; values below 50 percent on a big index mean a rebuild will shrink it substantially.

SELECT avg_leaf_density, leaf_fragmentation, pg_size_pretty(index_size)
FROM pgstatindex('public.orders_customer_id_idx');

Both functions read the relation, so run them off-peak or on a replica restored from a recent backup.

Tables without a primary key

SELECT n.nspname AS schema_name, c.relname AS table_name,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (
      SELECT 1 FROM pg_constraint k
      WHERE k.conrelid = c.oid AND k.contype = 'p'
  )
ORDER BY pg_total_relation_size(c.oid) DESC;

Logical replication cannot replicate UPDATE and DELETE on a table without a primary key or another replica identity; they fail with cannot update table "x" because it does not have a replica identity and publishes updates. Most change data capture tools have the same requirement. Adding a primary key to a large table is safest in two steps: build a unique index concurrently, then attach it as the constraint, which only takes a short lock:

CREATE UNIQUE INDEX CONCURRENTLY event_log_pkey_idx ON event_log (id);
ALTER TABLE event_log ADD CONSTRAINT event_log_pkey PRIMARY KEY USING INDEX event_log_pkey_idx;

If the column has no NOT NULL constraint, adding the primary key has to scan the table under a strong lock to check it. Since PostgreSQL 12 you can avoid that: add CHECK (id IS NOT NULL) NOT VALID, run VALIDATE CONSTRAINT (which takes a weaker lock), and SET NOT NULL then skips the scan.

Cleaning up safely

Dropping an index is instant to undo only if you kept its definition. Our routine:

1. Save the definition: SELECT pg_get_indexdef('index_name'::regclass); into the migration file.
2. Drop without blocking: DROP INDEX CONCURRENTLY index_name; (it cannot run inside a transaction block, so migration tools need their non-transactional mode).
3. Watch query latency and pg_stat_statements for a week; recreate with CREATE INDEX CONCURRENTLY if a regression appears.

To shrink bloated indexes, REINDEX INDEX CONCURRENTLY rebuilds them online. For bloated tables, VACUUM FULL rewrites the table but holds an ACCESS EXCLUSIVE lock for the whole run, which blocks reads too. On anything that must stay online, use the pg_repack extension, which rebuilds the table in the background and takes only short locks at the start and end. It needs free disk space for a full copy of the table and its indexes.

Common mistakes

Key takeaways

• Check stats_reset and replicas before deciding any index is unused.
• Exclude unique, primary key and constraint-backed indexes from cleanup lists.
• Use pgstattuple_approx and pgstatindex for real bloat numbers, not just dead tuple counts.
• Tables without a primary key break logical replication; add one with a concurrent unique index.
• Drop with DROP INDEX CONCURRENTLY and rebuild online with REINDEX CONCURRENTLY or pg_repack.

References

• PostgreSQL docs, The Cumulative Statistics System: https://www.postgresql.org/docs/18/monitoring-stats.html
• PostgreSQL docs, pg_index catalog: https://www.postgresql.org/docs/18/catalog-pg-index.html
• PostgreSQL docs, pgstattuple: https://www.postgresql.org/docs/18/pgstattuple.html
• PostgreSQL docs, Routine Vacuuming: https://www.postgresql.org/docs/18/routine-vacuuming.html
• PostgreSQL docs, Logical Replication publication and replica identity: https://www.postgresql.org/docs/18/logical-replication-publication.html
• pg_repack project: https://github.com/reorg/pg_repack