Blast Radius Analysis · Content Management Platform

Missing Index Query Degradation

partialContained

operational failure · isolated propagation

Blast Radius

0/18 nodes

Time to Detect

If pg_stat_statements or auto_explain is configured with slow query logging, detectable within seconds of the first slow query. Without tooling, detected through user-visible latency spikes or alert firing: typically minutes.

Preventive Mitigations

Confidence

Strong

Failure Cascade

(no topology nodes mapped for this failure mode)

This failure mode is referenced in the scenario knowledge but has no connected topology nodes. Impact scope is scenario-level, all components should be considered potentially affected.

Severity at this step: partial

Detection Signals

Latency SpikeCpu Saturation

Recovery time estimate: Index creation via CREATE INDEX CONCURRENTLY: minutes to hours depending on table size. A 100M-row table may take 30–60 minutes. Queries will continue to be slow during index creation. After creation, performance improves immediately.

Mitigation Checklist(2 preventive, 2 reactive)

Review all migrations that drop indexes before deployingpreventsmedium

Add a step in the migration review process that lists all indexes being dropped. For each dropped index, verify which queries use it (pg_stat_statements, code search) before proceeding. Index renames must be handled as add-then-drop.

Create the missing indexpreventslow

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.

Enable auto_explain with log_min_duration for slow query detectionlow

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.

Use pg_stat_statements to identify high total_time querieslow

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.

Affected Systems

Workloads

Document Search WorkloadMixed OLTP (SaaS Core)Read-Heavy API Backend

Blast radius analysis is derived from structured topology and failure mode knowledge. It models structural propagation patterns, not measured production behavior. Actual incident scope depends on runtime conditions, traffic, and recovery actions in place at the time of failure.