Replication from a primary to a replica happens in several stages. Which stages can lag independently, and what do PostgreSQL's pg_stat_replication view and MySQL's Seconds_Behind_Source field each tell you about them?

## Why staging matters Saying "the replica is behind" is not actionable. The fix for a saturated network link is nothing like the fix for a single-threaded applier that cannot keep up. Every mature lag investigation starts by locating **which hop in the pipeline** the backlog is sitting in. The pipeline, engine-agnostically: 1. **Generate** — the primary appends the change to its write-ahead/binary log as part of commit. 2. **Send** — a sender process streams those bytes to the replica over the network. 3. **Receive/write** — the replica writes the arriving bytes into its own local log (PostgreSQL: the WAL receiver writes WAL; MySQL: the I/O thread writes the relay log). 4. **Flush** — the replica fsyncs that log so the received data would survive a crash. This is the position that matters for durability and for how much data a failover can recover. 5. **Apply/replay** — a separate process reads the local log and mutates the actual data pages/rows. Only after this can a query on the replica see the change. Because 3–5 are decoupled, a replica can be perfectly current on receive and badly behind on apply. This is extremely common and is the single most useful distinction in the whole topic. ## Reading it in PostgreSQL On the **primary**, `pg_stat_replication` has one row per connected standby with four position columns that map one-to-one onto the pipeline: - `sent_lsn` — how far the primary has sent. - `write_lsn` — how far the standby has written into its WAL. - `flush_lsn` — how far the standby has fsynced. - `replay_lsn` — how far the standby has replayed into its data. Subtract each from the primary's current WAL position to get byte lag for that stage. PostgreSQL additionally publishes `write_lag`, `flush_lag`, `replay_lag` as time intervals — the measured round-trip time for a change to reach each stage on that standby. These are only populated while changes are actually flowing. The invariant is `sent_lsn >= write_lsn >= flush_lsn >= replay_lsn`. Where the big gap opens tells you the culprit: - gap between the primary's current position and `sent_lsn` → sender or network backpressure; - gap between `sent_lsn` and `flush_lsn` → replica disk write/fsync throughput; - gap between `flush_lsn` and `replay_lsn` → **recovery/apply is the bottleneck** (the usual case). On the **standby** itself, `pg_last_xact_replay_timestamp()` gives the commit time of the most recently replayed transaction; `now() - pg_last_xact_replay_timestamp()` is the classic time-lag expression — with the standard caveat that it grows on an idle primary simply because no new transaction has arrived. ## Reading it in MySQL MySQL splits the replica into threads: the **I/O (receiver) thread** pulls binlog events into the relay log, and the **SQL/applier thread(s)** execute them. Replica status therefore exposes two positions on the source's coordinates: - `Read_Source_Log_File` / `Read_Source_Log_Pos` — received. - `Relay_Source_Log_File` / `Exec_Source_Log_Pos` — applied. The difference between those two is **relay backlog**, i.e. apply lag in bytes. The difference between the source's current binlog position and `Read_Source_Log_Pos` is **receive lag**. `Seconds_Behind_Source` is computed from the timestamp stamped on the event the applier is currently executing versus the replica's clock. Three consequences follow, and interviewers probe all of them: - It is an **apply-side** metric only. If the I/O thread is stalled or disconnected, the applier drains its relay log and the field can fall to 0 while the replica drifts arbitrarily far behind reality. - It reads **NULL** whenever replication threads are not both running — that is a state, not a number, and dashboards that coerce NULL to 0 hide outages. - In a **chained** topology (A → B → C), events carry the original timestamp from A, so C's value reflects lag behind A, not behind B. Useful, but only if you know that is what you are reading. The performance-schema replication tables (`replication_connection_status`, `replication_applier_status_by_worker`) expose the same split with per-worker detail, which is what you use once multi-threaded apply is enabled. ## The heartbeat technique Because both engines' time metrics degrade when writes are sparse, production setups add a **heartbeat**: a one-row table on the primary updated with the current timestamp on a fixed interval (typically 1s) by an external agent. On the replica, `now() - heartbeat_ts` is a truthful, always-flowing end-to-end lag measurement that also proves the whole pipeline is alive. It measures exactly what a user experiences — write-to-visible latency — rather than an internal position. ## Putting it together A useful lag dashboard carries, per replica: byte lag at receive, byte lag at apply, heartbeat-derived seconds, and replication-thread/connection state. That set makes each failure mode distinguishable at a glance instead of leaving you with one ambiguous number.