Your Postgres already knows why it's slow - you just have to ask it

The data is already there: pg_stat_*

Every PostgreSQL instance continuously tracks statistics about how its tables and indexes are being used. These live in a family of system views:

• pg_stat_user_tables - per-table scans, rows inserted/updated/deleted, dead tuples, vacuum timestamps
• pg_stat_user_indexes - per-index scan counts and tuples read/fetched
• pg_statio_user_indexes - index block reads vs. cache hits
• pg_index, pg_class, pg_stats - structural metadata and column statistics

The best part: reading all of this is a SELECT. A read-only database user is enough. Nothing is written back to prod, no extension needs installing, no agent runs on the box. That was a hard requirement for me - I wanted to point this at a production replica without a second thought.

// server/db.ts - the entire database setup
import { Pool } from 'pg'

const isLocalhost = process.env.DATABASE_URL?.includes('localhost')

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: isLocalhost ? false : { rejectUnauthorized: false }, // auto-SSL for RDS etc.
})

That's it. Everything below is just queries against that pool.

Question 1: Which indexes is nobody using?

Indexes aren't free. Every one of them has to be updated on every INSERT, UPDATE, and DELETE to the underlying columns. An index that never serves a read is pure overhead - disk, cache, and write amplification, for nothing.

pg_stat_user_indexes.idx_scan tells you exactly how many times an index has been used to satisfy a scan. Zero scans is a strong signal.

SELECT
  s.relname AS table_name,
  s.indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
  s.idx_scan AS scans,
  pg_get_indexdef(s.indexrelid) AS definition
FROM pg_stat_user_indexes s
JOIN pg_index ix ON s.indexrelid = ix.indexrelid
WHERE s.schemaname = 'public'
  AND s.idx_scan = 0
  AND NOT ix.indisprimary          -- never suggest dropping a PK
  AND NOT ix.indisunique           -- unique indexes enforce constraints, keep them
ORDER BY pg_relation_size(s.indexrelid) DESC;

The two NOT clauses matter. A primary key or unique index might show zero scans but still be doing essential work enforcing a constraint - dropping it would be a bug, not an optimization. Everything left is a genuine candidate.

Caveat that will bite you: idx_scan is cumulative since the last pg_stat_reset() (or since the stats file was created). If your stats were reset last week, "zero scans" might just mean "not used this week." Always check how old your stats are before you drop anything.

Question 2: Are there exact duplicate indexes?

Over time, migrations and well-meaning "let me just add an index" moments produce indexes that cover the identical set of columns in the same order. They're 100% redundant - you can drop all but one with zero query-plan impact.

The trick is comparing indexes by their ordered column list, which means reconstructing that list from pg_index.indkey:

SELECT
  table_name,
  json_agg(index_name) AS index_names,
  json_agg(index_size) AS index_sizes,
  index_columns
FROM (
  SELECT
    t.relname AS table_name,
    i.relname AS index_name,
    pg_size_pretty(pg_relation_size(i.oid)) AS index_size,
    array_to_string(array_agg(a.attname ORDER BY x.n), ', ') AS index_columns
  FROM pg_index ix
  JOIN pg_class t ON t.oid = ix.indrelid
  JOIN pg_class i ON i.oid = ix.indexrelid
  JOIN pg_namespace n ON n.oid = t.relnamespace
  JOIN pg_attribute a ON a.attrelid = t.oid
  JOIN unnest(ix.indkey) WITH ORDINALITY AS x(attnum, n) ON a.attnum = x.attnum
  WHERE n.nspname = 'public'
  GROUP BY t.relname, i.relname, i.oid
) sub
GROUP BY table_name, index_columns
HAVING count(*) > 1   -- more than one index with the same column signature
ORDER BY table_name;

unnest(ix.indkey) WITH ORDINALITY is the key move - it expands the index's column list with its position preserved, so (a, b) and (b, a) are correctly treated as different indexes. HAVING count(*) > 1 surfaces only the true duplicates.

Question 3: Which tables are starving for an index?

The inverse problem. A table with many rows that Postgres keeps scanning sequentially - instead of using an index - is a latency problem waiting to happen. pg_stat_user_tables gives you seq_scan (sequential scans) and idx_scan (index scans) per table. High sequential scans on a large table where the index barely gets used is the smell:

SELECT
  relname AS table_name,
  seq_scan,
  seq_tup_read,
  idx_scan,
  n_live_tup AS live_rows,
  round(seq_tup_read::numeric / nullif(seq_scan, 0), 0) AS avg_seq_tup_read
FROM pg_stat_user_tables
WHERE schemaname = 'public'
  AND seq_scan > 100               -- scanned sequentially a meaningful number of times
  AND n_live_tup > 10000           -- big enough that a scan actually hurts
  AND idx_scan < seq_scan          -- indexes aren't pulling their weight
ORDER BY seq_tup_read DESC
LIMIT 30;

avg_seq_tup_read - average rows read per sequential scan - is the number I care about most here. Reading 500K rows per scan, thousands of times, is exactly the kind of thing that shows up as "the app feels slow at peak" without any single query looking obviously broken.

This query tells you which table needs help. It deliberately doesn't guess which column to index - for that you go to your slow query logs or pg_stat_statements. The tool points you at the table; you make the call.

Question 4: What is over-indexing costing my writes?

This is the one most people never measure, and it was the biggest eye-opener for me. Every index multiplies write cost. A table with 12 indexes taking 500K writes isn't doing 500K units of work - it's doing closer to 500K × 12. So I compute a simple write × index-count score and rank by it:

SELECT
  t.relname AS table_name,
  (SELECT count(*) FROM pg_stat_user_indexes si WHERE si.relid = t.relid) AS index_count,
  (t.n_tup_ins + t.n_tup_upd + t.n_tup_del) AS total_writes,
  t.n_tup_hot_upd AS hot_updates,
  CASE WHEN t.n_tup_upd > 0
    THEN round((100.0 * t.n_tup_hot_upd / t.n_tup_upd)::numeric, 1)
    ELSE 0
  END AS hot_update_pct,
  (t.n_tup_ins + t.n_tup_upd + t.n_tup_del) *
    (SELECT count(*) FROM pg_stat_user_indexes si WHERE si.relid = t.relid) AS write_index_cost
FROM pg_stat_user_tables t
WHERE t.schemaname = 'public'
  AND (t.n_tup_ins + t.n_tup_upd + t.n_tup_del) > 0
ORDER BY write_index_cost DESC
LIMIT 50;

There's a second signal hiding in there: HOT updates (n_tup_hot_upd). A HOT (Heap-Only Tuple) update is one where Postgres can update a row without touching every index - because no indexed column changed. A high HOT percentage is healthy. A low HOT percentage on a heavily-updated table means you're probably indexing a column that changes constantly, forcing a full index update on every write. That's a design smell worth chasing.

Question 5: How bloated are my indexes?

B-tree indexes bloat over time as rows are updated and deleted - dead entries leave gaps that never fully reclaim. A bloated index is bigger than it needs to be, which means more disk, worse cache locality, and slower scans.

Postgres doesn't hand you a bloat number directly, so this one is an estimate: reconstruct roughly how many pages the index should occupy (otta - the "optimal" size) from column statistics in pg_stats, then compare against actual relpages. The full query is a chunky CTE (the estimation math is fiddly), but the payoff line is simple:

CASE WHEN oc.otta = 0 THEN 0
  ELSE round((100.0 * (c.relpages - oc.otta) / oc.otta)::numeric, 1)
END AS bloat_pct

Be honest about this one. Statistics-based bloat estimation is a well-known heuristic - it can be off, especially on tables with skewed column widths. Treat a high number as "worth investigating with REINDEX," not gospel. For exact numbers, pgstattuple is the (extension-based) ground truth.

Turning data into decisions

Here's the thing: raw query output still leaves you doing the analysis. "This index has zero scans" - okay, but is it big enough to care about? "This table has dead rows" - how many is too many? The real value wasn't the queries. It was encoding the judgment calls into thresholds so the tool tells me what to do, ranked by severity.

That logic lives in one function that consumes all the query results and emits prioritized action items:

// Drop: unused indexes over 10 MB are critical; smaller ones batched as a warning
if (idx.index_size_bytes > 10 * 1024 * 1024) {
  push('critical', 'drop', `Drop unused index "${idx.index_name}"`, ...)
}

// Reindex: >50% bloat on a >50 MB index is critical; >30% is a warning
if (b.bloat_pct > 50 && b.bloat_size_bytes > 50 * 1024 * 1024)
  push('critical', 'reindex', ...)
else if (b.bloat_pct > 30)
  push('warning', 'reindex', ...)

// Vacuum: >40% dead rows is critical, >20% is a warning (and only if >10k dead rows)
if (t.dead_row_pct > 20 && t.dead_rows > 10000) {
  push(t.dead_row_pct > 40 ? 'critical' : 'warning', 'vacuum', ...)
}

// Review: index size > 2x table size, or 10+ indexes on a heavily-written table
if (r.ratio > 3 && r.indexes_size_bytes > 50 * 1024 * 1024)
  push('critical', 'review', ...)
if (w.index_count > 10 && w.total_writes > 100000)
  push(w.index_count > 15 ? 'critical' : 'warning', 'review', ...)

Every threshold there is a defensible line I drew from experience - the 10 MB floor on "unused" exists because reclaiming a 200 KB index isn't worth a migration; the dead-rows > 10,000 gate exists because 30% of a 12-row table is noise. You'll want to tune these to your own database. But once they're written down, the tool does in seconds what used to take me an afternoon: a single, sorted list of Drop / Reindex / Add / Vacuum / Review, critical items first.

The takeaway

The biggest surprise from all this: the wins rarely came from adding indexes. They came from finding what was quietly getting in the way - the unused index doubling a table's write cost, the duplicate nobody noticed, the bloat eating cache. And none of it required special tooling. It's all sitting in pg_stat_* on every Postgres instance you run, right now.

If you take one thing from this post, let it be this: next time your database feels slow, don't reach for a new tool first. Ask the database what it already knows.