What This Agent Does
A Postgres index agent reads pg_stat_user_indexes and pg_index on the primary and on every replica, works out which indexes have not been scanned anywhere for a window it can prove, which are exact or prefix duplicates of another index, which are invalid leftovers from a failed concurrent build, and which are bloated enough to be worth rebuilding. It then opens one pull request per index in the owning service's migrations directory, containing a DROP INDEX CONCURRENTLY or REINDEX INDEX CONCURRENTLY in that repository's migration format, the evidence, and the exact CREATE INDEX statement to restore it. It holds no credential that can run DDL and none that can read a row.
The reason this is an agent and not the eight-line query everyone has pasted from a wiki is that the query lies in four well-known ways. idx_scan = 0 on the primary says nothing about the reporting replica that uses the index all day. A counter that was reset nine days ago cannot prove a month of disuse. A unique index with zero scans is still enforcing a constraint. And an index that only matters during the quarterly purge will look dead for eighty-nine days. The agent exists to close those four holes with evidence before a human is asked to approve anything.
Why Unused Indexes Are Worth the Trouble
An index nobody reads is not free storage. Every INSERT writes to it, every UPDATE that touches one of its columns writes to it and loses the HOT optimization for that row, all of that is WAL that replicas must replay, and autovacuum scans it on every pass. On a write-heavy table with fourteen indexes, of which five are dead, the dead ones are roughly a third of the write amplification. They also occupy shared_buffers and provisioned storage, which is how this turns into a line item the RDS cost agent can only describe from the outside.
Step 1: Collect from Every Node, Because Stats Are Per Instance
Index usage counters live in each server's own cumulative statistics. A standby replays WAL but counts its own scans, so the collector runs the same query against the writer and each replica's instance endpoint. On Aurora, connect to the individual instance endpoints: the reader endpoint load-balances and you will sample one reader at random.
-- indexagent/collect.sql (role: pg_monitor, no table privileges)
SELECT s.schemaname, s.relname, s.indexrelname,
s.idx_scan,
pg_relation_size(s.indexrelid) AS bytes,
t.n_tup_ins + t.n_tup_upd - t.n_tup_hot_upd AS index_writes,
i.indisunique, i.indisprimary, i.indisexclusion,
i.indisreplident, i.indisvalid,
i.indexprs IS NOT NULL AS is_expression,
EXISTS (SELECT 1 FROM pg_constraint c
WHERE c.conindid = s.indexrelid) AS backs_constraint,
EXISTS (SELECT 1 FROM pg_inherits h
WHERE h.inhrelid = s.indexrelid) AS is_partition_child,
pg_get_indexdef(s.indexrelid) AS ddl
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;
On PostgreSQL 16 and later, add s.last_idx_scan to the select list. It is a timestamp of the most recent scan and is the single most useful column added to this view in years, because "last used 14 months ago" is an argument and "zero" is only a number.
The second query establishes how much history the counters hold:
SELECT pg_is_in_recovery() AS is_replica,
pg_postmaster_start_time() AS started,
(SELECT stats_reset FROM pg_stat_database
WHERE datname = current_database()) AS stats_reset;
stats_reset is often NULL, and counters also vanish without updating it: a crash recovery discards them, pg_upgrade does not carry them over, and a replica promoted during failover brings its own history, not the old primary's. So the agent does not trust the server's memory. It stores each daily collection in its own table and computes usage as the difference between snapshots. A counter that goes down between two days marks a reset, and the evidence window for that node restarts there.
# indexagent/merge.py
def merge(nodes: dict[str, dict]) -> dict:
"""nodes: {node_name: {"window_days": int, "indexes": {fq_name: row}}}"""
out = {}
window = min(n["window_days"] for n in nodes.values())
for node, data in nodes.items():
for name, row in data["indexes"].items():
m = out.setdefault(name, {**row, "scans": 0, "scans_by_node": {}})
m["scans"] += row["scans_in_window"]
m["scans_by_node"][node] = row["scans_in_window"]
for m in out.values():
m["window_days"] = window # weakest node bounds the claim
return out
Two rules fall out of that function. Scans are summed over every node, so one replica using an index keeps it alive. And the window is the minimum across nodes, so a replica added last Tuesday caps every claim at one week until it has aged. A node the agent could not reach makes the run fail closed: no verdicts, a message saying which node is missing.
Step 2: Deterministic Exclusions Before Any Judgement
Most of the dangerous mistakes are ruled out by code. An index is removed from the drop candidates, whatever its scan count, when any of these holds:
- It enforces something.
indisprimary,indisunique,indisexclusion, orbacks_constraint. Uniqueness checks on insert do not incrementidx_scan, so a unique index can show zero scans forever while rejecting duplicates every second. - It is the replica identity (
indisreplident). Logical replication uses it to locate rows on the subscriber. - The evidence window is under 35 days. Long enough to contain one month-end close. Tables the service catalog tags as
quarterlyneed 100. - It belongs to a partition and is attached to a parent index.
DROP INDEX CONCURRENTLYdoes not work on a partitioned index, and dropping one child's index alone is refused. These go to a human as a group. - The table is younger than the window, or the index was created inside it. New code may not be deployed yet.
What survives is ranked by bytes × index_writes, which puts the large index on the hot table first. A 40 MB dead index on a table that gets ten writes a day is true and not worth a pull request.
Step 3: Duplicates, Redundant Prefixes, and Invalid Leftovers
These three categories do not depend on usage statistics at all, which makes them the safest wins and the right place to start.
-- exact duplicates: same table, columns, opclasses, ordering, expressions, predicate
SELECT indrelid::regclass AS tbl,
array_agg(indexrelid::regclass ORDER BY indisprimary DESC, indisunique DESC) AS same
FROM pg_index
GROUP BY indrelid, indkey::text, indclass::text, indoption::text, indcollation::text,
coalesce(pg_get_expr(indexprs, indrelid), ''),
coalesce(pg_get_expr(indpred, indrelid), '')
HAVING count(*) > 1;
-- invalid: failed CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY
SELECT indexrelid::regclass, indisready
FROM pg_index WHERE NOT indisvalid;
Exact duplicates usually come from an ORM declaring index: true on a column that a later migration also indexed by hand. The agent keeps the first element of each array, which is the one backing a constraint if any does, and proposes dropping the rest.
Invalid indexes are the purest waste in the catalog. The planner never uses them, but an invalid index that is still marked ready is updated on every write. Names ending in _ccnew or _ccold are the debris of an interrupted REINDEX CONCURRENTLY. The schema migration review agent stops new ones at the door; this is the cleanup for the ones already inside.
Prefix redundancy needs more care. A B-tree on (tenant_id) is covered by one on (tenant_id, created_at) when both are plain, non-unique, non-partial, and use the same operator class. The wrapper computes that structurally and marks the narrow index redundant_prefix, but it does not auto-propose the drop. The narrow index is smaller, and a query doing millions of point lookups a minute may be measurably slower on the wide one. That verdict goes to the model with the size ratio and the scan counts of both.
Step 4: Bloat, Measured Sparingly
Estimating index bloat from catalog arithmetic is unreliable, and measuring it properly is expensive, so the agent measures only the top candidates by size, on a replica, with the pgstattuple extension:
SELECT avg_leaf_density, leaf_fragmentation, index_size
FROM pgstatindex('public.orders_customer_id_idx');
pgstatindex reads the entire index, so it runs against at most five indexes per night and never on the writer. A freshly built B-tree sits near 90% leaf density, the default fillfactor. Below 50% on an index over a gigabyte, the agent proposes REINDEX INDEX CONCURRENTLY and notes the two things that make it fail: it needs free disk equal to the new index, and it waits behind any long-running transaction, which is how the _ccnew leftovers get made. An index that is both unused and bloated gets a drop proposal only. Nobody should rebuild what they are about to delete.
Step 5: The Model Classifies One Index and Writes the Migration
The model receives one index: its definition, merged scan counts per node, the window, size, write rate, the flags from Steps 2 to 4, and the list of normalized queries from pg_stat_statements that mention the table. It never sees parameter values or rows.
VERDICT_SCHEMA = {
"type": "object",
"properties": {
"verdict": {"type": "string", "enum": [
"drop", "drop_duplicate", "drop_invalid", "reindex",
"keep", "needs_human"]},
"evidence": {"type": "string"},
"what_breaks_if_wrong": {"type": "string"},
"queries_that_could_use_it": {"type": "array", "items": {"type": "string"}},
},
"required": ["verdict", "evidence", "what_breaks_if_wrong"],
}
SYSTEM = (
"You review one PostgreSQL index. You are given catalog facts and usage "
"counters summed over the primary and all replicas.\n"
"drop: zero scans on every node for the whole window, and no query in the "
"provided statement list filters, joins, or orders on its leading column.\n"
"needs_human: the leading column is a foreign key to another table (deleting "
"parent rows needs this index and may happen rarely); the index is an "
"expression index (ANALYZE keeps statistics for it that other plans may "
"depend on); the table name suggests archival, audit, billing-period, or "
"purge work; or it is a redundant prefix with more scans than its superset.\n"
"keep: any node shows scans, however few. Say which node.\n"
"Cite the numbers given. Never argue that an index is 'probably' unused."
)
The foreign-key rule deserves its sentence. An index on order_items(order_id) may never be scanned by application queries, yet deleting a row from orders makes Postgres look up the referencing rows, and without that index each delete is a sequential scan of the child table. If parent deletes only happen in a retention job, the counter will read zero right up to the night the job takes six hours.
For verdicts that reach drop, the model's second job is mechanical: render the migration in the dialect the target repository already uses. The wrapper detects that from the migrations directory and supplies a template, because the part every framework gets wrong by default is the transaction.
# db/migrate/20261120093000_drop_unused_index_events_on_legacy_ref.rb
class DropUnusedIndexEventsOnLegacyRef < ActiveRecord::Migration[7.1]
disable_ddl_transaction!
def up
execute "SET lock_timeout = '3s'"
remove_index :events, name: :index_events_on_legacy_ref, algorithm: :concurrently
end
def down
add_index :events, :legacy_ref, name: :index_events_on_legacy_ref, algorithm: :concurrently
end
end
DROP INDEX CONCURRENTLY cannot run inside a transaction block, and a plain DROP INDEX takes an ACCESS EXCLUSIVE lock on the table, queueing every read and write behind it. In Flyway the equivalent switch is executeInTransaction=false in a script config file; in Alembic it is op.get_context().autocommit_block(). The lock_timeout makes the statement give up instead of waiting behind a long transaction while new queries stack up.
The PR body carries the evidence row, per-node scan counts, the window, the size reclaimed, and the full pg_get_indexdef output. Postgres has no invisible-index switch to flip back, so the rollback plan is rebuilding the index, and the reviewer should see how long that took last time: the agent includes the table size and an estimate from the most recent concurrent build it has on record.
Guardrails That Live in Code
- The database role is
pg_monitorand nothing else. NoSELECTon application tables, no DDL. It is the same role as the Postgres MCP server, plusEXECUTEonpgstatindexon replicas only. - The agent never executes a drop. Its only write is a pull request. The migration runs through the same pipeline, reviewers, and deploy window as any other schema change.
- One index per PR, at most three open per database. After each merge the agent watches for 48 hours: mean execution time of the table's statements in
pg_stat_statements, andseq_scanon the table. A rise in either pauses further proposals for that database and posts the revert migration. - Constraint-backing indexes are filtered by the wrapper regardless of what the model returns. The model may call one unused; the code drops the suggestion.
- Drops on tables above a size threshold need a second approver, following the approval-gate pattern, because the rollback is a multi-hour index build.
Run it in shadow mode for the first month, which costs nothing because the evidence window needs a month anyway. The agent posts its ranked table weekly, and the team marks which verdicts they would have accepted. The metric that matters is how many drop verdicts a reviewer overturned with knowledge the agent lacked. Each one is a missing exclusion rule.
Honest Limits
Zero scans in a window is evidence about the window. A 35-day window misses the annual tax export, the disaster-recovery runbook query, and the admin page somebody opens when a customer escalates. The catalog tags and the needs_human heuristics shrink that gap; they do not close it. The reviewer who knows the table is the control, which is why the PR goes to the owning team and not to the platform team.
Scan counts say the planner chose the index, not that it needed to. An index with forty scans a month might be serving a query that would be equally fast on another index, and one with zero scans may be about to be used by code merging tomorrow. The agent only argues the zero-scan case, and it cannot see undeployed branches.
Expression and partial indexes resist structural comparison. Two predicates can be logically equivalent and textually different, and the duplicate query will not match them. The agent reports what it can prove equal and stays quiet about the rest.
Finally, the saving is mostly write throughput and WAL volume, and those are hard to put in dollars beforehand. Storage on RDS does not shrink when an index is dropped; the space returns to the database, not to the bill. The honest pitch is fewer writes per row, faster vacuums, and less replica lag, with the query-tuning basics covering the other half of the job: the indexes you are missing.