public inbox for [email protected]
help / color / mirror / Atom feedFrom: Wasim Devale <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: Gaspare Boscarino, P.Eng. <[email protected]>
Cc: Pgsql-admin <[email protected]>
Cc: pgsql-admin <[email protected]>
Subject: Re: Replication lag
Date: Fri, 18 Apr 2025 16:21:20 +0530
Message-ID: <CAB5fag7w3vq_mzENSKrGYgJrBxL4wN1W6oVKbFb4kjUEYVwbLQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAB5fag5ek2y21Op8emh+t_omMVxjXz9rVOv5HR_YzPNreYtgfA@mail.gmail.com>
<CAB5fag7aCOwDbdjNyGkanANBL_k_GZ=YTc3Pd8NnVBuM7CUckQ@mail.gmail.com>
<CALFaTbNJZODDhn_tWzo-kggLE9gtWwC8agJXx3SNpk7g=wqU+A@mail.gmail.com>
<[email protected]>
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, <[email protected]>
wrote:
> > On Thu, Apr 17, 2025 at 5:15 AM Wasim Devale <[email protected]> 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
>
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Replication lag
In-Reply-To: <CAB5fag7w3vq_mzENSKrGYgJrBxL4wN1W6oVKbFb4kjUEYVwbLQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox