Common replication topologies and when to use each

-- Application connection routing
-- Writes: always go to the primary
UPDATE accounts SET balance = balance - 25000.00
WHERE account_id = 'SAV2001';  -- primary

-- Reads: routed to a replica (round-robin)
SELECT balance FROM accounts
WHERE account_id = 'SAV2001';  -- replica

package com.bank.config;

import javax.sql.DataSource;
import java.util.logging.Logger;

public class ReadWriteRoutingConfig {

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

    private final DataSource primaryDataSource;
    private final DataSource replicaDataSource;

    public ReadWriteRoutingConfig(DataSource primary, DataSource replica) {
        this.primaryDataSource = primary;
        this.replicaDataSource = replica;
    }

    public DataSource getWriteDataSource() {
        LOG.fine("Routing write to primary");
        return primaryDataSource;
    }

    public DataSource getReadDataSource() {
        LOG.fine("Routing read to replica");
        return replicaDataSource;
    }

    public DataSource getReadAfterWriteDataSource() {
        // For read-your-writes consistency, route to primary
        LOG.fine("Read-after-write: routing to primary");
        return primaryDataSource;
    }
}

Single-Primary Limits:

  Writes: all go to one node (bottleneck at high write volume)
  Reads: scale horizontally across replicas
  Lag: replica may be behind by milliseconds to seconds
  Read-your-writes: Rajesh deposits money, then immediately
    reads balance from a replica that has not received the
    write yet. Solution: route read-after-write to primary.

-- Conflict resolution strategies:

-- 1. Last-write-wins (timestamp-based)
-- The write with the latest timestamp wins
-- Simple but can lose data silently

-- 2. Per-column merge
-- If Node A changed name and Node B changed email,
-- merge both changes (no conflict)

-- 3. Application-level reconciliation
-- Queue conflicting writes for manual or automated resolution

package com.bank.service;

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

/**
 * Why active-active is dangerous for money:
 * Two concurrent withdrawals at different nodes
 * can both succeed, violating the balance invariant.
 */
public class ActiveActiveRisk {

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

    public void demonstrateRisk() {
        // Node A (Delhi): Rajesh withdraws 80000
        // Node B (Mumbai): Rajesh withdraws 80000
        // Balance: 100000
        //
        // Both nodes check: 100000 >= 80000? Yes.
        // Both nodes debit: 100000 - 80000 = 20000.
        // Replication merges: balance = 20000 on both nodes.
        // But actual withdrawals: 80000 + 80000 = 160000.
        // Real balance should be: 100000 - 160000 = -60000.
        // Money was created out of thin air.

        LOG.severe("Active-active for money is unsafe without "
            + "global coordination (which defeats the purpose)");
    }
}

-- PostgreSQL: cascading replication setup
-- Replica 2 connects to Primary
-- primary_conninfo = 'host=primary port=5432'

-- Replica 3 connects to Replica 2 (not Primary)
-- primary_conninfo = 'host=replica2 port=5432'

-- Replica 2 must have hot_standby = on
-- to allow downstream replicas to connect