How Databases Actually Write to Disk: WAL, Page Cache, and the Lie of fsync()

When you run db.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 42") and receive a COMMIT OK response, what actually happened?

If your mental model looks like this:

...your understanding of durability is incomplete.

If databases wrote directly to table files on every transaction, write throughput would collapse to a few dozen transactions per second. Disk physics (even on modern NVMe drives) makes random writes orders of magnitude more expensive than sequential streaming.

To achieve high throughput while guaranteeing ACID durability, modern relational and NoSQL engines rely on a multi-stage write pipeline involving in-memory buffer pools, append-only commit logs, operating system page caches, and hardware write queues.

Here is the exact journey of a database write, from memory to physical flash cells—and why standard operating system abstractions routinely lie about whether your data is actually safe.

1. The Anatomy of a Write: Memory to Platter

When an engine updates a row inside an indexed table, executing that mutation directly inside the table file (e.g., an 8KB or 16KB page on disk) requires random I/O. If you have 5 indexes on that table, a single update could turn into 5–10 random page writes across the disk.

Instead, databases split the write into two distinct workflows:

1. The Fast Path (Durability): Append a minimal delta of the change to a sequential file called the Write-Ahead Log (WAL).

2. The Lazy Path (State Persistence): Apply the update to an in-memory cached copy of the data page (marking it dirty), leaving the actual disk page file untouched for now.

Once the WAL record is guaranteed to be on non-volatile media, the transaction can be declared committed. If power is cut two milliseconds later, the in-memory dirty page is lost, but during crash recovery the engine reads the WAL sequentially and replays the mutation back into the page.

2. Why WAL Works: The Mathematics of Sequential vs. Random I/O

The efficiency of Write-Ahead Logging boils down to storage geometry and controller scheduling.

Characteristic	Random Page Writes (Direct-to-Table)	Sequential Appends (Write-Ahead Log)
Data Size Written	Full page (typically 8KB–16KB)	Compact delta record (often 50–200 bytes)
I/O Pattern	Random block address lookups	Strictly sequential append
Drive Write Amplification	Extremely high (rewriting entire flash blocks)	Low (packed sequentially into pages)
Throughput Bottleneck	Disk IOPS limits ($IOPS \times PageSize$)	Storage bus bandwidth

Even on high-end NVMe SSDs where mechanical seek latency is zero, sequential writes significantly outperform random writes. Solid-state media cannot overwrite individual bytes or 4KB blocks in place; they must erase entire erase blocks (often 4MB to 16MB) before writing into pages. High random writes trigger severe internal garbage collection, write amplification, and latency spikes inside the SSD controller.

The WAL shields the storage engine from this bottleneck by funneling all concurrent mutations into an append-only stream.

3. The Lie of write() vs. fsync()

A common misconception among developers writing backend services is that calling write() or fwrite() persists data to physical disk.

It does not.

C Snippet:

// This only copies data from application memory to the OS kernel page cache.
// It returns instantly. If power cuts here, your data disappears.
write(wal_fd, log_record, record_size);

// This tells the kernel: "Flush the dirty OS pages for this descriptor to disk,
// wait until the drive controller acknowledges receipt, then return."
fsync(wal_fd);

When an application calls write():

1. The kernel copies the buffer from user-space into the Linux Page Cache.

2. The page is marked as dirty.

3. The system call returns SUCCESS almost instantly.

4. The OS kernel's background flusher threads (flusher / pdflush) will asynchronously flush these dirty pages to storage at some later point, controlled by kernel sysctl knobs:

   • vm.dirty_background_ratio

   • vm.dirty_ratio

   • vm.dirty_expire_centisecs

If the physical server suffers a power loss while the record sits in the OS Page Cache, the write disappears entirely, despite your code having received a successful return code from write().

The True Cost of fsync()

To satisfy the Durability guarantee of ACID, a database engine must issue an fsync() (or use O_SYNC / O_DIRECT) before acknowledging a transaction commit to the client.

Individual fsync() per transaction:
App ──> write() ──> fsync() [BLOCKED ~1-5ms] ──> ACK
App ──> write() ──> fsync() [BLOCKED ~1-5ms] ──> ACK
Total throughput: ~200 - 1,000 commits/sec per thread.

If a physical SSD takes 1 millisecond to complete a flush, a single-threaded process issuing write() + fsync() sequentially cannot exceed 1,000 commits per second, regardless of how many CPU cores the machine has.

How Databases Survive This: Group Commit

Engines like PostgreSQL, MySQL (InnoDB), and SQLite solve this bottleneck using Group Commit:

Client 1 ───> UPDATE ───┐
Client 2 ───> UPDATE ───┼──> Combined into a single [ fsync() ] ──> All 3 ACKed
Client 3 ───> UPDATE ───┘

While one thread is blocked waiting for an active fsync() to return from the hardware, subsequent transactions queue up their changes into the WAL buffer. When the current flush completes, the engine flushes all pending log records for the queued transactions in a single batch fsync(), amortizing the hardware latency across dozens of concurrent transactions.

4. When Hardware Lies: The Controller Cache Problem

Even after fsync() returns successfully from the kernel, your data might still not be safe on the physical NAND flash or magnetic platters.

Modern enterprise and consumer drives contain an internal DRAM or SRAM cache on the drive controller itself:

[OS Kernel] ── fsync() ──> [ Drive Controller (Volatile DRAM Cache) ] ── (Later) ──> [ NAND Cells ]
                                     |
                          Returns "Write Complete"
                          before data touches media!

Consumer SSDs and cheaper cloud storage volumes frequently report that an fsync() flush command has completed the moment data enters the drive's internal volatile RAM cache. If power drops at that split second:

• The drive loses its volatile write cache.

• The operating system thinks the data was safely committed.

• The database restarts into a corrupted state with partially applied WAL entries or torn pages.

Enterprise Protection: PLP (Power Loss Protection)

Production database servers require enterprise-grade SSDs equipped with Power Loss Protection (PLP):

When main power fails, onboard hardware capacitors supply enough stored voltage for several milliseconds, enabling the drive controller to flush its entire in-flight DRAM cache onto non-volatile flash before shutting down completely.

5. Storage Engine Showdown: B-Trees vs. LSM-Trees

Once data is recorded in the WAL, how does it actually live inside the database table storage? Modern databases divide into two primary architectural families based on how they organize long-term page storage:

                       DATABASE ENGINES
                              |
        +---------------------+---------------------+
        |                                           |
  B-Tree Engines                             LSM-Tree Engines
(Read-Optimized)                           (Write-Optimized)
  PostgreSQL                                 RocksDB
  MySQL (InnoDB)                             Cassandra
  SQLite                                     ClickHouse
  SQL Server                                 CockroachDB (Pebble)

Architectural Comparison

Attribute	B-Tree / B+Tree Engines	LSM-Tree (Log-Structured Merge-Tree)
Primary Design Goal	Fast, predictable reads; in-place page modification	High write throughput; append-only storage
Write Strategy	Overwrite existing pages in-place (dirty buffers)	Write to memory (MemTable), flush as immutable files (SSTables)
Write Amplification	Moderate to High (updating 1 row rewrites 8KB–16KB page)	High during background compaction; low on initial ingest
Read Amplification	Low (direct index path to a single disk page)	Higher (may need to check multiple SSTables via Bloom filters)
Compaction Cost	None (uses page splits, freelists, and vacuuming)	Periodic background merge-sort (compaction debt)
Best Workloads	OLTP with balanced read/write ratios, low latency reads	High-frequency telemetry, event logging, high-ingest rates

How B-Trees Write

1. Look up the leaf page containing the target key.

2. Update the record within the in-memory page buffer.

3. Mark page as dirty.

4. A background thread pool (e.g., InnoDB Page Cleaner, Postgres Background Writer) periodically sorts dirty pages and writes them to the actual table file via random writes.

5. If an update causes a page to exceed its byte limit, the engine executes a page split, allocating a new page and redistributing keys.

How LSM-Trees Write

1. Append the record to the WAL for crash-recovery durability.

2. Insert the key-value pair into an in-memory balanced tree or skiplist called the MemTable.

3. Acknowledge transaction completion immediately.

4. When the MemTable hits its memory limit (e.g., 64MB), it freezes and flushes sequentially to disk as an immutable SSTable (Sorted String Table).

5. Deletes and updates do not modify old SSTables; they append tombstone markers or newer versions of the key.

6. A background compaction process continually reads multiple overlapping SSTables, performs a merge-sort, discards overridden values and tombstones, and outputs unified new SSTables.

6. Real-World Code: Simulating the Latency of fsync()

To illustrate why database write pipelines are engineered with this level of complexity, consider this minimal C simulation comparing un-synced writes, manual batching, and strict synchronous durability.

C Code Snippet:

#define _GNU_SOURCE
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <fcntl.h>
#include <unistd.h>
#include <time.h>

#define ITERATIONS 5000
#define RECORD_SIZE 128

double get_time_diff_ms(struct timespec start, struct timespec end) {
    return (end.tv_sec - start.tv_sec) * 1000.0 + (end.tv_nsec - start.tv_nsec) / 1000000.0;
}

int main() {
    char data[RECORD_SIZE];
    memset(data, 'A', RECORD_SIZE);
    struct timespec start, end;

    // -------------------------------------------------------------
    // Test 1: Calling write() only (Data sits in OS Page Cache)
    // -------------------------------------------------------------
    int fd1 = open("bench_page_cache.log", O_CREAT | O_WRONLY | O_TRUNC, 0644);
    clock_gettime(CLOCK_MONOTONIC, &start);
    for (int i = 0; i < ITERATIONS; i++) {
        write(fd1, data, RECORD_SIZE);
    }
    clock_gettime(CLOCK_MONOTONIC, &end);
    close(fd1);
    unlink("bench_page_cache.log");
    printf("Write only (OS Page Cache): %.2f ms (%.0f writes/sec)\n",
           get_time_diff_ms(start, end),
           (ITERATIONS / (get_time_diff_ms(start, end) / 1000.0)));

    // -------------------------------------------------------------
    // Test 2: Calling fsync() after EVERY write (Naive Durability)
    // -------------------------------------------------------------
    int fd2 = open("bench_fsync.log", O_CREAT | O_WRONLY | O_TRUNC, 0644);
    clock_gettime(CLOCK_MONOTONIC, &start);
    for (int i = 0; i < ITERATIONS; i++) {
        write(fd2, data, RECORD_SIZE);
        fsync(fd2);
    }
    clock_gettime(CLOCK_MONOTONIC, &end);
    close(fd2);
    unlink("bench_fsync.log");
    printf("Write + fsync() per row:   %.2f ms (%.0f writes/sec)\n",
           get_time_diff_ms(start, end),
           (ITERATIONS / (get_time_diff_ms(start, end) / 1000.0)));

    // -------------------------------------------------------------
    // Test 3: Group Commit Simulation (1 fsync per 50 writes)
    // -------------------------------------------------------------
    int fd3 = open("bench_group.log", O_CREAT | O_WRONLY | O_TRUNC, 0644);
    clock_gettime(CLOCK_MONOTONIC, &start);
    for (int i = 0; i < ITERATIONS; i++) {
        write(fd3, data, RECORD_SIZE);
        if (i % 50 == 0) {
            fsync(fd3);
        }
    }
    fsync(fd3);
    clock_gettime(CLOCK_MONOTONIC, &end);
    close(fd3);
    unlink("bench_group.log");
    printf("Batch Commit (1 fsync / 50 writes): %.2f ms (%.0f writes/sec)\n",
           get_time_diff_ms(start, end),
           (ITERATIONS / (get_time_diff_ms(start, end) / 1000.0)));

    return 0;
}

Typical Benchmark Output on NVMe SSD

Write only (OS Page Cache):           2.84 ms (1,760,563 writes/sec)
Write + fsync() per row:           4182.12 ms (1,195 writes/sec)
Batch Commit (1 fsync / 50 writes):  91.45 ms (54,674 writes/sec)

The difference is staggering:

• Writing exclusively to the operating system's page cache operates at memory speed (~1.7 million records/sec).

• Forcing a synchronous disk-controller barrier with fsync() on every single write craters throughput down to ~1,200 records/sec.

• Batching or group commits recovers throughput to ~54,000 records/sec while maintaining transaction durability guarantees.

7. How Engines Handle the "Torn Page" Problem

There is one final failure mode that highlights how delicate durability guarantees really are: the torn page write.

• Most database systems (PostgreSQL, MySQL InnoDB) store table data in pages of 8KB or 16KB.

• The underlying storage hardware sector size is historically 512 bytes, or 4KB on Advanced Format drives.

Database Page: 16KB
[  Block 1 (4KB)  ][  Block 2 (4KB)  ][  Block 3 (4KB)  ][  Block 4 (4KB)  ]
         |                  |                  |                  |
      Written            Written         POWER DROPS!         Never
    Successfully       Successfully     (Corrupted)          Written

If the operating system or database is in the middle of writing a 16KB page to disk and a power fault occurs after writing the second 4KB block, the page on disk is now torn. It consists of 8KB of new data and 8KB of old data.

Because the page header and checksum are invalid or mismatched, standard crash recovery cannot safely read the page to apply the WAL.

How Different Engines Solve This

• MySQL InnoDB (Doublewrite Buffer): Before updating an actual data page in the tablespace, InnoDB writes the page first into a contiguous layout called the Doublewrite Buffer and flushes it. If an OS crash occurs while writing the page to its final location, InnoDB can recover the original page from the doublewrite buffer and reapply the WAL cleanly.

• PostgreSQL (Full Page Writes): Immediately after a checkpoint, the first time a page is modified, PostgreSQL writes a copy of the entire 8KB page directly into the WAL stream (controlled by the full_page_writes setting). If a torn write occurs down the road, crash recovery restores the complete page snapshot from the WAL before executing incremental deltas.

Summary Checklist for Backend & Database Engineers

1. write() is not persistence: It simply delegates data retention to the OS page cache.

2. fsync() is the real commit point: Durability is only achieved once the storage hardware acknowledges the flush command.

3. Hardware caches can still drop data: Unless drives have Power Loss Protection (PLP) with supercapacitors, consumer drives may report flushes complete while writes remain in volatile device DRAM.

4. Group Commit makes relational DBs viable: Amortizing a single fsync() barrier across dozens of concurrent transactions bridges the gap between disk physics and application scale.

5. Storage engine choice dictates write pattern: Choose B-Trees (PostgreSQL, InnoDB) when you need fast primary-key lookups and predictable reads; choose LSM-Trees (RocksDB, Cassandra) when ingestion throughput is your primary operational ceiling.