Time-based replication-lag metrics can report zero or null while a replica is genuinely far behind. Why does that happen, and how would you measure lag so the number is trustworthy?

## What the metric actually computes Almost every built-in "seconds behind" figure is the same expression: ``` lag_seconds = replica_clock_now - timestamp_of_newest_change_the_replica_has_applied ``` That is a perfectly good definition **when changes are flowing**. Its failure modes all come from the fact that it is defined in terms of *work the replica has seen*, not in terms of *work the primary has produced*. If the replica has seen nothing recently, the metric has nothing to say and — critically — says "zero" rather than "unknown". ## Blind spot 1: the idle or low-write primary Suppose the primary takes one write per hour. Immediately after that write is replayed, the applied timestamp is fresh and lag reads ~0. An hour later, on PostgreSQL, `now() - pg_last_xact_replay_timestamp()` reads ~3600 seconds — alarming, but entirely false: the replica is perfectly current, there simply has not been a transaction. MySQL's `Seconds_Behind_Source` handles the same situation the other way, reporting 0 because the applier has nothing queued. So on a low-write system, the same underlying situation produces a scary number on one engine and a reassuring one on the other, and **neither is measuring health**. Byte distance is the honest metric here: zero outstanding bytes means genuinely caught up. ## Blind spot 2: receiver dead, applier caught up This is the dangerous one because the number is reassuring while the system is broken. MySQL splits receive and apply into different threads. `Seconds_Behind_Source` is derived from the event the **applier** is executing. If the I/O thread dies — network partition, credentials expired, source binlog purged — the applier keeps working through whatever is already in the relay log, finishes it, and then reports 0. The replica is now frozen in time, serving increasingly stale reads, with a green lag graph. The general lesson: **a metric computed entirely on the downstream node cannot detect that the upstream stopped talking to it.** Detection requires either the primary's view of the connection (does the primary still see this replica connected, and at what position?) or a signal that must traverse the whole pipeline (a heartbeat). ## Blind spot 3: NULL treated as zero When replication threads are stopped or errored, MySQL reports `Seconds_Behind_Source` as NULL. Many collection scripts cast that to 0 (or drop the sample), so a completely stopped replica looks flawless. The correct handling is to alert on the **state** — threads not running, last error non-empty, replica not present in the primary's connected-replica list — as a separate, higher-severity condition than a numeric threshold. ## Blind spot 4: clocks and chains - **Clock skew.** The expression subtracts a timestamp produced on one host from the clock of another. A replica whose clock runs ahead reports inflated lag; behind, deflated (and in some builds, negative values appear). Without NTP discipline the metric drifts on its own. - **Chained replication.** In A → B → C, change records generally keep the timestamp assigned at A. C's time-behind therefore measures lag against A — the end-to-end number you usually want, but not what "behind my upstream" implies. Interpreting it as lag behind B misattributes the problem. - **Sampling.** A one-minute scrape interval cannot see a 20-second stall; short conflicts and lock waits vanish between samples. ## Building a trustworthy measurement Use three complementary signals — no single one is sufficient: **1. Byte distance from the primary's perspective.** On the primary, compare its current log position with each connected replica's reported position. This is immune to idleness (zero writes means zero outstanding bytes) and, because it enumerates connections, it exposes a replica that has vanished entirely — the row simply disappears. It also feeds retention safety: how close a replica is to falling off the retained log and needing a rebuild. **2. Explicit state alerts.** Replication threads/workers running; last error empty; replica present in the primary's list of connected replicas; replication slot (where used) active. These are boolean conditions, alerted independently of any numeric threshold, and they must be alerted on missing data too — a metric that stops arriving is itself a symptom. **3. A heartbeat.** An agent on the primary updates a single-row table with the current timestamp every second. The replica computes `now() - heartbeat_ts`. This: - guarantees writes always flow, so the time metric is always meaningful; - traverses the entire pipeline (network, receive, flush, apply), so a stalled receiver shows up immediately as a rising number rather than a comforting zero; - measures exactly what an application experiences — the delay between a committed write and its visibility on the replica — rather than an internal position. Its one requirement is clock discipline between the hosts, since it is still a cross-host subtraction; keep NTP running and monitor skew as its own metric. ## The rule of thumb Treat any downstream-only, time-based lag figure as *best-effort*, never as proof of health. Prove health with the primary's view plus a signal that must cross the wire; use the time figure for staleness budgets once you know the pipeline is alive.