> 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.
