Lag-Aware Replica Weighting with HAProxy Agent Checks

Problem statement: one replica in your pool falls behind during a batch job, and your only control is a lag threshold that either keeps sending it a full share of reads or removes it entirely — with nothing in between.

HAProxy’s agent-check provides the in-between. It lets a small service on each replica report a live weight percentage that HAProxy multiplies into the configured weight, converting replication lag from a binary condition into a continuous control signal. This page builds that agent, chooses its thresholds, and drills it. The wider context is in load balancing reads across a replica pool.

Symptom Identification

You want lag-aware weighting when:

• A nightly batch job reliably pushes one replica past the lag budget, it is ejected, and the remaining replicas absorb its traffic — pushing one of them over as well.
• Lag on the busiest replica correlates with its read load, so removing all its traffic fixes the lag and restoring all of it recreates the problem — a control loop with only two settings.
• Your proxy’s default check is TCP or SELECT 1, which a replica thirty seconds behind passes perfectly.
• Ejection and reintroduction produce a visible sawtooth in the traffic graph, cycling every few minutes.
• Read freshness violations cluster in the seconds before an ejection, because the threshold is only crossed after users have already seen stale data.

The last point is the strongest argument for weighting. A binary threshold necessarily fires after the budget has been breached; a weighting curve begins acting before it.

Root Cause Analysis

Lag on a read replica is frequently caused by the read load on that replica. WAL replay is a single-process activity on PostgreSQL, and it competes with query execution for buffer cache, I/O bandwidth, and — through recovery conflicts — for locks. A replica carrying a heavy analytical query mix replays more slowly than an idle one, so read traffic and lag form a positive feedback loop.

That loop is why binary ejection over-corrects. Removing 100% of traffic from a lagging replica does fix the lag, quickly, and then full traffic returns and the loop restarts. Removing 40% of the traffic often fixes it too, more gently, with no discontinuity for anyone.

HAProxy’s agent-check exists precisely for this shape of signal. The agent-check mechanism opens a TCP connection to a nominated port on the server host at inter intervals and reads a single line. That line may contain a percentage, a state keyword, or both:

up 60%          → effective weight becomes 60 % of the configured weight
drain           → stop new sessions, let in-flight ones finish
down            → mark the server down immediately
ready           → clear a previous drain/maint state

The percentage is multiplied into the configured weight rather than replacing it, which is the property that makes the design clean: the static weight remains the capacity statement from weighted vs least-connections balancing, and the agent supplies an orthogonal health modifier.

The curve between the thresholds is the whole design. Below the soft threshold, full weight. Above the hard threshold, drain. Between them, a linear ramp. The soft threshold should sit where you want load to start moving; the hard threshold is the freshness budget itself.

Step-by-Step Resolution

Step 1 — Write the agent

#!/usr/bin/env python3
# /usr/local/bin/lag_agent.py — HAProxy agent-check on port 9101.
# Runs on each replica, connects to the LOCAL database over loopback.
import socketserver, psycopg

SOFT_MS, HARD_MS, REENTRY_MS = 200.0, 800.0, 120.0
FLOOR_PCT = 10                      # never fully zero — keep recovery observable

_drained = False                    # module state gives us the hysteresis

def lag_ms() -> float:
    with psycopg.connect("dbname=app host=/var/run/postgresql",
                         connect_timeout=1) as c, c.cursor() as cur:
        cur.execute("""SELECT CASE
                         WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()
                         THEN 0
                         ELSE EXTRACT(EPOCH FROM
                              (now() - pg_last_xact_replay_timestamp())) * 1000
                       END""")
        return float(cur.fetchone()[0] or 0.0)

def verdict() -> bytes:
    global _drained
    try:
        lag = lag_ms()
    except Exception:
        return b"drain\n"           # fail SAFE: an unreachable agent drains
    if lag >= HARD_MS:
        _drained = True
        return b"drain\n"
    if _drained and lag > REENTRY_MS:
        return b"drain\n"           # hysteresis: stay drained until well recovered
    _drained = False
    if lag <= SOFT_MS:
        return b"up 100%\n"
    pct = int(100 - (100 - FLOOR_PCT) * (lag - SOFT_MS) / (HARD_MS - SOFT_MS))
    return f"up {max(pct, FLOOR_PCT)}%\n".encode()

class Handler(socketserver.BaseRequestHandler):
    def handle(self):
        self.request.sendall(verdict())

if __name__ == "__main__":
    socketserver.ThreadingTCPServer.allow_reuse_address = True
    socketserver.ThreadingTCPServer(("0.0.0.0", 9101), Handler).serve_forever()

The except branch returning drain is the most important line. An agent that cannot measure lag must assume the worst — an agent crash that returned up 100% would silently disable the entire mechanism while every dashboard showed green.

Inline verification: printf "" | nc 127.0.0.1 9101 returns up 100% on a caught-up replica.

Step 2 — Choose the thresholds from the freshness budget

The hard threshold is not a free parameter: it is the freshness budget the read path already promises, described in routing queries based on data freshness requirements. The soft threshold is the tunable one — set it where lag begins to be unusual for your cluster, typically the p95 of steady-state lag.

-- Steady-state lag distribution over the last week, to site the soft threshold.
SELECT percentile_disc(0.50) WITHIN GROUP (ORDER BY lag_ms) AS p50,
       percentile_disc(0.95) WITHIN GROUP (ORDER BY lag_ms) AS p95,
       percentile_disc(0.99) WITHIN GROUP (ORDER BY lag_ms) AS p99
  FROM replica_lag_samples WHERE sampled_at > now() - interval '7 days';

Inline verification: the soft threshold sits above p95 — below it, the agent will be reducing weight during entirely normal operation.

Step 3 — Wire the agent into the backend

backend pg_read
    balance leastconn
    # The ordinary check stays: it is the liveness backstop. The agent
    # supplies freshness, which a liveness check cannot see.
    option pgsql-check user haproxy_check
    default-server inter 2s fall 3 rise 5 slowstart 60s

    server r1 10.0.1.11:5432 check weight 100 \
        agent-check agent-addr 10.0.1.11 agent-port 9101 agent-inter 2s
    server r2 10.0.1.12:5432 check weight 100 \
        agent-check agent-addr 10.0.1.12 agent-port 9101 agent-inter 2s
    server r3 10.0.1.13:5432 check weight 60 \
        agent-check agent-addr 10.0.1.13 agent-port 9101 agent-inter 2s

Keep both checks. pgsql-check marks a crashed node down within fall × inter regardless of what the agent says; the agent handles the case where the node is perfectly alive and simply stale.

Inline verification: echo "show servers state pg_read" | socat stdio /var/run/haproxy.sock shows a non-zero srv_admin_state change when the agent returns drain.

Step 4 — Confirm the hysteresis is doing its job

The _drained flag means the agent will not return up again until lag has fallen below REENTRY_MS, well under the soft threshold. Combined with HAProxy’s rise 5, a replica must be genuinely recovered for ten seconds before it is eligible again.

Inline verification: during a drill, watch the agent’s output while lag decays — it should continue returning drain while lag sits between 120 ms and 800 ms, then switch to up 100% in one step.

Step 5 — Drill with an injected lag spike

# Force lag on r3 and watch the weight curve respond.
psql -h 10.0.1.13 -c "SELECT pg_wal_replay_pause();"
watch -n2 'printf "" | nc 10.0.1.13 9101; \
           echo "show stat" | socat stdio /var/run/haproxy.sock | grep -E "^pg_read,r3"'

# Release and watch the gated, ramped recovery.
psql -h 10.0.1.13 -c "SELECT pg_wal_replay_resume();"

Inline verification: the agent output moves through up 100% → up 74% → up 41% → drain as lag climbs, and after resume goes straight from drain to up 100% once under the re-entry mark, with HAProxy’s slow-start ramping the effective weight from there.

Configuration Snippet

# /etc/systemd/system/pg-lag-agent.service
# The agent must be at least as available as the database it reports on.
[Unit]
Description=PostgreSQL replication-lag agent for HAProxy agent-check
After=postgresql.service
# Do NOT use Requires=: if postgres stops, we still want the agent running
# so it can report 'drain' rather than vanishing (a refused connection is
# ambiguous to HAProxy, an explicit drain is not).
Wants=postgresql.service

[Service]
ExecStart=/usr/local/bin/lag_agent.py
User=postgres
Restart=always
RestartSec=1
# Bound memory and restarts so a misbehaving agent cannot destabilise the node.
MemoryMax=64M

[Install]
WantedBy=multi-user.target

The Wants= rather than Requires= choice is deliberate and easy to get wrong. If the agent is stopped whenever PostgreSQL stops, then a crashed database produces a refused connection on the agent port. HAProxy’s handling of a refused agent connection is to leave the previous state in place — so a crashed replica can retain its last known good weight until the ordinary pgsql-check catches up. Keeping the agent alive so it can explicitly answer drain removes that ambiguity.

Verification and Rollback

Confirm the agent is being consulted, not merely running:

# HAProxy's own view of each server's agent state and effective weight.
echo "show servers state pg_read" | socat stdio /var/run/haproxy.sock \
  | awk 'NR>2 {printf "%-6s admin=%s oper=%s weight=%s\n", $4, $6, $7, $9}'

# And the agent directly, for comparison.
for h in 10.0.1.11 10.0.1.12 10.0.1.13; do
  echo -n "$h: "; printf "" | nc -w2 "$h" 9101
done

If HAProxy reports weight 100 while the agent is returning up 40%, the agent-check clause is missing or the agent-port is wrong — a silent misconfiguration, because everything looks healthy.

Confirm the curve is having the intended effect:

# Traffic share should track the agent's reported weight within a minute.
sum by (instance) (rate(pg_stat_database_xact_commit{role="replica"}[2m]))
  / ignoring(instance) group_left
    sum (rate(pg_stat_database_xact_commit{role="replica"}[2m]))

Rollback. The mechanism removes cleanly in either of two ways, depending on urgency:

# Immediate, no reload: force a server back to full weight at runtime.
echo "set server pg_read/r3 weight 100%" | socat stdio /var/run/haproxy.sock

# Permanent: drop the agent-check clause from the server lines and reload.
# The pgsql-check remains, so the pool reverts to liveness-only routing.
systemctl reload haproxy

Reverting to liveness-only is the correct emergency action if the agent itself is suspected of causing spurious weight reductions — for example if a monitoring query is timing out and every agent is returning drain, emptying the pool. Diagnose that case by checking whether all agents are draining simultaneously, which is far more likely to be an agent bug than a fleet-wide lag event.

Edge Cases and Gotchas

pg_last_xact_replay_timestamp() returns stale values on an idle primary

If no transaction has committed on the primary recently, the replica’s last replayed transaction timestamp keeps ageing even though the replica is perfectly current. The agent would report growing lag on an idle cluster. The CASE in the query above handles this by returning zero when receive and replay LSNs match — the replica has everything that was sent, so its lag is zero regardless of timestamps. A heartbeat table written on the primary every few seconds is the alternative fix and is more robust for MySQL, where the equivalent ambiguity also exists.

The agent shares the node’s fate

An agent on a host whose I/O has saturated may itself become slow to respond, and HAProxy will treat the timeout according to agent-inter and the fall counter — potentially marking the node down for the wrong reason. Keep the agent’s database connection timeout short (one second in the code above) and accept that an overloaded node draining is usually the right outcome anyway.

Weight percentages interact with slow-start

When a drained server returns, HAProxy applies slowstart and the agent’s percentage. During the ramp the effective weight is the product of the two, which can be lower than either suggests. This is correct behaviour but confusing when reading the stats page — a server showing weight 12 during recovery may be at 40% of a slow-start ramp that is itself at 30%. Wait for the ramp to complete before judging whether the agent’s curve is tuned correctly.

FAQ

Related

← Back to Load Balancing Reads Across a Replica Pool

• Weighted vs Least-Connections Balancing for Read Replicas — the static weight this agent modifies.
• Automatic Replica Ejection and Reintroduction on Lag Spikes — the binary backstop beneath the weighting curve.
• Detecting and Handling Replication Lag in Real-Time — the measurement the agent depends on, and its failure modes.