HowPostgresKeepsYourDataAliveWhenEverythingGoesWrong

The Problem Nobody Thinks About Until It's Too Late

Your server loses power at 2:47 AM. Maybe a rogue process eats all the memory. Maybe your cloud provider has a bad day. Whatever the reason, your database just died mid-transaction. You had hundreds of writes in flight. Customers were placing orders, updating profiles, moving money between accounts. And now the machine is off.

When it comes back up, what happens to all that data?

This is the question that keeps database engineers up at night. And it's the question that Write-Ahead Logging — or WAL — was designed to answer. Postgres uses WAL as its core durability mechanism, and understanding how it works will change the way you think about database reliability forever. I've found that most developers interact with Postgres for years without ever really grasping what's happening under the hood to keep their data safe, and that's a shame, because the mechanism is genuinely beautiful.

What "Durability" Actually Means

Before we get into WAL specifically, let's talk about what we're even trying to protect. The "D" in ACID — the four properties every serious database guarantees — stands for Durability. It means that once a transaction is committed, it stays committed. Even if the power goes out one millisecond after you receive that COMMIT confirmation, your data must survive.

This sounds obvious. Of course committed data should stay committed. But think about the mechanical reality of what's happening. Modern computers have multiple layers of storage — CPU caches, RAM, OS buffers, and finally the actual disk. When your application writes data, it might sit in RAM or an OS buffer for a while before physically landing on disk. If the machine dies during that window, the data evaporates. It never made it to persistent storage.

The naive solution would be to flush everything to disk on every single write. Technically that works. But hard drives and even SSDs have real latency. A database that fsync'd (forced a disk flush) after every single operation would be unbearably slow. You'd measure throughput in dozens of transactions per second instead of thousands. We need something smarter.

Enter the Write-Ahead Log

The core insight behind WAL is deceptively simple: write your intentions down before you act on them. Think of it like a chef's prep list. Before the chef starts cooking, they write down exactly what they plan to make. If they're interrupted mid-preparation, someone can look at the list and know exactly where things left off.

In Postgres, before any change is made to actual data files on disk, a record of that intended change is written to the WAL — a sequential, append-only log file. This log record is written and flushed to disk before the commit is acknowledged to the client. The actual data pages in the main database files might not be updated immediately. But that's okay, because if the system crashes, Postgres can read the WAL on restart and replay any changes that didn't make it to the main data files. Nothing is lost.

The WAL lives in your Postgres data directory under pg_wal/ (it was called pg_xlog/ in older versions). It's a series of segment files, each 16MB by default. Postgres writes to these sequentially, which is a huge performance advantage. Sequential writes on both spinning disks and SSDs are dramatically faster than random writes. By funneling all durability-critical writes into a sequential log, Postgres gets strong durability guarantees without destroying performance.

How WAL Records Are Structured

Each WAL record captures enough information to reconstruct a specific change. A record for an INSERT operation, for example, contains the transaction ID, the relation (table) being modified, the specific page within that relation, and the actual data being written. Postgres uses a concept called LSN — Log Sequence Number — to identify every position in the WAL. Think of the LSN as a unique address for every byte in the log.

When Postgres needs to write a WAL record, it first acquires space in an in-memory WAL buffer — a ring buffer in shared memory. The record is written there first. Then, at commit time, the WAL buffer is flushed to disk (that fsync operation we mentioned earlier). Only after that flush succeeds does Postgres tell your application "yes, your transaction committed." This is the contract: the WAL is on disk before you get confirmation. Your data is safe.

You can actually inspect WAL records yourself using the pg_waldump utility. It's a surprisingly useful debugging and learning tool.

# First, find your current WAL position
psql -c "SELECT pg_current_wal_lsn();"

# Then dump a WAL segment (replace with your actual segment file)
pg_waldump -p /var/lib/postgresql/data/pg_wal \
  000000010000000000000001

# You'll see output like:
# rmgr: Heap        len (rec/tot):     59/    59, tx:        492, lsn: 0/01234560,
#   prev 0/01234510, desc: INSERT off 1, blkref #0: rel 1663/16384/16385 blk 0

Reading that output reveals exactly what Postgres was doing at each moment — which table was being written to, which transaction was responsible, and exactly where in the log that record lives.

Recovery: The Magic That Happens at Startup

Here's where the payoff happens. After a crash, Postgres doesn't just shrug and hope for the best. It goes through a disciplined recovery process that I've always found satisfying in its methodical thoroughness.

When Postgres starts up after an unclean shutdown, it checks the control file (a small metadata file that tracks the last known consistent state) and looks at the WAL. It finds the point at which the last checkpoint occurred. A checkpoint is a periodic event where Postgres guarantees that all dirty data pages (pages modified in memory but not yet written to disk) have been flushed to the main data files. Think of a checkpoint as Postgres drawing a line and saying "everything before this line is definitely on disk."

After identifying the last checkpoint, Postgres replays all WAL records that came after it. If a record says "insert row X into table Y," Postgres applies that change to the data files, even if it had already done so before the crash. This process is called REDO. WAL records are designed to be idempotent in their replay — you can apply them more than once without causing corruption, because Postgres tracks page LSNs and skips records that have already been applied. When replay is complete, the database is in the exact state it was in at the moment of the crash.

-- You can watch recovery progress in real time using this query
-- (useful when you have a standby or after a crash recovery)
SELECT
  pg_is_in_recovery() AS in_recovery,
  pg_last_wal_receive_lsn() AS received_lsn,
  pg_last_wal_replay_lsn() AS replayed_lsn,
  pg_last_xact_replay_timestamp() AS last_replay_time;

This query is particularly useful when you're running a standby server that's consuming WAL from a primary — but it also tells you interesting things during crash recovery if you can connect during the recovery window.

WAL and Replication: The Same Log, More Power

One of the most elegant aspects of WAL is that it does double duty. The very same mechanism that protects against crashes also powers Postgres replication. Streaming replication — where a standby server stays continuously synchronized with a primary — works by having the standby consume the primary's WAL stream in near real time.

The standby connects to the primary, identifies its current LSN, and the primary starts streaming WAL records from that point forward. The standby applies those records to its own copy of the data files, staying in sync. Because WAL records describe changes at the physical page level, this replication is extremely efficient. You're not re-executing SQL queries on the standby — you're replaying already-computed changes.

This architecture means your standby server is essentially always in the middle of crash recovery, perpetually replaying WAL. In my experience, this mental model helps a lot when you're trying to understand replication lag or troubleshoot why a standby is falling behind. It's not magic — it's just a server that's continuously doing what crash recovery does, but in real time.

Checkpoints and Their Tuning

We mentioned checkpoints briefly, but they deserve more attention because they're where a lot of Postgres performance tuning happens. Checkpoints are expensive. When one occurs, Postgres must write all dirty pages to disk — potentially a huge amount of I/O. If checkpoints happen too frequently, you get constant I/O pressure. If they happen too rarely, crash recovery takes longer because there's more WAL to replay.

The relevant configuration parameters are checkpoint_completion_target, checkpoint_timeout, and max_wal_size. The checkpoint_timeout controls how frequently checkpoints occur at minimum (default is 5 minutes). The max_wal_size setting (default is 1GB) triggers a checkpoint if the WAL grows beyond that threshold. The checkpoint_completion_target (default 0.9) tells Postgres to spread the writes of a checkpoint over 90% of the time until the next checkpoint, smoothing out the I/O impact.

-- Check your current checkpoint configuration and activity
SELECT
  checkpoints_timed,
  checkpoints_req,
  checkpoint_write_time,
  checkpoint_sync_time,
  buffers_checkpoint,
  buffers_clean,
  maxwritten_clean
FROM pg_stat_bgwriter;

If checkpoints_req is high relative to checkpoints_timed, your checkpoints are being triggered by WAL size rather than time. That often means you should increase max_wal_size to give Postgres more breathing room. I've seen production systems where poorly tuned checkpoint settings caused mysterious periodic slowdowns every few minutes — always a checkpoint problem in disguise.

The synchronous_commit Trade-Off

Not every application needs the absolute strongest durability guarantee. Postgres gives you a dial to turn with the synchronous_commit setting. By default, it's on, meaning Postgres waits for the WAL flush to disk before confirming a commit. But you can set it to off, and Postgres will acknowledge commits before the WAL is flushed.

This sounds terrifying. And for financial transactions, it should — don't do it. But for things like session logging, metrics collection, or any data where losing the last few seconds of writes is acceptable, synchronous_commit = off can dramatically increase throughput. You're trading some durability for speed. The important thing to understand is that even with this setting, you never get corruption. The worst case is that you lose the last few committed transactions after a crash — not that your database ends up in an inconsistent state.

There's also synchronous_commit = local and synchronous_commit = remote_write, which give you intermediate options in replication scenarios. These let you tune the trade-off between latency and how confident you are that your data has made it off the primary machine. The existence of these options reflects that Postgres designers understood real-world systems have different needs and shouldn't be forced into one-size-fits-all durability.

Why This Matters for Your Application Architecture

Understanding WAL changes how you design systems. When you know that Postgres is writing an append-only log sequentially and then reconciling data files asynchronously, you start to appreciate why heavy random-write workloads (lots of small updates scattered across many rows) can stress the system differently than bulk sequential inserts. The WAL will faithfully record all of it, but the background writer struggling to keep data files updated can cause its own performance issues.

You also start to understand why logical replication — which transmits row-level changes rather than raw WAL records — exists. Raw WAL is tied to the physical layout of data on disk. Upgrade Postgres to a new major version, or change the physical schema, and the WAL format might change. Logical replication decodes WAL into a more abstract change stream that can be consumed by different systems, different versions, or even entirely different databases. It's WAL's power expressed in a more portable form.

The write-ahead log is, at its core, a simple idea executed with extraordinary care. Write down what you're going to do before you do it. Flush that record to durable storage. Then proceed. If something goes wrong, start from the last known good state and replay everything that followed. It's the same principle a surgeon uses when they count instruments before and after an operation, or a pilot uses when running a pre-flight checklist. The discipline of writing things down first is what makes the difference between "we might lose data" and "we guarantee we won't."