Re: Lag clarification with Sync Replication

[Raj kumar <rajkumar820999@xxxxxxxxx>]

Thanks Rui, Keith and Scott for nice explanation and blog :)

Thanks,

Raj

On Fri, May 22, 2020 at 10:18 PM Rui DeSousa <rui@xxxxxxxxxxxxx> wrote:

On May 22, 2020, at 12:36 AM, Raj kumar <rajkumar820999@xxxxxxxxx> wrote:

. But, ideally for sync replication, lag should be always 0 right.

Incorrect.  Synchronous replication means that a commit will not return until it has been safely written to disk on the primary and the replica.  That means the transaction is written to WAL file on both primary and replica.  On the primary, the transaction is also visible to transactions with a later xmin.  On the replica, the transaction has been recorded in the WAL; it still needs to get applied to the database for it to become visible to read transactions.

Also, changes could occur faster on the primary where the replica could be lagging behind — either by lagging in receiving the updates or writing the information to the WAL.  What will happen is when the commit is iussed; the session will hang until replica catches up and writes the all the data need safely to the WAL.

I would look at pg_stat_replication view on the primary/upstream provider.  Here’s a query that make it easier.

Sent_lag — data that still needs to be sent to the replica

Flush_lag —data that still has not been fsync’d to WAL files yet

Replay_lag — data that still need to be applied to the database so that it can be queried

select pg_stat_replication.client_addr

  , pg_stat_replication.application_name

  , pg_stat_replication.sync_priority

  , pg_stat_replication.sync_state

  , pg_stat_replication.state

  , case pg_is_in_recovery()

      when true then pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), sent_lsn))

      else pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn))

    end as sent_lag

  , case pg_is_in_recovery()

      when true then pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), flush_lsn))

      else pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) 

    end as flush_lag

  , case pg_is_in_recovery()

      when true then pg_size_pretty(pg_wal_lsn_diff(pg_last_wal_receive_lsn(), replay_lsn)) 

      else pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn))

    end as replay_lag

from pg_stat_replication

;