Secondary Index Write Saturation
SEV-3, Limited ImpactFan-Out propagation · storage · Affects 0 scenario(s)
Severity Classification
Classified as PARTIAL based on failure mode severity. The fan out propagation pattern increases risk of broad impact beyond the initial failure point.
Propagation Chain
Origin component
Secondary Index Write Saturation begins at the source component. Trigger: Write throughput increases beyond the I/O capacity of the number of configured indexes (typically >5,000 inserts/s with >8 indexes).
Immediate (T+0) · Signal: Disk Saturation
Downstream dependents
Failure propagates to directly dependent components via synchronous calls or shared resources. Latency increases and error rates rise on affected dependencies.
5–15 minutes via database write latency monitoring (alert on INSERT p99 > 10ms sustained for > 60 seconds). WAL write throughput monitoring (pg_stat_bgwriter.buffers_backend) provides earlier warning at 2–5 minutes. The diagnostic signature: write latency increase proportional to write rate, with disk I/O at or near the storage IOPS/bandwidth limit. · Signal: Latency spike, connection timeout, or error rate increase on dependents
Downstream of dependents (fan-out)
Failure spreads to multiple downstream systems simultaneously. Retry storms may amplify load on the failing component.
Within minutes of initial failure · Signal: Multiple services reporting elevated error rates
Blast Radius
Write latency increase affects all services writing to the table, regardless of which indexes they use in reads. If the table is a core entity (orders, events, messages), write latency spikes propagate to all user-facing write paths. WAL saturation on PostgreSQL can also delay checkpoint processing, increasing recovery time and extending the blast radius to read performance (hot pages not flushed, checkpoint I/O competing with read I/O).
Contributing Factors
MySQL is known to be susceptible to Secondary Index Write Saturation under medium operational burden. Not deploying ProxySQL: direct application connections to MySQL primary cause connection storms during failover and cannot route reads to replicas
This operational trigger enables Secondary Index Write Saturation: Write throughput increases beyond the I/O capacity of the number of configured indexes (typically >5,000 inserts/s with >8 indexes)
This operational trigger enables Secondary Index Write Saturation: Addition of new secondary indexes to a table that is already near its write I/O ceiling
This operational trigger enables Secondary Index Write Saturation: GIN or GiST indexes on wide columns (JSONB, arrays, tsvector) at high write throughput
Remediation Plan
Query pg_stat_user_indexes for idx_scan counts; identify zero-use indexes as immediate drop candidates
Effort: Minutes to hours (on-call response)
Check current WAL write throughput via pg_stat_bgwriter and storage I/O metrics to confirm saturation
Effort: Minutes to hours (on-call response)
Drop or disable the least-used index first; monitor write latency to quantify improvement per dropped index
Effort: Minutes to hours (on-call response)
Query pg_stat_user_indexes to identify indexes with zero or near-zero scans over the past 30 days (idx_scan = 0). Drop indexes that are never used by any query. A single dropped index reduces write amplification by 1/N where N is the total index count. For a 10-index table, dropping 3 unused indexes reduces write I/O by 30%. Run EXPLAIN on all critical write queries to confirm the dropped indexes are not needed for any plan.
Effort: 1 day to 1 week
Replace full-column indexes with partial indexes that only cover the subset of rows accessed by common queries: CREATE INDEX CONCURRENTLY idx_active_orders ON orders (user_id) WHERE status IN ('pending', 'processing'). Only the fraction of writes that insert rows matching the WHERE clause update the partial index. For a table where 95% of reads access 10% of rows (e.g., active records), partial indexes can reduce index write volume by 90%.
Effort: 1 day to 1 week
For bulk import jobs (>100,000 rows), disable or drop non-unique indexes before the import and rebuild them afterward using CREATE INDEX CONCURRENTLY. PostgreSQL's COPY command with indexes disabled performs at 50,000–200,000 rows/second; with indexes maintained inline, throughput drops to 5,000–20,000 rows/second for tables with many indexes. After import, rebuild indexes in parallel (one CONCURRENTLY per index) to restore query performance.
Effort: 1 day to 1 week
Configure alerts for: disk saturation, latency spike, alert. Set thresholds to fire at 70% of critical level to allow response before full failure.
Effort: 1–3 days
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.