> 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
