Thread: Replication lag

Replication lag

From

Wasim Devale

Date:

17 April, 08:51:30

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

Re: Replication lag

From

Wasim Devale

Date:

17 April, 15:14:32

Hi All

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

On Thu, 17 Apr, 2025, 11:21 am Wasim Devale, <wasimd60@gmail.com> wrote:

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

Re: Replication lag

From

"Kellyn Pot'Vin-Gorman"

Date:

17 April, 23:28:37

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 what I understand on logical replication, that is for specific tables for row level replication and is not for a full database replication. I believe that's a change that will require a restart, (wal change...)

wal_level = logical
		Kellyn Gorman DBAKevlar Blog about.me/dbakevlar

On Thu, Apr 17, 2025 at 5:15 AM Wasim Devale <wasimd60@gmail.com> wrote:

Hi All

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

On Thu, 17 Apr, 2025, 11:21 am Wasim Devale, <wasimd60@gmail.com> wrote:
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

Re: Replication lag

From

"Gaspare Boscarino, P.Eng."

Date:

18 April, 01:04:52

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

On Thu, Apr 17, 2025 at 5:15 AM Wasim Devale <wasimd60@gmail.com> wrote:

Hi All

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

On Thu, 17 Apr, 2025, 11:21 am Wasim Devale, <wasimd60@gmail.com> wrote:
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

Gaspare Boscarino, P.Eng., M.Eng., MASc.

Founder and CEO

Theorema Systems Inc.

www.theoremasystems.com | +1 604-765-0121

Assunto: Replication lag

From

Marcus Vinicius Castro

Date:

18 April, 01:13:38

First you have to check the indexes and the query.

After that you can research on Google ways to increase the performance in database.

Best regards!

Yahoo Mail: Busque, organize e aumente sua produtividade

Em qui., 17 17e abr. 17e 2025 às 2:52, Wasim Devale
<wasimd60@gmail.com> escreveu:
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

Re: Replication lag

From

Laurenz Albe

Date:

18 April, 09:48:55

> On Thu, Apr 17, 2025 at 5:15 AM Wasim Devale <wasimd60@gmail.com> wrote:
> > Does wal_level = logical can resolve the issue of replication lag?
> >
> > > 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

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

Re: Replication lag

From

Wasim Devale

Date:

18 April, 13:51:20

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

On Fri, 18 Apr, 2025, 12:18 pm Laurenz Albe, <laurenz.albe@cybertec.at> wrote:

> On Thu, Apr 17, 2025 at 5:15 AM Wasim Devale <wasimd60@gmail.com> wrote:
> > Does wal_level = logical can resolve the issue of replication lag?
> >
> > > 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

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

Re: Replication lag

From

Laurenz Albe

Date:

18 April, 14:08:50

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