Durability
The last ACID letter, and the one that answers a very specific, very practical question: when the database says "committed," what exactly is it promising survives a crash one millisecond later?
| Level | Definition |
|---|---|
| Beginner | Once the database tells you "committed," that data is safe forever — even if the power goes out one second later. |
| Technical | Durability guarantees that the effects of a committed transaction are permanently recorded and will survive any subsequent system failure, including a full crash and restart. |
| Interview-grade | Durability is implemented via a Write-Ahead Log — before a commit is acknowledged to the client, the transaction's changes must be flushed to durable storage (disk, not just memory) in the log, so that even a total power loss immediately after acknowledgment can be recovered from by replaying the log on restart. |
◆ The problem
Writing directly to a database's main data files on every single transaction commit would be extremely slow — those files are organized for efficient reading (indexes, B-trees), not for fast sequential appends, and updating them in place risks leaving the file itself corrupted if a crash happens mid-write.
A Write-Ahead Log (WAL) solves both problems: every change is first appended — sequentially, which is fast — to a simple, append-only log file. Only after that log entry is confirmed durably on disk does the database consider the transaction committed and acknowledge it to the client. The actual, slower update to the real data files (and indexes) can happen afterward, even asynchronously — because if a crash happens before that update completes, the database can just replay the WAL on restart to redo the work.
The commit is acknowledged the moment the WAL entry is durably on disk (①②) — the slower update to actual data files (③) can safely happen afterward, because a crash before ③ completes is recoverable by replaying the WAL.
◆ Story
You book the last seat on a flight. The app shows "Booking Confirmed." One second later, the airline's database server loses power entirely — a hardware failure, not a graceful shutdown. When it comes back online, durability is the guarantee that your seat is still booked: the moment "Booking Confirmed" was shown to you, that fact was already safely on disk (in the WAL, at minimum), not merely sitting in the server's RAM waiting to be written out "eventually." If durability weren't guaranteed, a confirmed booking could simply vanish on crash — arguably a worse failure than the ATM losing money, since the customer has no error message at all telling them anything went wrong.
-- default: full durability, commit waits for WAL fsync — safest, adds latency SET synchronous_commit = ON; -- commit returns before the WAL write is confirmed durable — faster, but a crash -- in that narrow window can lose a transaction the client was told succeeded SET synchronous_commit = OFF;
| Setting | Trade-off | Use for |
|---|---|---|
| synchronous_commit = on | Higher commit latency, zero data loss window | Financial transactions, anything where "confirmed" must be absolute |
| synchronous_commit = off | Lower latency, a small window where an acknowledged commit could theoretically be lost on crash | High-throughput analytics/logging data where occasional loss of the very latest records is acceptable |
▲ Common mistake
Assuming durability is an all-or-nothing property of "using a real database" rather than a tunable, per-workload setting. Many teams unknowingly run with weakened durability settings for performance reasons, discovering the trade-off only after losing data in an actual crash — know what your durability setting actually is for any system handling money or other genuinely irreversible real-world consequences.
💻 Code example
-- default: full durability, commit waits for WAL fsync — safest, adds latency SET synchronous_commit = ON; -- commit returns before the WAL write is confirmed durable — faster, but a crash -- in that narrow window can lose a transaction the client was told succeeded SET synchronous_commit = OFF;
Want a visual for this concept?
Generate a diagram tailored to “Durability” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.
Sign in to generate a visual →