Read Scaling with Hot Standby and Load Balancing
Learn how to offload read traffic to streaming replica standbys, understand replication lag, and route queries between primary and replicas for horizontal read scaling.
Read Scaling with Hot Standby and Load Balancing is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Why Scale Reads?
A single primary can become a bottleneck when read-heavy traffic grows. By sending SELECTs to read replicas, you free the primary to handle writes and scale reads horizontally by adding more standbys.
Hot Standby Basics
A hot standby is a streaming replica that accepts read-only queries while it continuously applies changes received from the primary. It stays nearly in sync via the write-ahead log (WAL).
Enabling Read Queries on a Standby
On the standby, hot_standby must be on (the default in modern versions) so it answers queries instead of just replaying WAL silently.
SHOW hot_standby;Detecting Recovery Mode
Your application can ask any node whether it is a read-only standby. A true result means do not send writes here.
SELECT pg_is_in_recovery();Understanding Replication Lag
Standbys apply WAL slightly behind the primary, so a read may not see the very latest write. This gap is replication lag. For most read traffic it is harmless, but read-after-write flows need care.
Measuring Lag
On a standby, compare how far replay is behind the last received WAL position to estimate lag in time.
SELECT now() - pg_last_xact_replay_timestamp() AS replay_lag;Routing in the Application
The simplest pattern keeps two connection pools: one to the primary for writes, one to replicas for reads. The app chooses based on the operation.
Read-After-Write Consistency
Right after a user writes data, reading from a lagging replica may show stale results. Mitigations:
- Route that user's next reads to the primary briefly
- Wait until the replica catches up to the write's WAL position
Load Balancing Across Replicas
A pooler or proxy such as PgBouncer, Pgpool-II, or HAProxy can distribute read connections across several standbys, spreading load and providing failover if one replica goes down.
Trade-offs and Limits
Read replicas are powerful but not magic:
- They do not scale write throughput
- Lag means eventual, not immediate, consistency on replicas
- Long queries on a standby can conflict with WAL replay
Plan routing around these realities.
Watching Replication from the Primary
The primary exposes every connected standby in a stats view, including how far behind each one is. Watch this to catch a replica falling dangerously behind.
SELECT client_addr, state,
replay_lag
FROM pg_stat_replication;Quick Check
Test your read-scaling knowledge.
Recap
You learned read scaling:
- Hot standbys serve read-only queries while replaying WAL
pg_is_in_recovery()identifies a standby- Replication lag means replicas can be slightly stale
- Route writes to primary, reads to replicas; handle read-after-write
- Use a proxy to load-balance and fail over across replicas
Frequently asked questions
Is the “Read Scaling with Hot Standby and Load Balancing” lesson free?
Yes — the full text of “Read Scaling with Hot Standby and Load Balancing” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.
What will I learn in “Read Scaling with Hot Standby and Load Balancing”?
Learn how to offload read traffic to streaming replica standbys, understand replication lag, and route queries between primary and replicas for horizontal read scaling. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start PostgreSQL Performance & Query Optimization?
No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Read Scaling with Hot Standby and Load Balancing” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?
Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Connection Pooling with PgBouncer
- Replication Strategies (Streaming, Logical)
- Sharding and Distributed PostgreSQL
- Read Scaling with Hot Standby and Load Balancing