Unused Indexes in PostgreSQL: A Per-Module Monolith Audit Checklist

What a per-module index audit is and who should run it

A per-module index audit attributes every index and every slow query to the module that owns it before anyone adds or drops anything. The output is not a single list of "unused" and "missing" indexes. It is a set of findings grouped by owner, where each finding is a change that the owning team can review.

A database-wide list is unsafe in a monolith for three reasons:

• It ignores ownership. An index with zero scans may support a billing job that another team runs. Dropping it based on a global report shifts the risk onto people who never saw the change.
• It misses rare code paths. Month-end reconciliation, annual exports, and incident-only admin queries may not run during the window your statistics cover. An index can look dead and still be the only thing that keeps one of those queries from doing a full table scan.
• It misses replica traffic. Usage counters in pg_stat_user_indexes are tracked separately on each server. An index that reporting queries use heavily on a read replica can show no scans on the primary.

The audit is for backend and platform engineers who share one schema. It pays off when write latency is growing, storage is under pressure, or slow queries keep surfacing that no one claims. Skip it for small or read-heavy databases, where extra indexes cost little. Also skip it if statistics are reset often enough that you have no stable usage history.

The main time saving is organizational. A vague complaint such as "the database is slow" becomes specific, owned changes. Each change has a module, an owner, and a rollback plan, which means it can go through normal code review instead of an ad hoc firefight.

Checklist step 1: Map tables and indexes to owning modules

Before you can judge whether an index is unused or missing, you need to know who is allowed to change it. The audit's output is a set of per-module changes, so the first artifact is a table-to-module map.

Most monoliths combine sources. A common pattern uses prefixes or schemas as the default and lets a manifest override them. A useful starting query reads pg_stat_user_indexes and groups by schemaname and split_part(relname, '_', 1), then counts indexes and sums pg_relation_size(indexrelid) for each group. Groups that do not match any known module are your first list of gaps. A manifest entry can be as small as a module key with owner: team-payments, tables: [billing_invoices, billing_line_items], and readers: [reporting].

The readers field matters for shared tables. An index that looks useless to the owning module may serve another module's join or a nightly export. Record cross-module readers so that a drop proposal notifies them before review, not after an incident.

Some tables will have no owner, or more than one team will claim them. Define an escalation path for these, such as a named platform owner who decides within a fixed period. Without one, these tables are skipped every audit, and they usually hold the oldest and largest indexes.

Copy-- Group every user index by schema and table-name prefix so each group
-- can be matched to an owning module (or flagged as unowned).
-- Assumes prefixes use the first underscore-delimited token, e.g.
-- billing_invoices -> 'billing'.
SELECT
  s.schemaname,
  split_part(s.relname, '_', 1)                      AS table_prefix,
  count(*)                                           AS index_count,
  -- Indexes with no scans since stats were last reset are drop candidates,
  -- but only after confirming with the owner and any recorded readers.
  count(*) FILTER (WHERE s.idx_scan = 0)             AS zero_scan_indexes,
  pg_size_pretty(sum(pg_relation_size(s.indexrelid))) AS total_index_size,
  -- Sample table names help spot legacy tables that break the convention.
  (array_agg(DISTINCT s.relname ORDER BY s.relname))[1:5] AS sample_tables
FROM pg_stat_user_indexes AS s
GROUP BY s.schemaname, split_part(s.relname, '_', 1)
ORDER BY sum(pg_relation_size(s.indexrelid)) DESC;

-- Compare the table_prefix column against your module list or manifest.
-- Any group with no matching module goes on the escalation list.
-- Example manifest entry kept in the repo (YAML):
--
--   payments:
--     owner: team-payments
--     tables: [billing_invoices, billing_line_items]
--     readers: [reporting]   # notified before any index drop is reviewed

A catalog query that groups indexes by schema and table prefix, with counts, zero-scan totals, and size per group, followed by a sample manifest entry used to map those groups to owning modules.

Checklist step 2: Find unused index candidates with pg_stat_user_indexes

Start with every index in the module's tables whose idx_scan is zero or near zero in pg_stat_user_indexes. Join to pg_index and exclude rows where indisunique, indisprimary or indisexclusion is true. Those indexes enforce constraints even if no query ever reads them, so a zero scan count does not mean they are safe to drop.

A zero is only meaningful if you know how long the counters have been accumulating. Check stats_reset in pg_stat_database first. Counters are wiped after crash recovery, after an explicit pg_stat_reset(), and in some failovers. A promoted replica keeps its own counters, not the old primary's. An index that serves a month-end report can look unused if stats were reset two weeks ago. If you don't have at least one full business cycle of history, wait.

Statistics are per instance. A replica serving read-only reporting traffic may scan an index that the primary never touches. Run the query on the primary and on every replica, then merge the results. Keep an index as a candidate only if it is unused everywhere.

Diagram: one candidate list built from per-instance stats

• Primary (own stats) → idx_scan per index
• Replica A (own stats) → idx_scan per index
• Replica B (own stats) → idx_scan per index
• All three feed into one step: sum idx_scan across instances → combined candidate list

Rank the candidates by how much it costs to keep them:

• Size: use pg_relation_size on the index.
• Write rate: use the table's n_tup_ins, n_tup_upd and n_tup_del. Every write must maintain every index on the table.
• HOT impact: compare n_tup_hot_upd with n_tup_upd. A low ratio on an update-heavy table suggests that indexes on frequently updated columns are blocking HOT updates. Dropping an unused index on those columns can restore HOT updates.

Copy-- Run on the primary AND on every replica; counters are per instance.
-- 1) How old are the counters? A zero only means something over a full business cycle.
--    stats_reset can be NULL if never explicitly reset; compare with server start too.
SELECT datname,
       stats_reset,
       now() - stats_reset         AS stats_age,
       pg_postmaster_start_time()  AS server_started
FROM pg_stat_database
WHERE datname = current_database();

-- 2) Unused or rarely used indexes in one module's schema, ranked by keep-cost.
--    Constraint-enforcing indexes are excluded: they do work even with zero scans.
SELECT s.schemaname,
       s.relname                                   AS table_name,
       s.indexrelname                              AS index_name,
       s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       -- Write counters are only meaningful on the primary; replicas replay WAL.
       t.n_tup_ins + t.n_tup_upd + t.n_tup_del     AS table_writes,
       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_update_pct
FROM pg_stat_user_indexes s
JOIN pg_index i            ON i.indexrelid = s.indexrelid
JOIN pg_stat_user_tables t ON t.relid = s.relid
WHERE s.schemaname = 'billing'          -- placeholder: the module being audited
  AND NOT i.indisunique                 -- also covers primary keys
  AND NOT i.indisprimary
  AND NOT i.indisexclusion
  AND s.idx_scan <= 10                  -- "near zero" threshold; tune per module
ORDER BY pg_relation_size(s.indexrelid) DESC,
         table_writes DESC;
-- Merge results from all instances by index name, sum idx_scan,
-- and keep only indexes that are unused everywhere.

Two queries to run on each instance: one checks how long statistics have been accumulating, the other lists non-constraint indexes with near-zero scans in a module's schema, ranked by size, table write volume and HOT-update ratio.

Checklist step 3: Detect redundant and duplicate indexes

Unused indexes are only half the waste. The other half is indexes that duplicate work another index already does. The query for this step reads pg_index and groups indexes by indrelid. Within each table, it compares indkey (the column numbers), indclass (operator classes), indcollation, indoption (sort direction and nulls ordering), indisunique, indexprs and indpred. Two indexes with the same values in every one of those columns are true duplicates. If one index's indkey is a leading subset of another's, compare the arrays element by element rather than by text prefix, because the text form of 1 2 is also a prefix of 1 23. That makes the shorter index a left-prefix candidate. Also compare indnkeyatts, so that INCLUDE columns are not counted as key columns.

Treat both kinds of result as candidates. Matching column lists can hide real differences:

A left prefix is not automatically redundant either. The planner may prefer the narrower index for some queries because it is smaller, has fewer pages to read, and has a better chance of staying in cache. Before dropping a prefix index, check its idx_scan count from the previous step, and confirm the plan with EXPLAIN on representative queries.

Copy-- Pair indexes on the same table whose key columns are identical or a left prefix.
-- Arrays are rebuilt with lower bound 1 and compared element by element, not as text.
WITH idx AS (
  SELECT i.indexrelid, i.indrelid, i.indisunique, i.indnkeyatts, i.indnatts,
         (string_to_array(i.indkey::text, ' ')::int2[])[1:i.indnkeyatts] AS keys, -- key cols only, INCLUDE excluded
         string_to_array(i.indclass::text, ' ')::oid[]      AS opclasses,
         string_to_array(i.indcollation::text, ' ')::oid[]  AS colls,
         string_to_array(i.indoption::text, ' ')::int2[]    AS opts,       -- DESC / NULLS FIRST bits
         pg_get_expr(i.indexprs, i.indrelid)                AS exprs,      -- key 0 means expression
         pg_get_expr(i.indpred,  i.indrelid)                AS pred
  FROM pg_index i
  JOIN pg_class c     ON c.oid = i.indrelid
  JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
)
SELECT a.indrelid::regclass   AS table_name,
       a.indexrelid::regclass AS candidate,   -- shorter (or equal) index
       b.indexrelid::regclass AS covered_by,
       CASE WHEN a.keys = b.keys THEN 'same keys' ELSE 'left prefix' END AS kind,
       -- Every column below must be true (or reviewed) before calling it redundant
       a.opclasses = b.opclasses[1:cardinality(a.opclasses)] AS same_opclass,
       a.colls     = b.colls[1:cardinality(a.colls)]         AS same_collation,
       a.opts      = b.opts[1:cardinality(a.opts)]           AS same_sort_order,
       a.exprs IS NOT DISTINCT FROM b.exprs                  AS same_expressions,
       a.pred  IS NOT DISTINCT FROM b.pred                   AS same_predicate,
       a.indnatts - a.indnkeyatts AS cand_include_cols,
       b.indnatts - b.indnkeyatts AS other_include_cols,
       a.indisunique AS cand_unique,                         -- dropping it drops a constraint
       pg_size_pretty(pg_relation_size(a.indexrelid)) AS cand_size
FROM idx a
JOIN idx b
  ON  a.indrelid = b.indrelid
  AND a.indexrelid <> b.indexrelid
  AND a.keys = b.keys[1:cardinality(a.keys)]                -- element-wise prefix match
  AND (cardinality(a.keys) < cardinality(b.keys)
       OR a.indexrelid < b.indexrelid)                      -- report exact pairs once
ORDER BY table_name, candidate;

A pg_index query that lists exact-duplicate and left-prefix index pairs per table, comparing key arrays element by element and surfacing operator class, collation, sort order, expression, predicate, INCLUDE and uniqueness differences for manual review.

Checklist step 4: Missing index detection from pg_stat_statements

Sort pg_stat_statements by total execution time first, then by mean execution time. Total time shows where the database spends its effort in aggregate, usually on frequent, moderately slow queries. Mean time shows individual queries that are slow every time they run, even when they run rarely.

Attribute each query to a module

pg_stat_statements does not record application_name. If each module sets application_name, match the queries through pg_stat_activity or through server logs that include it. SQL comments that name the module also work, with one limitation: comments do not affect the query ID. When two modules send the same statement, only one comment survives in the stored text. Separate database roles per module avoid this problem, because the userid column keeps their entries apart.

Treat sequential scans as leads

High seq_scan and seq_tup_read values in pg_stat_user_tables point to tables worth investigating. They do not prove an index is missing. For a table that fits in a few pages, or a query that reads most of the rows, a sequential scan costs less than random index lookups. The planner chooses it for that reason.

Confirm with a real plan

Run EXPLAIN (ANALYZE, BUFFERS) against production-like data volumes and distributions before you write any DDL. Staging databases with small or uniform data often produce different plans from production.

Know the limits

• Normalization merges literal values, so a query that is slow only for one skewed value is hidden in the average.
• The pg_stat_statements.max cap evicts the least-used entries, so rare but expensive queries can disappear before you look.
• An index that helps one module's read adds write cost for every module that inserts into or updates that table. Confirm with those modules' owners before you add it.

Copy-- 1. Top statements by total time, attributed to a module via a per-module role.
--    Column names are for PostgreSQL 13+ (older versions use total_time/mean_time).
SELECT r.rolname                            AS module_role,
       s.queryid,
       s.calls,
       round(s.total_exec_time::numeric, 1) AS total_ms,
       round(s.mean_exec_time::numeric, 1)  AS mean_ms,
       left(s.query, 80)                    AS query_start
FROM pg_stat_statements s
JOIN pg_roles r ON r.oid = s.userid
ORDER BY s.total_exec_time DESC   -- rerun with mean_exec_time DESC for slow-every-time queries
LIMIT 20;

-- 2. Leads, not proof: tables with heavy sequential reads.
--    A high seq_scan on a small table is expected and usually correct.
SELECT relname,
       n_live_tup,
       seq_scan,
       seq_tup_read,
       idx_scan,
       seq_tup_read / NULLIF(seq_scan, 0) AS avg_rows_per_seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 20;

-- 3. Confirm a candidate on production-like data before writing DDL.
--    ANALYZE executes the statement; wrap writes in BEGIN ... ROLLBACK.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, created_at
FROM billing_invoices
WHERE customer_id = 48213
  AND status = 'open';

Ranks pg_stat_statements entries by total time per module role, lists high sequential-read tables as leads, and confirms one candidate query with EXPLAIN (ANALYZE, BUFFERS).

Checklist step 5: Build and drop indexes safely on a live database

Treat every drop as a staged change, in this order:

• Confirm the candidate. It came out of steps 2 and 3 and is not backing a primary key, unique constraint, or exclusion constraint.
• Get sign-off from the owning module. The module's team knows about the admin report that runs once a year. The statistics do not.
• Observe for a full business cycle. The window must include month-end and quarter-end jobs. A billing reconciliation that scans an index four times a year will show zero scans for most of that year.
• Save the definition. Run pg_get_indexdef on the index OID and store the output in the module's manifest. Rollback then means running a known statement, not reconstructing one under pressure.
• Drop it with DROP INDEX CONCURRENTLY. This avoids the exclusive lock a plain DROP INDEX takes, which would block reads and writes on the table while it waits.
• Watch afterward and record the outcome. Track p95 latency and the top statements for the affected tables over the next cycle. Record the outcome (dropped, restored, or deferred) in the manifest.

Building follows the same discipline with CREATE INDEX CONCURRENTLY. It is slower than a plain build but lets writes continue. If it fails partway, for example on a deadlock or a uniqueness violation, PostgreSQL leaves the index in place and marks it invalid. The planner will not use it, but writes still maintain it, so you pay the cost with no benefit. Find these by checking pg_index.indisvalid = false. Then drop the index and retry the build.

Both concurrent commands refuse to run inside a transaction block. Many migration frameworks wrap each migration in a transaction by default, so the migration fails. Use your tool's per-migration option to disable the wrapping transaction. Also keep each concurrent statement in its own migration, so one failure does not leave other changes half-applied.

Making the audit repeatable, alternatives, and impact on cost

…migrations stranded half-applied.

Make it repeatable

Cumulative counters are only useful as trends. Schedule a job, daily or weekly, that copies idx_scan from pg_stat_user_indexes and n_tup_ins, n_tup_upd, n_tup_del and seq_scan from pg_stat_user_tables into a history table. Stamp each row with the capture time and the server it came from. A stats reset or failover then shows up as a counter drop instead of erasing your evidence. Replicas keep their own statistics, so snapshot every node that serves reads. Tag rows with the owning module from step 1 so each team queries only its own trend.

Who pays, and where to start

When one module adds an index to a shared table, every module that writes to that table pays the extra write and storage cost. Record the requesting module on each index so that cost can be traced back to it. Dropping indexes on read-heavy tables saves little, so start with high-write tables and with the slowest queries.

Illustrative estimate (assumed numbers, not a benchmark). Index entries avoided per day = (inserts + non-HOT updates per day) × unused indexes dropped. Assume an events table with 2,000,000 writes/day and 3 unused indexes: 6,000,000 entries per day avoided. A lookup table with 10,000 writes/day and the same 3 unused indexes saves 30,000.

When to avoid it

Small, low-write tables like that rarely justify the review time. More broadly, skip or postpone the audit when the data feeding it can't be trusted or the change can't be owned.

• Statistics were recently reset. Usage counters are cleared after a crash, an explicit reset, or a restore onto a new instance. If counters cover only days, every index used by a weekly or monthly job will look unused. Check when stats were last reset before you trust a zero.

• Replicas serve real traffic. Index usage counters are tracked per instance. An index with zero scans on the primary may carry the reporting queries on a read replica. If you can't collect stats from every node that serves reads, a "zero scans" result is incomplete.

• The workload is seasonal or event-driven. Billing runs, quarter-end reports, and annual compliance exports may touch an index a few times a year. If your observation window doesn't include those events, the audit will recommend dropping exactly the index that keeps them fast.

• The module is about to be extracted or rewritten. Index tuning on tables that will move to a separate service in the next quarter is wasted effort. The new access patterns will need a fresh audit anyway.

• No one owns the module. A drop recommendation without an owner who knows the query paths turns into guesswork. Fix ownership in the step 1 mapping first, then audit.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to unused indexes postgresql, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• The dependency graph answers "what does this touch?" in seconds instead of a manual audit.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• You can size a change's blast radius before merging, avoiding surprise cross-team firefighting.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.