Subject: Replication lag

From: Wasim Devale <[email-masked]>
Date: Thu, 17 Apr 2025 11:21:30 +0530

Hi everyone,

We have a setup of primary and replica database. We are using the replica
as read only purpose. But the queries are long running queries that takes
30 minutes to complete.

Do we have any settings in place that will not show replication lag and the
queries also executes on replica database without competition on WAL reply?

The settings:
Hot standby is off
And maximum streaming delay is set to -1

Thanks,
Wasim

---

From: Wasim Devale <[email-masked]>
Date: Thu, 17 Apr 2025 17:44:32 +0530

Hi All

Does wal_level = logical can resolve the issue of replication lag?

On Thu, 17 Apr, 2025, 11:21 am Wasim Devale, <[email-masked]> wrote:

> Do we have any settings in place that will not show replication lag and
> the queries also executes on replica database without competition on WAL
> reply?
>
> The settings:
> Hot standby is off

---

From: "Kellyn Pot'Vin-Gorman" <[email-masked]>
Date: Thu, 17 Apr 2025 13:28:37 -0700

Hey Wasim,
You've already checked the lag information in pg_stat_replication,
pg_stat_statements and pg_stat_activity?

Is there any delay in the setup that might be causing the lag?
max_standby_streaming_delay and/or max_standby_archive_delay

---

From: "Gaspare Boscarino, P.Eng." <[email-masked]>
Date: Thu, 17 Apr 2025 15:04:52 -0700

Hello Wasim,

If I understand your problem correctly, you are trying to use the replica
to run queries for some kind of report. For those cases, I recommend
setting up a logical replication which will allow you to have a replica
that can be modified based on your needs. For instance, on the target
database (replica) you could create indices to improve the performance of
your query. An analysis of the execution plan would be necessary, of course.

Regards,

   Gaspare

-- 
Gaspare Boscarino, P.Eng., M.Eng., MASc.
Founder and CEO
*Theorema Systems Inc.*
www.theoremasystems.com | +1 604-765-0121

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 18 Apr 2025 08:48:55 +0200

> > > We have a setup of primary and replica database. We are using the replica as
> > > read only purpose. But the queries are long running queries that takes 30 minutes
> > > to complete.
> > > 

In short: no.

A more detailed discussion:

If I understand correctly, you are fighting with replication conflicts, and you
want no replay delay and no canceled queries.

The only way you can have that is if you don't have replication conflicts, and
that is something you can guarantee.  However, you can reduce the frequency of
replication conflicts:

- Setting "hot_standby_feedback = on" will probably get rid of the majority of
  replication conflicts, but the price is that long-running queries on the standby
  can bloat the tables and indexes on the primary.

- Setting "vacuum_truncate = off" (available from v18 on) will get rid of another
  set of replication conflicts.  Before v18, you'd have to disable VACUUM truncation
  on each table individually.

You will probably still get some buffer pin replication conflicts, and commands
like TRUNCATE, ALTER TABLE or VACUUM (FULL) will always cause them.

Changing "wal_level" has no impact on all that, except that if you set it to
"minimal", you cannot have replication any more, which would get rid of replication
conflicts.

Similarly, setting "hot_standby = off" on the standby would immediately get rid of
all replication conflicts, because you could no longer connect to the standby and
run queries there.

Yours,
Laurenz Albe

---

From: Wasim Devale <[email-masked]>
Date: Fri, 18 Apr 2025 16:21:20 +0530

So finally long running on a replica won't minimise replication lag to zero
in any scenario? Correct?

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 18 Apr 2025 13:08:50 +0200

On Fri, 2025-04-18 at 16:21 +0530, Wasim Devale wrote:
> So finally long running on a replica won't minimise replication lag to zero in any scenario? Correct?

I am not sure I understand that sentence correctly.

Yes, if you are running long-running queries on a standby server, that
won't minimize replication lag.  But I am surprised that anyone could
imagine it would.

Yours,
Laurenz Albe