When to Use Logical vs Physical Replication for Read Scaling

Architectural decision guide for scaling read workloads. Physical replication copies disk blocks or WAL segments byte-for-byte, maintaining exact binary parity between primary and standby nodes. Logical replication decodes changes at the row or transaction level, enabling schema divergence and targeted data distribution. For foundational concepts on WAL streaming and consistency guarantees, review Database Replication Fundamentals & Architecture before configuring routing layers.

Configuration Matrix: Selecting the Replication Protocol

Map workload characteristics to the replication protocol before provisioning infrastructure. Physical replication requires identical major PostgreSQL versions, OS architectures, and filesystem layouts, making it optimal for low-latency, full-cluster read scaling. Logical replication supports cross-version routing, partial table synchronization, and heterogeneous target engines (e.g., routing to analytical warehouses), but introduces decoding overhead and requires explicit primary key enforcement.

Configuration Steps:

1. Audit primary schema for primary keys and unique constraints (mandatory for logical decoding). Tables lacking explicit keys must be altered: ALTER TABLE <table> REPLICA IDENTITY FULL;
2. Set max_wal_senders and max_replication_slots in postgresql.conf. Minimum: max_wal_senders = <num_standbys> + 2, max_replication_slots = <num_logical_subs> + 2. Restart required.
3. Initialize replication stream:

• Physical: pg_basebackup -D /var/lib/postgresql/data --wal-method=stream -R -P -v
• Logical: CREATE PUBLICATION read_scaling_pub FOR TABLE users, orders; then CREATE SUBSCRIPTION read_scaling_sub CONNECTION 'host=primary port=5432 dbname=app' PUBLICATION read_scaling_pub;

4. Validate replication stream:

• Physical: SELECT pid, client_addr, state, sent_lsn, replay_lsn, write_lag, flush_lag, replay_lag FROM pg_stat_replication;
• Logical: SELECT slot_name, plugin, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots; and SELECT * FROM pg_logical_slot_peek_changes('slot_name', NULL, NULL);

Connection Routing & Proxy Configuration

Deploy connection routers (PgBouncer, ProxySQL, HAProxy) to distribute read queries across replica fleets. Configure health checks against pg_is_in_recovery() for physical standbys or monitor pg_stat_subscription lag metrics for logical subscribers. Route write traffic exclusively to the primary, and split read traffic based on consistency requirements. Understand how Understanding Synchronous vs Asynchronous Replication impacts routing latency and stale-read tolerance when defining SLA thresholds.

Configuration Steps:

1. Define read/write split rules in proxy configuration:

# PgBouncer example
[databases]
app_primary = host=primary port=5432 dbname=app
app_read = host=replica_pool port=5432 dbname=app

2. Implement lag-aware routing thresholds: Reject reads if lag exceeds tolerance. Example ProxySQL query rule: SELECT CASE WHEN pg_last_wal_replay_lsn() - pg_last_wal_receive_lsn() > 1048576 THEN 'unhealthy' ELSE 'healthy' END;
3. Configure connection pooling parameters: Use pool_mode = transaction for logical replicas (stateless, connection-safe). Use pool_mode = session for physical standbys if temporary tables or session-level GUCs are required by analytical queries.
4. Test failover routing with pg_is_in_recovery() state transitions: Simulate primary promotion and verify proxy health checks automatically demote the former primary to read-only or drain connections.

Runbook: Symptom Identification & Root Cause Analysis

Monitor read scaling degradation continuously. Common symptoms include elevated query latency, connection pool exhaustion, and replication slot retention spikes. Root causes typically stem from long-running analytical queries blocking logical decoding, network MTU mismatches causing WAL fragmentation, or missing REPLICA IDENTITY FULL on tables with frequent updates.

Symptom Identification:

• Read queries timeout despite healthy primary CPU/memory metrics
• Replication lag exceeds configured routing threshold (replay_lag > 2s)
• Connection router marks replicas as unhealthy due to failed health probes
• pg_stat_replication shows state = streaming but write_lag/replay_lag continuously growing

Root Cause Analysis:

• Logical decoding backlog: Unindexed UPDATE/DELETE operations generate excessive TOAST and tuple versioning, overwhelming the decoder. Verify with EXPLAIN (ANALYZE, BUFFERS) on hot update paths.
• Physical WAL shipping interrupted: Firewall rules or TCP keepalive misconfigurations drop idle streaming connections. Check netstat/ss for ESTABLISHED vs CLOSE_WAIT states on port 5432.
• Proxy health check misconfigured: Logical replicas do not respond to pg_is_in_recovery() (always returns false). Ensure logical health probes query pg_subscription or custom lag functions instead.

Mitigation & Rollback Procedures

Execute immediate mitigation to restore read availability, followed by safe rollback if protocol mismatch or decoding corruption is confirmed. Mitigation involves temporarily routing reads to the primary, increasing max_replication_slots, or pausing logical decoding. Rollback requires dropping the publication, rebuilding the replica via physical base backup, and reverting proxy routing rules.

Mitigation Steps:

1. Switch connection router to primary-only mode: routing_mode = primary (or equivalent proxy directive). Accept increased primary load to prevent stale reads.
2. Clear stuck logical replication slots: SELECT pg_drop_replication_slot('stuck_slot_name'); Warning: This discards undelivered changes. Verify downstream consumers are drained first.
3. Increase wal_keep_size to prevent WAL recycling during lag spikes: ALTER SYSTEM SET wal_keep_size = '2GB'; SELECT pg_reload_conf();
4. Restart proxy to reset connection pool states: systemctl restart pgbouncer or equivalent. Verify pool exhaustion clears.

Rollback Steps:

1. Stop replica: pg_ctl stop -D /var/lib/postgresql/data -m fast
2. Remove recovery parameters: Delete postgresql.auto.conf entries for primary_conninfo or restore_command.
3. Rebuild replica using physical base backup:

rm -rf /var/lib/postgresql/data/*
pg_basebackup -D /var/lib/postgresql/data --wal-method=stream -R -P -v -h primary_host

4. Reconfigure proxy to physical standby routing: Update health checks to SELECT pg_is_in_recovery(); and revert pool_mode to session if required.
5. Validate read consistency:

SELECT pg_last_wal_replay_lsn() AS replay_lsn, 
pg_last_wal_receive_lsn() AS receive_lsn,
(pg_last_wal_replay_lsn() - pg_last_wal_receive_lsn()) AS lag_bytes;

Confirm lag_bytes = 0 before re-enabling automated read routing.