🗂️ AI-Assisted Postgres Index Design and Killing Redundant Indexes

The fastest way to make a Postgres database slower is to add indexes carelessly. Every index you create is a tax on every write to that table, and a surprising number of them never get used by a single query. I inherited a database once where a 200GB table had fourteen indexes; profiling showed that four were never scanned and three were strict prefixes of others. We were paying for fourteen index updates on every insert to serve queries that needed maybe five. AI is a good thinking partner for index design — it reasons clearly about column order, selectivity, and covering indexes — but it has no idea which of your indexes are actually being hit. That answer is sitting in the catalog. You combine the two.

This is how I design indexes that earn their keep and delete the ones that don’t.

First, find the dead weight

Before adding anything, audit what you already have. Postgres tracks how often each index gets scanned. An index with zero scans after weeks of real traffic is dead weight — it slows writes and serves nothing.

SELECT
  s.relname AS table,
  s.indexrelname AS index,
  s.idx_scan AS scans,
  pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE NOT i.indisunique         -- keep unique/PK constraints
ORDER BY s.idx_scan ASC, pg_relation_size(s.indexrelid) DESC;

Anything with scans = 0 is a candidate for removal, with two caveats: stats reset on restart and replica reads aren’t always counted here, so confirm the index has been in place long enough to be trusted. I paste this output to AI and ask it to flag indexes that are unused and large, since those are the ones worth dropping first.

Find redundant prefixes

A composite index on (a, b, c) already serves queries that filter on a, and on (a, b). A separate index on just (a) is usually redundant. Spotting these by eye across dozens of indexes is tedious and error-prone, so I let AI do the pattern matching after pulling the definitions.

SELECT indexrelid::regclass AS index, indrelid::regclass AS table,
       pg_get_indexdef(indexrelid) AS definition
FROM pg_index
ORDER BY indrelid;

Here are all index definitions for this database. Identify cases where one index is a left-prefix of another on the same table (e.g. an index on (customer_id) when (customer_id, created_at) also exists). For each, tell me which one is the redundant one to drop and why. Ignore unique constraints and indexes that back foreign keys.

The model is reliably good at this because it is pure structural reasoning. I still verify each suggestion: a standalone (a) index can be worth keeping if it’s much smaller and a-only lookups are hot. But the default is to drop the prefix.

Drop without locking

Removing an index takes a lock unless you say otherwise. Use the concurrent form so writes keep flowing.

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_customer_id;

Do this one at a time, watch error rates, and keep the CREATE INDEX statement handy so you can roll back in seconds if a plan regresses.

Design new indexes from real queries

For new indexes, the input that matters is the actual query and its plan — not a vague description. I give AI the query, the EXPLAIN (ANALYZE, BUFFERS) output, and the row counts, then ask for a justified proposal.

Given this query and its plan, propose the minimal index that turns the sequential scan into an index scan. Justify the column order using selectivity and the ORDER BY. Tell me whether a partial index or a covering INCLUDE clause would help, and estimate the write-cost tradeoff. Give me the exact CREATE INDEX CONCURRENTLY statement.

Column order matters: equality predicates go first, then the range or sort column. A covering index can let Postgres answer from the index alone (an index-only scan), which is powerful for read-heavy paths.

CREATE INDEX CONCURRENTLY idx_orders_cust_created
ON orders (customer_id, created_at DESC)
INCLUDE (total, status);

CONCURRENTLY is non-negotiable on a live table — it builds without blocking writes, at the cost of two table scans and the risk of an invalid index if it fails midway. Always check afterward:

SELECT indexrelid::regclass, indisvalid
FROM pg_index WHERE NOT indisvalid;

An invalid index from a failed concurrent build still costs you on writes and serves nothing — drop and rebuild it.

Prove it, then keep it honest

The verification loop is the same every time: capture the plan before, create the index concurrently on a replica with production-like data, re-run the plan, confirm the expensive node changed and total time dropped. Then watch idx_scan over the following days to confirm the real workload actually uses it. An index that looks perfect in EXPLAIN but never gets scanned in production is just write tax with extra steps.

AI compresses the design work — column order, partial predicates, covering clauses — into a few minutes of clear reasoning. It does not get to push CREATE INDEX on its own, because it can’t see your write volume or your catalog. I keep my index-review prompts in the prompt library, and the rest of the database tuning material lives under the Postgres guides. Index changes pair naturally with query debugging, so I usually do both in the same session.