Replication Lag

A 09:42 incident. A customer opens an order in the admin tool, refreshes the order list, and sees nothing. The page is then reloaded a few seconds later and the order is there. The admin tool reads from a replica; the customer’s write is on the primary. The replica was four seconds behind. The customer service agents call support. Support escalates. The on-call engineer pulls out pg_stat_replication and finds replay_lag rising over five seconds across the past hour.

This is the most common production shape that replication metrics are designed to catch. A replica is part of the database’s read scale plan, the read-after-write promise, the fail-over plan, and the disaster-recovery plan. When a replica lags, every one of those plans degrades in a different way. The right metric depends on which plan is under stress.

What replication lag is

Replication lag is the difference between the moment a transaction commits on the primary and the moment it is visible on the replica. The difference is measured in three units; each one tells a different story.

                 write accepted      write reordered      write applied
                   on primary       into WAL stream       on replica
                       |                   |                    |
                       v                   v                    v
              primary commit        streamer/WAL          replay/lag
                                       shipping
                       |                                        |
                       +----------------------------------------+
                                            |
                                       read-after-write
                                          contract

The three units:

1. Bytes of WAL pending. The difference between the primary’s LSN (pg_current_wal_lsn()) and the replica’s received_lsn. Measures how much the primary has written that the replica has not yet acknowledged receipt. Independent of replay speed.
2. Seconds behind primary. replay_lag_seconds. Measures how far the replica’s replay position is behind the primary’s commit position in wall-clock terms. Computed from the timestamp the primary attached to the WAL record when it was generated. Sensitive to the difference between shipping speed and replay speed.
3. Transactions behind. replay_lag (transactions). On the modern PostgreSQL implementation, this is the integer count of records the replica has not replayed. Often the most useful single number for an application that needs to know “how far behind is my read-after-write window”.

MySQL exposes the equivalent through SHOW REPLICA STATUS: Seconds_Behind_Master (seconds), Relay_Log_Space (bytes), and Replicas_Behind_Master (transactions). The semantics are similar but the units are exposed differently.

  Primary                              Replica
  ---------                            -------
  user commits T1                      receives WAL at position X
    |                                   |
    v                                   v
  LSN advanced                          received_lsn = X
    |                                   |
    v                                   v
  replication sends WAL                 applied_lsn = X - 0
    |                                   |
    v                                   v
  shipped_lsn = X                       replay_lag_seconds = 0
    |                                   |
    v                                   v
  ... time passes, replica is slow     replay_lag_seconds = 5.4
  ... new commits happen                shipped-lsn = X + N
    |                                   received_lsn = X + N
    v                                   applied_lsn = X + N - 1
  primary LSN = X + N + M               replay_lag_seconds = M / primary_rate

The picture has two competing states: shipping (primary writes WAL, replica receives) and replay (replica applies WAL, transactions become visible). When shipping is slower than replay, the WAL accumulates; when replay is slower than shipping, the WAL arrives faster than the replica can apply. Most lag incidents are replay-bound, not network-bound.

Why a sysadmin cares

Four distinct operational guarantees depend on the replica. Each one fails on a different lag threshold.

1. Read-after-write. A user submits an order, the primary commits, the user reads the same order from the replica. The contract holds only if the replica’s lag is below the application’s replica_read_after_write_timeout, typically a few seconds.
2. Read scale. A replica serves reads behind a load balancer. Lag is fine until the user notices the gap; from the application’s view, lag is a stale-data problem.
3. Fail-over. When the primary fails, the replica takes over. The shorter the lag, the smaller the loss of committed writes. Lag of 0 is the fail-over target; lag of minutes is a disaster.
4. Disaster recovery. A regional replica is the backup for a region-level outage. The replica’s relationship to the primary is identical; the lag threshold is the RPO.

Each of these has a different acceptance threshold and a different alerting metric. The same replica can be on the edge of breach for one and healthy for another.

How it works

PostgreSQL streaming replication uses the WAL stream as the data path. The replica connects to the primary over TCP, starts streaming from a starting LSN, and applies the WAL records as they arrive.

  Primary                 WAL stream                Replica
  --------                -----------                ------
  backend                 walsender                  walreceiver
  transaction             TCP                        apply worker
    |                       |                            |
    |  (record)             |                            |
    +--------> WAL queue -->+--------> TCP recv --------> replay buffer
                                                          |
                                                          v
                                                    database buffer
                                                          |
                                                          v
                                                    visibility map /
                                                    row visibility

The two key state machines are independent:

• WAL streaming. Each new WAL record is shipped over TCP as soon as it’s generated. The replica acknowledges received records; the primary knows at what LSN the replica is received.
• WAL replay. Records in the WAL receiver are applied to the database. Some are no-ops (HOT prune); some depend on other records (constraints, foreign keys); some require holding a lock. The apply worker processes them serially.

The two often run at the same rate on a healthy replica. A long-running query (a 30-minute report against a 10 GB table) on the replica can hold a row’s visibility for the duration of the report; the apply worker cannot release the lock; WAL applies stall; replay_lag_seconds climbs while shipping continues.

How to configure it

The exporter is configured for replication through two flags on PostgreSQL.

# /etc/default/prometheus-postgres-exporter
ARGS="--collector.replication \
      --collector.stat_replication \
      --collector.stat_wal_receiver"

The exporter emits one row per replica host, labelled by upstream_node, application_name, client_addr and sync_state (async, potential, sync, quorum). For MySQL, the equivalent is the binary log + replica state.

The Prometheus alert set:

# /etc/prometheus/rules/replication.yaml (excerpt)
groups:
  - name: replication
    rules:
      # Seconds behind primary. The most operationally useful alert.
      - alert: ReplicaLagging
        expr: |
          pg_stat_replication_replay_lag_seconds > 5
        for: 5m
        labels:
          severity: page
        annotations:
          summary: 'Replica {{ $labels.application_name }} lagging
                  by {{ $value }}s on {{ $labels.instance }}'

      # The replica is too far behind. A fail-over would lose data.
      - alert: ReplicaFarBehind
        expr: |
          pg_stat_replication_replay_lag_bytes
            > 100 * 1024 * 1024
        for: 5m
        labels:
          severity: page

      # Synchronous replica has fallen behind. Critical for
      # committed-write-loss-bounded applications.
      - alert: SyncReplicaDown
        expr: |
          count(pg_stat_replication_sync_state{state="sync"} == 1) == 0
        for: 30s
        labels:
          severity: page

      # WAL is accumulating on the primary. The replica
      # cannot catch up; a fail-over is impossible.
      - alert: WALAccumulating
        expr: |
          pg_stat_replication_pending_lsn_bytes
            > 500 * 1024 * 1024
        for: 10m
        labels:
          severity: ticket

      # The replay worker is stuck (long-running query on replica).
      - alert: ReplicaReplayStalled
        expr: |
          delta(pg_stat_replication_replay_lsn[5m]) == 0
        labels:
          severity: page

The five alerts cover: normal lag, far-behind lag, the synchronous replica is missing, WAL is accumulating on the primary, the apply worker has stopped.

How to validate it

Every step is READ-ONLY.

# 1. Read the replica state from the primary.
psql -h 10.0.4.10 -U db_exporter -d app <<'SQL'
SELECT application_name,
       client_addr,
       sync_state,
       state,
       write_lsn,
       replay_lsn,
       sync_priority,
       replay_lag
FROM pg_stat_replication;
SQL

 application_name | client_addr  | sync_state | state     | write_lsn  | replay_lsn  | sync_priority |  replay_lag
------------------+--------------+------------+-----------+------------+-------------+---------------+--------------
 replica-r1       | 10.0.4.20    | async      | streaming | 0/5AB82C0  | 0/5AB8220   |             0 | 00:00:01.243
 replica-r2       | 10.0.4.21    | potential  | streaming | 0/5AB82C0  | 0/5AB8160   |             1 | 00:00:02.871

# 2. Read the lag from the replica side.
psql -h 10.0.4.20 -U db_exporter -d app \
  -c "SELECT now() - pg_last_xact_replay_timestamp() AS replica_lag_seconds;"

 replica_lag_seconds
--------------------
      00:00:00.945

# 3. Read the MySQL replica state (if applicable).
mysql -h 10.0.4.20 -u db_exporter -e \
  "SHOW REPLICA STATUS\\G" | grep -E "Seconds_Behind|Relay_Log_Space|Replicas_Behind"

   Seconds_Behind_Master: 1
   Relay_Log_Space: 1048576

# 4. Confirm the exporter is publishing replication metrics.
curl -sf http://10.0.4.7:9187/metrics | grep -E '^pg_stat_replication_' | head -8

# 5. Confirm the alert rules parse.
promtool check rules /etc/prometheus/rules/replication.yaml

The outputs confirm: the primary sees the replica, the replica is streaming, the write LSN is advancing, the replay LSN is catching up, the lag in seconds is small, the alert rules parse.

How it can fail

Five failure shapes cover the overwhelming majority of production replication incidents.

1. Replay lag from a long-running query on the replica. A 30-minute report against a 10 GB table holds a row lock; replay queues behind it; the gap grows linearly with the report’s remaining time. Symptom: replay_lag rises steadily while sent_lsn advances; the replica’s pg_stat_activity shows the long-running query still running. Fix: terminate the query; promote the report to its own dedicated replica.
2. Network partition. The primary cannot reach the replica. Symptom: state = 'startup' or state = 'connection lost'; write_lsn does not advance; sent_lsn continues. Fix: restore the network; if the replica’s WAL is too far behind to recover, rebuild from a base backup.
3. Disk pressure on the replica. The replica’s WAL files accumulate; the apply worker slows because of dirty-page writeback. Symptom: flush_lsn rises; replay_lsn stays; the replica’s I/O wait climbs. Fix: identify the I/O contention (a backup, a vacuum, a slow disk).
4. Synchronous replica missing. The synchronous replica is offline; the primary refuses to commit; the application’s latency rises. Symptom: synchronous replication alerts; primary’s pg_stat_replication sync_state shows the replica as potential or absent. Fix: restore the synchronous replica, or temporarily fall back to asynchronous replication via pg_replication_slot management (and accept the data-loss risk for the duration).
5. WAL archive failure. WAL segments are not being archived because the archive_command is failing. Symptom: pg_stat_archiver shows failed_count rising; the primary retains WAL files indefinitely; disk fills. Fix: restore the archive_command; replay is unaffected, but PITR is.

How to troubleshoot it

Diagnose in this order. The cheapest evidence comes first.

1. Read the lag. Confirm replay_lag_seconds is the number you are seeing; the granularity of the units matters for the alert.
2. Read the per-replica state. streaming is healthy; startup, backup, catchup are transitional; connection lost is network.
3. Distinguish shipping from replay. Compare flush_lsn to replay_lsn; a large delta is the apply side, not the network. Compare sent_lsn to write_lsn; a large delta is the network.
4. Inspect the replica’s pg_stat_activity. A long-running query (over 60 s) is the most common cause of replay lag. Terminate it with pg_cancel_backend or the application-side equivalent.
5. Inspect pg_stat_replication on the primary. A replica in state='startup' is rebuilding; a replica in state='backup' is running a base backup; a replica not in the row at all is not connected.
6. Look at the network. pg_stat_replication does not report round-trip time, but the sent_lsn to write_lsn delta is a proxy: a delta of hundreds of MB is a network problem, not a replay problem.

Security implications

Replication exposes two surfaces:

• WAL contents. WAL segments contain the full write activity of the primary. A replica with read access to WAL files (the default for streaming replication) sees every committed transaction. Bind WAL storage to an internal subnet; do not store WAL in a publicly accessible object store.
• Replication user. Streaming replication uses a dedicated role (REPLICATION privilege in PostgreSQL). The role has no other privileges by design. Treat it as confidential and rotate credentials at least annually.
• Replica’s read scale. A replica that serves reads exposes the same data as the primary; row-level security applies at the primary’s grants, not the replica’s. Ensure the replica’s database user runs the application query with the same row-level policy.

Performance implications

Replication trades latency for read scale and durability. The costs fall in three places.

• Primary WAL retention. wal_keep_size/wal_keep_segments bounds the WAL retained for a slow replica. The default (16 MB on PostgreSQL) is rarely enough for production; one to ten GB is common.
• Replica replay bandwidth. The apply worker is a single backend; it can be saturated by a fast primary under heavy write load. Replication slots decouple the primary’s WAL retention from the replica’s pace.
• Replica read scale. Reads on the replica can interfere with replay (long-running queries hold locks the apply worker needs). Hot-RW replica pools and reporting read-replicas are the typical responses.

Production guidance

• Set replay_lag_seconds alerting threshold based on the application’s read_after_write_timeout, not a rule of thumb.
• Use replication slots to bound primary WAL retention automatically; alarm on pg_replication_slots activity.
• Run long-running reports on a dedicated replica, not on the read-scaled one.
• Quorum-based synchronous replication (synchronous_standby_names = 'ANY 2 (replica-r1, replica-r2)') when committed-write loss is unacceptable; alarm on SyncReplicaDown (the alert in the previous section).
• Document the fail-over checklist: pre-flight checks include replay_lag_seconds < RPO for every candidate replica.

Verification

You should now be able to answer:

• What is the difference between WAL bytes pending and replay lag in seconds, and why is the latter the right alerting signal for read-after-write?
• What two pg_stat_replication columns distinguish a network-side stall from a replay-side stall?
• What is the correct first action when a long-running query on the replica is causing replay lag?
• What does synchronous_standby_names = 'ANY 2 (...)' do, and why does it need the SyncReplicaDown alert?
• What is the cost of failing over to a replica with a non-zero replay lag?

Quiz