Understanding Durability

After exploring Atomicity and Consistency, I tested Durability which makes sure that once a transaction is committed, it is never lost even after a crash.

Creating a base table

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    balance INT NOT NULL CHECK (balance >= 0),
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO accounts (name, balance) VALUES ('Christina', 1000), ('Sharon', 500);

Step 1: Perform a Transfer

BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE name = 'Christina';
UPDATE accounts SET balance = balance + 200 WHERE name = 'Sharon';
COMMIT;

After commit:
Christina = 800
Sharon = 700

Step 2: Simulate a crash by closing abruptly

I closed my database connection then reconnected and checked:

SELECT name, balance FROM accounts;

Result After:

• Christina = 800
• Sharon = 700 The committed transaction was saved.

What I Learned

• Once I use COMMIT, the data is permanently saved
• Even if the system crashes, committed changes are not lost
• PostgreSQL uses mechanisms like Write-Ahead Logging (WAL) to ensure this