Learn With Bagus

Appearance

Write-Ahead Log: Why Databases Write Twice but Stay Fast ​

12 min readdatabase

Every committed database transaction promises one thing that sounds expensive: the data must survive even if the database server suddenly dies. This guarantee is called durability.

Writing to disk is slow. If every data change had to be written directly to the table file on disk before COMMIT could be confirmed, transactions would be extremely slow, and the database would spend most of its time waiting for the disk.

Modern relational databases satisfy both demands at once, strong durability and fast commits, with one mechanism called the Write-Ahead Log (WAL). Interestingly, this solution makes the database write "twice": once to the log, and later once more to the table file. Yet strangely, it is exactly this design that makes commits much faster.

What is a Write-Ahead Log? ​

The Write-Ahead Log works with one simple rule that gives it its name: before a data page in the table file is changed, the change must first be recorded to the log on disk. Write ahead means "the log is written first".

The structure of the WAL is very simple:

  • The log file is append-only: new records are always added at the end of the file, and old records are never modified.
  • Each record contains a change delta: "row X on page Y changed from value A to value B", not a copy of the whole data page.
  • Records are written in order over time, forming a complete history of every change in the database.

The mechanism has a different name in each database, but the core idea is the same: WAL in PostgreSQL and the redo log in MySQL InnoDB.

Mental Model

The WAL is not the final storage place for data. The WAL is a change record used for two purposes: making COMMIT crash-safe (durability), and delaying expensive data writes to a better time.

Mental Model: two storage places while a transaction runs ​

To understand the WAL, you need to separate three components by their role:

  • Buffer cache (memory): the database does not read and write data pages directly to disk for every operation. Pages that are actively accessed are first placed in a memory area called the buffer cache (called shared buffers in PostgreSQL and the buffer pool in MySQL). Changes from UPDATE or INSERT are first applied to the page in this memory. A page that has been changed in memory but not yet synced to disk is called a dirty page.
  • WAL file (disk): when a transaction performs a COMMIT, the only thing that must reach the disk is the WAL record of that change. Data pages in the buffer cache do not need to be flushed.
  • Checkpoint: a background process that cleans dirty pages (flush): it writes changed pages from the buffer cache to the table file, so the copy in memory and the copy on disk become the same. After a dirty page is saved, the WAL records that were already used are no longer needed for recovery, and their log files can be recycled.

The write flow looks like this:

Loading diagram...

Notice the order: the WAL is written and flushed to disk first. Once the WAL record is safe, COMMIT can be confirmed to the application, even though the data pages on disk have not changed at all.

One note about terms before we continue: flush always means writing memory contents to disk, both for the WAL and for data pages. Flushing the WAL means writing log records to the WAL file on disk, to make them crash-safe. Flushing a dirty page means writing the dirty page to the table file, and once it is saved, the page status changes from dirty to clean. The word cleaning used for the checkpoint refers to this page status change, not to deleting log contents.

When does a checkpoint run? ​

A checkpoint does not run on every commit. There are two triggers you need to understand (examples from PostgreSQL):

  • Time based: the checkpoint_timeout setting, 5 minutes by default. The database runs a checkpoint at this interval, unless the second trigger is reached first.
  • WAL size based: the max_wal_size setting. If the amount of WAL written since the last checkpoint approaches this limit, the database speeds up the checkpoint even if 5 minutes have not passed yet. Applications that write a lot, such as large batches or ETL jobs, trigger checkpoints more often than low-traffic applications.

MySQL InnoDB does not use a time interval. Its checkpoints are adaptive: they follow the available redo log capacity and the proportion of dirty pages in the buffer pool. The idea is the same as PostgreSQL: checkpoints run periodically based on write load, not on every commit.

Mental Model

The consequence: recovery time after a crash depends on how much WAL has not been moved since the last checkpoint. The more often a checkpoint runs, the less WAL has to be replayed when the database boots, and the faster the database is ready to serve again.

The Lifecycle of WAL Files ​

Now that the checkpoint is clear, the next side to discuss is the log file itself: what happens to it over time.

If every COMMIT keeps writing to the WAL, does the WAL file grow without limit? The answer is no, and the checkpoint is exactly what cleans the log. The WAL is made of fixed-size segments (in PostgreSQL, 16 MB per segment). When a checkpoint finishes, all changes recorded in the old segments are already safely saved in the table file. Those segments are no longer needed for recovery, so the database may recycle them: reuse them as the place for the next records, or delete them if capacity is more than enough.

Loading diagram...

A segment that is recycled is a segment whose entire contents are already saved in the table file. After being recycled, its old log copy no longer exists in the WAL, but the data is not lost because it is already saved permanently in the table file. Because of this, disk usage for the WAL goes up and down: it grows since the last checkpoint, then stays stable after old segments are recycled. Only the active segment being written is always present, so the size never returns to zero.

An important rule: segments must not be recycled while some reader has not finished consuming them, for example a replication server or a CDC connector. If one of them stops for a long time, the WAL piles up and the database disk can become full. This is why replication lag and CDC lag need to be monitored in production.

What about SELECT after COMMIT, when data has not been moved to the main table yet? ​

This is a natural question: if COMMIT only writes to the WAL and writing to the table file is delayed until the checkpoint, what about a SELECT query that runs right after? Does that query read old data because the table file on disk has not been updated?

The answer is no. To understand it, look again at what happens during COMMIT:

  1. The database writes the WAL record to disk.
  2. At the same moment, the data change is already applied to the page in the buffer cache (memory). That dirty page already holds the newest value.

So when the next SELECT runs, the process looks like this:

Loading diagram...

Here is the key to understanding this: SELECT always reads from the buffer cache first. A page that was just changed by a committed transaction is definitely still in the buffer cache, complete with its newest value. Even if the table file on disk still holds the old value, the SELECT query never reads that old value, because the memory copy is newer and that is what the database treats as the truth.

Whenever the checkpoint cleans a dirty page to the table file, nothing changes from the reading side: the value in memory and the value on disk finally become the same. The WAL mechanism makes data truth never depend on how fast the table file is flushed.

Limits of this explanation

The rule "the buffer cache holds the newest value" applies to committed transactions. Other transactions may read the state before the commit depending on the isolation level, which is the setting that controls how visible uncommitted changes from other transactions are. What the WAL guarantees is: after a commit, the newest value cannot be lost, and later reads will never get the old value just because the data has not been flushed yet.

As a final note: if the server crashes before the checkpoint gets a chance to clean the dirty pages, recovery at boot reads the WAL and replays the changes that were already committed, so the table file becomes complete again.

Why does the WAL matter? ​

The WAL carries two important roles at once:

  • Durability: COMMIT is confirmed only after the WAL record is truly safe on disk. Because of that, even if the server dies the next second, the database can replay the WAL and restore every committed change. The durability guarantee of database transactions is technically built on top of this WAL.
  • Performance: a transaction only waits for one small write to the log, not for the expensive writing of data pages. Commits become fast, and the database stays busy serving the next queries.

Without a WAL, a database would have to choose: fast commits with the risk of losing data on crash, or strong durability with slow commits.

Why is the WAL a good design? ​

Now for the question that comes up most often: if the WAL and the table file are both on disk, why is writing to the WAL faster than writing to the main table? There are three main reasons:

1. Sequential append vs random I/O ​

Writes to the WAL always go to the end of the file, in order, without needing to find a position (seek). Writes to the table file are spread across many distant locations: accounts pages in one area, orders pages in another area, index pages somewhere else.

On an HDD, every head movement is expensive in time. On an SSD, sequential write is still more efficient than random write because the access pattern is easier for the controller to predict. One sequential write to the WAL is cheaper than ten scattered writes to the table file, even when the total bytes are similar.

2. Write the delta, not the whole page ​

A WAL record stores only the change, not the whole state of the data. Example: a money transfer application stores the balance of each account in the accounts table, and one payment only changes the balance column of one row. The WAL records this change as "balance of ACC-001 changed from 50000 to 150000". Meanwhile, to save the data page as a whole, the database must write the entire page block, even if only one row inside it changed. A small delta means fewer and faster writes.

3. Group commit ​

Many transactions that commit at almost the same time can be merged into one WAL flush to disk. Five transactions committing in the same millisecond only pay the disk sync cost once, not five times.

Mental Model

The core of the WAL design: move the expensive and scattered writes (data pages) to a relaxed time in the background, and trade them for a cheap and sequential write (the log) on the critical commit path. Durability is protected by the log, and throughput is protected by delaying the data writes.

To close this article, the same log turns out to be reused for other purposes at work: streaming replication (a standby server replays the WAL to follow the primary server), point-in-time recovery (restoring the database to a specific point in time from a backup plus the WAL), and change data capture or CDC (tools like Debezium read the WAL again to stream every data change to other systems such as search indexes, caches, and data warehouses). One simple design, many benefits.

Further reading

To see how the WAL is used as an event source and its role in solving the dual write problem, read the article Change Data Capture: Streaming Every Data Change in Near Real-Time.

Summary ​

  • The WAL enforces one rule: write the log to disk first, then change the data pages. That is where its name comes from, write-ahead.
  • During a COMMIT, the only thing that must be safe on disk is the WAL record. Data changes are applied in the buffer cache (memory) as dirty pages.
  • SELECT always reads from the buffer cache first, so query results stay up to date even when the table file has not been flushed.
  • A checkpoint cleans dirty pages to the table file (flush), triggered by time (checkpoint_timeout, 5 minutes by default in PostgreSQL) or WAL size (max_wal_size). InnoDB uses an adaptive checkpoint.
  • The WAL is faster than writing directly to the table because its writes are sequential, only store the change delta, and can be group committed.
  • Flushing the WAL means writing records to disk to make them crash-safe, not emptying the log. Old segments are only recycled after a checkpoint has saved their changes to the table file, so the WAL size goes up and down and never returns to zero.
  • The same log is used for recovery, streaming replication, point-in-time recovery, and CDC.

References ​