PostgreSQL read replica cookbook covers traffic offloading, routing and per-replica tuning

Database consultant Christophe Pettus published the second part of a guide on PostgreSQL binary read replicas, focusing on practical deployment patterns. The article recommends application-level routing over middleware like Pgpool-II, using libpq's target_session_attrs=standby and load_balance_hosts=random for replica selection, and taking lagging replicas out of rotation via health checks. It emphasizes that each replica should be tuned for a single workload class—such as interactive queries versus reporting—since postgresql.conf is not replicated and settings like work_mem, max_parallel_workers_per_gather, and hot_standby_feedback should differ by use case.

Replica routing and workload-specific tuning

• Application-level routing preferred: Two connection pools (primary and replicas) with code choosing per request, rather than middleware like Pgpool-II that parses SQL and guesses intent
• libpq replica selection: target_session_attrs=standby (PostgreSQL 14+) and load_balance_hosts=random (16+) for client-side routing; JDBC equivalent is targetServerType=secondary&loadBalanceHosts=true
• Connection pool lifetime: Set maximum connection lifetime so recycled connections reach newly returned replicas
• Lag-aware health checks: Patroni's GET /replica?lag=16MB returns 503 for lagging replicas; threshold in WAL bytes, not time
• Per-replica configuration: postgresql.conf is not replicated after initial clone; ALTER SYSTEM writes to each replica's own postgresql.auto.conf
• Workload-specific settings: Interactive replicas need modest work_mem, low max_parallel_workers_per_gather, seconds-level statement_timeout, hot_standby_feedback=on; reporting replicas need large work_mem, high parallel workers, long/no timeout, hot_standby_feedback=off
• Role-based settings: ALTER ROLE ... SET and ALTER DATABASE ... SET replicate via catalogs, useful for carrying work_mem and statement_timeout across servers

Why it matters

The guidance on per-replica tuning and against middleware-based query routing could shift how teams architect PostgreSQL read scaling, particularly for mixed OLTP/analytic workloads where cache efficiency matters.