Postgres Indexing skill for Claude Code

An index earns its place when a named, frequent query uses it (proven by EXPLAIN), it is the narrowest index that serves that query, and its write and storage cost is acceptable. Every index slows every INSERT and most UPDATEs on its table; add with evidence, drop with evidence.

Workflow

- [ ] 1. Collect the queries the index must serve, with real parameters
- [ ] 2. Inspect existing indexes on the table (avoid duplicates/near-duplicates)
- [ ] 3. Pick the index type and column order (tables below)
- [ ] 4. Narrow it: partial predicate, INCLUDE columns, expression
- [ ] 5. Build it CONCURRENTLY, check it is valid
- [ ] 6. Prove it with EXPLAIN (ANALYZE, BUFFERS) before/after
- [ ] 7. Audit for unused/duplicate indexes to drop (with user confirmation)

1-2. Inspect before adding

\d orders                                       -- existing indexes and constraints
SELECT indexrelid::regclass AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan, pg_get_indexdef(indexrelid) AS def
FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid)
WHERE relid = 'orders'::regclass
ORDER BY pg_relation_size(indexrelid) DESC;

If an existing index has your proposed columns as a prefix, or your proposed index is a prefix of an existing one, extend or reuse rather than add.

3. Choose the type

Query pattern	Index
=, <, >, BETWEEN, ORDER BY, IN, LIKE 'abc%' (C collation or text_pattern_ops)	B-tree (default)
JSONB containment @>, key existence ?	GIN (jsonb_path_ops if only @>)
Array @>, &&	GIN
ILIKE '%term%', similarity	GIN with pg_trgm (gin_trgm_ops)
Full-text @@ to_tsquery	GIN on tsvector (stored generated column or expression)
Ranges &&, geometry, nearest neighbour, exclusion constraints	GiST (or SP-GiST for some geometric/text cases)
Huge append-only table filtered by a column correlated with insert order (time)	BRIN
Equality only, large keys, never range	Hash (rarely better than B-tree; default to B-tree)
Vector similarity	HNSW/IVFFlat via pgvector (see vector-search)

4. Column order in multicolumn B-trees

Rule: equality columns first, then the range or sort column, then optional INCLUDE columns for index-only scans.

-- Query: WHERE tenant_id = $1 AND status = 'open' ORDER BY created_at DESC LIMIT 50
CREATE INDEX CONCURRENTLY tickets_tenant_status_created_idx
  ON tickets (tenant_id, status, created_at DESC);

• A range predicate stops further index columns from narrowing the scan. (created_at, tenant_id) for the query above reads every tenant’s rows in the time range.
• ORDER BY a, b DESC needs index order matching all directions (or all reversed).
• Leading-column rule: before PG18, an index on (a, b) is effectively useless for WHERE b = ?. PG18 adds B-tree skip scan, which helps when a has few distinct values; do not rely on it for high-cardinality leading columns.

5. Narrow it

Partial - index only the rows queries ask for:

CREATE INDEX CONCURRENTLY jobs_pending_run_at_idx
  ON jobs (run_at) WHERE status = 'pending';
-- The query's WHERE must imply the predicate: WHERE status = 'pending' AND run_at <= now()

Good fits: soft-deleted rows excluded (WHERE deleted_at IS NULL), queue tables, rare flags. A partial index is not used if the predicate is a bind parameter the planner cannot prove matches (e.g. status = $1 with a generic plan).

Covering - avoid heap fetches for hot read paths:

CREATE INDEX CONCURRENTLY orders_customer_placed_idx
  ON orders (customer_id, placed_at DESC) INCLUDE (total_cents, status);

Index-only scans also need the visibility map to be current; check Heap Fetches in EXPLAIN and vacuum health if it stays high.

Expression - index exactly what the WHERE clause computes:

CREATE UNIQUE INDEX CONCURRENTLY users_email_lower_key ON users (lower(email));
-- Must query with the same expression: WHERE lower(email) = lower($1)
CREATE INDEX CONCURRENTLY events_payload_type_idx ON events ((payload ->> 'type'));

Expression functions must be IMMUTABLE. date(timestamptz_col) is not (depends on time zone); index (created_at AT TIME ZONE 'UTC')::date or rewrite the query to a range.

Trigram search:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY products_name_trgm_idx ON products USING gin (name gin_trgm_ops);

BRIN for time series (tiny, but only useful when physical order tracks the column):

CREATE INDEX CONCURRENTLY metrics_recorded_brin ON metrics USING brin (recorded_at);

6. Build safely

SET lock_timeout = '5s';          -- fail fast instead of queueing behind long transactions
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_id_idx ON orders (customer_id);

• CONCURRENTLY does not block reads or writes but takes longer, waits for existing transactions, and cannot run inside a transaction block. Many migration tools wrap each migration in a transaction; disable that per migration (Rails disable_ddl_transaction!, Django atomic = False, Prisma/Drizzle: separate raw SQL migration run outside a transaction, golang-migrate/Flyway: one statement per file).
• A failed concurrent build leaves an INVALID index that still costs writes. Find and drop it, then retry:

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY IF EXISTS orders_customer_id_idx;

• Monitor progress: SELECT * FROM pg_stat_progress_create_index;
• Speed up big builds in a session: SET maintenance_work_mem = '2GB'; max_parallel_maintenance_workers controls parallel B-tree builds.
• Unique constraints on existing tables: CREATE UNIQUE INDEX CONCURRENTLY ... then ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX ....
• Rebuild bloated indexes without blocking: REINDEX INDEX CONCURRENTLY idx_name; (PG12+).

7. Prove it

EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;   -- before and after, same parameters

Done when the target query uses the new index (Index Scan, Index Only Scan, or Bitmap Index Scan on it), buffers and time drop materially, and the planner is not choosing it only because the table is tiny in dev.

To test an index idea without building it on a big table, the hypopg extension creates hypothetical indexes visible to plain EXPLAIN (not ANALYZE), if it is installed.

Auditing and dropping

Run the diagnostic queries in references/index-diagnostics.md for: unused indexes, duplicates and prefix-redundant indexes, invalid indexes, FK columns without indexes, bloat estimates, and HOT-update blockers.

Before dropping any index, confirm all of these and ask the user to approve the list:

• idx_scan = 0 over a window covering monthly jobs and reports. Check when stats were last reset: SELECT stats_reset FROM pg_stat_database WHERE datname = current_database(); PG16+ also has last_idx_scan in pg_stat_user_indexes.
• Usage on every replica: pg_stat_user_indexes is per-instance; a read replica may be the only place the index is used.
• It does not back a PRIMARY KEY, UNIQUE, or exclusion constraint (those enforce rules even with zero scans).
• Keep the CREATE INDEX statement in the PR so it can be restored.

DROP INDEX CONCURRENTLY IF EXISTS orders_legacy_status_idx;

Gotchas

• Indexing every column “just in case” multiplies write cost and blocks HOT updates: an UPDATE that changes any indexed column cannot be a heap-only tuple update.
• Low-selectivity columns alone (booleans, status with 3 values) rarely help unless partial or combined with a selective column.
• LIKE 'abc%' uses a B-tree only with C collation or text_pattern_ops/varchar_pattern_ops.
• An index on (a) is redundant if (a, b) exists, except when (a) is unique or much smaller and very hot.
• GIN indexes with fastupdate can make some reads slow while the pending list is large; for write-heavy tables watch gin_pending_list_limit.
• Indexes on foreign keys are not automatic in Postgres. Parent DELETE/UPDATE of the key scans the child table without one.
• Large index builds generate WAL quickly; watch replica lag during the build.

Output: index change note

**Query served:** tickets inbox (queryid 4410), 12k calls/hour.
**Index:** `CREATE INDEX CONCURRENTLY tickets_tenant_status_created_idx ON tickets (tenant_id, status, created_at DESC);`
**Evidence:** Seq Scan 640 ms / 98k buffers -> Index Scan 1.8 ms / 41 buffers.
**Cost:** 310 MB; insert p95 unchanged in load test.
**Dropped (approved):** tickets_status_idx (0 scans since 2026-07-01 on primary and both replicas).