How to Diagnose PostgreSQL Replication Lag Before It Affects Users

PostgreSQL replication helps applications maintain copies of database data on separate servers. A primary server processes database changes, while one or more standby servers receive and replay those changes. Applications can use standby servers for read-only queries, reporting, and disaster recovery.

However, replication is not always instantaneous. Under certain conditions, a standby server may fall behind the primary. This delay is known as replication lag.

Replication lag can cause more than a monitoring warning. An application that reads from a standby may show outdated information immediately after a user updates a record. A reporting system may miss recent transactions, and a standby intended for failover may need additional time to catch up before it contains the latest committed changes.

The challenge is that replication lag has several possible causes. The primary may be generating changes faster than the standby can receive them, the network may be slow, or the standby may be struggling to replay changes. A standby can also appear behind because of a long-running query that delays recovery.

To diagnose the problem correctly, developers and database administrators need to identify where the delay occurs rather than treating every lag measurement as the same issue.

This article explains how PostgreSQL replication works, how to measure replication lag using SQL, how to distinguish transmission delays from replay delays, and how to investigate common causes before stale reads or failover problems affect users.

Understand How PostgreSQL Replication Works

PostgreSQL commonly uses streaming replication to maintain a standby server. In this arrangement, the primary records database changes in its Write-Ahead Log (WAL). The standby receives the WAL records and replays them to bring its database state closer to the primary.

WAL is a record of changes that PostgreSQL uses for crash recovery and replication. Instead of immediately copying every changed database page to the standby, PostgreSQL streams the log records needed to reproduce those changes.

The replication process involves several stages:

1. The primary generates WAL records as transactions modify database data.

2. The primary makes the WAL available to replication clients.

3. The standby receives the WAL records over the replication connection.

4. The standby writes the received records to its own storage.

5. PostgreSQL replays the records to update the standby's database state.

A delay can develop at any of these stages.

For example, if the primary generates WAL faster than the standby can receive it, the standby may have a growing transmission backlog. If the standby receives the records quickly but cannot replay them fast enough, the received WAL can accumulate while the standby's database state remains behind.

These situations require different solutions. Increasing network capacity will not necessarily solve a replay bottleneck caused by slow storage or recovery conflicts.

Understand the difference between a primary and a standby

The primary is the server that accepts the relevant write operations. A physical standby maintains a copy of the primary's database by receiving and replaying WAL records.

Applications often direct writes to the primary and send some read queries to a standby. This can reduce read load on the primary, but it introduces a consistency consideration: a query against the standby may not see a recent transaction that has already committed on the primary.

Replication lag is therefore both a database performance concern and an application-design concern.

Measure Replication Lag on the Primary

The first step is to determine whether the primary is generating WAL faster than its standby servers can process it.

PostgreSQL provides the pg_stat_replication view on the primary. It exposes information about connected WAL senders, including the standby's write, flush, and replay positions.

Run the following query on the primary:

SELECT
    application_name,
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

The output helps identify connected standbys and compare their reported WAL positions.

Each column represents a different stage of the replication process.

• sent_lsn indicates the WAL position sent by the primary.

• write_lsn indicates the WAL position written by the standby.

• flush_lsn indicates the WAL position flushed to durable storage on the standby.

• replay_lsn indicates the WAL position replayed by the standby.

• write_lag, flush_lag, and replay_lag provide time-based lag estimates associated with recent WAL activity.

The lag estimates are useful, but they should not be treated as a permanent measurement of the current backlog. They can become null when there is no recent relevant activity, and their meaning depends on the timing and state of replication.

Likewise, a difference between WAL positions does not directly tell you how many seconds behind the standby is. WAL generation rates vary, so the same byte difference can represent very different amounts of elapsed time under different workloads.

Interpret the results carefully

Suppose a standby's sent_lsn is significantly ahead of its replay_lsn. That indicates that the primary has sent WAL beyond the point the standby has replayed.

However, it does not immediately identify the root cause. The standby may be receiving the records slowly, writing them slowly, or replaying them slowly.

Compare the WAL positions across multiple samples rather than relying on a single query. If the gap consistently grows, the standby is falling further behind. If it remains stable or shrinks, the standby may be catching up.

Also check the state column. A standby that is disconnected or not in the expected streaming state needs a different investigation from one that is connected but processing WAL too slowly.

Measure Replication Progress on the Standby

The primary shows what has been sent and what the standby has reported. To understand the standby's local progress, connect directly to the standby and inspect its recovery status.

Run:

SELECT
    pg_is_in_recovery() AS is_standby,
    pg_last_wal_receive_lsn() AS last_received_lsn,
    pg_last_wal_replay_lsn() AS last_replayed_lsn,
    pg_last_xact_replay_timestamp() AS last_replayed_transaction_time;

The query returns several useful values.

The pg_is_in_recovery function indicates whether the server is operating in recovery mode, as expected for a physical standby.

The last received WAL location shows how far the standby has received WAL. The last replayed WAL location shows how far it has applied the changes.

If the received location advances but the replayed location does not, investigate whether replay is progressing slowly or has been delayed. If neither location advances, investigate connectivity, WAL delivery, replication configuration, and whether the primary is generating WAL.

The last replayed transaction timestamp can provide context, but it has an important limitation: it records the time associated with the last replayed transaction. It is not a reliable universal measure of replication lag, especially when the primary has been idle or has not generated recent transactions.

A large difference between the current time and that timestamp does not necessarily mean that the standby is behind by the same amount. There may simply have been no new transactions to replay.

Distinguish Transmission Lag From Replay Lag

Once you have collected the primary and standby measurements, determine which stage is responsible for the delay.

These observations are diagnostic clues, not definitive proof. For example, a gap between received and replayed WAL can be caused by normal processing under load, but it can also indicate that recovery is being delayed.

Collect repeated measurements and correlate them with PostgreSQL logs, system metrics, and application traffic.

Check whether the standby is receiving WAL

If the primary reports that WAL has been sent but the standby's receive position does not advance, start by checking the replication connection.

Look for connection interruptions, authentication failures, network errors, and receiver-process problems. Review the PostgreSQL logs on both servers around the time lag began.

Also verify that the standby is connected to the expected primary and that the replication configuration has not changed unexpectedly.

Do not immediately increase replication settings or restart the database. First determine whether the problem is connectivity, configuration, or resource capacity.

Check whether replay is falling behind

If the standby continues receiving WAL but replay stops or progresses slowly, investigate the standby's workload and resources.

A standby can experience high disk latency, CPU pressure, memory pressure, or recovery conflicts. Queries running on a hot standby can also conflict with WAL replay when replaying changes would invalidate data that those queries still need to read.

The correct solution depends on the actual bottleneck. Increasing network capacity will not solve a replay problem caused by slow storage or long-running reporting queries.

Investigate Long-Running Queries on the Standby

PostgreSQL hot standbys can serve read-only queries while replaying WAL. However, some replay operations conflict with queries that are still using older database snapshots.

For example, a reporting query may scan a large table while the primary performs a schema-related change or removes row versions that the standby query still needs. The standby may need to cancel the query or delay replay, depending on the recovery configuration.

Inspect active queries on the standby:

SELECT
    pid,
    usename,
    application_name,
    state,
    query_start,
    now() - query_start AS query_duration,
    wait_event_type,
    wait_event,
    LEFT(query, 200) AS query_sample
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

This query helps identify long-running activity, but it does not prove that a query is blocking WAL replay. Use it alongside recovery-conflict statistics and PostgreSQL logs.

For physical standbys, pg_stat_database_conflicts can help identify conflicts that have caused queries to be canceled during recovery.

For example:

SELECT
    datname,
    confl_tablespace,
    confl_lock,
    confl_snapshot,
    confl_bufferpin,
    confl_deadlock
FROM pg_stat_database_conflicts;

The counters are cumulative, so inspect them at intervals to determine whether they increase during the period when replication lag develops.

If long-running queries are involved, consider optimizing the queries, scheduling heavy reporting workloads, or reviewing the standby's recovery-conflict settings. Changing conflict settings can involve a trade-off between query completion and replay delay, so it should be based on the application's consistency and availability requirements.

Check WAL Generation and Retention on the Primary

Replication lag can also create pressure on the primary if WAL accumulates faster than it can be safely recycled.

PostgreSQL retains WAL needed by connected standbys and other consumers, subject to its retention and replication configuration. If a standby falls behind significantly, the primary may need to retain more WAL than usual.

Inspect the replication slots, if used:

SELECT
    slot_name,
    slot_type,
    active,
    restart_lsn,
    confirmed_flush_lsn,
    pg_wal_lsn_diff(
        pg_current_wal_lsn(),
        restart_lsn
    ) AS retained_wal_bytes
FROM pg_replication_slots;

This query uses pg_current_wal_lsn on the primary and reports the distance between the current WAL position and each slot's restart location. The result is a byte difference, not a direct measurement of elapsed time.

The confirmed_flush_lsn column is relevant to logical replication slots and may be null or not applicable for physical slots. Interpret the columns according to the slot type.

A large retained-WAL value may indicate that a slot is holding back WAL recycling. Investigate whether the slot is active, whether the corresponding consumer is healthy, and whether retention is expected.

Do not drop an inactive replication slot simply because it is old. A consumer may need that slot to resume safely. Removing it can prevent the consumer from continuing from its previous position and may require a new synchronization or base backup.

Also check disk capacity. Excessive WAL retention can eventually fill the storage volume, potentially affecting the primary's ability to accept writes.

Monitor Replication Lag Over Time

A single SQL query is useful during an investigation, but production systems need continuous monitoring.

Track several related signals rather than relying on one lag number:

• WAL positions on the primary and standby.

• Whether the replication connection remains active.

• Changes in received and replayed positions over time.

• Time-based lag estimates when they are meaningful.

• WAL retention and available disk space.

• Recovery conflicts and long-running standby queries.

• CPU, memory, and storage latency on both servers.

• Application latency and the age of data returned by read queries.

Establish thresholds that reflect the application's requirements. A reporting replica may tolerate a delay of several seconds or longer, while an application that immediately reads back recently updated data may require a different consistency strategy.

Alerting should also account for idle periods. If the primary has not generated new WAL, a stale replay timestamp does not automatically indicate a failure. Monitor whether the standby is connected and whether its progress advances when new changes are generated.

For managed PostgreSQL services, use the provider's monitoring facilities alongside PostgreSQL statistics. Managed services may expose additional metrics, but their names and definitions can differ by provider and service tier.

Test How Your Application Behaves During Replication Lag

Database monitoring identifies the delay, but application tests determine whether users are affected.

Consider an application that writes a new order to the primary and immediately reads the order from a standby. If the standby has not replayed the transaction, the second request may report that the order does not exist.

This can be confusing because the write succeeded. The read simply reached a server whose data had not caught up.

For operations that require read-your-writes consistency, consider routing the immediate follow-up read to the primary or using a deliberate consistency strategy. Do not assume that a successful write guarantees that every replica can immediately return the new value.

Create integration tests that simulate delayed replication where possible, and verify that the application handles temporarily stale data correctly.

For example, an application may need to display a pending status, retry a read for a limited period, or route a consistency-sensitive query to the primary. The correct choice depends on the user experience and the cost of an additional primary read.

Avoid infinite retries. If replication remains delayed, repeated requests can increase database load without solving the underlying problem.

Common Mistakes When Diagnosing Replication Lag

Treating every lag measurement as seconds

A difference between WAL positions is a byte distance, not a time measurement. Time-based lag estimates have their own limitations. Use repeated measurements and correlate them with transaction activity before estimating how far behind a standby is.

Assuming the network is always responsible

A standby may receive WAL quickly but replay it slowly. Check both receive and replay positions before investigating network capacity.

Ignoring standby queries

Long-running read queries can conflict with recovery operations. Review active queries, recovery-conflict counters, and logs before increasing hardware resources.

Dropping replication slots without understanding the consequences

An inactive slot may still be needed by a consumer. Removing it can make recovery impossible from the previous position and may require reinitializing the replica.

Restarting PostgreSQL before collecting evidence

A restart may temporarily change the symptoms without addressing the cause. Capture replication positions, logs, resource metrics, and relevant statistics before making disruptive changes.

Best Practices for Production PostgreSQL Replication

Monitor replication connections, WAL positions, replay progress, retained WAL, and disk capacity continuously. Use the primary and standby statistics together so that the monitoring system can distinguish transmission delays from replay delays.

Keep PostgreSQL updated within your organization's supported maintenance process, and review replication configuration when workloads or retention requirements change. Optimize long-running queries on standby servers and verify that recovery-conflict settings match the application's consistency requirements.

For applications that read from replicas, document which operations require read-your-writes consistency and which can tolerate stale data. Test those behaviors under simulated lag rather than assuming that the database will always be fully synchronized.

Finally, define recovery procedures for a standby that has fallen too far behind, lost required WAL, or disconnected for an extended period. A monitoring alert is most useful when the team also knows how to investigate and recover from the condition.

Advantages and Disadvantages of Streaming Replication

Advantages

• Read-only queries can be distributed across standby servers, reducing some of the load on the primary.

• A standby can provide an additional copy of database state for recovery and availability planning.

• Continuous WAL streaming can keep the standby close to the primary under normal operating conditions.

• Replication statistics provide useful information for diagnosing transmission and replay problems.

• Applications can choose different read-routing strategies based on consistency requirements.

Disadvantages

• Standby reads may return stale data while replication is delayed.

• Replication consumes network, storage, and processing resources.

• Long-running queries and recovery conflicts can complicate replay behavior.

• Retained WAL can consume significant disk space when consumers fall behind.

• Failover planning must account for replication state and the possibility that recent changes have not reached a standby.

Summary

PostgreSQL replication lag occurs when a standby cannot keep up with the WAL generated by the primary. The most effective way to diagnose it is to measure progress at different stages rather than relying on a single timestamp or lag value.

Start with pg_stat_replication on the primary, then inspect receive and replay positions on the standby. If WAL is not arriving, investigate the connection and receiver. If WAL arrives but replay falls behind, examine storage performance, resource pressure, recovery conflicts, and long-running queries.

Also monitor replication slots and retained WAL to prevent lag from creating storage problems on the primary. Finally, test how applications behave when a replica is behind, especially when a user writes data and immediately reads it back.

The objective is not simply to make a lag metric return a small number. It is to identify the stage causing the delay, protect database availability, and ensure that applications behave correctly when replicated data is temporarily behind.