As part of ACID series, i am refreshing on topic Durability. In this blog i jot down notes on durability for better understanding.

What is Durability?

Durability ensures that the effects of a committed transaction are permanently saved to the database. This property prevents data loss by ensuring that committed transactions survive unexpected interruptions such as power outages, crashes, or system reboots.

PostgreSQL achieves durability through a combination of

• Write-Ahead Logging (WAL): Changes are written to a log file before they are applied to the database.
• Checkpointing: Periodic snapshots of the database state.
• fsync and Synchronous Commit: Ensures data is physically written to disk.

How PostgreSQL Achieves Durability

Miro Board for Postgres Architechture – https://miro.com/app/board/uXjVLD2T5os=/

1. Write-Ahead Logging (WAL)

PostgreSQL uses WAL to ensure durability. Before modifying the actual data, it writes the changes to a WAL file. This ensures that even if the system crashes, the database can be recovered by replaying the WAL logs.

-- Enable WAL logging (default in PostgreSQL)
SHOW wal_level;

2. Checkpoints

A checkpoint is a mechanism where the database writes all changes to disk, ensuring the database’s state is up-to-date. Checkpoints reduce the time required for crash recovery by limiting the number of WAL files that need to be replayed.

-- Force a manual checkpoint
CHECKPOINT;

3. Synchronous Commit

By default, PostgreSQL ensures that changes are flushed to disk before a transaction is marked as committed. This is controlled by the synchronous_commit setting.

-- Show current synchronous commit setting
SHOW synchronous_commit;

-- Change synchronous commit setting
SET synchronous_commit = 'on';

4. Backup and Replication

To further ensure durability, PostgreSQL supports backups and replication. Logical and physical backups can be used to restore data in case of catastrophic failures.

Practical Examples of Durability

Example 1: Ensuring Transaction Durability

BEGIN;

-- Update an account balance
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

-- Commit the transaction
COMMIT;

-- Crash the system now; the committed transaction will persist.

Even if the database crashes immediately after the COMMIT, the changes will persist, as the transaction logs have already been written to disk.

Example 2: WAL Recovery after Crash

Suppose a crash occurs immediately after a transaction is committed.

Scenario:

BEGIN;
INSERT INTO transactions (account_id, amount, transaction_type) VALUES (1, 500, 'credit');
COMMIT;

During the recovery process, PostgreSQL replays the WAL logs to restore the committed transactions.

Example 3: Configuring Synchronous Commit

Control durability settings based on performance and reliability needs.

-- Use asynchronous commit for faster performance (risking durability)
SET synchronous_commit = 'off';

-- Perform a transaction
BEGIN;
UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;
COMMIT;

-- Changes might be lost if the system crashes before the WAL is flushed.

Trade-offs of Durability

While durability ensures data persistence, it can affect database performance. For example:

• Enforcing synchronous commits may slow down transactions.
• Checkpointing can momentarily impact query performance due to disk I/O.

For high-performance systems, durability settings can be fine-tuned based on the application’s tolerance for potential data loss.

Durability and Other ACID Properties

Durability works closely with the other ACID properties:

1. Atomicity: Ensures the all-or-nothing nature of transactions.
2. Consistency: Guarantees the database remains in a valid state after a transaction.
3. Isolation: Prevents concurrent transactions from interfering with each other.