Production failure modes and the index audit playbook

A DELETE FROM posts WHERE id = 42 with ON DELETE CASCADE takes 7 minutes and holds row-level locks the entire time. Root cause: comments(post_id) is not indexed. Postgres does a sequential scan of 200M comments to find the rows to delete. One missing index on a foreign key caused a production incident. This lesson catalogs the failure modes and the playbook to find them before they find you.

The seven failure modes

1. Missing index on FK column

Foreign-key columns are not automatically indexed. When you add REFERENCES posts(id), Postgres creates an index on posts(id) (the referenced column), but not on comments(post_id) (the referencing column). ON DELETE CASCADE on the parent table does a sequential scan of the child table to find rows to cascade to.

-- This is the dangerous pattern:
ALTER TABLE comments ADD CONSTRAINT fk_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE;
-- No index on comments(post_id) -- DELETE FROM posts is now O(n) on comments

-- Fix:
CREATE INDEX CONCURRENTLY idx_comments_post_id ON comments(post_id);

Rule: index every FK column unless you can prove the parent table is never deleted and no queries filter the child by the FK.

2. Implicit type coercion defeats the index

-- orders.user_id is BIGINT; index on orders(user_id) exists
SELECT * FROM orders WHERE user_id = '42';  -- '42' is TEXT

Postgres applies an implicit cast from TEXT to BIGINT — but to do so on every row, it cannot walk the B-tree (the cast is applied to the column, not the constant). Result: Seq Scan. Diagnosis: EXPLAIN ANALYZE on the query. Fix: use typed parameters ($1::BIGINT or the correct type in the query builder).

Other common coercion traps: created_at::date = '2024-01-01' defeats an index on created_at; use created_at >= '2024-01-01' AND created_at < '2024-01-02' instead.

3. Stale statistics cause wrong plan choice

The planner’s cost model depends on pg_statistic data — value distributions, row counts, correlations. After a bulk insert, large delete, or schema change, autovacuum schedules an ANALYZE, but by default only after 20% of rows change. Until ANALYZE runs, the planner has wrong estimates.

Symptom: EXPLAIN shows rows=50 but actual was rows=500,000 — a 10,000x miss. The planner picked a nested-loop with an index that is catastrophic at that row count.

Fix: run ANALYZE table_name after bulk data operations. For tables with highly skewed data (e.g., 90% of orders are for 5 workspaces), use CREATE STATISTICS (ndistinct) ON workspace_id, status FROM orders to give the planner better multi-column statistics.

4. Index bloat slows scans

Indexes accumulate dead entries from updates and deletes. Postgres 14+ does bottom-up index deletion (aggressive cleanup on hot leaf pages without waiting for vacuum), but heavy-update tables can still develop significant bloat over time.

Symptom: index size is 5-10x the expected size for the row count; queries are slow despite using the index.

-- Rebuild the index without blocking reads/writes
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

REINDEX CONCURRENTLY (Postgres 12+) builds a new index in the background and then swaps it in. Duration is similar to the initial CREATE INDEX CONCURRENTLY build.

5. Wrong composite column order

A composite index (status, workspace_id) where most queries filter by workspace_id alone does not accelerate those queries. The planner falls back to Seq Scan.

This is the leading-column rule violation discussed in lesson 02. In production, it often appears when an index was designed for one query but a second hot query was added later with a different filter pattern.

Fix: add a second index with the correct leading column, or redesign the composite if the original query shape is less common.

6. JSONB GIN cardinality bomb

A JSONB column where documents contain millions of unique keys creates a GIN index with millions of distinct posting-list entries. This index overwhelms shared_buffers (evicting hot heap pages) and has enormous write cost (updating the posting list for every unique key on each insert).

Symptom: GIN index size is 10-50x the underlying column size; shared_buffers cache-hit ratio drops; insert latency spikes after adding the GIN.

Fix: use expression B-tree indexes on known-hot fields instead of a GIN on the whole column:

-- Instead of: CREATE INDEX ON events USING GIN (payload)
-- Use:
CREATE INDEX ON events ((payload->>'event_type'));
CREATE INDEX ON events ((payload->>'user_id'));

This indexes only two known-hot fields with small B-tree indexes instead of a monolithic GIN.

7. “Has an index” but still slow

The most insidious failure: the right index exists, but the plan is still slow because:

• The index is not covering (heap fetches dominate at scale).
• The ORDER BY column is not in the index (full sort after index scan).
• The index’s leading column does not match the most selective filter.

Diagnosis is always EXPLAIN (ANALYZE, BUFFERS) on the exact query with realistic parameters — not a contrived test query, not a staging environment with 100x fewer rows.

The quarterly index audit playbook

Run this playbook every quarter on every production Postgres database. It typically takes 1-2 hours and returns 10-30% storage savings plus measurable write-throughput improvements.

Step 1: find unused indexes

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Caveats: (a) reset pg_stat_reset() happens on restart — if the server was recently restarted, the scan count is misleading; use a monitoring system that tracks these over time. (b) Some indexes are used only by periodic batch jobs — cross-reference pg_stat_statements over a longer window. (c) Unique indexes on constraint columns (UNIQUE, exclusion constraints) may show idx_scan = 0 but are still enforcing the constraint on every insert.

Step 2: find bloated indexes

-- Requires pgstattuple extension
SELECT
  indexrelid::regclass AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS total_size,
  round(100 * (approx_free_space + dead_tuple_len)::numeric
    / GREATEST(1, pg_relation_size(indexrelid)), 2) AS bloat_pct
FROM (
  SELECT indexrelid, (pgstattuple_approx(indexrelid::regclass)).*
  FROM pg_stat_user_indexes
) AS s
ORDER BY bloat_pct DESC;

Indexes with bloat_pct over 30% are candidates for REINDEX CONCURRENTLY. Schedule during off-peak.

Step 3: find missing indexes

Pull the top-20 queries from pg_stat_statements by total_exec_time. For each, run EXPLAIN (ANALYZE, BUFFERS) with realistic parameters. Look for Seq Scan on tables over 10k rows where the filter is selective (rows returned is small fraction of total). Each such query is a missing-index candidate.

Step 4: find redundant indexes

Two indexes are redundant when one’s key columns are a prefix of another’s. idx_on_a and idx_on_a_b — the idx_on_a is redundant if the idx_on_a_b composite is always used instead. Drop the shorter one.

-- Find potential duplicates (manual inspection required)
SELECT
  i.indexrelid::regclass AS index_name,
  array_to_string(array_agg(a.attname ORDER BY x.n), ', ') AS columns
FROM pg_index i
JOIN pg_class c ON c.oid = i.indrelid
CROSS JOIN LATERAL unnest(i.indkey) WITH ORDINALITY AS x(attnum, n)
JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = x.attnum
GROUP BY i.indexrelid, i.indrelid
ORDER BY i.indrelid, columns;

Index strategy across environments

Different environments have different needs:

Development: minimal indexes — only PKs and essential unique constraints. The goal is fast schema iteration, not query performance. Adding all production indexes slows migrations and obscures schema design (you should not lean on indexes to compensate for bad schema choices).

Staging: mirror production indexes when running load tests or benchmarking; skip them for pure functional testing. The staging index set helps catch “works in dev, slow in staging” before production.

Production: the full deliberate index set, with all the controls described in this unit — covering composites, partial indexes, INCLUDE, regular audits. Production index additions go through migration review alongside the query that requires them.

Replicas / analytics copies: may have additional OLAP-specific indexes (wide composites, BRIN on date ranges, GIN for full-text) that would be too expensive to maintain on the OLTP primary. If using logical replication to a dedicated analytics Postgres, add those indexes only on the replica.

Strategic posture

The single most common performance postmortem at production scale: “missing index on this hot query.” The second most common: “too many indexes slowing writes.” Both have the same root cause — indexes were not treated as part of the design.

Senior teams:

• Add the index to the migration that ships the feature (query + index in one PR).
• Review indexes in code review alongside the SQL.
• Own index strategy at the platform level, not per-team.
• Audit quarterly.

The cost of this discipline: one checklist item per PR touching SQL. The cost of not doing it: a 3am page when a customer dashboard times out because someone added a new FK last week without indexing the referencing column.

slow_query: SELECT id, total_cents FROM orders WHERE workspace_id = 42 AND status = 'pending' ORDER BY created_at DESC LIMIT 50;
execution_time: 4280 ms
rows_returned: 50

EXPLAIN ANALYZE:
Limit  (cost=320..380 rows=50 width=24) (actual time=4271..4280 rows=50 loops=1)
->  Sort  (cost=320..18420 rows=180000 width=24) (actual time=4270..4275 rows=50 loops=1)
      Sort Key: created_at DESC
      Sort Method: top-N heapsort  Memory: 32kB
      ->  Index Scan using idx_orders_workspace_status on orders
            (cost=0.42..18000 rows=180000 width=24) (actual time=0.02..3800 rows=178240 loops=1)
            Index Cond: ((workspace_id = 42) AND (status = 'pending'::text))

Index definitions:
idx_orders_workspace_status: btree (workspace_id, status)
idx_orders_workspace_status size: 1.2 GB
table size: 18 GB, row count: 80M

Statistics:
n_dead_tup: 12.4M (15% of total)
last_autovacuum: 14 days ago
last_analyze: 30 days ago