Write amplification is when a single logical write triggers multiple physical writes to storage. It’s one of those problems that’s invisible at small scale and becomes a serious bottleneck as data grows. LSM trees were designed to minimize it. Replication inherently causes it. Understanding where it comes from helps you predict system behavior under load.

Sources of Write Amplification#

Storage layer: writing 4KB of data to a filesystem that uses 512-byte sectors requires updating multiple sectors plus the inode, the directory entry, and potentially metadata blocks. SSDs have their own write amplification: they erase in large blocks (128KB-512KB) and write in pages (4-16KB). Writing 4KB might require reading a 512KB block, modifying 4KB, and writing the entire 512KB block back.

LSM tree compaction: compaction strategies merge SSTables to reclaim space and improve read performance. A write to an LSM tree might be written to the memtable, flushed to L0, then rewritten during L0-to-L1 compaction, then rewritten again during L1-to-L2 compaction. The same data might be written 10-30x its original size over its lifetime.

Replication: a write to a primary database node is replicated to all replica nodes. With 3 replicas, one logical write becomes 4 physical writes (1 primary + 3 replicas). For synchronous replication, the client waits for all writes to complete, making latency sensitive to the slowest replica.

Secondary indexes: a row update in MySQL with 5 secondary indexes requires updating 6 structures (the primary row plus 5 index entries). For a write-heavy workload, this multiplies write cost by 6.

graph TD A[Single logical write: update user record] --> B[Primary row: 1 write] A --> C[Secondary index 1: 1 write] A --> D[Secondary index 2: 1 write] A --> E[Replica 1: 1 write] A --> F[Replica 2: 1 write] A --> G[WAL entry: 1 write] B --> H[Write amplification factor: 6x for 2 indexes, 2 replicas] C --> H D --> H E --> H F --> H G --> H style A fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style B fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style C fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style D fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style E fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style F fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style G fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff style H fill:#000000,stroke:#00ff00,stroke-width:2px,color:#fff

Why It Matters#

Write amplification directly affects the throughput ceiling of a write-heavy system. If you can sustain 100MB/s of writes to disk, and your write amplification factor is 10x, your logical write throughput is only 10MB/s.

SSD endurance is also affected: SSDs have a finite number of write cycles per cell. High write amplification wears out SSDs faster. A database workload with 20x write amplification on SSDs rated for 100 TBW (terabytes written) will exhaust them in one-fifth the expected lifetime.

Reducing Write Amplification#

Minimize secondary indexes on write-heavy tables: every index adds a write. Keep only indexes that are actually used by queries.

Use write-behind caching: batch multiple writes into one, reducing the write rate to the underlying storage.

Tune LSM compaction: Cassandra and RocksDB let you tune compaction aggressiveness. Less aggressive compaction reduces write amplification from compaction but increases read amplification and space amplification.

Asynchronous replication: don’t wait for all replicas to acknowledge before confirming the write. Trades durability guarantee for write latency. Only appropriate when you can tolerate small data loss windows.

At Oracle#

A heavily-indexed MySQL table was causing replication lag to spike during batch inserts. The batch job inserted into a table with 7 secondary indexes. Each row insert triggered 8 writes (1 row + 7 indexes). At 10,000 rows per second, this was 80,000 write operations per second hitting the database. Replication couldn’t keep up. We dropped 4 underused indexes, reducing write amplification from 8x to 4x. Replication lag dropped from 90 seconds to under 5. Same data, half the writes.

What I’m Learning#

Write amplification is the gap between the throughput you think you’re using and the throughput you’re actually consuming. It’s multiplicative across all the layers: storage, indexes, replication. Auditing your write amplification factor is a useful exercise when a write-heavy system approaches its capacity limit.

Have you diagnosed a write throughput problem that turned out to be high write amplification from indexes or replication?