Write Amplification Compounds With Every Secondary Index
“Every secondary index on a write-heavy table multiplies the write I/O by exactly one additional B-tree update per insert, update, or delete: and that cost is permanent, not amortizable. ”
A table with 5 secondary indexes writes 6 B-tree pages for every 1 logical row write. On NVMe-backed PostgreSQL at 50k writes/second with 5 indexes, that is 300k I/O operations per second from index maintenance alone. Indexes are added one at a time, each individually justified: the compound effect is never evaluated at time of addition, and it accumulates silently until the write path saturates.
Why It Matters
The danger of write amplification is that it is invisible until it is catastrophic. Each index addition is a local optimization decision: an engineer adds an index to speed up a specific query, tests it in isolation, and ships it. What no one evaluates is the cumulative write tax already imposed by the five other indexes on that table. At 50k logical writes/second, the true I/O burden has grown to 300k IOPS before anyone notices the write latency climbing.
WAL amplification compounds the problem further. Every index update generates WAL records alongside the heap update. With 5 secondary indexes, WAL volume scales roughly 4x versus a no-index baseline. This increases replication lag under write pressure, checkpoint pressure on the primary, and recovery time after a crash. The index that saved 5ms on one read query costs 200MB/s of extra WAL volume at scale.
The systemic failure mode is index bloat. Writes to a B-tree index create new pages but do not immediately reclaim pages from deleted or updated rows. Dead index pages accumulate until autovacuum reclaims them: but autovacuum cannot keep up with a sustained write workload at high velocity. The result is an index that is twice the size it needs to be, slower for reads than it should be, and still generating full write amplification on every insert.
Failure Modes
- ·Index bloat from unreclaimed dead pages slowing both reads and writes simultaneously
- ·WAL saturation from index update records flooding the write-ahead log under sustained write load
- ·Autovacuum falling behind dead tuple accumulation, causing table bloat and vacuum lock contention
- ·Write throughput ceiling hit at the I/O layer while CPU and network appear healthy
- ·Checkpoint amplification from index WAL volume causing fsync storms at checkpoint boundaries
Amplification Risks
- ⚡Each index added multiplies the total write I/O: 6 indexes produce 6x write amplification, not 1x plus a small overhead
- ⚡WAL amplification from indexes increases replication lag, which delays failover readiness under write pressure
- ⚡Index bloat plus dead tuple accumulation combines into a vacuum storm that can cause transient write stalls
Temporal Behavior
- ⟳Index bloat accumulates gradually and becomes visible only after sustained high-write periods
- ⟳Autovacuum catch-up cycles create periodic write amplification spikes as dead pages are reclaimed
- ⟳WAL volume grows monotonically with index count and only shrinks if indexes are dropped
Boundary Implications
- ◈The I/O boundary of the primary datastore is where write amplification becomes a failure surface
- ◈WAL amplification crosses the replication boundary, degrading replica consistency indirectly
- ◈The operational responsibility boundary for index management must sit with the team owning the write path, not the team adding read optimizations
Topology
- ·Primary datastore nodes with high write throughput require explicit index budget tracking as a topology constraint
- ·Replica nodes absorb the full WAL amplification from index maintenance: replication lag grows proportionally
- ·Write-heavy topology paths must account for per-index I/O multiplier when sizing primary storage
- ·Standby promotion cost increases with index count due to WAL replay amplification
Scaling
- ·Write amplification scales linearly with index count but the operational impact is non-linear at I/O saturation points
- ·Horizontal sharding reduces per-shard write volume but each shard carries the full index maintenance overhead
- ·At high write scale, partial indexes and filtered indexes reduce amplification without eliminating index utility
- ·Index count must be re-evaluated at each order-of-magnitude write volume increase
Resilience
- ·Systems with high index counts recover more slowly after a crash due to WAL replay amplification
- ·Autovacuum contention during peak write load reduces system resilience by blocking cleanup of dead tuples
- ·Removing unused indexes before failure conditions arrive is a resilience investment, not a cleanup task
Governance Implications
- ·Index additions to write-heavy tables must require a write amplification impact assessment before approval
- ·Total index count per write-heavy table should be tracked as a first-class capacity metric
- ·Unused indexes (idx_scan = 0) must be identified and removed on a regular audit cycle
- ·pg_stat_user_indexes must be reviewed before any index is added to a table exceeding 10k writes/minute
Evolution Implications
- ·Migrating a write-heavy table requires evaluating whether the full index set is still necessary
- ·Adding a new high-cardinality write path requires an index audit of the target table before launch
- ·Introducing event sourcing or CQRS removes write amplification from the write path by eliminating secondary indexes on the event log
Mitigation Patterns
- →Audit pg_stat_user_indexes for idx_scan = 0 and drop unused indexes before adding new ones
- →Use partial indexes (WHERE clause) to limit index maintenance to the relevant row subset
- →Use covering indexes to consolidate multiple read optimizations into a single index
- →Set autovacuum cost delay and scale factor aggressively on write-heavy tables to prevent bloat accumulation
- →Track IOPS per index using EXPLAIN ANALYZE before committing an index to production
Cross-References