Engineering Atlas › System Design Fundamentals

How databases make committed data durable

13 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

How it works

Why not write the data pages directly?

A row lives in an 8 KB or 16 KB page. Updating pages in place on every commit means random writes, a crash mid-write can leave a torn page, and a transaction touching ten pages would need all ten writes to land atomically, which no disk offers.

The WAL sidesteps this. Every change is first described as a log record and appended sequentially, the access pattern storage handles best. Once a transaction's commit record is on stable storage it is durable, even though its data pages are still dirty in memory.

The rules that make it correct

  1. Log before data. A dirty page may not be written to the data file until every log record describing changes to it is durable. Each page carries the log sequence number (LSN) of its latest change, and the buffer manager checks it against the flushed LSN.
  2. Force the log at commit. The commit returns only after the log up to and including the commit record is flushed.
  3. Checkpoint. Periodically, flush dirty pages and record a checkpoint LSN. Recovery only needs to replay from there, which bounds restart time and lets old log segments be recycled.

On restart, recovery reads from the last checkpoint, redoes every logged change, then undoes transactions that never committed. This is the essence of ARIES. Engines that never write uncommitted data to pages (many LSM stores) need only redo.

What "on stable storage" actually means

A write() only reaches the OS page cache. Only fsync, fdatasync, or O_DSYNC pushes bytes to the device and asks it to flush its volatile cache. Three layers can lie to you:

Torn pages need separate handling: PostgreSQL logs a full page image on the first change after a checkpoint (full_page_writes, on by default), and InnoDB uses a doublewrite buffer. Checksums on log records detect a torn log tail, which recovery truncates.

Group commit

If 2,000 commits per second each needed a 1 ms flush, the device would saturate. Instead, commits arriving during a flush wait, and the next flush covers all of them. Throughput scales with batch size while each commit pays at most about two flush latencies. PostgreSQL can widen the batch on purpose: commit_delay waits a few microseconds before flushing, but only when at least commit_siblings (default 5) other transactions are active, so an idle server pays nothing (PostgreSQL WAL settings).

Durability beyond one machine

Replicated systems define commit as "the log record is durable on a quorum." Synchronous replication adds a network round trip (0.2 to 1 ms in one zone) to every commit. Asynchronous replication keeps latency low but can lose the last seconds of commits on failover, which is a durability decision whether or not anyone wrote it down.

Down the stack

Layer 1The contract

A durable commit promises that once the client receives success, every later read after any crash, power loss, or restart observes the transaction's effects, and that a transaction which never returned success is either fully present or fully absent. The test: cut power at a random moment, restart, and check every acknowledged transaction. Configuration moves the line. PostgreSQL's synchronous_commit ranges from off (acknowledge before the flush, risking commits from the last three wal_writer_delay intervals, 600 ms at the 200 ms default, but never corruption) through local, remote_write, on, and remote_apply (PostgreSQL WAL settings). InnoDB's innodb_flush_log_at_trx_commit=1 (the default) flushes the redo log on every commit; 2 writes to the OS at commit and flushes about once a second, so it survives a process crash but not an OS crash or power loss; 0 both writes and flushes once a second. MySQL's own manual warns that drives which fake the flush void even the default (MySQL InnoDB parameters).

Layer 2The mechanism

PostgreSQL writes WAL into 16 MB segment files made of 8 KB pages. Each record's XLogRecord header carries its length, transaction id, a pointer to the previous record (xl_prev), the resource manager that replays it, and a CRC-32C. Recovery reads forward from the checkpoint's redo pointer and stops at the first record whose CRC or xl_prev does not match, which is how a torn tail is detected and discarded. PostgreSQL recovery is redo only: aborted transactions leave tuples behind, but the commit log (pg_xact) marks them aborted and MVCC visibility hides them, so no undo pass is needed. The first change to a page after each checkpoint logs a full-page image, so replay starts from a known-good copy rather than a torn one.

InnoDB writes redo in 512-byte blocks and keeps undo logs to roll back uncommitted changes, closer to ARIES. Since MySQL 8.0.30 the redo log is sized by one setting, innodb_redo_log_capacity, and kept as 32 files in a #innodb_redo directory, superseding innodb_log_file_size and innodb_log_files_in_group when set (MySQL redo log). For torn data pages it uses the doublewrite buffer: dirty pages are first written in a batch to dedicated doublewrite files (such as #ib_16384_0.dblwr) and flushed, then written to their home locations. If a home write tears, recovery finds an intact copy there. The doubled write is cheap because it is one large sequential write and one fsync, and innodb_doublewrite=DETECT_ONLY keeps only metadata, so torn pages are detected but not repaired (MySQL doublewrite buffer).

Layer 3The machine

write() only dirties pages in the page cache. fsync() writes the dirty pages, waits for completion, and asks the device to flush its volatile write cache (an NVMe Flush command or a write with Force Unit Access). fdatasync() skips metadata such as modification time unless the file size changed, which is why PostgreSQL preallocates WAL segments: appends inside a preallocated file need only fdatasync, its default wal_sync_method on Linux and FreeBSD. A flush costs tens of microseconds on an enterprise SSD with power-loss protection, where the capacitor-backed cache can acknowledge immediately, and can reach milliseconds on consumer SSDs or spinning disks.

Atomicity at the device is only guaranteed for one sector, usually 512 bytes or 4 KiB. An 8 KB PostgreSQL page or a 16 KB InnoDB page spans several sectors, so a power cut mid-write can leave half old, half new: the torn page. The WAL tolerates this through record checksums; data pages need full-page images or doublewrite. Error reporting is a further trap. The 2018 "fsyncgate" discussion showed that after a failed writeback Linux may mark pages clean, so a retried fsync can succeed while data is lost; newer kernels (4.14 and 4.16 onward) report the error, but only once (PostgreSQL wiki). PostgreSQL now treats an fsync failure as a PANIC and recovers from WAL instead of retrying, controlled by data_sync_retry, which defaults to off (PostgreSQL error handling).

Why it's built this way

  1. Devices promise only atomic sector writes and caches hold data until flushed, so commits become explicitly flushed appends.
  2. A flush costs far more than a commit's CPU work, so many commits share one flush.
  3. Pages are larger than sectors, so data page writes can tear, and engines keep a second copy (full-page image or doublewrite) to repair them.
  4. Replaying an unbounded log makes restart slow, so checkpoints bound recovery time and let old log segments be recycled.
  5. A whole machine can disappear, so the strongest settings mean "flushed on a quorum of machines."

Diagram

sequenceDiagram
    participant C as Client
    participant E as Engine
    participant B as "Buffer pool"
    participant L as "WAL file"
    participant D as "Data file"
    C->>E: COMMIT
    E->>B: modify page, set page LSN
    E->>L: append log records + commit record
    E->>L: fsync (group commit)
    L-->>E: durable up to LSN 9120
    E-->>C: OK
    Note over B,D: later, at checkpoint
    B->>D: write dirty page if page LSN <= flushed LSN

Worked example

A payments service runs 3,000 commits per second on PostgreSQL. The WAL lives on an NVMe drive whose fsync takes about 0.4 ms. Without batching, the ceiling is roughly 2,500 flushes per second, so the database would queue. With group commit, an average batch of 6 commits needs only 500 flushes per second, leaving headroom, and commit latency stays near 1 ms.

Each commit writes about 1.5 KB of WAL, so the log grows at 4.5 MB/s. Stretching checkpoints from 5 to 15 minutes cuts full-page images but means up to 4 GB of WAL to replay after a crash, around 20 seconds at 200 MB/s.

A synchronous replica in-region costs about 0.5 ms per commit; cross-region it would cost 30 ms. The team keeps the synchronous replica in-region and ships WAL asynchronously across regions, accepting a few seconds of loss in a regional disaster.

In the wild

Trade-offs

OptionStrengthsWeaknessesChoose when
fsync every commit (with group commit)No acknowledged commit is lost on crashLatency bound by flush timeMoney, orders, anything you must not lose
Async commit (flush every N ms)Much higher throughput, sub-ms commitsLast few ms of commits lost on crashMetrics, caches, recomputable data
Synchronous replication to a quorumSurvives losing a disk or hostAdds network RTT to every commitPrimary data with strict RPO of zero
Async replicationNo commit latency costData loss window on failoverCross-region DR, read replicas
Battery or capacitor-backed write cacheFast flushes that are still safeHardware cost, must verify it is realWrite-heavy OLTP on owned hardware

Pitfalls in production

Leadership lens

Check yourself

  1. Why can a database acknowledge a commit while the modified data pages are still only in memory?

    Show answer

    The commit record and all changes are already durable in the WAL, so recovery can rebuild those pages by replaying the log after a crash.

  2. What does group commit trade, and why is it nearly free?

    Show answer

    Each commit may wait up to one extra flush interval, but one flush now persists many commits, so throughput rises sharply while latency changes little.

  3. Why must a dirty page never be written before its log records are durable?

    Show answer

    After a crash, recovery would find changes with no log record to undo them, breaking atomicity.

  4. A system uses async replication with fsync on the primary. Which failure loses acknowledged data?

    Show answer

    Losing the primary's disk or host, followed by promoting a replica that had not yet received the latest commits.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →