Re: Replication lag

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(dot)albe(at)cybertec(dot)at>
wrote:

> > On Thu, Apr 17, 2025 at 5:15 AM Wasim Devale <wasimd60(at)gmail(dot)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
>