Synchronous vs Asynchronous Database Replication

# postgresql.conf

# Level: on (default)
# Primary and standby both flush WAL before ack. Zero RPO.
synchronous_commit = on

# Level: remote_write
# Standby has written WAL to OS buffer (not flushed to disk).
# Lower latency than 'on'; standby OS crash can lose data.
# synchronous_commit = remote_write

# Level: remote_apply
# Standby has also replayed the WAL (data is visible on standby).
# Highest latency; required for synchronous hot-standby reads.
# synchronous_commit = remote_apply

# Level: local
# Only the primary flushes; standby is effectively async.
# synchronous_commit = local

# Level: off
# Neither primary nor standby flushes before ack. Maximum throughput.
# Risk: up to wal_writer_delay ms of data loss on primary crash.
# synchronous_commit = off

# Quorum standby selection — requires ANY or FIRST keyword:
# ANY 1: ack from any one of the listed standbys satisfies the quorum.
# FIRST 1: only the first (highest-priority) standby in the list.
synchronous_standby_names = 'ANY 1 (replica_east, replica_west)'

# Timeouts: when a standby goes silent, the sender marks it stale.
wal_receiver_timeout = 60s
wal_sender_timeout   = 60s

# postgresql.conf — asynchronous read-replica baseline

synchronous_commit = local          # Primary flushes; replicas are async
wal_level          = replica        # Minimum level needed for streaming replication
max_wal_senders    = 10             # Headroom for replicas + monitoring connections
wal_keep_size      = 1GB            # Buffer for slow replicas; prevents slot WAL deletion
max_replication_slots = 10

# Tune wal_sender process buffering for high-throughput writes:
wal_sender_timeout = 60s

-- ProxySQL routing configuration
-- Hostgroup 10 = primary (writes + strict-consistency reads)
-- Hostgroup 20 = async read pool (analytics, reporting)

INSERT INTO mysql_servers
  (hostgroup_id, hostname,                  port, max_connections, max_replication_lag, comment)
VALUES
  (10,           'primary.db.internal',     3306, 500,             0,                  'Primary — writes + sync reads'),
  (20,           'async-read-01.db.internal',3306, 1000,           5,                  'Async — analytics'),
  (20,           'async-read-02.db.internal',3306, 1000,           5,                  'Async — analytics');

-- Route SELECTs to the async pool; writes to primary
INSERT INTO mysql_query_rules
  (rule_id, active, match_digest,                destination_hostgroup, apply)
VALUES
  (100,     1,      '^SELECT.*',                 20,                    1),
  (101,     1,      '^(INSERT|UPDATE|DELETE).*', 10,                    1);

LOAD MYSQL SERVERS TO RUNTIME;   SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

; pgbouncer.ini

[databases]
primary_db   = host=primary   port=5432 dbname=app pool_mode=transaction
async_read   = host=replica01 port=5432 dbname=app pool_mode=transaction

[pgbouncer]
max_client_conn       = 2000
default_pool_size     = 50
server_idle_timeout   = 30    ; reclaim idle backend connections quickly
server_connect_timeout = 5    ; fail fast on replica network flaps
server_lifetime       = 3600  ; recycle long-lived connections to prevent resource leaks

import time

class ConsistencyRouter:
    """
    Routes reads to the async replica unless a write has occurred
    recently in this session (read-your-writes guarantee).
    Falls back to primary if replica lag exceeds the threshold.
    """
    def __init__(
        self,
        primary_engine,
        async_engine,
        lag_threshold_ms: int = 2000,
        pin_window_ms: int = 5000,
    ):
        self.primary      = primary_engine
        self.async_engine = async_engine
        self.lag_threshold = lag_threshold_ms / 1000.0
        self.pin_window    = pin_window_ms / 1000.0
        self._session_pins: dict[str, float] = {}

    def get_engine(self, session_id: str, is_write: bool = False):
        if is_write:
            self._session_pins[session_id] = time.monotonic()
            return self.primary

        pin_time = self._session_pins.get(session_id, 0.0)
        if time.monotonic() - pin_time < self.pin_window:
            return self.primary  # enforce read-your-writes

        if self._replica_lag_seconds() < self.lag_threshold:
            return self.async_engine
        return self.primary     # safety fallback when replica is lagging

    def _replica_lag_seconds(self) -> float:
        # Query pg_stat_replication.write_lag or a heartbeat table
        raise NotImplementedError

# prometheus/rules/replication.yml
groups:
  - name: replication_sla
    rules:

      # Primary: replica is falling behind the commit LSN
      - alert: ReplicaWriteLagExceedsSLA
        expr: pg_replication_lag_seconds > 3.0
        for: 2m
        labels:
          severity: critical
        annotations:
          summary: "Replica {{ $labels.instance }} write lag > 3 s"
          description: >
            Automated traffic drain should begin.
            Check WAL sender/receiver buffer sizes and disk IOPS on the standby.

      # WAL apply throughput drop — sustained disk or CPU constraint on standby
      - alert: WALApplyRateDegradation
        expr: rate(pg_stat_replication_write_lag_bytes[5m]) < 10485760
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "WAL apply rate on {{ $labels.instance }} < 10 MB/s"
          description: "Check standby disk IOPS, fsync latency, and wal_receiver_status."

      # Sync standby disconnect — primary may enter write-blocking state
      - alert: SynchronousStandbyDisconnected
        expr: pg_stat_replication_state{sync_state="sync"} == 0
        for: 30s
        labels:
          severity: critical
        annotations:
          summary: "No synchronous standbys connected to {{ $labels.instance }}"
          description: >
            If synchronous_standby_names has no async fallback,
            the primary will block all commits. Investigate network
            and standby process health immediately.

-- Current replication state and lag per standby
SELECT
    application_name,
    sync_state,
    state,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn))  AS send_lag,
    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, write_lsn))             AS write_lag,
    pg_size_pretty(pg_wal_lsn_diff(write_lsn, flush_lsn))            AS flush_lag,
    pg_size_pretty(pg_wal_lsn_diff(flush_lsn, replay_lsn))           AS replay_lag
FROM pg_stat_replication
ORDER BY sync_state DESC;

-- WAL slot retention pressure (async logical replication)
SELECT
    slot_name,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY retained_wal DESC;

-- Check if any sync standbys are connected
SELECT application_name, sync_state, state
FROM pg_stat_replication
WHERE sync_state = 'sync';
-- Empty result means the primary is or will soon be blocked

-- Option A: Temporarily degrade to async (accept RPO risk)
ALTER SYSTEM SET synchronous_commit = 'local';
SELECT pg_reload_conf();

-- Option B: Remove the missing standby from the sync list
ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (replica_west)';
SELECT pg_reload_conf();

-- After the standby recovers, restore the original config:
ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (replica_east, replica_west)';
SELECT pg_reload_conf();

# ProxySQL: check which servers are OFFLINE due to lag
mysql -h 127.0.0.1 -P 6032 -e \
  "SELECT hostgroup_id, hostname, status, max_replication_lag, replication_lag
   FROM runtime_mysql_servers;"

# postgresql.conf (PostgreSQL 13+)
max_slot_wal_keep_size = 10GB   # Invalidate slot rather than fill disk

-- Identify the bloated slot
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn))
FROM pg_replication_slots ORDER BY 3 DESC;

-- If the subscriber is gone, drop the slot immediately
SELECT pg_drop_replication_slot('slot_name_here');

-- Restore the subscriber from a fresh base backup rather than
-- letting it catch up — catching up from a large WAL backlog
-- generates sustained I/O pressure on the primary.

# postgresql.conf on the publisher
logical_decoding_work_mem = 256MB   # Default 64 MB is insufficient for bulk loads

-- PostgreSQL logical replication setup

-- On the publisher (primary):
ALTER SYSTEM SET wal_level = 'logical';
SELECT pg_reload_conf();  -- requires restart if changing from 'replica'

-- Publish only the tables needed for read-scaling
CREATE PUBLICATION read_scaling_pub FOR TABLE users, orders, sessions;

-- On the subscriber (analytics replica):
CREATE SUBSCRIPTION read_scaling_sub
  CONNECTION 'host=primary port=5432 dbname=app user=replicator password=secret'
  PUBLICATION read_scaling_pub
  WITH (copy_data = true, create_slot = true);