The Durability of the Unforgiven Log

The Lie of the Buffer Pool

There is no such thing as instant persistence. When you execute a COMMIT statement in PostgreSQL, the database engine does not immediately write your updated row to the table’s main storage files on disk. Doing so would require random disk I/O, a luxury that modern hardware, even with NVMe drives, cannot afford at scale.

Instead, the engine alters the data in memory—specifically, in the shared buffer pool. The page containing your row becomes "dirty." If the database crashed at this exact microsecond, that modification would vanish forever into the volatile ether of RAM.

To prevent this, we rely on the Write-Ahead Log (WAL). Before any dirty page is permitted to be written to the heap file, the change must be written sequentially to the WAL. A sequential write is fast, predictable, and clean. Once the operating system confirms that the WAL bytes have been physically flushed to disk via an fsync() system call, only then does the engine report a successful commit to your client application.

We live in the dirty buffers; we survive because of the log.

The Incident at 3:14 AM

Last Tuesday, a primary database node in our reporting cluster suffered a hardware-induced kernel panic. Priya, our systems lead, argued that we could rely on our replica’s memory state to reconstruct the missing transactions because the replication lag was reported as zero.

But replication metrics can deceive. They measure network transit, not physical serialization.

When we brought the node back up in recovery mode, the engine ignored the state of the heap files. It did not care about what we thought we had saved. It started from the last known checkpoint—a physical marker in the log indicating that all dirty pages up to that transaction ID had been safely written to disk. From that point forward, it performed redo recovery.

-- Checking replication lag and physical flushes
SELECT
    client_addr,
    application_name,
    pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_lag_bytes,
    pg_wal_lsn_diff(sent_lsn, flush_lsn) AS flush_lag_bytes
FROM pg_stat_replication;

During recovery, the engine reads the raw WAL records and reapplies them to the heap pages. The WAL does not negotiate. It is a strict chronological record of state changes: Page 1042, Offset 24, set value to X.

If a transaction is not in the WAL, it never happened. It does not matter if your application logs show a 200 OK response. If the disk platters or flash cells did not receive the physical alignment of electrons representing that LSN (Log Sequence Number), the transaction is a ghost.

The Cost of Truth

This durability has a cost. Every time you write to a database, you compete for the WAL write lock. If you have a high-concurrency write workload, you will eventually hit a bottleneck called WALWriteLock contentions.

Some developers try to bypass this by setting synchronouscommit = off. This tells PostgreSQL to acknowledge commits as soon as they are written to the WAL buffer in memory, rather than waiting for them to be flushed to disk. It is a dangerous trade-off. You gain a three-fold increase in write throughput, but you accept the risk of losing up to two times the walwriter_delay window of data (typically 200 milliseconds) during a crash.

In my line of work, we do not turn that dial. A database that loses 200 milliseconds of financial ledger entries under load is not a database; it is an expensive cache.

Outdated Statistics and Bad Decisions

We see a parallel in the query planner. The planner relies on pg_statistic to build execution trees. It estimates whether to use a sequential scan or an index scan based on these numbers.

If the statistics are stale, the planner assumes the table is small and chooses a nested loop join over a hash join. The result is a CPU spike that brings the application to its knees. The planner makes decisions on what it remembers the table looking like, not what the table actually is.

Like the planner, we often make assumptions based on stale cached states. We assume our systems are stable because they were stable yesterday. But the only metric that matters is the one validated by a hard sync.

The Cold Recovery

At 4:45 AM, the recovery completed. The engine processed 1.2 gigabytes of WAL files, reconciling the discrepancies between the memory buffers and the physical storage blocks. Every single database page was brought to a consistent state.

We did not lose a single row of data because we did not trust the memory. We waited for the disk to confirm reality.

Outside, the first signs of dawn appeared over the data center's exhaust vents. The servers continued their monotonous hum, indifferent to our anxiety, writing pages, dirtying memory, and logging every single truth to the disk.