Message-ID: <b14b297b691d8d89226bd93c97d5df0eeed1c30c.camel@cybertec.at>
Subject: Re: Replication lag
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Gaspare Boscarino, P.Eng." <gaspare.boscarino@theoremasystems.com>,
 Wasim Devale <wasimd60@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>, pgsql-admin
	 <pgsql-admin@postgresql.org>
Date: Fri, 18 Apr 2025 08:48:55 +0200
In-Reply-To: 
 <CALFaTbNJZODDhn_tWzo-kggLE9gtWwC8agJXx3SNpk7g=wqU+A@mail.gmail.com>
References: 
	<CAB5fag5ek2y21Op8emh+t_omMVxjXz9rVOv5HR_YzPNreYtgfA@mail.gmail.com>
	 <CAB5fag7aCOwDbdjNyGkanANBL_k_GZ=YTc3Pd8NnVBuM7CUckQ@mail.gmail.com>
	 <CALFaTbNJZODDhn_tWzo-kggLE9gtWwC8agJXx3SNpk7g=wqU+A@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/b14b297b691d8d89226bd93c97d5df0eeed1c30c.camel%40cybertec.at>
Precedence: bulk

> On Thu, Apr 17, 2025 at 5:15=E2=80=AFAM Wasim Devale <wasimd60@gmail.com>=
 wrote:
> > Does wal_level =3D logical can resolve the issue of replication lag?
> >=20
> > > We have a setup of primary and replica database. We are using the rep=
lica as
> > > read only purpose. But the queries are long running queries that take=
s 30 minutes
> > > to complete.
> > >=20
> > > Do we have any settings in place that will not show replication lag a=
nd the
> > > queries also executes on replica database without competition on WAL =
reply?
> > >=20
> > > 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 =3D on" will probably get rid of the majori=
ty 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 =3D off" (available from v18 on) will get rid of=
 another
  set of replication conflicts.  Before v18, you'd have to disable VACUUM t=
runcation
  on each table individually.

You will probably still get some buffer pin replication conflicts, and comm=
ands
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 t=
o
"minimal", you cannot have replication any more, which would get rid of rep=
lication
conflicts.

Similarly, setting "hot_standby =3D off" on the standby would immediately g=
et rid of
all replication conflicts, because you could no longer connect to the stand=
by and
run queries there.

Yours,
Laurenz Albe