Replication Lag & Capacity Alerting Across Shards

Replication lag and capacity exhaustion are the two slow-burning failure classes in a sharded fleet: neither takes a shard down instantly, but both silently erode the guarantees your failover and read-scaling designs depend on. This guide is part of the Partition Monitoring & Failover Automation section; it covers how to measure lag correctly per shard in PostgreSQL and MySQL, which capacity signals predict trouble weeks in advance, and how to design multi-window alerts that page the right shard team — complementing partition skew detection & monitoring, which finds imbalance between shards, and automated failover orchestration, which consumes these signals to decide when promotion is safe.

Problem Framing

A payments platform runs 24 PostgreSQL shards, each a primary with two streaming replicas. Read traffic is split: shard-local reads go to replicas, and a reporting service fans out cross-shard queries every five minutes. One Tuesday, shard 14’s replica falls 40 seconds behind — an ETL job on the primary generated a burst of WAL that the replica’s single-threaded replay process could not absorb. Nothing pages. The reporting service, which joins balances across all 24 shards, silently produces a report where shard 14’s numbers are 40 seconds staler than the other 23. Finance reconciliation flags a discrepancy; three engineers spend a day proving the database was “wrong” in a way no single-node monitoring dashboard could show.

Two weeks later the same replica is 6 minutes behind during a primary hardware failure. The failover orchestrator promotes it anyway because the only health check was “replica is streaming.” Six minutes of confirmed payment writes are gone.

Both incidents were preventable with the same instrumentation: per-shard lag measured in both bytes and seconds, exported with a shard label, evaluated by alerts that distinguish a transient burst from sustained degradation, and consumed as a promotion gate by the failover system. Single-instance lag monitoring does not transfer to fleets: with 24 shards and 48 replicas, a global “max lag” number hides which shard is degraded, per-instance thresholds generate pages nobody can triage, and lag on any one shard corrupts every cross-shard read.

Architecture Overview: Where Lag Is Actually Measured

Replication lag is not one number. In PostgreSQL streaming replication, a committed transaction’s WAL passes through four observable positions, and the distance between any two of them is a different “lag” with a different operational meaning.

The distinctions matter operationally:

• Send lag (commit → point 1) indicates the primary’s WAL sender is throttled — often max_wal_senders exhaustion or network saturation.
• Write/flush lag (1 → 2/3) indicates replica disk or network bottlenecks; the data is on the wire but not durable on the replica yet. Flush position is the data-loss boundary: WAL flushed on the replica survives a replica crash.
• Replay lag (3 → 4) is the most common problem: the replica has the WAL safely on disk but its single-threaded startup process cannot apply it fast enough. Reads on the replica see the replayed position, so replay lag is what makes reads stale.

A replica can have zero flush lag and minutes of replay lag simultaneously — durable but stale. Failover data loss is bounded by flush lag; read staleness is bounded by replay lag. Alert on both, separately.

Measuring Lag Correctly Per Shard

PostgreSQL: bytes and seconds from pg_stat_replication

Run this on each shard’s primary. It returns one row per attached replica with byte lag at all four positions plus the server-computed time lags (PostgreSQL 10+ maintains write_lag, flush_lag, and replay_lag as interval columns when track_commit_timestamp sampling conditions are met):

SELECT
  application_name,
  client_addr,
  state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)   AS send_lag_bytes,
  pg_wal_lsn_diff(sent_lsn, write_lsn)              AS write_lag_bytes,
  pg_wal_lsn_diff(write_lsn, flush_lsn)             AS flush_lag_bytes,
  pg_wal_lsn_diff(flush_lsn, replay_lsn)            AS replay_lag_bytes,
  pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS total_lag_bytes,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication;

The replica-side view answers a different question — “how stale am I?” — and works even when the primary is unreachable:

SELECT
  pg_last_wal_receive_lsn()  AS received_lsn,
  pg_last_wal_replay_lsn()   AS replayed_lsn,
  CASE
    WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0
    ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())
  END AS replay_lag_seconds;

The CASE guard is essential. pg_last_xact_replay_timestamp() returns the commit time of the last replayed transaction; on an idle primary with no new commits, that timestamp ages while the replica is perfectly caught up, and a naive now() - timestamp metric climbs forever. Comparing receive and replay LSNs first turns “caught up on an idle shard” into an honest zero. Skip this guard and your quietest shards page at 3 a.m. — the most common false positive in fleet lag monitoring.

MySQL: why Seconds_Behind_Source lies, and what to use instead

MySQL’s SHOW REPLICA STATUS exposes Seconds_Behind_Source, and it is the most misleading lag metric in wide deployment. It is computed as the difference between the replica SQL thread’s clock and the timestamp inside the event being applied — which means it reads 0 when the SQL thread is idle even if the I/O thread is minutes behind fetching binlog, reads NULL when replication is stopped (which naive exporters coerce to 0), and jumps wildly across relay-log boundaries and after replica restarts.

For fleets running GTID mode, measure the gap directly — the set of transactions the source has executed that the replica has not:

-- On the replica: transactions executed on the source but not yet applied here
SELECT GTID_SUBTRACT(
  (SELECT RECEIVED_TRANSACTION_SET
     FROM performance_schema.replication_connection_status
     WHERE channel_name = ''),
  @@GLOBAL.gtid_executed
) AS unapplied_gtids;

-- Applier queue depth and per-worker apply timestamps (MySQL 8.0)
SELECT
  worker_id,
  LAST_APPLIED_TRANSACTION,
  TIMESTAMPDIFF(SECOND,
    LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,
    LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP) AS apply_delay_s
FROM performance_schema.replication_applier_status_by_worker;

An empty unapplied_gtids string means genuinely caught up. A growing GTID range with Seconds_Behind_Source = 0 means the I/O thread is behind — the exact case the legacy metric hides. The most robust cross-version approach remains a heartbeat table: a scheduled event on the source writes (shard_id, now()) every second, and the replica-side lag is simply now() - max(heartbeat_ts) — immune to idle workloads, thread-state edge cases, and clock semantics, at the cost of one tiny write per second per shard.

Whichever engine you run, every measurement must carry the shard identity as a label (shard="s14"), not embedded in the metric name. Labels let one alert expression cover the whole fleet and let routing split by shard, as shown in the Prometheus cross-shard lag alerting walkthrough.

Why Lag Hurts More in a Sharded Fleet

Stale cross-shard reads are inconsistent, not just old. On a single database, a replica 30 seconds behind gives every query a uniformly stale-but-consistent view. In a fleet, a cross-shard aggregation reads shard 3 as of now and shard 14 as of 40 seconds ago: totals that never existed at any point in time, foreign references that dangle across shards, funds that appear in neither the source nor destination account of a just-moved balance. The probability that some shard is lagged scales with shard count — at 24 shards each independently exceeding your staleness budget 0.5% of the time, roughly 11% of cross-shard reads touch at least one stale shard. Lag monitoring is therefore a fleet-level correctness control, not a per-instance performance nicety.

Failover data loss multiplies by shard count. With asynchronous replication, the flush-lag window at the moment a primary dies is the write set you lose on promotion. Each shard is an independent trial: more shards, more primaries, more failure events per year, and each event’s loss is bounded by that shard’s lag at that moment. A fleet-wide p99 lag of 5 seconds means your realistic RPO is 5 seconds, whatever the design document says. This is why lag metrics must feed the promotion gate in your automated failover orchestration — an orchestrator that cannot see per-replica replay positions will eventually promote the wrong replica, and tooling like Patroni makes the gate explicit via maximum_lag_on_failover, covered in the Patroni shard failover guide.

Capacity Signals: The Alerts That Fire Weeks Early

Lag tells you a shard is struggling now. Capacity signals tell you which shard will struggle next month. Three are worth first-class treatment.

Disk growth per partition

Track size and growth rate per partition and per shard, not just per volume. A shard whose largest partition grows 3× faster than the fleet median is tomorrow’s hot shard — and if it is also the shard with the least free disk, it is tomorrow’s incident:

-- Per-partition size snapshot; ship to your metrics store with shard + partition labels
SELECT
  c.relname                                   AS partition_name,
  pg_total_relation_size(c.oid)               AS total_bytes,
  pg_relation_size(c.oid)                     AS heap_bytes,
  s.n_dead_tup,
  s.last_autovacuum
FROM pg_class c
JOIN pg_inherits i        ON i.inhrelid = c.oid
LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE i.inhparent = 'orders'::regclass
ORDER BY pg_total_relation_size(c.oid) DESC;

Alert on the derivative, not the level: “shard 14 disk will be full in 14 days at the current 7-day growth rate” is actionable; “disk is 80% full” arrives either far too early or far too late depending on the shard’s growth curve. Sustained divergence in per-partition growth is also the earliest symptom of key-distribution problems — hand those off to partition skew detection & monitoring before they become lag problems.

WAL generation rate

WAL bytes per second on the primary is the load your replicas must absorb; when it exceeds a replica’s sustainable replay rate, lag grows without bound until the workload subsides. Sample pg_current_wal_lsn() and difference it over time:

-- Run at interval t0 and t1; WAL rate = diff / (t1 - t0)
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') AS wal_bytes_written;

Benchmark each replica class’s maximum replay rate once (replay a captured WAL burst on an idle replica and time it), then alert when the primary’s sustained WAL rate exceeds ~70% of it. This single ratio predicts replay-lag incidents better than any lag threshold, because it fires while lag is still zero. It also bounds catch-up time: a replica 2 GB behind that replays 50 MB/s faster than the primary generates needs about 40 seconds — if the margin is only 5 MB/s, it needs seven minutes, and your failover risk window is seven minutes wide.

Connection pool saturation

Each shard’s primary has a fixed connection budget, and per-shard poolers (PgBouncer, ProxySQL) saturate independently. A shard at 95% pool utilization exhibits queueing latency that looks exactly like slow queries and often triggers retry storms that finish the job. Export per-shard active, waiting, and max connection counts and alert on waiting > 0 sustained for minutes, and on active/max > 0.85. Pool saturation on one shard is also a classic secondary symptom of replay lag on that shard’s replicas: reads that were load-balanced to replicas get rerouted to the primary when staleness checks fail, and the primary’s pool fills. Correlating the two on one dashboard row (see below) turns a confusing multi-alert incident into an obvious causal chain.

Designing Multi-Window, Burn-Rate Style Alerts

Static thresholds (“page when lag > 30 s”) fail in fleets: set tight, every bulk load pages; set loose, slow degradation reaches the failover-risk zone unnoticed. Borrow the multi-window burn-rate pattern from SLO alerting: define a staleness budget, then alert when the budget is being consumed fast (short window, page) or steadily (long window, ticket).

Treat “replica within 30 s of primary” as the objective. Then:

• Fast burn (page): lag has exceeded the threshold for most of a short window — e.g. replay lag > 30 s true for ≥ 80% of the last 5 minutes, confirmed over the last 30 minutes at a lower ratio. Catches genuine replay stalls within minutes.
• Slow burn (ticket): lag exceeded the threshold for ≥ 50% of the last 6 hours. Catches the shard drifting toward its capacity ceiling — the WAL-rate-vs-replay-rate margin eroding — without paging anyone at night.
• Hard ceiling (page, immediately): lag exceeds the failover RPO gate (e.g. 5 minutes) at all, for even one evaluation. Above this line your promotion safety is gone; there is no acceptable duration.

Expressed as Prometheus rules over a per-shard recording rule (full walkthrough in the Prometheus lag alerting guide):

# Fast burn: sustained 30s+ lag, short + confirming window
(
  avg_over_time((shard:replication_lag_seconds:max > bool 30)[5m:30s]) > 0.8
and
  avg_over_time((shard:replication_lag_seconds:max > bool 30)[30m:30s]) > 0.3
)

# Hard ceiling: promotion gate breached
shard:replication_lag_seconds:max > 300

Two refinements earn their complexity in production. First, alert on the worst replica per shard (max by (shard)), because failover safety is bounded by the replica the orchestrator would promote; track the best replica separately for read-routing decisions. Second, add a fleet-dispersion rule: if the p95 of per-shard lag is fine but one shard’s lag is 10× the fleet median, that shard has a local problem worth a ticket even below absolute thresholds.

Routing Alerts Per Shard Team and Laying Out the Dashboard

In fleets past a dozen shards, ownership is usually split — by product domain, region, or tenant tier. Alert routing should follow ownership, which is another reason shard identity must be a label. Maintain a small mapping (shard → team) in your Alertmanager configuration or as an info-level metric joined at alert time, and route on it:

route:
  receiver: dba-fallback
  group_by: ["alertname", "shard"]
  routes:
    - matchers: ['shard =~ "s(0[0-9]|1[0-1])"']   # shards 00-11: payments
      receiver: payments-oncall
    - matchers: ['shard =~ "s(1[2-9]|2[0-3])"']   # shards 12-23: marketplace
      receiver: marketplace-oncall
    - matchers: ['severity = "ticket"']
      receiver: dba-ticket-queue

Grouping by shard is deliberate: when one shard degrades, its lag, WAL-rate, and pool-saturation alerts arrive as one grouped notification instead of three interleaved pages, and when a network event degrades ten shards at once, each team receives only its own.

For the dashboard, resist the per-instance wall of graphs. The layout that works at fleet scale is:

1. Fleet heatmap (top): shards on the y-axis, time on the x-axis, cell color = max replay lag. One glance shows whether a problem is one shard (horizontal stripe) or systemic (vertical band).
2. Worst-N table: top 5 shards by current lag, with byte lag, time lag, WAL rate, and disk-days-remaining side by side, linking to the per-shard row.
3. Per-shard drill-down row: for the selected shard — lag (all four positions), WAL generation vs. replay rate, pool utilization, and disk growth on one row so the causal chain reads left to right.
4. Failover-readiness panel: per shard, the best promotable replica’s lag against the RPO gate — the number the orchestrator will consult, made visible to humans.

Configuration Reference

Operational Contrast

The three guides in this section watch different failure axes, and conflating them produces alerts that fire for the wrong reasons. Partition skew detection & monitoring is about spatial imbalance — data or load unevenly distributed across shards — measured in row counts, sizes, and per-shard QPS, on a horizon of weeks; its remedy is rebalancing or key redesign. Lag and capacity alerting, this page, is about temporal integrity — each shard’s replicas keeping up and each shard staying within its physical budget — on a horizon of seconds to days; its remedies are replay tuning, load shedding, and hardware. Automated failover orchestration is the actuator both feed: it consumes lag as a promotion gate and capacity as a placement constraint, and acts in seconds.

The interplay is directional: unaddressed skew becomes a lag problem (the hot shard generates WAL faster than replicas can replay), and unmonitored lag becomes a failover problem (the orchestrator promotes a stale replica or refuses to promote at all). If you instrument only one thing first, instrument lag — it is both a user-facing correctness signal on its own and the safety input for everything downstream.

Failure Modes

Common Mistakes

• Averaging lag across replicas or shards. The mean of one healthy replica (0 s) and one stalled replica (600 s) is a meaningless 300; averages across shards are worse. Always aggregate with max by (shard) for safety signals, and keep per-replica series for diagnosis.
• Alerting only in seconds, never in bytes. Time-based lag collapses to zero the moment replay catches the last commit, even when the replica just chewed through a 4 GB backlog and will fall behind again on the next burst. Byte lag against the shard’s WAL rate exposes the actual margin.
• One fleet-wide threshold for heterogeneous shards. A 100 MB byte-lag threshold is an outage on a shard writing 1 MB/s and noise on a shard writing 50 MB/s. Normalize thresholds to each shard’s WAL generation rate, or express them in seconds-of-catch-up.
• Treating missing metrics as healthy. The most dangerous lag value is the one that never arrives — exporter down, replica detached, scrape blocked by the same network fault that broke replication. Every per-shard lag series needs a companion absence alert with the same severity as the hard ceiling.

FAQ

Related

• Partition Monitoring & Failover Automation — parent overview of the monitoring and failover signal chain for sharded fleets
• Alerting on Cross-Shard Replication Lag with Prometheus — concrete walkthrough: postgres_exporter custom queries, recording rules, two-tier alerts, and shard-based routing
• Automated Failover Orchestration — how lag signals become promotion gates and how orchestrators act on them
• Partition Skew Detection & Monitoring — the spatial counterpart: finding the imbalance that eventually becomes a lag problem