Write-ahead log and commits

What a durable write costs, what a crash loses, and how group commit gets both.

On the roadmap:Advanced · step 3: Storage engines

Intermediateabout 10 to 15 min6 experiments

Experiments

New here? A few short steps show what happens, one at a time.

Settings

When a write is acknowledged

Do not wait for the diskFlush the log for every writeFlush once for a group of writes

Each write is forced to disk (fsync) before it is acknowledged. Nothing acknowledged is ever lost, but the disk handles one flush at a time, so writes queue behind each other.

Clients writing at once32

Time for one disk flush2.0 ms

Power fails after12 s

Writes per second

495

Wait for each write

64.6 ms

Writes per flush

1.0

Lost in the crash

Nothing acknowledged is ever lost, but the disk can only flush about 495 times a second, so the system manages 495 writes a second and each client waits about 65 ms in line.

The same disk, three ways of using it

50.0k writes/s · wait 0.02 ms · 100,000 lost in a crash
495 writes/s · wait 64.6 ms · nothing acknowledged is lost
12.1k writes/s · wait 4.0 ms · nothing acknowledged is lost

Bars show writes per second. A single machine appending to a log, with a 20 microsecond cost per record. The operating system is assumed to write cached data out every 5 seconds.

What happens after a crash

1. Restart
The database starts with the tables as they were last saved, which may be old.
2. Replay
It reads the log from the last checkpoint and redoes each change in order. About 5,940 writes are recovered here.
3. Carry on
Only records that reached the disk can be replayed. That is why the flush must come before the acknowledgement.

In plain words

Before a database changes its tables it appends a short record of the change to a log. If power fails, it replays the log and recovers. But the log is only safe once it has reached the disk, and forcing it there (an fsync) is slow. How often you pay that cost decides both how fast the database is and how much a crash can lose.

Why it matters

Every serious database, file system and message broker works this way. The log is why a committed transaction survives a crash, and the flush is why commits are not free. Understanding group commit explains how databases reach tens of thousands of safe writes a second on a disk that can only flush a few hundred.

How real systems do it

The simulation is a simplified model. Here is what production systems add or do.

PostgreSQL writes its write-ahead log (WAL) before changing data files. synchronous_commit controls whether a commit waits for the flush, and commit_delay and commit_siblings enable group commit. Turning off synchronous_commit risks losing recent commits but not corrupting the database.
MySQL's InnoDB has a redo log, with innodb_flush_log_at_trx_commit set to 1 for a flush at every commit, and 2 for a flush each second. Redis offers appendfsync always, everysec or no, and everysec is the common compromise.
Kafka does not flush every message. It relies on replication to other brokers for durability, and lets the operating system write to disk. That moves the safety from one disk to several machines.
Disks and SSDs may report a write as done while it sits in a volatile cache, which is why databases use fsync and why enterprise SSDs have capacitors to finish writes on power loss. A drive that lies about flushing undoes the whole scheme.