Write Amplification

Write amplification is the ratio of bytes physically written to bytes logically changed, driven by indexes, replication, logging, and storage compaction.

databases

Definition

Write amplification measures how much physical write work a logical write causes. Inserting one row may update several indexes, append a write-ahead log record, write the data page, replicate the change, and later rewrite parts of the storage engine during compaction. The ratio of physical bytes to logical bytes is a key driver of disk wear, throughput limits, and tail latency.

Context and scope

Write amplification matters for write-heavy workloads, SSD endurance, and storage engines such as LSM trees. It also explains why adding indexes speeds reads but slows writes and increases storage.

Common misconceptions

Write amplification is only an SSD concern — it also consumes database CPU, I/O bandwidth, and replication capacity
One index costs one extra write — a single index update can touch multiple pages and log records
Batching always reduces amplification — very large batches can increase transaction and checkpoint work

Anti-patterns

Adding an index for every query without measuring write cost
Updating a wide row or a large JSON document when only one field changed
Using a tiny batch size that forces one flush per record

Related concepts

Related examples

Authoritative source

https://www.postgresql.org/docs/current/runtime-config-wal.html

One logical write, many physical writes

A single INSERT can touch more than one place. The write-ahead log records the change, the table heap receives a new tuple, each index receives an entry, and a replica may receive the same records. Background processes later write dirty pages and checkpoints. The physical bytes can be several times the logical row size.

-- Measure the physical write rate, not only the logical row count
SELECT pg_stat_database.datname,
       pg_size_pretty(pg_stat_database.temp_bytes) AS temp_bytes,
       pg_stat_database.xact_commit
FROM pg_stat_database
WHERE datname = current_database();

The exact ratio depends on row width, index count, fill factor, and workload. A narrow row with three indexes and a replica can amplify a write far more than a wide row with no indexes. Measure with the real schema and workload; estimates from a single table are misleading.

Indexes trade read speed for write cost

Every index must be updated on insert, update, and delete. A rarely used index still costs write bandwidth and storage. Review index usage with pg_stat_user_indexes and remove indexes that are not used by real queries. For a write-heavy table, fewer, more selective indexes are often better than many narrow ones.

Updates are not free

An update in PostgreSQL creates a new row version and marks the old one for vacuum. Updating a large JSON document or a wide row rewrites the whole row, even when one field changed. Normalize hot fields, avoid unnecessary updates, and batch small changes when the business logic allows. Frequent updates to the same rows also create bloat and vacuum pressure.

When to use this lens

Use write-amplification reasoning when write throughput, SSD endurance, replication lag, or checkpoint latency becomes a problem. The advantage is a concrete way to compare schema and batching decisions; the drawback is that it requires measurement rather than intuition. Compared to optimizing only query latency, reducing amplification often improves the whole system’s headroom, but it can make reads slower if indexes are removed.