Replication Lag and Consistency Guarantees

Primary → WAL records → Standby applies WAL → Standby has copy of data

-- Primary configuration
ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM SET max_wal_senders = 5;
ALTER SYSTEM SET synchronous_standby_names = '';  -- Empty = async
 
-- Standby configuration
ALTER SYSTEM SET primary_conninfo = 'host=primary port=5432 user=replicator';
SELECT pg_create_physical_replication_slot('standby1');
 
-- Check replication status on primary
SELECT
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag_pretty
FROM pg_stat_replication;

-- Async replication (default)
ALTER SYSTEM SET synchronous_standby_names = '';
-- Primary commits immediately
-- Standby receives WAL asynchronously
-- Lag can be milliseconds to seconds

Timeline:
Primary:    [Write] [Write] [Write] [CRASH]
Standby:    [Write] [Write] [apply]  [missing!]
                         ↑
                    Lag of 1 write
                    This data is LOST

-- Sync replication to one standby
ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 (standby1)';
-- Primary blocks until standby1 confirms receipt
 
-- Sync replication to any 2 of 3 standbys
ALTER SYSTEM SET synchronous_standby_names = 'ANY 2 (s1, s2, s3)';
-- Primary blocks until any 2 standbys confirm
 
-- Check sync status
SELECT
    application_name,
    sync_state,
    sync_priority
FROM pg_stat_replication;

-- User A updates their profile on primary
UPDATE users SET email = 'new@example.com' WHERE id = 42;
COMMIT;  -- Success
 
-- User A's next request hits a standby (load balanced)
SELECT email FROM users WHERE id = 42;
-- Returns old email! Standby hasn't applied the write yet

# 1. Session stickiness: Route reads to primary for recently-written data
class ReadRouter:
    def __init__(self, primary, standbys):
        self.primary = primary
        self.standbys = standbys
    
    def read(self, user_id, last_write_time):
        if time.now() - last_write_time < timedelta(seconds=5):
            return self.primary.query(user_id)
        else:
            return random.choice(self.standbys).query(user_id)
 
# 2. Wait for replay: Read from standby but wait until it catches up
def read_after_write(standby, primary_lsn, timeout_ms=100):
    """Read from standby, waiting until it has replayed past primary_lsn"""
    start = time.now()
    while time.now() - start < timeout_ms / 1000:
        standby_lsn = standby.execute("SELECT pg_last_wal_replay_lsn()")
        if standby_lsn >= primary_lsn:
            return standby.query(...)
        time.sleep(0.001)  # 1ms poll
    
    # Timeout: fallback to primary
    return primary.query(...)
 
# 3. Causal consistency: Use session tokens
def write_with_causal_token(user_id, data):
    result = primary.execute(
        "UPDATE users SET ... RETURNING xmin",
        (data,)
    )
    return CausalToken(
        lsn=result.xmin,
        timestamp=time.now()
    )
 
def read_with_causal_token(user_id, token):
    for standby in standbys:
        if standby.replay_lsn() >= token.lsn:
            return standby.query(user_id)
    # Fallback: wait for any standby to catch up

-- Real-time lag monitoring
SELECT
    now() - pg_last_xact_replay_timestamp() AS replication_lag_time,
    pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS replay_lag_bytes;
 
-- On primary: check all standbys
SELECT
    application_name,
    state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS byte_lag,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag,
    replay_lag  -- Time since last WAL replay
FROM pg_stat_replication;
 
-- Alert on excessive lag
SELECT
    CASE
        WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) > 100 * 1024 * 1024
        THEN 'ALERT: Lag > 100MB'
        WHEN pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) > 10 * 1024 * 1024
        THEN 'WARNING: Lag > 10MB'
        ELSE 'OK'
    END AS status,
    application_name,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag
FROM pg_stat_replication;

-- Tune replay parallelism
ALTER SYSTEM SET max_parallel_workers = 4;  # For WAL replay
ALTER SYSTEM SET max_worker_processes = 8;
 
-- Monitor replay workers
SELECT
    pid,
    state,
    application_name
FROM pg_stat_activity
WHERE backend_type = 'walreceiver';
 
-- Check WAL generation rate
SELECT
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0')
    ) AS total_wal_generated;