DEV Community

Manoj Kumar
Manoj Kumar

Posted on

Does Your Payment Data Survive a Crash? Testing Durability in PostgreSQL

Everything we explored before this was about correctness during a transaction. This one is about what happens after. Specifically, once a transfer is committed and the system crashes five seconds later, does that transfer still exist when the database comes back up? The answer should always be yes and understanding why that is the case is what this problem is really about.

This is the D in ACID. Durability.

The Transfer — Committing the Transaction

Starting with Alice at 1000 and Bob at 500. We do a clean transfer of 300 from Alice to Bob and commit it.

BEGIN;

UPDATE accounts
SET balance = balance - 300,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 300,
    last_updated = CURRENT_TIMESTAMP
WHERE name = 'Bob';

COMMIT;
Enter fullscreen mode Exit fullscreen mode

Check the balances right after:

SELECT name, balance FROM accounts;
Enter fullscreen mode Exit fullscreen mode

Alice is at 700. Bob is at 800. Transaction committed, data updated.

Simulating a Crash or Restart

Now we simulate what happens when the system goes down. In a real scenario this could be a server power cut, a container crash, or PostgreSQL being forcefully stopped. To simulate it locally you can stop the PostgreSQL service abruptly.

On Linux or Mac:

sudo systemctl stop postgresql
Enter fullscreen mode Exit fullscreen mode

Or forcefully kill the process to simulate a hard crash:

sudo pkill -9 postgres
Enter fullscreen mode Exit fullscreen mode

Wait a few seconds and then bring it back up:

sudo systemctl start postgresql
Enter fullscreen mode Exit fullscreen mode

Now reconnect to the database and check the balances again:

SELECT name, balance FROM accounts;
Enter fullscreen mode Exit fullscreen mode

Alice is still at 700. Bob is still at 800.

The committed transfer survived the crash completely. Nothing was lost.

Why Does This Work — The Write Ahead Log

PostgreSQL does not write changes directly to the data files the moment you run an UPDATE. Instead, before touching the actual data, it writes a record of the change to something called the Write Ahead Log or WAL. This is a sequential log file on disk that records every change that is about to happen.

The rule is simple. The log entry must be written to disk before the actual data change is confirmed as committed. So by the time COMMIT returns successfully to you, the change is already recorded in the WAL on disk.

If the system crashes after COMMIT but before the actual data files are updated, PostgreSQL reads the WAL during startup and replays any changes that were logged but not yet written to the main data files. Your data is recovered automatically.

This is why the committed transfer survived. It was in the WAL before the crash happened.

What Happens If the Crash Occurs Just Before COMMIT

This is the other scenario worth thinking about. The transfer started, Alice was deducted, Bob was credited, but the system crashed a split second before COMMIT ran.

When PostgreSQL restarts it looks at the WAL and sees a transaction that was started but never committed. It does not replay those changes. The incomplete transaction is rolled back during recovery.

Alice goes back to 1000. Bob goes back to 500. The crash is treated exactly like a ROLLBACK.

-- After restart, if crash happened before COMMIT
SELECT name, balance FROM accounts;

-- Alice: 1000
-- Bob: 500
Enter fullscreen mode Exit fullscreen mode

No partial update survives. No money is lost in either direction.

The Two Scenarios Side by Side

Crash after COMMIT means the transfer is permanent. WAL has the full record and PostgreSQL replays it on startup. Both balances reflect the transfer.

Crash before COMMIT means the transfer never happened as far as the database is concerned. The incomplete transaction is discarded during recovery. Both balances go back to what they were before the transfer started.

There is no in between state that persists. Either the full transaction made it or none of it did.

What About the Tiny Gap Right at COMMIT

There is a brief moment during COMMIT where PostgreSQL is in the process of flushing the WAL to disk. If the hardware fails at exactly that microsecond, PostgreSQL uses checksums and log sequencing to figure out during recovery whether the commit completed or not. If it cannot confirm the commit was fully written, it treats the transaction as incomplete and rolls it back.

This is why PostgreSQL recommends running on hardware with reliable disk write guarantees and why things like UPS systems and battery backed disk controllers matter in production payment systems. The software guarantees durability as long as the hardware faithfully confirms that a disk write actually happened.

What I Understood from This

Durability is the promise that a committed transaction is permanent no matter what happens next. The WAL is the mechanism that makes that promise real. It is a running record of everything that should happen, written before it actually happens, so that a crash at any point can always be recovered from cleanly.

For a payment system this is not optional. Users need to know that a successful transfer confirmation means the money moved, full stop. Not probably moved, not moved unless the server crashes, but moved permanently. The WAL plus PostgreSQL's crash recovery is what makes that guarantee possible.

ACID is not just four letters. Each one is a specific guarantee and Durability is the one that makes all the others matter even when the power goes out.

Top comments (0)