DEV Community

Kfir
Kfir

Posted on • Originally published at levelup.gitconnected.com on

Understanding Durability in PostgreSQL -A Deep Dive into the “D” in ACID

#postgres #database #durability #acid


Photo by Matheus Viana: https://www.pexels.com/photo/person-on-bike-2372972/

So far in this series, we’ve looked at how PostgreSQL handles Atomicity, Consistency, and Isolation. But what happens after you commit a transaction? What if the power goes out, the server crashes, or someone trips over the plug?

This is where Durability , the final letter in ACID, comes in. Durability means that once a transaction is committed, it will not be lost- no matter what.

What Does Durability Really Mean?

In practical terms, Durability means that when your application gets a COMMIT confirmation, the data is safely stored and recoverable even if the database crashes immediately afterward.

PostgreSQL achieves this with its Write-Ahead Logging (WAL) mechanism. Instead of immediately rewriting table files on disk, PostgreSQL writes changes to a log file first. If the system crashes, PostgreSQL can replay this log to get back to a consistent state.

How Does WAL Work?

Here’s the basic flow:

  1. You run a transaction that changes some data.
  2. PostgreSQL writes the changes to the WAL file on disk.
  3. Only after the WAL record is safely on disk does PostgreSQL acknowledge the COMMIT.

So the WAL acts like a black box flight recorder for your database.

Let’s See Durability in Action

You can see WAL files in action in your data directory. Here’s a simple demo.

First, check your WAL settings:

SHOW wal_level;
SHOW synchronous_commit;
Enter fullscreen mode Exit fullscreen mode

By default, synchronous_commit is on. This means PostgreSQL waits until WAL changes are flushed to disk before confirming the commit.

Test: Crash Recovery (Safe Experiment)

Let’s simulate how WAL protects your data.

  1. Create a test table: 
CREATE TABLE durability_test (id SERIAL PRIMARY KEY, data TEXT);
Enter fullscreen mode Exit fullscreen mode
  1. Insert some rows and commit: 
INSERT INTO durability_test (data) VALUES ('Important Data 1'), ('Important Data 2'); COMMIT;
Enter fullscreen mode Exit fullscreen mode
  1. Now, force PostgreSQL to checkpoint to flush data to disk: 
CHECKPOINT;
Enter fullscreen mode Exit fullscreen mode
  1. If the server crashes after the WAL is written but before data files are updated, PostgreSQL will replay the WAL on startup to make sure your rows are still there.

Of course, don’t yank the power cord — but you can trust that WAL would restore the committed rows.

When Durability Can Be Tuned

Sometimes, applications may want to trade off strict Durability for speed. For example, you can turn off synchronous_commit:

SET synchronous_commit = off;
Enter fullscreen mode Exit fullscreen mode

Now PostgreSQL can acknowledge a commit before the WAL is flushed to disk. If the server crashes immediately, you could lose the last few transactions.

This is faster but obviously less durable — so use it only when you’re okay with that risk (like bulk loads or cache tables).

Real-World Takeaway

Durability is what lets you sleep at night knowing your committed data won’t vanish. PostgreSQL’s WAL and crash recovery have been battle-tested for decades. As long as you keep your WAL files safe (and your disks healthy), your data stays safe too.

References

Top comments (0)