Write-Ahead Logging: How Databases Protect Every Write

⏱️ Lectura: 13 min

Every time PostgreSQL or SQLite confirm a write, they’ve already recorded that change twice before touching the final data file: first in a sequential log, then in the actual page. This deliberate duplication is called write-ahead logging, and it’s why a power outage in the middle of a transaction doesn’t leave you with a corrupted database.

📑 En este artículo

1. TL;DR
2. What write-ahead logging is and why it exists
3. How it works internally
4. Practical examples
5. Getting started: enabling and verifying the WAL step by step
6. Real-world use cases
7. Common mistakes and best practices
8. Comparison with alternatives
9. Going deeper: group commit and logical replication
10. Frequently Asked Questions
    1. Does write-ahead logging replace backups?
    2. What happens if the .wal file grows too large in SQLite?
    3. Can I disable the WAL in PostgreSQL to gain speed?
    4. What’s the difference between physical and logical WAL?
    5. Do SQLite and PostgreSQL implement the WAL the same way?
11. References

The concept originated with IBM’s ARIES project in the nineties and now runs inside nearly every serious transactional engine: PostgreSQL, MySQL with InnoDB, SQLite, and SQL Server use it in different ways but with the same underlying idea. This article explains how it works internally, how to enable and verify it with real commands, and in which cases it’s better to avoid it.

TL;DR

• PRAGMA journal_mode=WAL; enables write-ahead logging mode in SQLite in a single line.
• Every write is recorded in the log first and only applied to the data file afterward.
• pg_current_wal_lsn() in PostgreSQL reveals the exact log position at any moment.
• A poorly tuned checkpoint lets the .wal file grow until it takes up more space than the database itself.
• PostgreSQL streaming replication literally sends the WAL to each replica.
• SQLite in WAL mode allows concurrent readers while a single writer modifies data.
• fsync=off gains speed, but turns every sudden power loss into corrupted data.
• SHOW wal_level; and PRAGMA wal_checkpoint(TRUNCATE); confirm the actual state of the log.

What write-ahead logging is and why it exists

Think of a ship’s logbook. Before the captain updates the official chart with the final position, every maneuver gets written down first: the time, the heading, the wind. If something goes wrong halfway through the voyage, that logbook lets you reconstruct exactly what happened and where the ship ended up. A database with write-ahead logging does the same with every change: it records it in a sequential log before touching the definitive data page.

The problem it solves is simple to state and hard to solve well. A transaction can modify several pages in memory, and the operating system can restart before those pages reach disk. Without a prior record of the change, there’s no way to know whether a half-written page on disk belongs to a committed transaction, an aborted one, or a corrupted mix of both.

The ARIES algorithm, published by IBM researchers, formalized the solution in 1992: every change is first described as a log record with a sequence number (LSN, log sequence number), that record is forced to disk with fsync before the transaction is confirmed to the client, and only afterward does the engine calmly apply the modified page to the data file. If the process crashes in the middle, the log has everything needed to redo committed transactions and undo the ones that were left halfway through.

The naive alternative would be to write each modified page directly and hope nothing fails at the exact wrong moment. Engines that did this in the eighties lost data with every badly timed power outage, which is why write-ahead logging ended up as the de facto standard in PostgreSQL, MySQL, SQLite, and practically every modern relational database.

How it works internally

The flow has three actors: the application requesting the write, the log (a sequence of files that grow append-only), and the final data file. When you commit a transaction, the engine builds a record with the exact change, assigns it an increasing LSN, and appends it to the end of the current log. Only once that record has durably reached disk does the engine respond with the commit to the application.

The modified data page stays in memory, in the buffer pool, and gets written to disk later, in a process called a checkpoint. A checkpoint walks through the dirty pages in memory, flushes them to the data file, and records in the log up to which LSN has already been applied. That bounds how much log needs to be replayed if the system restarts: instead of redoing the entire history from the first record, recovery starts from the last confirmed checkpoint.

The following diagram summarizes that flow for a single write, from when the application requests it to when the checkpoint flushes it to the definitive file.

sequenceDiagram
    participant App as Application
    participant Log as Write-Ahead Log
    participant Disk as Data file
    App->>Log: requests to write a change
    Log-->>App: confirms after fsync
    Note over Log,Disk: the checkpoint runs in the background
    Log->>Disk: applies the modified page

What makes this scheme fast is that fsync only touches the log, which is sequential and therefore cheap to write. The data page, which can be scattered anywhere in the file, gets updated later without rushing the client waiting for confirmation.

Practical examples

SQLite implements write-ahead logging as an alternative mode to the default journal. Enabling it takes a single statement:

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;

The first line changes the journaling mode: instead of copying the original pages to a rollback file before modifying them, SQLite now writes changes to a separate name.db-wal file and leaves the main database untouched until the next checkpoint. The second line relaxes the fsync level because in WAL mode you no longer need the stricter guarantee that synchronous=FULL requires; the SQLite manual itself documents this combination as the recommended one for most applications.

A more realistic example: an application inserting rows while another process reads the same file at the same time, something that would block the reader under the classic journal mode.

import sqlite3

con = sqlite3.connect("inventario.db")
con.execute("PRAGMA journal_mode=WAL;")
con.execute(
    "INSERT INTO productos (nombre, stock) VALUES (?, ?)",
    ("mechanical keyboard", 120),
)
con.commit()
con.close()

While that process writes, another process can open the same database and run a SELECT without waiting: in WAL mode, readers see a consistent snapshot of the file as it was before the write in progress, without blocking against it.

In PostgreSQL the WAL isn’t optional, it’s always active, but its level of detail can be configured. For streaming replication you need to raise wal_level above the minimum:

-- in postgresql.conf
wal_level = replica
max_wal_senders = 5
wal_keep_size = 512MB

With wal_level set to replica, every log record includes enough information for a replica server to reconstruct the same state without access to the original data file.

Getting started: enabling and verifying the WAL step by step

For SQLite the flow is straightforward. Run the PRAGMA once per connection and confirm the result:

sqlite3 inventario.db "PRAGMA journal_mode=WAL;"
-- expected response: wal

sqlite3 inventario.db "PRAGMA journal_mode;"
-- confirms it's still wal after reconnecting

Two new files will appear next to the database: inventario.db-wal with the pending changes, and inventario.db-shm, a shared memory area that coordinates readers and the writer.

In PostgreSQL, after editing postgresql.conf and restarting the service, confirm the active level and the current log position:

SHOW wal_level;
-- expected response: replica

SELECT pg_current_wal_lsn();
-- returns something like 0/3000060, the current log position

If you’re going to enable replication, the next step is to create a dedicated user and, on the replica server, point primary_conninfo to that user. The exact details depend on the version, and PostgreSQL’s WAL parameters documentation covers them with the detail needed for each release.

Real-world use cases

PostgreSQL streaming replication is, at its core, the WAL traveling over the network: the primary server doesn’t compute anything special to replicate, it simply sends the same log records it already generates for its own crash recovery. Each replica applies them in the same order and reaches the same state, with a delay of milliseconds to seconds depending on network latency.

SQLite uses the same mechanism for a different purpose: mobile and desktop applications that need several threads to read the database while a background process syncs data. A huge number of embedded apps rely on that combination of non-blocking readers and a single writer.

Other systems borrow the same idea without calling it write-ahead logging. Apache Kafka, for example, is essentially a distributed append-only log that applies the same principle at cluster scale; the parallel with a relational database’s WAL isn’t a coincidence, both solve the same problem of order and durability with the same underlying data structure.

Common mistakes and best practices

The most common mistake in SQLite is opening a long transaction on a database in WAL mode and leaving it open. As long as that read transaction stays active, the automatic checkpoint can’t advance past the point where it started, and the .wal file grows without limit until the transaction closes.

⚠️ Heads up: a .wal file that keeps growing almost always means there’s an open read connection nobody closed.

In PostgreSQL the equivalent is a replica that’s down or disconnected while wal_keep_size or another replication slot keeps retaining log segments for it. If that replica takes too long to come back, the primary can accumulate gigabytes of retained WAL waiting for someone to consume it.

Another classic mistake is disabling fsync to gain speed under heavy write loads. fsync=off in PostgreSQL or PRAGMA synchronous=OFF in SQLite eliminate the full durability guarantee: the operating system can report the write as complete without the physical disk having actually received it yet, and a power outage at that exact instant corrupts the database.

As a best practice, measure first. The pg_stat_bgwriter view in PostgreSQL shows how many checkpoints were forced by time versus how many were forced by accumulating too much WAL, a direct signal of whether it’s worth raising max_wal_size or reviewing the write load.

Comparison with alternatives

Write-ahead logging isn’t the only technique for guaranteeing durability, though it is the most widespread one in modern relational engines.

Technique	When to use it	Advantage	Limitation
Write-ahead logging	General-purpose transactional engines (PostgreSQL, SQLite, InnoDB)	Fast recovery and native replication from the same log	You have to manage log growth and tune checkpoints
Shadow paging	Minimalist embedded engines with few concurrent writes	No redo phase needed, recovery is nearly instant	Fragments the file and complicates concurrent transactions
Asynchronous writes without a log	Caches or metrics that can tolerate losing the last few seconds	Maximum possible write speed	Guaranteed data loss on a power outage

Going deeper: group commit and logical replication

A single fsync per transaction would be slow under heavy load, so modern engines group several pending commits and force them to disk in a single fsync. This is called group commit. PostgreSQL enables it through commit_delay and commit_siblings, waiting a few extra microseconds to gather more transactions before paying the cost of the fsync.

There’s also a distinction between physical WAL and logical WAL. Physical WAL describes changes in terms of disk pages, sufficient and fast for recovery and for replicas running the exact same engine version. Logical WAL describes changes as operations (insert this row, update this column), which allows replicating between different versions or feeding external systems like event queues.

💡 Tip: if you need to send changes from PostgreSQL to Kafka or another external system, look up logical decoding in the documentation: it uses the same WAL as its source, but translates it into a format readable outside the engine itself.

PostgreSQL’s replication slots solve the problem of which WAL segments to keep: they tell the primary not to delete anything until that particular replica has consumed it, at the cost that a replica that’s down for good can fill up the primary’s disk if nobody removes its slot.

flowchart TD
    A["Primary: generates WAL"] --> B["Replication slot retains segments"]
    B --> C["Replica: consumes the WAL"]
    C --> D[("Replica database")]
    subgraph PostgreSQL Cluster
    A
    B
    D
    end

📖 Summary on Telegram: View summary

Your next step: enable PRAGMA journal_mode=WAL; on a test SQLite database and open two simultaneous connections, one writing in a loop and another reading, to see with your own eyes that the reader never blocks.

Frequently Asked Questions

Does write-ahead logging replace backups?

No. The WAL protects against process or operating system crashes, but a corrupted WAL or a disk that fails completely takes both the log and the data with it. Backups are still necessary; in fact, PostgreSQL uses the WAL itself to build continuous backups, but that’s an additional use, not a replacement.

What happens if the .wal file grows too large in SQLite?

The automatic checkpoint triggers every 1000 pages by default, but if a long read blocks it, the file keeps growing until that read finishes. Forcing it manually with PRAGMA wal_checkpoint(TRUNCATE); empties the log and shrinks the file to its minimum size.

Can I disable the WAL in PostgreSQL to gain speed?

Not entirely: PostgreSQL always writes WAL because it needs it for crash recovery, even in a single-node installation. What you can adjust is the level of detail with wal_level and how aggressive fsync is, with the durability trade-off that implies.

What’s the difference between physical and logical WAL?

Physical WAL records low-level changes to disk pages; logical WAL translates those same changes into readable operations like inserts or updates, designed for replicating between different versions or exporting data to external systems.

Do SQLite and PostgreSQL implement the WAL the same way?

They share the principle, not the implementation. SQLite keeps a single .wal file per database and flushes it entirely on each checkpoint; PostgreSQL splits the log into 16 MB segments that get recycled and can keep several replicas consuming the same stream simultaneously.

References

• PostgreSQL Documentation: official introduction to the write-ahead log and its durability guarantees.
• PostgreSQL Documentation: complete reference of WAL configuration parameters.
• SQLite.org: specification of write-ahead logging mode, its PRAGMAs, and its limits.
• Wikipedia: history of the ARIES algorithm and the write-ahead logging concept.

📱 Do you like this content? Follow @programacion on Telegram for daily tech content in Spanish: quick summaries, fresh content every day.

Imagen destacada: Foto de Denny Müller en Unsplash