Synchronous vs asynchronous replication

-- PostgreSQL: configure synchronous replication
-- postgresql.conf on primary
-- synchronous_commit = on
-- synchronous_standby_names = 'replica1'

-- When Rajesh's transfer commits, the primary waits for
-- replica1 to confirm before returning success to the app.

BEGIN;
UPDATE accounts SET balance = balance - 25000.00
WHERE account_id = 'SAV2001';
UPDATE accounts SET balance = balance + 25000.00
WHERE account_id = 'SAV2002';
COMMIT;  -- blocks until replica1 confirms

-- PostgreSQL: asynchronous replication (default)
-- synchronous_commit = off (or no synchronous_standby_names)

BEGIN;
UPDATE accounts SET balance = balance - 25000.00
WHERE account_id = 'SAV2001';
COMMIT;  -- returns immediately, WAL shipped in background

package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;
import java.util.logging.Logger;

public class ReplicationDemoService {

    private static final Logger LOG = Logger.getLogger(
        ReplicationDemoService.class.getName());

    private final Connection primaryConn;
    private final Connection replicaConn;

    public ReplicationDemoService(Connection primaryConn, Connection replicaConn) {
        this.primaryConn = primaryConn;
        this.replicaConn = replicaConn;
    }

    public void demonstrateLag(String accountId) throws SQLException {
        // Write to primary
        String updateSql = "UPDATE accounts SET balance = balance + 1000.00 "
                         + "WHERE account_id = ?";
        try (PreparedStatement ps = primaryConn.prepareStatement(updateSql)) {
            ps.setString(1, accountId);
            ps.executeUpdate();
        }
        LOG.info("Committed on primary");

        // Immediately read from async replica: may see stale data
        String selectSql = "SELECT balance FROM accounts WHERE account_id = ?";
        try (PreparedStatement ps = replicaConn.prepareStatement(selectSql)) {
            ps.setString(1, accountId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    BigDecimal balance = rs.getBigDecimal("balance");
                    LOG.info("Replica balance: " + balance
                        + " (may be stale with async replication)");
                }
            }
        }
    }
}

-- PostgreSQL: quorum-based synchronous replication
-- Wait for ANY 1 of 3 replicas to confirm
-- synchronous_standby_names = 'ANY 1 (replica1, replica2, replica3)'

-- If replica1 is slow, the primary gets an ACK from replica2
-- and returns success. No single replica can stall the primary.

Failover Scenario:

  Synchronous:                      Asynchronous:
  Primary crashes after commit.     Primary crashes after commit.
  Replica has the commit.           Replica may NOT have the commit.
  Promote replica: no data loss.    Promote replica: last N txns lost.

  Rajesh's transfer of 25000:       Rajesh's transfer of 25000:
  Replica shows 25000 debited.      Replica may show old balance.
  RPO = 0.                          RPO = replication lag.

package com.bank.service;

// WRONG: write then immediately read from replica
// The replica may not have the write yet
public void transferAndConfirm(String fromId, String toId, java.math.BigDecimal amount) {
    transferService.transfer(fromId, toId, amount);  // writes to primary
    var balance = replicaRepo.getBalance(fromId);     // reads from replica
    // balance may still show the old value!
}

// CORRECT: read from primary for consistency-critical reads
public void transferAndConfirm(String fromId, String toId, java.math.BigDecimal amount) {
    transferService.transfer(fromId, toId, amount);  // writes to primary
    var balance = primaryRepo.getBalance(fromId);     // reads from primary
}

-- PostgreSQL: fallback behavior
-- If synchronous_standby_names lists one replica and it goes down,
-- the primary stalls on every COMMIT (waiting for an ACK that never comes).
-- Fix: use quorum (ANY 1 of N) or set synchronous_commit = local
-- as a manual emergency fallback.

-- Monitor replication lag
SELECT client_addr,
       state,
       sent_lsn,
       write_lsn,
       flush_lsn,
       replay_lsn,
       sent_lsn - replay_lsn AS lag_bytes
FROM pg_stat_replication;