DURABILITY

For this task, I am trying to understand durability. That means once a transaction is committed, the data should not be lost even if the system crashes.

First I check the current data in the table.

Query
select * from accounts;

Here I can see Alice has 1000 and Bob has 500.

Now I perform a money transfer from Alice to Bob.

Query
begin;

update accounts
set balance = balance - 200
where name = 'Alice';

update accounts
set balance = balance + 200
where name = 'Bob';

commit;

Here I started a transaction, deducted 200 from Alice and added 200 to Bob, and then committed it.

After commit, the changes are saved permanently.

Now I check the data again.

Query
select * from accounts;

Now I can see Alice has 800 and Bob has 700. This confirms that the transaction is successful.

Next step is to simulate a system crash or restart. In real scenario, this means database stops and starts again.

After reconnecting, I again run the same select query.

Query
select * from accounts;

I can still see Alice has 800 and Bob has 700.

So even after restart, the data is not lost.

From this I understand that once commit is done, the database permanently stores the data.

This is called durability.

Now I think about two situations.

If failure happens before commit
In this case, transaction is not completed. So PostgreSQL will rollback the changes automatically. Data will remain same as before.

If failure happens after commit
In this case, even if system crashes, the committed data will not be lost. When database restarts, it will restore the committed changes.

This is possible because PostgreSQL stores changes in logs internally before confirming commit. So even if crash happens, it can recover the data.

So finally what I understood is

Once commit is executed, the data is safe and will not be lost.
If commit is not done, changes will not be saved.

This ensures durability in the system and avoids data loss in applications like payment systems.