Weighted vs Least-Connections Balancing for Read Replicas

Problem statement: you have three or more read replicas and need to choose a selection algorithm, and the documentation for every proxy describes what each algorithm does without telling you which one your workload wants.

The choice is decidable rather than a matter of taste, and it turns on one measurable property: how much your read queries vary in cost. This page shows how to measure that, what the measurement implies, and the one configuration detail that makes the better algorithm safe. The surrounding context is in load balancing reads across a replica pool.

Symptom Identification

Signs your current algorithm is the wrong one:

  • Per-replica connection counts are near-identical while CPU utilisation differs by a factor of two or more — round robin distributing count, not work.
  • One replica’s p99 query latency is consistently worse than its siblings’ despite identical hardware and configuration.
  • A replica that has just been restarted or reintroduced immediately shows the worst latency in the fleet, then recovers over the following minute — least connections without a ramp.
  • Query rate is even, but pg_stat_statements shows the expensive digests clustered on one node.
  • After adding a replica, aggregate throughput improved by much less than one node’s worth.

The diagnostic question in every case is the same: which quantity is even, and which is not. Even connections with uneven CPU points at cost variance. Uneven connections points at pinning, which no algorithm change will fix.

Root Cause Analysis

Round robin equalises the number of decisions, not the amount of work. It sends the n-th request to the n-th replica regardless of what any replica is currently doing. When every query costs about the same, this is optimal and free. When one query in fifty takes 400 ms and the rest take 2 ms, round robin will hand a replica that is already running a 400 ms query the next request anyway, because it is that replica’s turn.

Least connections approximates equalising concurrent work. It sends the next request to whichever node currently holds the fewest open connections, which is a live proxy for busyness. A node stuck on a slow query accumulates connections and stops being chosen, so the algorithm self-corrects without needing to know anything about query cost.

The crossover is governed by the coefficient of variation. Define it as the standard deviation of per-query execution time divided by the mean. When it is small, queries are interchangeable and counting them is a fine proxy for work. When it is large, counting them is nearly meaningless. The rough thresholds — under 0.5 round robin is fine, over 1.5 least connections is clearly better — are not precise physics, but they separate the regimes reliably.

Least connections has one pathological case, and it is a common one. The node with fewest connections is sometimes the node that has fewest connections because it just arrived. Choosing it aggressively floods a cold node. This is not a reason to avoid the algorithm; it is a reason to always pair it with a warm-up ramp.

Query-cost distribution decides which algorithm balances work Two histograms of per-query execution time. The first is tight around two milliseconds with a coefficient of variation near zero point three; counting queries is a good proxy for work, so round robin distributes evenly. The second has a large spike at two milliseconds and a heavy tail out to four hundred milliseconds with a coefficient of variation above two; a few queries in the tail carry most of the total work, so equal counts produce very unequal load and least connections is required. uniform workload — CV ≈ 0.3 0 5 ms 10 ms counting queries ≈ counting work → round robin is correct and cheapest heavy-tailed workload — CV ≈ 2.2 0 100 ms 400 ms the tail carries most of the work equal counts → very unequal load → least connections, with slow-start Measure the coefficient of variation on your own digests before choosing — it takes one query and settles the argument.

Step-by-Step Resolution

Step 1 — Measure the coefficient of variation

sql
-- PostgreSQL 13+: per-digest mean and stddev, weighted by call count.
-- Run on a replica after a representative traffic period.
WITH stats AS (
  SELECT calls,
         mean_exec_time,
         stddev_exec_time,
         calls * mean_exec_time AS total_ms
    FROM pg_stat_statements
   WHERE query ~* '^\s*select' AND calls > 10
)
SELECT round(sum(total_ms) / sum(calls), 2)                    AS mean_ms,
       round(sqrt(sum(calls * (stddev_exec_time ^ 2 +
             (mean_exec_time - sum(total_ms) OVER () /
              sum(calls) OVER ()) ^ 2)) / sum(calls))::numeric, 2) AS pooled_sd_ms,
       round((sqrt(sum(calls * (stddev_exec_time ^ 2)) / sum(calls)) /
              (sum(total_ms) / sum(calls)))::numeric, 2)       AS coefficient_of_variation
  FROM stats;

Inline verification: the query returns a single row; a coefficient_of_variation under 0.5 or over 1.5 gives an unambiguous answer, and a value between them means either algorithm is acceptable.

Step 2 — Apply the implication

Coefficient of variation Choose Because
< 0.5 round robin counting is a good proxy for work; simplest and cheapest
0.5 – 1.5 either; prefer least connections if adding replicas often the difference is small, but leastconn degrades more gracefully
> 1.5 least connections a few queries dominate total work and must be routed around

Inline verification: after switching, the ratio of maximum to mean per-replica CPU should fall. If it does not, the imbalance was never about the algorithm — check for connection pinning instead.

Step 3 — Derive weights from usable capacity

Do not assume homogeneity. Measure what each node can actually serve:

bash
# Ramp load against one replica at a time until p99 crosses your latency
# objective; the sustained rate at that point is its usable capacity.
pgbench -h replica-1 -c 32 -j 8 -T 120 -S -P 10 app | tail -5
pgbench -h replica-3 -c 32 -j 8 -T 120 -S -P 10 app | tail -5

If replica-1 sustains 9,000 tps at your latency ceiling and replica-3 sustains 5,400, their weights should be roughly 100 and 60 — not 100 and 100 because both are “the same instance family”.

Inline verification: the measured tps ratio matches the weight ratio to within about 10%.

Step 4 — Configure, with slow-start mandatory

text
backend pg_read
    balance leastconn
    option pgsql-check user haproxy_check
    # slowstart is not optional with leastconn: without it, the node with
    # the fewest connections is the node least able to serve them.
    default-server inter 2s fall 3 rise 5 slowstart 60s maxconn 120

    server r1 10.0.1.11:5432 check weight 100
    server r2 10.0.1.12:5432 check weight 100
    server r3 10.0.1.13:5432 check weight 60     # measured, not assumed

Inline verification: restart r3, then watch the HAProxy stats page — its effective weight should climb gradually from a low value to 60 over the slow-start window rather than jumping straight to 60.

Step 5 — Compare realised share against configured intent

promql
# What each replica actually served, as a fraction of the total.
sum by (instance) (rate(pg_stat_database_xact_commit{role="replica"}[10m]))
  / ignoring(instance) group_left
    sum (rate(pg_stat_database_xact_commit{role="replica"}[10m]))

Expected shares for weights 100/100/60 are 0.385, 0.385 and 0.23. A large discrepancy means weights are not being realised, which almost always means the balancer is deciding at connection establishment while your clients hold long-lived pooled connections.

Inline verification: realised share is within a few percent of configured share; if not, set a connection lifetime and re-measure.

Configuration Snippet

text
# HAProxy — weighted least connections with the safety settings that make it work.
backend pg_read
    balance leastconn
    option pgsql-check user haproxy_check

    default-server inter 2s fall 3 rise 5 \
                   slowstart 60s \
                   maxconn 120 \
                   on-marked-down shutdown-sessions

    server r1 10.0.1.11:5432 check weight 100
    server r2 10.0.1.12:5432 check weight 100
    server r3 10.0.1.13:5432 check weight 60
sql
-- ProxySQL equivalent. Weight is relative within the host group and is
-- applied to the connection-selection probability.
UPDATE mysql_servers SET weight = 100 WHERE hostgroup_id = 20 AND hostname IN ('r1','r2');
UPDATE mysql_servers SET weight = 60  WHERE hostgroup_id = 20 AND hostname = 'r3';

-- The equivalent of slow-start: cap how fast connections are created to a
-- host that has just come back, so a cold node is not flooded.
UPDATE global_variables SET variable_value = '20'
 WHERE variable_name = 'mysql-connect_retries_on_failure';

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- Verify realised distribution — the configured weight is only an intent.
SELECT srv_host, Queries, ConnUsed, ConnFree,
       round(100.0 * Queries / sum(Queries) OVER (), 1) AS pct_of_reads
  FROM stats_mysql_connection_pool WHERE hostgroup = 20;

maxconn 120 on the HAProxy default-server line is worth a second look. It bounds how many connections HAProxy will open to any one replica, which converts a replica that has become very slow into a source of queueing at the proxy rather than a source of connection exhaustion at the database. That bound is what keeps one degraded node from consuming the database’s entire connection budget, and it should be set below the replica’s max_connections divided by the number of proxy instances.

Reintroducing a replica with and without slow-start Two paired traces over the two minutes after a replica rejoins the pool. Without slow-start its share of traffic jumps immediately to a third and its p99 latency spikes far above the fleet before slowly recovering, and in many cases it is ejected again. With slow-start its share climbs linearly to a third over sixty seconds and its latency never leaves the normal band. without slowstart share jumps to 1/3 instantly p99 spikes on cold cache often re-ejected before it warms with slowstart 60s share ramps to 1/3 p99 stays in band warms while carrying a trickle The ramp costs sixty seconds of slightly reduced capacity and removes the entire re-ejection loop.

Verification and Rollback

Confirm the algorithm changed the thing you wanted:

promql
# Work-share spread — the number that should fall after switching to leastconn.
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]))

Take a baseline for a full traffic cycle before switching, then compare over an equivalent window afterwards. A spread that falls from 1.6 to 1.15 is the algorithm working; a spread that does not move means the imbalance had a different cause.

Confirm the slow-start ramp is live:

bash
# HAProxy: effective weight during a ramp should be below the configured weight.
echo "show servers state pg_read" | socat stdio /var/run/haproxy.sock \
  | awk '{print $4, $6, $9}'    # name, operational state, current weight

Rollback. Both changes revert cleanly and independently:

bash
# Back to round robin without touching weights.
echo "set server pg_read/r1 weight 100" | socat stdio /var/run/haproxy.sock
# Then edit `balance leastconn` → `balance roundrobin` and reload.
systemctl reload haproxy

HAProxy reloads without dropping established connections, so reverting the algorithm is safe during traffic. Reverting weights is also safe, but note that with a connection-level balancer neither change affects existing connections — the effect appears only as connections turn over, so allow a full connection-lifetime before judging the result.

Edge Cases and Gotchas

Least connections is meaningless behind a transaction-pooling proxy

If PgBouncer sits between the balancer and the database in transaction mode, the balancer sees a small, stable number of connections to each pooler that has almost no relationship to current load. Least connections then compares numbers that do not vary. In that topology, balance at the pooler layer or use a statement-aware proxy — the layering question covered in choosing a read replica routing proxy.

A weight of zero is not the same as removing a server

Setting weight 0 stops new connections being assigned but leaves existing ones in place and keeps the server in the pool for backup purposes. If the intent is maintenance, drain the server explicitly instead — weight 0 on a connection-level balancer with long-lived pooled connections can leave a “disabled” replica serving most of its previous traffic for hours.

Cost variance changes when you add an index

The coefficient of variation is a property of the current workload and schema, not a constant. A new index that turns a 400 ms sequential scan into a 3 ms index lookup can move a workload from CV 2.2 to CV 0.4, at which point round robin becomes adequate again and least connections is merely harmless. Re-measure after significant schema changes rather than treating the choice as permanent.

FAQ

How do I measure query-cost variance?

Compute the coefficient of variation — standard deviation divided by mean — of per-call execution time across your read digests, weighted by call count. Below roughly 0.5 the workload is uniform enough that round robin distributes work evenly. Above about 1.5 a single slow query can occupy a replica for as long as hundreds of fast ones, and least connections becomes clearly better. Between those, either works and other factors decide.

Why does least connections need slow-start?

Because a node that has just returned to the pool has zero connections, which makes it the most attractive candidate at precisely the moment it is least able to serve — cold buffer cache, possibly still catching up on replay. Without a ramp it receives a disproportionate burst, slows down, and is often ejected again. Slow-start raises its effective weight gradually so the feedback loop stays stable.

Should weights be based on CPU count?

Only as a starting point. Usable read capacity depends on memory relative to working-set size and on I/O throughput at least as much as on cores — a replica with half the RAM may serve a quarter of the traffic before its cache hit ratio collapses. Start from cores, then correct with measured throughput at equal latency, and re-check after any working-set growth.

Can I combine weights with least connections?

Yes, and it is usually the right configuration. Weighted least-connections divides each node’s connection count by its weight before comparing, so capacity differences and current busyness are both respected. HAProxy applies server weights to leastconn automatically, and ProxySQL weights within a host group behave the same way.

← Back to Load Balancing Reads Across a Replica Pool