Durability

Today I learned about durability in databases by doing a small experiment using a wallet system example.

I used the same accounts table where Alice has 1000 and Bob has 500. First, I checked the initial balances to make sure the data is correct.

Then I performed a money transfer from Alice to Bob.

BEGIN;

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

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

COMMIT;

After committing the transaction, I checked the balances again. Alice’s balance became 700 and Bob’s balance became 800. This showed that the transaction was successful.

Next, I wanted to test what happens if the system restarts. So I disconnected and reconnected to the database, similar to a system restart. After reconnecting, I queried the accounts table again.

I observed that the balances were still 700 and 800. The changes were not lost. This shows that once a transaction is committed, the data is stored permanently.

From this, I understood the concept of durability. It means that once a transaction is completed and committed, the data will remain safe even if there is a crash or restart.

I also thought about what happens if a failure occurs before commit. In that case, the transaction will not be saved, and the database will roll back to the previous state. So no partial changes will be stored.

If a failure happens just after commit, the database uses log files to make sure the data is not lost. PostgreSQL writes changes to a log before saving them to the actual data files. This helps in recovering the data after a crash.