Post-Mortem Framework · Operational: Missing Index Query Degradation

Missing Index Query Degradation

SEV-3, Limited Impact

Isolated propagation · operational · Affects 1 scenario(s)

Severity Classification

Classified as PARTIAL based on failure mode severity.

Propagation Chain

Origin component

Missing Index Query Degradation begins at the source component. Trigger: New query pattern introduced without a corresponding index.

Immediate (T+0) · Signal: Latency Spike

Blast Radius

The slow query holds a connection for its full duration, consuming connection pool capacity. On a busy system, multiple concurrent slow queries can exhaust the connection pool, causing all other queries to queue at the pool boundary regardless of whether they use the missing index. Sequential scans also saturate shared_buffers and disk I/O, increasing latency for all concurrent queries. Blast radius can expand from the specific query to the full application.

Contributing Factors

Trigger Condition: New query pattern introduced without a corresponding indexoperational

This operational trigger enables Missing Index Query Degradation: New query pattern introduced without a corresponding index

Trigger Condition: Table grows past optimizer threshold causing switch from indoperational

This operational trigger enables Missing Index Query Degradation: Table grows past optimizer threshold causing switch from index scan to sequential scan

Trigger Condition: Index dropped accidentally during schema migrationoperational

This operational trigger enables Missing Index Query Degradation: Index dropped accidentally during schema migration

Remediation Plan

ImmediateRun EXPLAIN (ANALYZE, BUFFERS) on the slow query to confirm sequential scan

Run EXPLAIN (ANALYZE, BUFFERS) on the slow query to confirm sequential scan

Effort: Minutes to hours (on-call response)

ImmediateCheck pg_stat_user_tables for the table: high seq_scan rate confirms the pattern

Check pg_stat_user_tables for the table: high seq_scan rate confirms the pattern

Effort: Minutes to hours (on-call response)

ImmediateCREATE INDEX CONCURRENTLY on the relevant column(s): safe to run in production

CREATE INDEX CONCURRENTLY on the relevant column(s): safe to run in production

Effort: Minutes to hours (on-call response)

Short-TermCreate the missing index

EXPLAIN (ANALYZE) the slow query to confirm it is doing a sequential scan. CREATE INDEX CONCURRENTLY does not block reads or writes and is safe for production. For multi-column indexes, column order matters: put equality conditions before range conditions.

Effort: 1 day to 1 week

Short-TermEnable auto_explain with log_min_duration for slow query detection

SET auto_explain.log_min_duration = '1s'; logs the full query plan for any query exceeding 1 second. This identifies missing indexes before they become severe by catching slow queries early when tables are smaller.

Effort: 1 day to 1 week

Short-TermUse pg_stat_statements to identify high total_time queries

SELECT query, total_exec_time, calls, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; identifies the queries consuming the most cumulative time: these are the candidates for index optimization. Reset stats after index changes to measure improvement.

Effort: 1 day to 1 week

Short-TermAdd alerting for documented detection signals

Configure alerts for: latency spike, cpu saturation. Set thresholds to fire at 70% of critical level to allow response before full failure.

Effort: 1–3 days

Long-TermArchitecture review for Missing Index Query Degradation resilience

Conduct a structured architecture review focused on preventing recurrence. Review topology for blast radius reduction, mitigation coverage, and observability gaps. Consider whether the current architecture scenario should evolve.

Effort: 1–2 sprints

This post-mortem framework is derived from structured architecture knowledge. It provides an evidence-grounded starting point, not a substitute for a live incident review conducted by the team closest to the system. Adjust remediation priorities based on actual runtime observations.