Indexing
Write Amplification
IntermediateHow a single application write triggers multiple physical I/O operations across WAL, heap pages, and indexes, why this limits write throughput, and how LSM trees trade read amplification for lower write amplification.
Step 1 of 5
Single Write: Baseline Amplification
Every PostgreSQL INSERT generates at minimum two physical writes: one to the Write-Ahead Log (WAL) and one to the heap page. The WAL write is sequential and cheap. The heap page write is random if the table is fragmented. With no secondary indexes, write amplification factor is 2.
1
Application Write (INSERT)1 logical write
1WAL record (sequential)
1Heap page (random)
Total physical writes per INSERT
2xamplification
No indexes: WAL + heap = 2 physical writes per INSERT
Key Takeaways
- →Every write generates at least 2 physical I/Os: WAL record + heap page
- →WAL is sequential: heap page writes are random and more expensive on spinning disk
1 / 5