Load Balancing Reads Across a Replica Pool

Adding replicas increases read capacity only to the extent that reads actually spread across them. The operational problem this page addresses is the gap between those two things: a fleet of five replicas where one carries 60% of the query volume has the cost of five nodes and the throughput ceiling of roughly two, and the failure surface of the busiest one. This sits downstream of the routing decision described in connection routing and pooling strategies — classification decides whether a query may go to a replica, balancing decides which replica it goes to, and the second decision is the one that determines whether your capacity investment pays off.

The SLA risk is specific. An unbalanced pool fails earlier than a balanced one at the same aggregate load, and it fails in a way that looks like a capacity problem when it is really a distribution problem — so the instinctive response, adding another replica, does not help.

What “Balanced” Actually Means

Three different quantities get called load, and they diverge in ways that matter.

Connection count. How many client connections are currently attached to each replica. This is what most balancers actually equalise, because it is what they can observe cheaply. It is a good proxy for load only when connections are short-lived and uniformly busy.

Query rate. Queries per second landing on each replica. Diverges from connection count whenever connections are pooled and long-lived, because one pooled connection can carry a thousand times the traffic of another.

Work. CPU time, buffer reads, and I/O consumed. Diverges from query rate whenever query cost varies — and in a read-replica fleet it almost always does, because replicas are exactly where the expensive analytical queries get sent.

A pool is balanced when work is even. Balancing connections is a means to that end and a poor one under pooling. This is the single most common source of confusion when debugging an uneven fleet: the balancer is doing precisely what it was configured to do, and what it was configured to do is not what you wanted.

Scope: this page is about selecting among replicas that are already eligible. Deciding eligibility — is this replica fresh enough, is it healthy — belongs to routing queries based on data freshness requirements and to health checking. Selection operates on whatever set those leave behind.

Connections, queries, and work across the same four replicas Three grouped bar charts of the same four-replica fleet. In the connections view all four bars are nearly equal. In the queries-per-second view the bars are moderately uneven because pooled connections carry different traffic volumes. In the CPU-seconds view one replica towers over the others because it is receiving the expensive analytical queries. The caption notes that only the third view describes capacity. connections what the balancer equalises r1 r2 r3 r4 even — looks healthy queries / s skewed by pooled reuse r1 r2 r3 r4 moderately uneven CPU seconds what capacity actually is r1 r2 r3 r4 r2 is the real ceiling Same fleet, same minute. Adding a fifth replica moves the first two charts and leaves the third almost unchanged. Always confirm which quantity is uneven before choosing a remedy — they have different causes and different fixes.

Mechanism Deep-Dive: How a Replica Gets Chosen

A balancer evaluates a selection function at a specific moment, and when it evaluates matters as much as how.

At connection establishment (connection-level balancing). HAProxy in TCP mode, and any DNS-based scheme, pick a backend once per connection. Every query on that connection then goes to the same replica for the connection’s whole life. With a pooled client holding connections for hours, this means the distribution was decided by conditions that existed hours ago — including any replica that happened to be down at the time and therefore received nothing.

At query dispatch (statement-level balancing). ProxySQL and ORM-level routers choose per statement. This tracks current conditions and reacts to lag or saturation immediately, at the cost of parsing every statement and maintaining more state.

The distinction explains most real-world imbalance. Consider the common sequence: a replica is restarted for patching; the application fleet reconnects and spreads over the remaining nodes; the patched replica comes back and receives no traffic because nobody is establishing new connections. Connection-level balancing has no mechanism to correct this. The pool is now permanently skewed until something forces reconnection.

python
# The three classic selection functions, stated precisely enough to reason about.
# `conns` is the current connection count per replica; `w` the static weight.

def round_robin(replicas, state):
    """Even counts. Ignores how busy each node is."""
    state.i = (state.i + 1) % len(replicas)
    return replicas[state.i]

def least_connections(replicas, conns):
    """Self-correcting under variable query cost — but floods an empty node."""
    return min(replicas, key=lambda r: conns[r])

def weighted_least_connections(replicas, conns, w, ramp):
    """What you actually want: capacity-aware, with a warm-up ramp so a node
    that just came back is not chosen simply because it has zero connections."""
    return min(replicas, key=lambda r: conns[r] / max(w[r] * ramp(r), 1e-6))

The ramp(r) term is the part most hand-rolled balancers omit and every mature proxy includes (HAProxy calls it slowstart). Without it, least-connections selection has a pathological interaction with recovery: the node least able to serve traffic — cold caches, possibly still catching up on WAL replay — is the node most attractive to the algorithm, because it has the fewest connections. It gets flooded, slows down, and is often ejected again. The ramp turns an unstable feedback loop into a stable one.

Trade-off Comparison

Strategy Evens out Handles cost variance Behaviour on node return Cost to run
DNS round robin Nothing reliably — resolver caching and client behaviour dominate No Poor: no reconnection trigger Free, and worth about that
Round robin (connection-level) Connection count No Poor: existing connections never move Negligible
Round robin (statement-level) Query count No Immediate Low — proxy parses each statement
Least connections Concurrent work approximately Yes Dangerous without slow-start Low
Weighted least connections Work, in proportion to capacity Yes Safe with slow-start ramp Low
Lag-derived dynamic weight Work and freshness Yes Safe; also pre-empts breaches Moderate — needs a lag agent
Zone-preferring Deliberately uneven; optimises latency and transfer cost Inherits the inner strategy Depends on inner strategy Moderate — topology awareness

Read this table together with the trade-off matrix in the parent guide: a proxy that gives you excellent statement-level classification but only connection-level balancing will still produce a skewed fleet. The two capabilities are independent, and tool documentation frequently conflates them. Choosing a read replica routing proxy compares the common tools on exactly this axis.

Configuration Runbook

HAProxy: weighted least-connections with a lag-derived weight

text
backend pg_read
    balance leastconn
    option pgsql-check user haproxy_check
    # slowstart is not optional: it is what makes leastconn safe on node return.
    default-server inter 2s fall 3 rise 5 slowstart 60s maxconn 120

    # Static weights reflect usable capacity, not instance count.
    # r3 is a smaller instance and is weighted accordingly.
    server r1 10.0.1.11:5432 check agent-check agent-addr 10.0.1.11 agent-port 9101 weight 100
    server r2 10.0.1.12:5432 check agent-check agent-addr 10.0.1.12 agent-port 9101 weight 100
    server r3 10.0.1.13:5432 check agent-check agent-addr 10.0.1.13 agent-port 9101 weight 60

The agent on each host returns a percentage that HAProxy multiplies into the static weight, which is how a lag signal becomes a gradual traffic shift rather than an ejection:

python
# lag_agent.py — returns a weight percentage derived from current lag.
# Below soft_ms the replica is at full weight; between soft and hard it
# degrades linearly; above hard it drains. Gradual, not binary.
SOFT_MS, HARD_MS = 200, 800

def weight_for(lag_ms: float) -> bytes:
    if lag_ms >= HARD_MS:
        return b"drain\n"
    if lag_ms <= SOFT_MS:
        return b"up 100%\n"
    pct = int(100 * (HARD_MS - lag_ms) / (HARD_MS - SOFT_MS))
    return f"up {max(pct, 10)}%\n".encode()

The max(pct, 10) floor matters: dropping straight to zero weight is ejection by another name, and it removes the small trickle of traffic that lets you observe whether the node is recovering.

ProxySQL: weights inside a read host group

sql
-- Weight is relative within the host group; ProxySQL also removes a host
-- automatically when monitored lag exceeds max_replication_lag.
UPDATE mysql_servers SET weight = 100, max_replication_lag = 3
 WHERE hostgroup_id = 20 AND hostname IN ('r1','r2');
UPDATE mysql_servers SET weight = 60,  max_replication_lag = 3
 WHERE hostgroup_id = 20 AND hostname = 'r3';
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- Observe the resulting distribution, not the configured intent.
SELECT hostgroup, srv_host, Queries, ConnUsed, ConnFree
  FROM stats_mysql_connection_pool WHERE hostgroup = 20;

Always verify with the second query. Configured weight and realised share diverge whenever connections are long-lived, and stats_mysql_connection_pool is the only place the divergence is visible.

Zone preference without hard partitioning

text
backend pg_read_zone_a
    balance leastconn
    default-server inter 2s fall 3 rise 5 slowstart 60s
    server a1 10.0.1.11:5432 check weight 100
    server a2 10.0.1.12:5432 check weight 100
    # Cross-zone nodes are backups: used only when no local node is available,
    # so normal traffic stays in-zone but a zone outage does not become an outage.
    server b1 10.0.2.11:5432 check weight 100 backup
    server b2 10.0.2.12:5432 check weight 100 backup

Marking cross-zone servers as backup rather than omitting them is the difference between a latency optimisation and a single-zone dependency.

Binary ejection versus lag-derived weighting Two traces of one replica's share of read traffic as its lag rises. The ejection policy holds a flat full share, then steps to zero the moment the lag budget is breached, and steps back to full when the replica recovers. The weighting policy begins reducing share as soon as lag passes a soft threshold, so the share declines smoothly, the load causing the lag is relieved, and the hard budget is never reached. share rising lag → soft threshold (200 ms) hard budget (800 ms) ejection — full share until the cliff zero share, then straight back to full weighting — share falls as lag rises relieving the load that caused it binary ejection lag-derived weight Weight first; eject as the backstop.

Monitoring and Alerting Signals

Balance is a distribution property, so the useful alerts are about spread, not about any single node’s absolute value.

promql
# 1. Query-share spread: the busiest replica versus the fleet mean.
#    Above ~1.4 sustained, something is pinning traffic.
max by (cluster) (rate(pg_stat_database_xact_commit{role="replica"}[5m]))
  / avg by (cluster) (rate(pg_stat_database_xact_commit{role="replica"}[5m])) > 1.4

# 2. Work-share spread — the one that actually predicts saturation.
max by (cluster) (rate(node_cpu_seconds_total{mode!="idle",role="replica"}[5m]))
  / avg by (cluster) (rate(node_cpu_seconds_total{mode!="idle",role="replica"}[5m])) > 1.5

# 3. A replica receiving effectively nothing — usually a post-restart pinning artefact.
rate(pg_stat_database_xact_commit{role="replica"}[10m]) < 1

# 4. Realised weight versus configured weight (ProxySQL).
proxysql_connection_pool_queries_total / on(hostgroup) group_left
  sum by (hostgroup) (proxysql_connection_pool_queries_total)

Signal 3 is the highest-value and least-deployed of the four. A silent replica costs full price, contributes nothing, and — critically — is untested: you discover it cannot serve traffic at the moment you finally need it to. Alerting on near-zero traffic catches post-maintenance pinning within minutes instead of at the next incident. The exporter and recording-rule scaffolding for these series is covered in Prometheus metrics for replica health and lag.

Failure Modes and Recovery Steps

  1. Post-restart pinning: one replica receives no traffic. Diagnosis: signal 3 fires; connection counts are uneven while the balancer reports all backends healthy. Recovery: force reconnection — recycle the application pools in a rolling fashion, or set a server_lifetime on the pooler so connections age out naturally. Prevention: set a maximum connection lifetime (PgBouncer server_lifetime, HikariCP maxLifetime) so the fleet continuously re-balances instead of only at deploy time.

  2. Least-connections floods a recovering node. Diagnosis: a replica that just came back immediately shows the highest latency and often re-ejects within a minute. Recovery: drain it manually and re-introduce with a weight ramp. Prevention: slowstart on every backend, sized to how long your working set takes to warm — typically 60 s, longer for large buffer pools.

  3. Analytical queries concentrate on one node. Diagnosis: connection and query counts are even but CPU is not; pg_stat_statements on the hot node shows a small number of very expensive digests. Recovery: move the expensive workload to a dedicated replica rather than trying to balance it. Prevention: segregate by workload class — a reporting host group and an OLTP host group — because no balancing algorithm can make a 40-second query and a 2 ms query interchangeable. See query performance analysis on read replicas for identifying the digests.

  4. Zone preference becomes a zone dependency. Diagnosis: a single zone’s replica outage causes a full read outage for callers in that zone. Recovery: remove the zone restriction immediately; accept the cross-zone latency. Prevention: configure out-of-zone replicas as backup servers rather than omitting them, and drill the zone-loss case.

  5. Weights configured but not realised. Diagnosis: realised share does not match configured weight; signal 4 is flat regardless of configuration changes. Recovery: confirm whether the balancer is connection-level — if so, weights only bias new connections, and long-lived pooled connections make them nearly inert. Prevention: pair weights with connection lifetime limits, or move balancing to statement level.

Production-Readiness Checklist

In This Section

FAQ

Why is one replica getting far more traffic than the others?

The usual cause is not the balancing algorithm but connection reuse. With long-lived pooled connections the balancer only makes a decision at connection establishment, so a fleet that connected while one replica was briefly unavailable stays skewed indefinitely. Check whether your imbalance is in connections or in queries per connection: if connections are even but queries are not, the cause is query cost variance; if connections themselves are uneven, it is a pinning artefact and rebalancing requires recycling connections.

Should I use round robin or least connections?

Round robin is correct when query cost is roughly uniform, because it is cheap and produces a perfectly even count. Least connections is correct when cost varies widely — analytics queries mixed with point lookups — because it naturally sends the next request to whichever node is least busy. The failure mode of round robin is a slow node accumulating a queue it never escapes; the failure mode of least connections is a freshly restarted node with zero connections being flooded, which is why it should be paired with a slow-start ramp.

How do lag-aware weights differ from simply ejecting a lagging replica?

Ejection is binary and late: the replica carries a full share until it breaches the budget, then carries nothing. Weighting is proportional and early: as lag rises the replica’s share falls, which often relieves the load causing the lag and prevents the breach entirely. In practice you want both — weighting as the gradual response and ejection as the backstop when weighting fails to help.

Does zone-aware routing conflict with even balancing?

Yes, deliberately. Zone-preferring routing keeps reads inside the caller’s availability zone to cut latency and cross-zone data transfer cost, which makes traffic uneven whenever your application tier is unevenly distributed across zones. That is an acceptable trade only if each zone’s replicas can absorb their zone’s whole read load. Configure zone preference as a preference, not a hard rule, so a zone whose replicas are saturated or lagging can still spill over.

← Back to Connection Routing & Pooling Strategies