Write-ahead logs, fsync and group commit: an interactive durability simulator
A database makes a commit durable by writing it to a write-ahead log and forcing that log to disk with fsync before saying the commit succeeded. Until the fsync completes, the change sits in the operating system’s memory, and a power cut loses it. An fsync takes from a fraction of a millisecond to several milliseconds, so how often a database calls it decides both its write speed and how much acknowledged data a crash can take with it.
The playground commits for ten seconds under four flush policies, then cuts the power. Change the commit rate and how long an fsync takes on this disk.
1,000 commits a second for 10 seconds, on a disk where an fsync takes 1 ms, then the power fails. Flushing every commit separately allows at most 1,000 commits a second, so commits queue for the disk and the median one waits 117 ms. Group commit puts every waiting commit into the next fsync, 1.4 on average, so it keeps up at 1.3 ms and still loses nothing. Flushing once a second answers in 0.02 ms, but the 975 commits since the last flush were acknowledged and are gone. With no fsync at all, the OS writes data back after about 30 seconds, so all 10,097 commits of this run are lost.
How a write-ahead log works
- Append the change to the end of the log, a sequential write, which is fast.
- fsync the log so the disk has really stored it, not just the operating system’s cache.
- Acknowledge the commit to the client.
- Apply later: update the actual tables and indexes in memory, and write them to disk lazily, at checkpoints.
- Recover after a crash by replaying the log from the last checkpoint.
The log is also what replication ships to replicas and what change data capture reads, which is why so many systems are built around one.
Why one fsync per commit does not scale
If every commit waits for its own fsync, one after another, the disk sets a hard ceiling: with a 1 ms fsync, at most 1,000 commits a second. At 1,000 a second the disk is fully busy and commits queue for it, a median of 117 ms in the playground; at 5,000 a second, the queue grows without bound. On a network-attached cloud disk, where an fsync can take 5 ms, the ceiling is 200 a second.
Group commit: durability at full speed
Commits that arrive while an fsync is running all wait for the next one, which covers them together. Under load each fsync carries more commits, so throughput rises with load instead of hitting a wall: at 5,000 commits a second each fsync carries about 5, and commits still take under 2 ms, with nothing lost when the power fails. PostgreSQL, MySQL and most serious databases do this automatically.
Trading durability for speed
Acknowledging before the fsync makes commits nearly free, at the cost of a window of acknowledged data that a power cut erases. Flushing once a second, Redis’s default and InnoDB’s setting 2, loses up to about a second: 975 commits at 1,000 a second in the playground. Never calling fsync leaves it to the operating system, which on Linux writes dirty pages back after about 30 seconds, so a crash can take everything from the last half minute. That can be the right choice for caches, metrics and anything rebuildable; it is the wrong one for orders and payments. Replication changes the picture: a commit acknowledged only after a replica has it survives one machine losing power even without a local fsync.
Flush settings in practice
# PostgreSQL: every commit waits for its WAL fsync (the default)
synchronous_commit = on
commit_delay = 0 # microseconds to wait for more commits to share a flush
# PostgreSQL, for data you can afford to lose: acknowledge first, flush within ~3 x wal_writer_delay
synchronous_commit = off
# MySQL InnoDB
innodb_flush_log_at_trx_commit = 1 # fsync at every commit (with group commit)
innodb_flush_log_at_trx_commit = 2 # write at commit, fsync about once a second
# Redis append-only file
appendfsync always # fsync every write
appendfsync everysec # the default: lose at most about a second
appendfsync no # let the OS decideIn a system design interview
When durability matters, say where the line is: “A payment is acknowledged only after it is in the primary’s write-ahead log, fsynced, and on a synchronous standby, so losing a machine loses nothing we confirmed. Group commit keeps that at a few milliseconds per commit at our peak of a few thousand a second. The analytics events stream is different: it is fine to lose a second of it, so it flushes once a second.” Expect the follow-ups: what fsync costs on the disks you are using, and whether the write load is sequential.
Read the guide to storage engines, see LSM trees for a storage engine built around a log, or watch a strong candidate design a payment system.
Questions people ask
What is a write-ahead log?
An append-only file where a database records every change before applying it to its data files. Once the log entry is safely on disk the change is durable, because after a crash the database can replay the log to redo it. Data files are updated later in the background, and the log also feeds replication and change data capture.
What does fsync do?
It asks the operating system to write a file’s buffered data to the physical disk and returns only when the disk reports it stored. Without it, written data may sit in the OS page cache for many seconds and is lost if the machine loses power. An fsync takes from well under a millisecond on fast local SSDs to several milliseconds on network disks.
What is group commit?
A technique where commits that arrive while an fsync is in progress wait and are made durable together by the next fsync. Each commit still waits for its data to be on disk before it is acknowledged, but many commits share one flush, so throughput is no longer capped at one commit per fsync and grows with load.
How much data can be lost with fsync once a second?
Up to about a second of acknowledged writes, plus the time the fsync itself takes: whatever was committed since the last completed flush lives only in memory when the power fails. Redis’s default appendfsync everysec and InnoDB’s innodb_flush_log_at_trx_commit = 2 make this trade for much faster writes.
Why is fsync slow?
Because it waits for the storage device to confirm data is on non-volatile media, which means a device round trip and, on some drives, flushing a write cache. Local NVMe drives with power-loss protection can do it in tens of microseconds, consumer SSDs in around a millisecond, network block storage in milliseconds, and spinning disks in about ten.
Does replication remove the need for fsync?
Partly. If a commit is acknowledged only after one or more replicas have received it, losing one machine’s power does not lose it, even if that machine had not flushed. But a simultaneous failure, such as a whole data centre losing power, can still lose unflushed data on every copy, so critical systems usually do both.
How do databases recover after a crash?
They start from the last checkpoint, the point up to which data files are known to match the log, and replay every log record after it to redo committed changes, discarding the effects of transactions that never committed. Recovery time depends on how much log has accumulated since the checkpoint.