Replication lag

Symptom

Replicas are behind the primary. Reads served from them return stale data, and the durability window in a failover has widened.

Impact

Stale reads, read-your-writes violations, and increased data loss if the primary fails. If lag keeps growing, the replica will eventually fall outside the log retention window and need a full rebuild.

Triage: which component is lagging?

The three components have different causes, and treating them the same wastes time.

-- PostgreSQL, on the primary.
SELECT application_name, state, sync_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, replay_lsn)           AS replay_lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

-- MySQL: read the thread states, not just the seconds.
SHOW REPLICA STATUS\G
SELECT * FROM performance_schema.replication_applier_status_by_worker\G

Diagnosis by component

Replay lag is the most common. Usual causes:

• A bulk write on the primary — a large UPDATE, a backfill, an index build — producing more log than a single applier can replay.
• A long-running query on the replica conflicting with apply.
• Single-threaded apply where parallel apply is available but not configured.

-- PostgreSQL: is a query on the standby blocking replay?
SELECT pid, now() - query_start AS duration, left(query, 80)
FROM pg_stat_activity WHERE state = 'active' ORDER BY query_start LIMIT 10;

Write lag points at replica storage. Compare its I/O capability with the primary's — a smaller replica cannot keep up with a larger primary's write rate.

Send lag points at the network, or at a primary generating log faster than the link carries.

Mitigation

Stop generating the backlog. Pause the batch job, backfill or index build responsible. This is almost always the fastest fix.

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND now() - query_start > interval '5 min';

Route reads away from the lagging replica so users see correct data while it catches up.

Enable parallel apply where available:

# MySQL
replica_parallel_workers = 8
replica_parallel_type = LOGICAL_CLOCK
binlog_transaction_dependency_tracking = WRITESET

Verification

• All three lag components are near zero and stable.
• The replica's thread or apply state is healthy.
• Reads routed back to the replica return current data.

Follow-up

• Alert on each lag component separately, with thresholds from the staleness budget.
• Configure parallel apply if it was not enabled.
• Size replicas as candidate primaries, not as weaker read servers.
• Batch large writes on the primary and pause between batches when lag rises.
• Decide deliberately between hot_standby_feedback (stable replica queries, bloat on the primary) and max_standby_streaming_delay (low lag, cancelled queries).