Write Amplification Cascade

Each logical application write triggers multiple physical writes through index maintenance, WAL generation, MVCC versioning, and replication, causing actual disk IOPS to exceed the provisioned I/O ceiling while the logical write rate appears modest.

A single SQL UPDATE on a PostgreSQL table generates far more physical I/O than it appears. Consider UPDATE orders SET status = 'shipped' WHERE id = 12345 on a table with 5 secondary indexes. The physical write path is: (1) write the new heap tuple version to the data page; (2) write a dead tuple (MVCC keeps the old version for active snapshots); (3) write 5 index page updates: one per index, each index entry must point to the new tuple's location; (4) write the WAL record covering all of the above; (5) if synchronous_commit = on, wait for the WAL to fsync; (6) replicate the WAL to each standby. For a 100-byte row update, the physical write footprint may be 10–50KB across all these pages.

Write amplification factor (WAF) = physical bytes written / logical bytes written. For a simple row update with 5 indexes, WAF may be 20–100x. At 5,000 updates/ second, this translates to 100,000–500,000 IOPS on a system that may only have 3,000–16,000 provisioned IOPS (GP2 EBS: 3,000 IOPS; GP3: up to 16,000 IOPS; io1/io2: up to 64,000 IOPS).

The cascade happens when write amplification causes the storage I/O to saturate. Once I/O saturates, all disk operations queue. WAL fsync latency increases from <1ms to >100ms, which means all write transactions stall waiting for WAL flush. Replication falls behind because WAL cannot be sent faster than it can be flushed. Checkpoints take longer because dirty page flushes queue behind other writes. Autovacuum falls behind because it too is waiting for I/O, allowing dead tuple accumulation (index bloat). The system enters a feedback loop of degradation.

Write amplification is structural and cannot be mitigated by adding RAM alone. Adding shared_buffers reduces read I/O but not write I/O: dirty pages must eventually be flushed to disk at the WAL/checkpoint rate.

"Write amplification" is not one thing, and collapsing it into a single bucket hides where the cost actually comes from and which mitigation applies. Four distinct kinds compound in the scenario above, and they do not all move together:

Application-level amplification. Business logic issues more physical writes than the triggering event logically requires: an audit-log insert alongside the row update, a denormalized copy updated in a second table, a cache-invalidation write. This is a design choice, not an engine property, and the fix is architectural (batch it, make it async, or remove the redundancy), not a database knob.

Secondary-index amplification. Every index on a table is a second (or third, or fourth) physical structure that must be kept consistent with the base table on every write that touches an indexed column. This is the "5 index page updates" in the example above: it is linear in index count and is the same structural cost whether the base table is a PostgreSQL heap or an InnoDB clustered table, though the exact page-write mechanics differ by engine.

WAL and log amplification. The engine's durability log records more bytes than the logical change, so that a crash can replay it. PostgreSQL's full_page_writes is the concrete mechanism above: the first touch of a page after a checkpoint logs the whole 8KB page, not the changed bytes. MySQL binlog amplification is a related but distinct mechanism: with binlog_format = ROW (the modern default), each row change is logged in full in the binary log independent of InnoDB's own redo log, so a single UPDATE can generate both an InnoDB redo log record and a full-row binlog record, two separate log amplification paths rather than one.

Storage-engine compaction amplification, and this is the one that must not be collapsed into the others. Everything above assumes a B-Tree engine with in-place updates: PostgreSQL and InnoDB write a new or updated page roughly once per touch, so total write amplification (physical bytes written / logical bytes written), once WAL and index overhead are included, typically lands around 2-4x for a well-tuned B-Tree engine, even though the PostgreSQL example above shows a higher figure once five indexes and MVCC are counted per row. An LSM (log-structured merge-tree) engine, such as RocksDB, Cassandra's or ScyllaDB's SSTable storage, works on a fundamentally different model: writes land in an in-memory memtable and flush to an immutable SSTable on disk, and the same logical row is rewritten again and again as compaction merges SSTables across levels to bound read cost and reclaim space from overwrites and deletes. Every compaction pass that touches a key's SSTable rewrites that key's bytes to a new file, so a single row can be physically rewritten many times over its lifetime purely from compaction, independent of how many times the application logically wrote it. This is why a poorly-tuned LSM engine can see write amplification in the 10-30x range: it is not the same phenomenon as B-Tree index or WAL overhead, it is a cost paid to keep read amplification (SSTable count per read) and space amplification bounded, and it trades in the opposite direction from a B-Tree engine, which pays most of its overhead per logical write rather than as an ongoing background rewrite of data already on disk. See lsm_compaction_debt for the failure mode this specific mechanism produces when compaction falls behind the write rate.

InnoDB's own log write amplification differs from PostgreSQL's WAL in a way worth naming specifically, not just by analogy. InnoDB maintains two separate logs where PostgreSQL has one: the redo log (physical, for crash recovery, analogous in role to PostgreSQL WAL) and the undo log (logical, holding the previous version of a row for MVCC readers and for rollback, stored in the InnoDB system tablespace or dedicated undo tablespaces rather than as WAL records). A single UPDATE therefore generates a redo log record for the physical page change and an undo log record for the pre-image, and unlike a PostgreSQL dead tuple (which lives in the same heap page and is reclaimed by VACUUM), InnoDB's undo records are reclaimed by the purge thread, a background process distinct from anything PostgreSQL runs. Long-running transactions hold back purge the same way they hold back PostgreSQL's vacuum horizon, but the accumulating structure (undo log/history list length) and the operational signal (history list length growing in INFORMATION_SCHEMA.INNODB_TRX-adjacent monitoring) are InnoDB-specific, not a renamed version of PostgreSQL bloat.