Write-Ahead Logging (WAL) and how it shapes a ledger backup strategy

-- When Rajesh transfers money at the National Bank of India:
BEGIN;
UPDATE accounts SET balance = balance - 50000 WHERE account_id = 'SAV2001';
UPDATE accounts SET balance = balance + 50000 WHERE account_id = 'SAV2002';
COMMIT;

-- What happens internally:
-- 1. WAL record: "debit SAV2001 by 50000" → appended to WAL
-- 2. WAL record: "credit SAV2002 by 50000" → appended to WAL
-- 3. COMMIT record → appended to WAL
-- 4. fsync WAL to disk → WAL is physically durable
-- 5. Return "COMMIT" to the application
-- 6. Actual data pages updated LATER by the checkpointer

package com.bank.service;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.sql.DataSource;

public class TransferService {

    private final DataSource dataSource;

    public TransferService(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // This transfer is durable because of WAL
    // Even if the server crashes after COMMIT returns,
    // the WAL ensures the transfer is recovered on restart
    public void transfer(String from, String to, BigDecimal amount)
            throws SQLException {
        try (Connection conn = dataSource.getConnection()) {
            conn.setAutoCommit(false);
            try {
                execute(conn, "UPDATE accounts SET balance = balance - ? "
                      + "WHERE account_id = ?", amount, from);
                execute(conn, "UPDATE accounts SET balance = balance + ? "
                      + "WHERE account_id = ?", amount, to);
                conn.commit();  // WAL fsynced before this returns
            } catch (SQLException e) {
                conn.rollback();
                throw e;
            }
        }
    }

    private void execute(Connection conn, String sql, BigDecimal amount,
                         String accountId) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setBigDecimal(1, amount);
            ps.setString(2, accountId);
            ps.executeUpdate();
        }
    }
}

package com.bank.admin;

public class CrashRecoveryExplanation {

    // After a crash, PostgreSQL automatically recovers:
    // 1. Finds the last checkpoint in pg_control
    // 2. Reads WAL from that checkpoint forward
    // 3. Replays all committed transactions
    // 4. Discards uncommitted transactions
    // 5. Database is ready for connections

    // The application does NOT need to do anything special
    // WAL guarantees that committed transactions survive crashes
    // This is why ACID's Durability guarantee holds
}

-- On the primary: configure streaming replication
-- postgresql.conf:
-- wal_level = replica
-- max_wal_senders = 3
-- archive_mode = on
-- archive_command = 'cp %p /archive/%f'

-- On the replica: start in recovery mode
-- recovery.conf / standby.signal:
-- primary_conninfo = 'host=primary.nbi.internal port=5432'
-- restore_command = 'cp /archive/%f %p'

-- Take a base backup
SELECT pg_start_backup('nbi_weekly_backup');
-- Copy data directory to S3
SELECT pg_stop_backup();

-- Or use pg_basebackup utility:
-- pg_basebackup -h primary.nbi.internal -D /backup/base -P

-- Recover to a specific point in time:
-- In recovery.conf / postgresql.conf:
-- restore_command = 'aws s3 cp s3://nbi-wal-archive/%f %p'
-- recovery_target_time = '2024-06-11 09:59:59'
-- recovery_target_action = 'promote'

package com.bank.admin;

public class BackupStrategy {

    // National Bank of India backup strategy:
    //
    // 1. Weekly base backup (Sunday 02:00)
    //    pg_basebackup → S3 (nbi-backups/base/)
    //
    // 2. Continuous WAL archiving
    //    archive_command → S3 (nbi-backups/wal/)
    //    Every filled WAL segment (16MB) is archived immediately
    //
    // 3. Recovery Point Objective (RPO): ~0
    //    Every committed transaction is in an archived WAL segment
    //    Maximum data loss = transactions since last archived segment
    //    With synchronous commit: truly zero data loss
    //
    // 4. Recovery Time Objective (RTO): depends on WAL to replay
    //    More frequent base backups = less WAL to replay = faster recovery
    //    Weekly base + 7 days of WAL vs Daily base + 1 day of WAL
    //
    // 5. Test restores monthly
    //    An untested backup is a hope, not a strategy
}

Checkpoint frequency trade-off:

Frequent checkpoints (every 5 minutes):
  + Faster crash recovery (less WAL to replay)
  - More write amplification (more dirty pages flushed)
  - Higher I/O load during checkpoints

Infrequent checkpoints (every 30 minutes):
  + Less write amplification
  + Lower steady-state I/O
  - Slower crash recovery (more WAL to replay)

For a banking ledger:
  Moderate frequency (every 10-15 minutes)
  checkpoint_completion_target = 0.9 (spread I/O over the interval)

-- PostgreSQL checkpoint configuration
-- postgresql.conf:
-- checkpoint_timeout = '10min'        -- max time between checkpoints
-- max_wal_size = '1GB'                -- checkpoint if WAL exceeds this
-- checkpoint_completion_target = 0.9  -- spread I/O over 90% of interval

-- Monitor checkpoints:
SELECT * FROM pg_stat_bgwriter;
-- checkpoints_timed:  how many triggered by timeout
-- checkpoints_req:    how many triggered by WAL size
-- buffers_checkpoint:  pages written during checkpoints

-- WRONG: archive_command fails silently
-- WAL segment 00000001000000010000002A was not archived
-- PITR chain is broken: cannot recover past that point

-- CORRECT: monitor archive status
SELECT * FROM pg_stat_archiver;
-- last_archived_wal, last_failed_wal, failed_count
-- Alert if failed_count > 0 or if last_archived_wal is stale

-- CORRECT: test restores regularly
-- An untested backup is a hope, not a strategy

package com.bank.monitoring;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;

public class WalArchiveMonitor {

    private final DataSource dataSource;

    public WalArchiveMonitor(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void checkArchiveHealth() throws SQLException {
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(
                     "SELECT last_archived_wal, last_failed_wal, failed_count "
                   + "FROM pg_stat_archiver")) {
            if (rs.next()) {
                int failedCount = rs.getInt("failed_count");
                if (failedCount > 0) {
                    System.err.println("WAL ARCHIVE ALERT: "
                            + failedCount + " failed archives!");
                    System.err.println("Last failed: "
                            + rs.getString("last_failed_wal"));
                }
            }
        }
    }
}

-- Synchronous commit (default, safe for banking)
SET synchronous_commit = on;
-- COMMIT waits for fsync → zero data loss on crash

-- Asynchronous commit (faster, risk of recent loss)
SET synchronous_commit = off;
-- COMMIT returns before fsync → last ~200ms of commits may be lost
-- Acceptable for non-critical data like activity logs