When the database says "saved", the user moves on. A write is durable if nothing that can happen next (a crash, a power cut, a dead disk, a failover) can lose it. How far a database goes before it says "saved" decides which of those it survives, and what every write costs.
What "saved" means
A database keeps recent changes in memory, because memory is fast, and writes them to disk, because disk survives a restart. If it acknowledges a write while the change is still only in memory, a crash in the next moment loses a write the user was told was saved.
Durable on one machine: on disk before the answer.
Writing to a file isn't enough either: the operating system holds file writes in its own memory for a while. The database has to flush them (fsync) and wait until the disk confirms, which takes from a fraction of a millisecond on a good SSD to several on a busy disk.
The write-ahead log
Updating every changed table and index on disk for each write would be slow, so databases first append each change to a write-ahead log (WAL): one file, written only at the end, which is the fastest thing a disk does. Once the log entry is flushed, the write is durable. The tables are updated in the background.
| After a crash, the database | Because |
|---|---|
| replays the log from the last checkpoint (the last moment the tables were known to be fully on disk) | every acknowledged change is in it |
| drops changes that never reached the log | with synchronous commit, they were never acknowledged |
In PostgreSQL the same log is what replicas receive: replication is shipping the WAL to other machines and replaying it there. MySQL ships a separate log of changes, the binlog, in much the same way.
Synchronous and asynchronous commit
Synchronous commit says "saved" only after the log entry is flushed. Asynchronous commit says "saved" first and flushes a moment later, with the next batch (PostgreSQL's WAL writer flushes every 200 ms by default). Each write skips its fsync, so it's a little faster, and others see it at once; a crash loses everything written since the last flush.
Copies on other machines
A flushed write survives a crash, but not the disk dying, or the machine never coming back. For that the write needs to be on another machine too, and the same choice comes up again:
- Asynchronous replication: the primary says "saved" and ships the change to replicas afterwards. A failover promotes a replica that may be missing the writes it hadn't received yet, and those are lost: usually milliseconds' worth, but seconds' worth or more if it had fallen behind.
- Synchronous replication: the primary waits until at least one replica has received and stored the change before saying "saved". Nothing acknowledged is lost in a failover, as long as the failover promotes a replica that has it, and every write waits one round trip to a replica longer: a millisecond or two to a replica in the same region, tens of milliseconds to one in another.
Real databases bundle the two knobs differently: PostgreSQL's synchronous_commit setting, for example, controls both the local flush and the wait for a replica. The simulator keeps them apart so you can see what each one does.
Crash the primary at different moments and see which writes the app had been told were saved. The lab starts with a crash shortly after the first writes are acknowledged.
Move the crash later, switch the commit to flush first, then make the replica take over for good. Each change closes one gap and opens the next question.
Synchronous replication also means a write can't complete if no replica is reachable, so systems keep at least two replicas, or fall back to asynchronous while one is missing.
Backups and recovery targets
Replicas copy mistakes as faithfully as they copy data: a bad migration or an accidental delete reaches every replica in milliseconds. Backups (snapshots, plus the WAL since the last snapshot) let you restore to a moment before the mistake, called point-in-time recovery.
Two numbers describe what a design promises when things go wrong:
| Target | Question it answers | Set by |
|---|---|---|
| RPO (recovery point objective) | how much recent data may be lost | commit and replication modes, backup frequency |
| RTO (recovery time objective) | how long until it works again | failover time, how long a restore takes |
In the wild
The ideas in this chapter, and where you'll meet them in real systems and interviews:
| Idea | Where you'll meet it |
|---|---|
| Commit modes | PostgreSQL's synchronous_commit, MySQL's innodb_flush_log_at_trx_commit |
| Synchronous replication | PostgreSQL synchronous_standby_names, Amazon RDS Multi-AZ, MySQL semi-synchronous replication (which falls back to asynchronous after a timeout, so it can still lose writes) |
| Write-ahead logs | PostgreSQL's WAL, MySQL's redo log |
| The same flush choice in other logs | Kafka's flush settings, Redis's append-only file (appendfsync everysec) |
| Backups and point-in-time recovery | Amazon RDS automated backups, pg_basebackup with WAL archiving |
Your practice
Inkwell Blogs now fails over on its own, but after the last failover a few dozen writers lost posts they had been told were saved. Choose how the database makes a write safe so that no acknowledged post is ever lost, and see what that costs each save.