Durability

Understanding Durability through a Simple Wallet Transfer System

Introduction

While using payment apps, we expect that once a transaction is completed, it should remain saved permanently. Even if the system crashes or restarts, the updated balance should not disappear.

This behavior is ensured by Durability, one of the ACID properties of a database. Durability guarantees that once a transaction is committed, its changes are permanently stored and will not be lost.

To understand this better, I experimented with a simple wallet transfer system using PostgreSQL and observed how committed data behaves after reconnecting to the database.

Setting up the table

I started by creating a basic table to store account details.

CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
balance INT NOT NULL CHECK (balance >= 0),
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Then I inserted two users:

INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);

Checking the table:

SELECT * FROM accounts ORDER BY id;

At this point:

• Alice has 1000
• Bob has 500

Performing a successful transfer

First, I performed a transfer of 200 from Alice to Bob inside a transaction.

BEGIN;

UPDATE accounts
SET balance = balance - 200,
last_updated = CURRENT_TIMESTAMP
WHERE id = 1;

UPDATE accounts
SET balance = balance + 200,
last_updated = CURRENT_TIMESTAMP
WHERE id = 2;

COMMIT;

After running this, I checked the table again:

SELECT * FROM accounts ORDER BY id;

Now:

• Alice = 800
• Bob = 700

This confirms that the transaction was successfully committed.

Simulating a restart or reconnect

To test durability, I disconnected from the database and reconnected again (or restarted the session).

After reconnecting, I ran:

SELECT * FROM accounts ORDER BY id;

The balances were still:

• Alice = 800
• Bob = 700

This shows that the committed changes were permanently saved and not lost after reconnecting.

What happens if failure occurs before commit

Next, I considered what happens if the system fails before the transaction is committed.

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE id = 1;

UPDATE accounts
SET balance = balance + 200
WHERE id = 2;

If the system crashes at this point before COMMIT, the transaction is incomplete.

After reconnecting, the balances will remain:

• Alice = 1000
• Bob = 500

This means uncommitted changes are not saved.

What happens if failure occurs after commit

Now consider a failure happening immediately after COMMIT.

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE id = 1;

UPDATE accounts
SET balance = balance + 200
WHERE id = 2;

COMMIT;

If the system crashes after this, once we reconnect and check:

SELECT * FROM accounts ORDER BY id;

The balances will still be:

• Alice = 800
• Bob = 700

This proves that committed data is not lost.

How PostgreSQL ensures durability

PostgreSQL ensures durability using a mechanism called Write-Ahead Logging (WAL).

Before permanently updating the actual data, PostgreSQL first records the changes in a log. Once the transaction is committed, this log ensures that even if the system crashes, the database can recover and restore the committed data.

Because of this:

• committed transactions are always preserved
• uncommitted transactions are discarded
• the database can recover correctly after failure

What I learned

From this experiment, I understood that:

• Once a transaction is committed, the data is permanently saved
• Reconnecting or restarting does not remove committed changes
• If failure occurs before commit, changes are not saved
• Durability applies only to committed transactions

Conclusion

This experiment helped me understand how durability works in a database system.

Durability ensures that once a transaction is committed, its changes remain saved even after crashes or restarts. This is very important in financial systems like wallet applications, where losing committed transactions could lead to serious issues such as money loss or incorrect balances.

In simple terms, durability guarantees that once a payment is successful, it will always remain successful.