The scene lasts milliseconds. A transaction has just modified three rows, the engine has updated its indexes, the application is about to receive the confirmed commit… and the breaker trips. The server dies on the spot, with no time to flush anything to disk. Five minutes later the power comes back, the database starts, and the result is clean: the operation exists in full or it does not exist at all. It never appears half-written. That is neither luck nor accounting magic, it is a protocol that has been refined for decades and rests on one simple idea: write down the intention before touching the data.
The promise has a name: durability
The acronym ACID (atomicity, consistency, isolation, durability) is always quoted as a block, but each letter costs something different. Atomicity means a transaction is not executed in part: all or nothing. Durability is the most expensive of the four, because it is the only one that forces the engine to talk to the hardware and wait for an answer. When the engine tells you “saved”, it is signing a contract promising that the data will survive a blackout, a kernel panic and someone yanking a cable. Keeping that promise does not depend on the database alone: it depends on the whole chain, from the file system to the controller and the drive itself.
That is why, to understand whether a database engine is fast or slow, you have to look at where it decides to say “done” and how many times it pays the price of making sure that is true.
Write the journal before the data
The core technique is called a write-ahead log (WAL), and its rule fits in one line: no data page is modified on disk before the matching change has been written and synchronised in the log. The WAL works like a sequential notebook: every record carries what is needed to redo the change and, depending on the engine, to undo it, plus the exact position of the affected page. Each entry gets an LSN (log sequence number), a monotonic number that orders absolutely everything and lets the engine compare what has already been applied with what has not.
This is where the performance advantage comes from. Writing randomly inside an 8 KB page is slow; appending at the end of a file is very fast. The WAL turns a scattered set of modifications into a single sequential write, and the data pages can linger in memory (the buffer pool, the cache of dirty pages) with no rush to come down.
So when is a commit considered finished? When its record is in the journal and the journal is physically on the medium. That word, “physically”, becomes a system call: fsync(), or its cheaper cousin fdatasync(), which forces the operating system cache and the drive cache to be flushed. Without it, the engine has only handed the data to the kernel’s volatile memory and is already talking about durability without having paid for it.
How an engine gets back up after a crash
Recovery follows a classic procedure described in the 1990s by the ARIES algorithm: analysis, redo and undo. The analysis phase rebuilds the map of what was dirty at the moment of death. The redo phase reapplies every logged change since the last checkpoint, including those from transactions that never confirmed. The undo phase rolls back the ones left half-finished. Redo is designed to be idempotent: applying it twice yields the same result, which is essential when the recovery process itself can crash again.
A checkpoint is the moment the engine forces dirty pages down to disk and records up to which LSN everything is applied, so recovery does not have to read months of journal. Good ones are fuzzy: they do not freeze the system, they simply mark a reasonable point to start from.
There is also a silent enemy: the torn page. An 8 KB page is not written in a single physical blow; the drive stores it in 512-byte or 4 KB sectors, and a cut halfway through leaves half a new page and half an old one. PostgreSQL fights this with full page writes: after every checkpoint it writes the whole page into the WAL the first time it is modified, so the journal can rebuild it completely. MySQL InnoDB does something similar with its doublewrite buffer: it writes pages first to a contiguous area and only afterwards to their final location.
When the drive is the one lying
The whole theory rests on one assumption: that fsync(), when it returns, has actually done its job. And that is not always true. Many drives advertise volatile caches you can disable, but some spinning disks ignore write barriers to look faster, and some SSDs report success without having flushed their internal memory to the NAND cells.
The most notorious case arrived in 2018: a PostgreSQL engineer showed that, on a real write failure, the operating system could discard the error and fsync() would still return success. The problem affected MySQL too and forced both families to patch their error handling; it is now known as fsyncgate. The lesson was uncomfortable: an honest engine has to check the return value of every synchronisation and stop rather than keep promising data that is not safe. That is why PostgreSQL exposes fsync = off: it is for tests and reproducible loads, never for data that matters.
At the opposite end, copy-on-write file systems with per-block checksums, such as ZFS or Btrfs, attack the problem from below: they never overwrite a live block, they write the new one and then update the pointer, so an interruption always leaves a coherent structure behind.
The shortcuts that are actually legitimate
If every commit demanded its own fsync(), and each one cost between 1 and 10 milliseconds on a mechanical disk, any payment server would crawl. The industry’s answer is measured compromise:
- Group commit: several transactions confirming almost simultaneously share a single journal synchronisation. Ten thousand operations per second can fit into a few hundred
fsync()calls. - Asynchronous commits: with
synchronous_commit = off, the engine confirms without waiting for the disk and gains latency, in exchange for a declared loss window: whatever was confirmed in the last few milliseconds may vanish in a blackout. Nothing is ever corrupted, you simply travel back in time. - Synchronous replicas: the commit waits until another machine has written the journal (
remote_write) or even applied it (remote_apply). You gain tolerance to a full node failure at the cost of adding network latency. - Battery-backed hardware: a controller with battery-backed cache can confirm writes without reaching the platter, because its memory survives the outage. It is one of the few shortcuts that does not change the semantics.
What “saved” really means
Every time an application closes a sale, registers a licence plate or stores a medical record, someone has decided how much durability to buy. An engine in its strictest mode will write the journal, synchronise, touch the data and synchronise again. Another one, tuned for performance, will tell you “ok” before reaching the platter and trust that no outage happens. Both use the same architecture: a sequential journal written before the data, a map of dirty pages coming down at leisure, and a three-phase recovery that redoes and undoes without mercy.
Next time a system shows you the message “saved successfully”, remember there is a chain of promises behind it: the engine to the file system, the file system to the controller, the controller to the cells. When someone says a confirmed piece of data was lost, computer science is rarely the thing that failed: the link nobody checked is.
