How Databases Guarantee Durability

Imagine you are building a banking app. A user transfers $10,000 to their friend. The app says "Transfer Successful!" A millisecond later, someone trips over the power cord in the data center, and the database server hard crashes.

When the server reboots, what happened to that $10,000?

If the database promised the user it was successful, that data must survive the crash. This absolute guarantee that committed data will survive power failures, crashes, and catastrophes is the "D" in ACID: Durability.

But how does a database actually guarantee this without being unbearably slow?

The Conflict: Speed vs. Safety

As we know, writing to a hard disk (especially randomly) is slow. Writing to RAM (memory) is incredibly fast.

If a database wants to be fast, it should keep all its changes in RAM and only occasionally save them to disk. But if it does that, a sudden power loss will wipe the RAM, and any recent changes are gone forever.

If a database wants to be perfectly safe, it must write every single transaction directly to the physical disk before telling the user "success". But doing random disk writes for every tiny transaction would make the database run at a crawl.

How do we get the speed of RAM with the safety of a disk?

The Savior: The Write-Ahead Log (WAL)

The solution adopted by almost every modern database (PostgreSQL, MySQL, Oracle) is a concept called the Write-Ahead Log (WAL).

Think of the WAL as an append-only diary.

Here is the exact sequence of events when a database processes a transaction:

1. Write to the Diary First: Before the database actually updates the real tables or B-Trees, it takes the details of the transaction ("Deduct $10k from User A, Add $10k to User B") and appends it to the end of the Write-Ahead Log.
2. Sync to Disk: It forces this diary entry to be saved to the physical disk immediately.
3. Update RAM: The database then updates the actual data tables in its lightning-fast RAM buffers.
4. Return Success: The database tells the user, "Success! Your transaction is complete."

Notice what happened here: The actual database tables on disk haven't been touched yet! They are still outdated. The only place the new data safely exists is in RAM and in the append-only WAL diary.

Why is this faster?

Writing to the WAL is a sequential append. The database just tacks the new data onto the end of a file. Sequential disk writes are remarkably fast, often 10x to 100x faster than the random disk writes required to navigate a B-Tree and update a specific row.

By using a WAL, the database avoids doing slow random writes while the user is waiting.

The Checkpoint: Cleaning Up

If the database only updates the WAL, the actual database files would never get updated, and the WAL would grow to infinity.

To fix this, databases run a background process called a Checkpoint. Every few minutes, the database takes all the updated data sitting in RAM, does the heavy lifting of sorting it, and quietly flushes it to the actual database files on disk (the B-Trees).

Once the data is safely in the main files, the database marks a "checkpoint" in the WAL, essentially saying, "Everything before this point is permanently saved. You can delete the old diary entries."

Surviving the Crash

Let's go back to our banking example. The power goes out right after the user sees "Success."

1. The server boots back up.
2. The database looks at its main data files. They are outdated because the checkpoint hadn't run yet.
3. The database looks at the WAL diary and sees a transaction that happened after the last checkpoint.
4. Crash Recovery: The database reads the WAL and "replays" the transactions, updating its tables exactly as they were before the crash.
5. The $10,000 transfer is recovered. Durability is achieved.

Real-World Examples

• PostgreSQL: Uses a WAL. You can even use the WAL for features like Point-In-Time Recovery. If you accidentally delete a table at 2:00 PM, you can restore a backup from yesterday and replay the WAL right up until 1:59 PM.
• MySQL (InnoDB): Calls its implementation the "Redo Log", but the concept is exactly the same.
• Distributed Systems (Raft/Paxos): Even distributed consensus algorithms use a form of write-ahead logging to ensure multiple servers agree on the state of the world before committing a transaction.

Summary

• Durability guarantees that once a transaction is committed, it survives crashes and power losses.
• Writing directly to database tables on disk for every transaction is too slow (random I/O).
• Databases use a Write-Ahead Log (WAL). They append the transaction to a fast, sequential log file on disk before doing anything else.
• Actual data tables are updated in RAM (for speed) and slowly written to disk in the background via Checkpoints.
• If a crash occurs, the database simply replays the WAL to reconstruct the lost data in RAM, ensuring zero data loss.