Write-Ahead Logging Explained: fsync, Group Commit, Checkpoints, Torn Pages and How Databases Survive Crashes

Pull the power cord on a busy database server and, when it boots, every transaction it acknowledged is still there. That is not luck. It is the result of write-ahead logging, a forty-year-old discipline that turns a messy pile of half-written pages into a recoverable state. Yet the guarantee rests on a chain of promises that is only as strong as its weakest link: the kernel’s fsync, the drive’s write cache, the page size of the file system and the recovery code itself.

This matters now because the weak links moved. NVMe drives expose volatile caches and Force Unit Access semantics that most engineers never inspect. Cloud volumes hide the hardware entirely. And the 2018 “fsyncgate” episode proved that even PostgreSQL had misread what a failed fsync means on Linux.

You will leave with a mental model of the WAL write path, worked group-commit throughput math, a map of how five engines defend against torn pages, a runnable 80-line WAL in Python, and a checklist for auditing durability end to end.

What this covers: the WAL rule and log sequence numbers, fsync semantics down to the device, group commit, checkpoints, torn pages, ARIES-style database crash recovery, PostgreSQL, InnoDB, SQLite, RocksDB and Raft logs, and the failure modes that still bite in 2026.

Context and Background

Every transactional database faces the same dilemma. Changing a data page in place is the fastest way to keep data organised, but a crash in the middle of a multi-page update leaves the files inconsistent. Writing every changed page to disk at commit time would fix that, but it turns one logical change into many random writes and makes commits painfully slow.

The industry’s answer, formalised in IBM’s ARIES paper (Mohan, Haderle, Lindsay, Pirahesh and Schwarz, ACM Transactions on Database Systems, 1992), is to separate durability from data placement. Each change is first described in a sequential, append-only log. Only the log must reach stable storage before a commit is acknowledged. The data pages can be written later, lazily, in any order, because the log holds enough information to redo committed work and undo uncommitted work after a crash. The ARIES paper on IBM Research notes it was implemented in DB2, Starburst and several other systems; its vocabulary of log sequence numbers, redo and undo passes, and compensation records is still how engineers describe recovery today.

Today almost every serious storage engine uses some form of write-ahead logging. PostgreSQL calls it WAL and stores it in pg_wal. MySQL’s InnoDB calls it the redo log. SQLite offers a WAL journal mode. RocksDB and other log-structured merge (LSM) engines write a WAL before inserting into the in-memory memtable, as covered in our LSM-tree and RocksDB compaction deep dive. Consensus systems such as Raft treat the replicated log itself as the write-ahead log of a replicated state machine.

Most explanations stop at “write the log first, then the data.” My thesis is different: write-ahead logging does not give you durability; it concentrates the durability problem into a single, measurable point — the log flush — so you can engineer, test and pay for it deliberately. Everything that goes wrong with WAL in production comes from not knowing exactly what that flush guarantees on your specific stack, or from paying for it more times than necessary. The rest of this post follows that single flush from the transaction down to the flash cells and back up through recovery.

How Write-Ahead Logging Works: The WAL Durability Architecture

Direct answer: Write-ahead logging is a recovery protocol in which every change to a database page is first described in an append-only log, and that log record must reach stable storage before the modified page does. A commit is durable once its commit record is flushed. After a crash, the engine replays the log from the last checkpoint to rebuild a consistent state.

Figure 1: The WAL write path. A transaction dirties a page in the buffer pool and appends an LSN-stamped record to the WAL buffer. A flush makes the log durable and releases the commit acknowledgement. The checkpointer may write a data page only when that page’s LSN is already covered by the flushed log.

Read the figure as two independent pipelines that share one rule. The upper pipeline is the hot path: the transaction generates a log record, the record is copied into a WAL buffer in shared memory, and a flush writes and syncs the buffer to the log file before the client hears “COMMIT”. The lower pipeline is the cold path: dirty pages sit in the buffer pool and are written back later by a checkpointer or background writer. The decision diamond is the whole protocol. A data page may only go to disk if every log record that touched it is already durable.

The one rule: log before data, and why it permits “steal, no-force”

Recovery theory describes buffer management with two policies. Steal means the buffer manager may write a page containing uncommitted changes to disk, for example to free memory. No-force means a committing transaction does not have to force its modified pages to disk. Steal plus no-force is the most flexible and fastest combination, and it is also the hardest to recover from. Stolen pages put uncommitted data on disk, which needs undo. Unforced pages mean committed data may exist only in memory, which needs redo.

The WAL rule makes that combination safe. Because a log record always precedes its page to disk, any uncommitted change that reached the data file has a log record describing how to undo it. Because the commit record is flushed before the acknowledgement, any committed change missing from the data file has a log record describing how to redo it. The data files may be arbitrarily stale or arbitrarily ahead; the log is the authority.

The performance consequence is the reason WAL won. A commit costs one sequential append plus one flush, regardless of how many pages the transaction touched. Random page writes are deferred, batched and spread out over time. On spinning disks the difference between sequential and random I/O was two orders of magnitude; on NVMe the gap is smaller, but a flush is still the most expensive thing a transaction does.

LSNs: the clock that ties log and pages together

A log sequence number (LSN) identifies a position in the log. In PostgreSQL it is literally “a byte offset into the WAL, increasing monotonically with each new record,” exposed as the pg_lsn type, according to the PostgreSQL WAL internals documentation. InnoDB also tracks its redo log with an ever-increasing LSN and records the checkpoint LSN in the redo file header.

The engine maintains a handful of LSNs, and each enforces part of the protocol. Every data page carries a pageLSN: the LSN of the last record that modified it. The log manager tracks a flushedLSN: everything up to this point is on stable storage. The buffer manager checks pageLSN <= flushedLSN before writing a page, and if the check fails it flushes the log first. During recovery, comparing a record’s LSN with the pageLSN on disk tells the engine whether the change is already present. That comparison is what makes redo idempotent, so recovery itself can crash and restart safely.

The checkpoint adds two more positions. The redo point is the oldest LSN that recovery might need, and the control file (PostgreSQL’s pg_control) records where the latest checkpoint record lives. Anything older than the redo point can be recycled or archived. This is how a log that grows forever in theory occupies bounded disk in practice.

Commit means “log is durable”, not “data is written”

The most common misunderstanding about WAL durability is about what a commit acknowledgement certifies. It certifies that the commit record and every record before it reached the storage layer through a flush that the engine believes is durable. It says nothing about the heap or the index. Those may be written seconds or minutes later.

This framing explains most configuration knobs. PostgreSQL’s synchronous_commit (default on) decides whether a commit waits for that flush. MySQL’s innodb_flush_log_at_trx_commit makes the same choice with values 0, 1 (the default) and 2. SQLite’s synchronous pragma does it for WAL mode. RocksDB exposes it per write through WriteOptions::sync. Every one of them is answering a single question: does the client wait for the log flush, or not?

It also explains why the log’s storage deserves more attention than the data files’. The log takes small, latency-critical sequential writes with a flush on almost every commit. Data files take large, throughput-oriented writes that can be spread out. Putting them on separate devices, or at least understanding which one dominates latency, is the first tuning decision for any WAL-based system.

Walk-Through: fsync, Group Commit, Checkpoints, Torn Pages and ARIES Recovery

The architecture is simple on a whiteboard. The engineering lives in five places: what a flush actually guarantees, how many commits share each flush, how checkpoints bound the log, how partial page writes are repaired, and how recovery replays it all. We take them in that order, then compare five real engines and build a tiny WAL in Python.

What fsync actually promises, layer by layer

When a database calls write() on a log file, the bytes usually land in the kernel page cache, which is ordinary volatile RAM. Nothing has reached the device. The POSIX fsync() call asks the operating system to push a file’s dirty data and metadata to the storage device and to wait until the device reports it is stable. fdatasync() does the same but skips metadata that is not needed to read the data back, such as modification times. PostgreSQL’s wal_sync_method defaults to fdatasync on Linux, with open_datasync, fsync, fsync_writethrough and open_sync as alternatives, according to the PostgreSQL WAL configuration reference.

Figure 2: Where a “durable” write can still be lost. Each layer between the database process and non-volatile media may cache data. fsync drives the data through the page cache and asks the block layer to flush or use FUA; drives with power-loss protection can safely acknowledge from their cache.

Below the kernel there are two more caches. A RAID or host bus adapter may hold writes in its own memory, and the drive itself usually has a write cache. Linux handles the drive cache through two request flags documented in the kernel’s writeback cache control guide. REQ_PREFLUSH tells the device to flush its volatile cache before the request, and REQ_FUA (Force Unit Access) delays completion of a write until its data is on non-volatile storage. On devices that report no volatile cache, the block layer simply strips those flags because they have nothing to do.

That last detail is why enterprise SSDs with power-loss protection (PLP) are so much faster at fsync. PLP drives carry capacitors that hold enough energy to drain the on-board cache to NAND when power fails, so they can safely report their cache as non-volatile. A flush then costs little more than a normal write. Consumer drives without PLP must actually program flash on every flush, and the flush latency can be an order of magnitude or more higher. The exact numbers depend on the device, so measure them rather than trusting a datasheet.

The flags O_DIRECT and O_DSYNC change the picture again. O_DIRECT asks the kernel to bypass the page cache and move data straight between user buffers and the device, subject to alignment rules. It does not by itself guarantee durability, because the device cache may still be volatile. O_DSYNC makes each write() behave as if it were followed by fdatasync(), which lets an engine combine the write and the flush into one system call. PostgreSQL’s open_datasync method uses this approach, and InnoDB commonly runs with innodb_flush_method=O_DIRECT for data files. MySQL 8.4 changed that default to O_DIRECT where supported, falling back to fsync, as summarised in Oracle’s MySQL 8.4 defaults write-up by Frédéric Descamps.

The PostgreSQL documentation is blunt about the gap between theory and hardware. Its reliability chapter warns that consumer SATA drives and many SSDs ship with volatile write-back caches, that “many solid-state drives” do not honour cache flush commands by default, and that administrators should test the stack with diskchecker.pl. It also notes that file systems issuing cache flushes can erase much of the benefit of a battery-backed controller unless barriers are reconfigured. The lesson is that the database can only be as honest as the layer below it.

Group commit: paying for one flush, committing many transactions

If every commit needed its own flush, a single log could commit at most 1 / flush_latency transactions per second. Group commit removes that ceiling. While one flush is in flight, new commit records accumulate in the WAL buffer. When the flush completes, the next waiting transaction becomes the leader, flushes everything accumulated so far and wakes every transaction whose commit LSN is now covered. One expensive flush retires a whole batch.

Here is a worked example. The latencies below are illustrative assumptions, not measurements of any specific device.

Scenario	Assumed flush latency	Concurrent committers	Commits per flush	Approx. commits/s
No group commit, slow flush	2 ms	1	1	500
No group commit, slow flush	2 ms	64	1	500 (serialised)
Group commit, slow flush	2 ms	64	about 32 to 64	16,000 to 32,000
No group commit, PLP drive	0.05 ms	1	1	20,000
Group commit, PLP drive	0.05 ms	64	a few	bounded by CPU and WAL insert locks

The arithmetic for the third row works like this. In a closed loop where 64 clients each commit and then immediately start the next transaction, a client that arrives during a flush waits for that flush to finish and then for its own, so average commit latency sits between one and two flush times. By Little’s Law, throughput equals concurrency divided by latency: 64 clients divided by roughly 2 to 4 ms gives roughly 16,000 to 32,000 commits per second. Latency per commit gets slightly worse, but throughput rises by more than an order of magnitude.

The PLP rows show the flip side. When flushes are cheap, batches stay small, and the bottleneck moves to the CPU work of inserting records into the WAL buffer and the locks that serialise it. That is why modern engines invest so much in parallel log insertion and why “just buy faster disks” eventually stops helping.

PostgreSQL performs group commit naturally and offers commit_delay (default 0, in microseconds) and commit_siblings (default 5) to make it more aggressive. With a non-zero delay, the group-commit leader sleeps briefly after acquiring the flush lock so that other backends can add their records first; the sleep is skipped when fewer than commit_siblings other transactions are active. The WAL configuration chapter suggests starting at about half the average flush time that pg_test_fsync reports for an 8 kB write. MySQL and RocksDB use comparable leader-follower batching internally, and RocksDB lets applications batch explicitly with WriteBatch.

Asynchronous commit is the other lever, and it is a different trade. With synchronous_commit = off, PostgreSQL acknowledges before the flush. The WAL writer flushes every wal_writer_delay (default 200ms), and the documented worst-case loss window is three times that delay. Crucially, the documentation states that asynchronous commit risks losing the last few transactions but not corrupting the database, unlike fsync = off, which can cause “arbitrarily bad corruption”. InnoDB’s innodb_flush_log_at_trx_commit = 2 writes the log at commit but flushes roughly once per second, which survives a mysqld crash but not an operating system crash or power loss.

Checkpoints: bounding the log and the recovery time

Without checkpoints, recovery would have to replay the log from the beginning of time. A checkpoint flushes dirty pages so that the data files reflect all changes up to some LSN, then writes a checkpoint record and saves its position in the control file. Recovery starts at the checkpoint’s redo point, and older log segments can be recycled.

PostgreSQL triggers a checkpoint when checkpoint_timeout (default 5min) elapses or when WAL growth approaches max_wal_size (default 1GB), whichever comes first. To avoid I/O storms it spreads page writes across checkpoint_completion_target (default 0.9) of the interval. If checkpoints arrive closer than checkpoint_warning, the server logs a hint to raise max_wal_size. WAL is stored in segments that are normally 16 MB, configurable only at initdb time with --wal-segsize.

Checkpoint frequency is a three-way trade. Frequent checkpoints shorten recovery because there is less log to replay. They also increase total write volume, because hot pages are written repeatedly and, as the next section explains, because each checkpoint resets the full-page-image logic. Infrequent checkpoints reduce write volume and smooth I/O, at the cost of longer recovery and more disk for retained WAL. A reasonable starting point is to size max_wal_size so that checkpoints are triggered by time, not by volume, during normal peaks.

InnoDB uses a different design called fuzzy checkpointing. It continuously flushes dirty pages from the flush list in LSN order and advances the checkpoint LSN as the oldest dirty page moves forward. The redo log is a fixed-capacity ring; since MySQL 8.0.30 its size is governed by innodb_redo_log_capacity, and InnoDB maintains 32 redo files in the #innodb_redo directory, each one thirty-second of the capacity, per the InnoDB redo log documentation. If writes outrun page flushing, the log fills and InnoDB must flush synchronously, a stall that shows up as sudden latency spikes.

Torn pages: when the disk writes half a page

A torn page, or partial write, happens when power fails while a multi-sector page is being written. PostgreSQL uses 8 kB pages while disks traditionally guarantee atomicity only for 512-byte or 4 kB sectors, so a crash can leave a page that is half old and half new. Redo cannot fix this on its own, because many redo records are deltas that assume a self-consistent page to apply to.

Engines solve this in different ways. PostgreSQL’s full_page_writes (default on) logs a complete image of each page the first time it is modified after a checkpoint. Recovery restores that image and replays subsequent deltas on top. The cost is extra WAL volume right after each checkpoint, which is why checkpoint frequency and WAL volume are linked. The documentation allows turning it off only on file systems that prevent partial page writes, citing ZFS as an example.

InnoDB uses a doublewrite buffer instead. Before writing pages to their home location, InnoDB writes them to a sequential doublewrite area and syncs it. If a home write tears, recovery finds an intact copy in the doublewrite files. The InnoDB doublewrite documentation stresses that this does not double I/O operations because the doublewrite batch is one large sequential write with one sync. innodb_doublewrite accepts ON, OFF, DETECT_AND_RECOVER and DETECT_ONLY, and the docs reserve OFF for cases where performance matters more than integrity.

Detection is the companion to repair. PostgreSQL protects every WAL record with a CRC-32C and, since PostgreSQL 18, initdb enables data page checksums by default. A checksum cannot fix a torn page, but it turns silent corruption into a loud error. SQLite and RocksDB take the simpler path of checksumming log records and treating a bad checksum at the tail as the end of the log.

Figure 3: ARIES-style database crash recovery. Analysis finds loser transactions and the earliest dirty page. Redo repeats history from the minimum recLSN, skipping records whose effects are already on the page. Undo rolls back losers and logs compensation records so a second crash cannot undo twice.

Database crash recovery, ARIES-style: analysis, redo, undo

ARIES recovery runs three passes. The analysis pass reads forward from the last checkpoint to reconstruct two tables: the active transaction table, which lists transactions without a commit or abort record, and the dirty page table, which records, for each page that may be stale on disk, the LSN of the first record that dirtied it (its recLSN). The smallest recLSN tells recovery where redo must begin.

The redo pass “repeats history”. It replays every logged change from the minimum recLSN forward, including changes from transactions that will later be rolled back. For each record it compares the record LSN with the pageLSN on disk and skips work that is already applied. Repeating history, rather than redoing only winners, is what lets ARIES support fine-grained row locks and steal buffering together.

The undo pass then walks backward through the losers’ records and reverses them. Each reversal is itself logged as a compensation log record (CLR) that points to the next record to undo. If the system crashes during recovery, the next recovery sees the CLRs and does not undo the same change twice. That is the property that makes ARIES restartable at any point.

Not every engine needs all three passes. PostgreSQL’s heap uses multiversion concurrency control: aborted transactions leave dead tuple versions that are simply invisible, so recovery is essentially redo from the checkpoint’s redo point, with no undo pass. The cleanup of those versions happens later through vacuum, which is why the MVCC isolation and snapshot guide and WAL are two sides of the same design. InnoDB is closer to classic ARIES, using redo for durability and separate undo logs for rollback and consistent reads. LSM engines like RocksDB only redo: they replay the WAL into a fresh memtable.

Same idea, five engines: a WAL comparison matrix

The table below maps the same write-ahead logging concepts onto five widely deployed systems. Defaults are from the official documentation cited in this post and may change between releases, so confirm them against your version.

Concern	PostgreSQL	MySQL InnoDB	SQLite WAL mode	RocksDB	Raft log, e.g. etcd
Log name	WAL in `pg_wal`	Redo log in `#innodb_redo`	`-wal` file plus `-shm` index	WAL files, one per memtable generation	Replicated log
Commit durability knob	`synchronous_commit`	`innodb_flush_log_at_trx_commit`	`PRAGMA synchronous`	`WriteOptions::sync`	Persist before replying to RPCs
Torn-page defence	`full_page_writes`	Doublewrite buffer	Checksummed WAL frames, checkpoint copies pages	No in-place page updates; immutable SSTs	Entry checksums, truncate bad tail
Bounding the log	Checkpoints, `max_wal_size`	Fuzzy checkpoints, `innodb_redo_log_capacity`	Auto-checkpoint at 1000 pages by default	Memtable flush, `max_total_wal_size`	Snapshots and log compaction
Recovery style	Redo from redo point	Redo plus undo logs	Rebuild index from WAL	Replay WAL into memtable	Replay log into state machine

SQLite deserves a closer look because its WAL mode inverts the usual layout. The main database file is left untouched during a transaction, and changes are appended to a separate -wal file, as the SQLite WAL documentation explains. Readers check the shared-memory -shm index to find the newest version of each page, so readers and a single writer do not block each other. A checkpoint copies pages back into the main file, by default when the WAL reaches 1000 pages. With synchronous=FULL the WAL is synced on every commit; with synchronous=NORMAL only checkpoints sync, so the most recent transactions can roll back after a power failure while the database stays consistent. SQLite also states plainly that WAL mode does not work over a network file system.

RocksDB is the purest example of log-plus-immutable-files. Every write goes to the WAL and the memtable; when the memtable flushes to a sorted string table (SST) file, the WAL generations it covered can be deleted or archived. Because SST files are never modified in place, torn pages in the classic sense do not arise. The remaining question is what to do with a damaged log tail, which RocksDB answers with wal_recovery_mode. Point-in-time recovery, the current default, stops replay at the first inconsistency; kTolerateCorruptedTailRecords was the LevelDB-era default; kAbsoluteConsistency refuses to open on any corruption; and kSkipAnyCorruptedRecords is a salvage mode for disasters. The RocksDB WAL wiki also documents manual_wal_flush and disableWAL, both of which trade durability for speed.

Raft makes the analogy with write-ahead logging explicit. In the Raft algorithm, a server’s current term, its vote and its log entries must be on stable storage before it responds to a request, and a leader considers an entry committed once a majority has persisted it. Each node’s log is a write-ahead log for its local state machine, and the cluster as a whole adds replication on top. If a Raft implementation acknowledges before fsync, a correlated power loss across a majority can lose committed entries. Our comparison of Raft and Paxos failure modes covers the election side; the storage side is pure WAL engineering.

Build a tiny WAL in Python: checksums, fsync and torn-tail recovery

The fastest way to internalise write-ahead logging is to build a log yourself. The code below is a deliberately small log for a key-value store. Each record carries an LSN, a length and a CRC-32 of its payload. commit() is the durability point, and recover() replays records until it meets a gap, a short record or a bad checksum, then truncates the torn tail.

import os, struct, zlib, json

HEADER = struct.Struct("<QII")  # lsn, payload length, crc32 of payload

class TinyWAL:
    """Append-only log: every record is [lsn | len | crc | payload]."""

    def __init__(self, path, next_lsn=1):
        self.path = path
        self.fd = os.open(path, os.O_RDWR | os.O_CREAT | os.O_APPEND, 0o644)
        self.next_lsn = next_lsn

    def append(self, op: dict) -> int:
        payload = json.dumps(op, sort_keys=True).encode()
        lsn = self.next_lsn
        rec = HEADER.pack(lsn, len(payload), zlib.crc32(payload)) + payload
        os.write(self.fd, rec)      # lands in the OS page cache only
        self.next_lsn += 1
        return lsn

    def commit(self) -> None:
        os.fsync(self.fd)           # durability point: one flush covers every
                                    # record appended since the last commit

    def close(self):
        os.close(self.fd)

def recover(path):
    """Replay valid records; stop at the first torn or corrupt one."""
    state, last_lsn, good_bytes = {}, 0, 0
    with open(path, "rb") as f:
        data = f.read()
    off = 0
    while off + HEADER.size <= len(data):
        lsn, length, crc = HEADER.unpack_from(data, off)
        start, end = off + HEADER.size, off + HEADER.size + length
        if end > len(data) or lsn != last_lsn + 1:
            break                   # torn tail or garbage
        payload = data[start:end]
        if zlib.crc32(payload) != crc:
            break                   # checksum mismatch: stop replay here
        op = json.loads(payload)
        if op["op"] == "put":
            state[op["k"]] = op["v"]
        elif op["op"] == "del":
            state.pop(op["k"], None)
        last_lsn, off, good_bytes = lsn, end, end
    # Truncate the torn tail so new appends start on a clean boundary.
    with open(path, "r+b") as f:
        f.truncate(good_bytes)
    return state, last_lsn

if __name__ == "__main__":
    p = "demo.wal"
    if os.path.exists(p):
        os.remove(p)
    w = TinyWAL(p)
    w.append({"op": "put", "k": "a", "v": 1})
    w.append({"op": "put", "k": "b", "v": 2})
    w.commit()
    w.append({"op": "del", "k": "a"})
    w.commit()
    w.close()
    # Simulate a crash mid-write: append half of a record.
    with open(p, "ab") as f:
        payload = json.dumps({"op": "put", "k": "c", "v": 3}).encode()
        rec = HEADER.pack(4, len(payload), zlib.crc32(payload)) + payload
        f.write(rec[: len(rec) // 2])
    state, lsn = recover(p)
    print("recovered state:", state, "last LSN:", lsn)
    print("file size after truncate:", os.path.getsize(p))

Running it with Python 3 on Linux prints:

recovered state: {'b': 2} last LSN: 3
file size after truncate: 133

The half-written fourth record is detected as a short read and discarded, exactly as a real engine discards a torn log tail. To reopen the log after recovery, construct TinyWAL(path, next_lsn=lsn + 1) so LSNs stay contiguous.

This toy omits several things a production log needs, and each omission maps to a real-world bug class. It never calls fsync on the parent directory after creating the file, so on some file systems a crash right after creation can lose the file’s directory entry entirely. It has no segment rotation or checkpoint, so the log grows forever. Its sequential LSN check means a stale record from a recycled file would stop replay, which is why PostgreSQL stores the expected address in each WAL page header and RocksDB’s recyclable record format carries the log number. And it treats every short tail as benign, which is correct for torn writes at the very end of the log but dangerous if corruption appears in the middle — the situation RocksDB’s recovery modes exist to distinguish.

Trade-offs, Gotchas, and What Goes Wrong

The write-ahead logging protocol is sound. Production incidents come from the assumptions underneath it. Four failure modes account for most of the real damage.

Figure 4: Why a failed fsync must not be retried. Before 2018, PostgreSQL logged the error and retried at the next checkpoint. Linux had already marked the pages clean, so the retry succeeded without writing anything and the WAL that could have repaired them was recycled. PostgreSQL now PANICs and replays WAL instead.

fsync errors are not retryable. In 2018 Craig Ringer reported on the PostgreSQL hackers list that, on Linux, a failed writeback could mark dirty pages clean and report the error only once. A later fsync would then succeed even though the data never reached disk. PostgreSQL had been retrying failed checkpoint fsyncs and then recycling the WAL that described the lost writes. The fix, committed by Thomas Munro in late 2018 and back-patched to every supported branch in the February 2019 minor releases, makes PostgreSQL PANIC on a data-file fsync failure and recover from WAL; the data_sync_retry setting (default off) exists only for platforms known to keep dirty pages. The PostgreSQL wiki page on fsync errors records that InnoDB and WiredTiger made similar changes, and a USENIX ATC 2020 study, Can Applications Recover from fsync Failures?, found that across ext4, XFS and Btrfs the pages are marked clean after a failed fsync and that none of the applications it studied, including PostgreSQL, SQLite, LevelDB, LMDB and Redis, handled the failure sufficiently.

Hardware that lies. A drive that acknowledges flushes from a volatile cache defeats every layer above it. The failure is invisible in normal operation and appears only after a power cut, as missing commits or corrupted pages. Virtualised and cloud volumes add another layer whose cache semantics you cannot inspect directly. The only defence is to choose hardware with documented power-loss protection, read the provider’s durability documentation, and run pull-the-plug tests before trusting a new platform.

Turning off the safety to win a benchmark. fsync = off, full_page_writes = off, innodb_doublewrite = OFF, innodb_flush_log_at_trx_commit = 0 and RocksDB’s disableWAL all make write benchmarks look spectacular. Each converts a crash from a few lost transactions into potential corruption or unbounded loss. If you need speed and can tolerate a small loss window, prefer the asynchronous-commit options, which keep the log consistent and lose only the tail.

Checkpoint and log-capacity stalls. When the log cannot be recycled, things degrade suddenly rather than gradually. In PostgreSQL, an inactive replication slot, a failing archive_command or a lagging standby pins WAL indefinitely until the disk fills and the server stops. In InnoDB, an undersized redo log forces synchronous flushing when writes outrun the page cleaners. Bulk loads also amplify WAL: a large COPY right after a checkpoint writes a full page image for nearly every page it touches. Maintenance operations that rewrite whole tables produce WAL at the same scale, a cost covered in our comparison of PostgreSQL REPACK, pg_repack and VACUUM FULL.

Finally, remember that local WAL durability is not disaster recovery. A durable log on one machine survives a power cut, not a lost disk or a lost region. Replication and archiving extend the same log across machines, which is why PostgreSQL high availability with Patroni and streaming replication is built entirely on shipping WAL.

Practical Recommendations

Treat write-ahead logging durability as an end-to-end property that you verify, not a setting you assume. Start by deciding, per workload, how much acknowledged data you can afford to lose: zero, a fraction of a second, or a few seconds. That answer chooses between synchronous commit, asynchronous commit and replication-backed commit, and it should be written down, not inherited from a default.

Next, measure the cost of a flush on the exact storage the log lives on. pg_test_fsync is useful even outside PostgreSQL because it reports how many synced 8 kB writes per second each sync method achieves. If that number is low, group commit and a PLP-equipped log device will matter more than any query tuning. Size checkpoints so they are time-driven under normal peaks, and alert on WAL retention so a stuck slot or archive never fills the disk silently.

Then prove recovery works. Kill the database process with SIGKILL, hard-reset a test VM, and on dedicated hardware cut the power during a write-heavy benchmark. Compare acknowledged transactions with what survives. Recovery code paths run rarely, which is exactly why they hide bugs.

A short checklist:

Record the acceptable loss window per database and map it to synchronous_commit, innodb_flush_log_at_trx_commit, PRAGMA synchronous or WriteOptions::sync.
Keep fsync, full_page_writes and the InnoDB doublewrite buffer on unless the file system or hardware provably prevents torn writes.
Use enterprise drives with power-loss protection for the log, and verify cache behaviour with diskchecker.pl or an equivalent test.
Run a supported PostgreSQL minor release so the PANIC-on-fsync-failure behaviour is present, and never set data_sync_retry = on on Linux.
Monitor checkpoint frequency, WAL generation rate, replication slot lag and archive failures.
Rehearse crash recovery and measure recovery time against your objective.

Frequently Asked Questions

What is write-ahead logging in simple terms?

Write-ahead logging means a database writes a description of every change to a sequential log before it changes the actual data files. Only the log has to be safely on disk when a transaction commits; the data pages can be written later in any order. If the server crashes, the database reads the log from the last checkpoint and replays it, re-applying committed changes and discarding or undoing uncommitted ones. The result is that every acknowledged transaction survives, and the files return to a consistent state, without paying for random page writes on every commit.

Does fsync guarantee my data is on disk?

fsync guarantees that the operating system has sent the file’s data to the storage device and that the device reported it as stable. Whether that is true depends on the device. Drives with power-loss protection can safely acknowledge from their cache, while drives with volatile caches must honour flush or Force Unit Access commands. Some consumer drives and misconfigured controllers have historically ignored flushes. In addition, if fsync returns an error on Linux, retrying it is not safe, because the kernel may already have discarded the dirty pages and a later call can succeed falsely.

What is group commit and how does it improve throughput?

Group commit lets many transactions share one log flush. While one flush is in progress, other transactions append their commit records to the WAL buffer and wait. The next flush makes all of them durable at once, so the cost of the expensive sync is divided across the batch. With a slow flush and dozens of concurrent clients, this can raise commit throughput by more than an order of magnitude at a modest cost in per-transaction latency. PostgreSQL does this automatically and offers commit_delay and commit_siblings to tune it.

Why do databases need checkpoints if they already have a WAL?

Checkpoints bound the amount of log that recovery must replay and the amount of log that must be kept on disk. A checkpoint flushes dirty data pages and records a redo point, so recovery can start there instead of at the beginning of history, and older log segments can be recycled or archived. Frequent checkpoints mean faster recovery but more write volume, partly because PostgreSQL logs a full page image on the first change to each page after a checkpoint. Infrequent checkpoints reduce writes but lengthen recovery and retain more WAL.

What is a torn page and how do databases prevent it?

A torn page is a data page that was only partially written when power failed, leaving a mix of old and new bytes. It happens because database pages, such as PostgreSQL’s 8 kB pages, span several disk sectors that are not written atomically. PostgreSQL defends with full_page_writes, logging a complete page image after each checkpoint so recovery can restore it. InnoDB writes pages to a doublewrite buffer before their home location. LSM engines such as RocksDB avoid in-place updates entirely, and checksums on pages and log records detect any damage that slips through.

How does ARIES database crash recovery work?

ARIES, published by Mohan and colleagues in 1992, recovers in three passes. Analysis scans from the last checkpoint to find unfinished transactions and the pages that may be stale on disk. Redo repeats history, replaying every logged change from the oldest dirty page forward and skipping changes already present by comparing log sequence numbers. Undo then rolls back unfinished transactions, writing compensation log records so the undo is never repeated if recovery itself crashes. InnoDB follows this model closely; PostgreSQL needs mainly redo because its multiversion storage leaves aborted rows invisible rather than undone.

Write-Ahead Logging Explained: fsync, Group Commit, Checkpoints, Torn Pages and How Databases Survive Crashes (2026)