public inbox for [email protected]
help / color / mirror / Atom feedReplication lag
12+ messages / 7 participants
[nested] [flat]
* Replication lag
@ 2025-04-17 05:51 Wasim Devale <[email protected]>
0 siblings, 2 replies; 12+ messages in thread
From: Wasim Devale @ 2025-04-17 05:51 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>; pgsql-admin
Hi everyone,
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
Thanks,
Wasim
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Replication lag
@ 2025-04-17 12:14 Wasim Devale <[email protected]>
parent: Wasim Devale <[email protected]>
1 sibling, 2 replies; 12+ messages in thread
From: Wasim Devale @ 2025-04-17 12:14 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>; pgsql-admin
Hi All
Does wal_level = logical can resolve the issue of replication lag?
On Thu, 17 Apr, 2025, 11:21 am Wasim Devale, <[email protected]> wrote:
> Hi everyone,
>
> 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
>
> Thanks,
> Wasim
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Replication lag
@ 2025-04-17 20:28 Kellyn Pot'Vin-Gorman <[email protected]>
parent: Wasim Devale <[email protected]>
1 sibling, 0 replies; 12+ messages in thread
From: Kellyn Pot'Vin-Gorman @ 2025-04-17 20:28 UTC (permalink / raw)
To: Wasim Devale <[email protected]>; +Cc: Pgsql-admin <[email protected]>; pgsql-admin
--000000000000e7fd6e0632ff43ff
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Hey Wasim,
You've already checked the lag information in pg_stat_replication,
pg_stat_statements and pg_stat_activity?
Is there any delay in the setup that might be causing the lag?
max_standby_streaming_delay and/or max_standby_archive_delay
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Replication lag
@ 2025-04-17 22:04 Gaspare Boscarino, P.Eng. <[email protected]>
parent: Wasim Devale <[email protected]>
1 sibling, 1 reply; 12+ messages in thread
From: Gaspare Boscarino, P.Eng. @ 2025-04-17 22:04 UTC (permalink / raw)
To: Wasim Devale <[email protected]>; +Cc: Pgsql-admin <[email protected]>; pgsql-admin
Hello Wasim,
If I understand your problem correctly, you are trying to use the replica
to run queries for some kind of report. For those cases, I recommend
setting up a logical replication which will allow you to have a replica
that can be modified based on your needs. For instance, on the target
database (replica) you could create indices to improve the performance of
your query. An analysis of the execution plan would be necessary, of course.
Regards,
Gaspare
On Thu, Apr 17, 2025 at 5:15 AM Wasim Devale <[email protected]> wrote:
> Hi All
>
> Does wal_level = logical can resolve the issue of replication lag?
>
> On Thu, 17 Apr, 2025, 11:21 am Wasim Devale, <[email protected]> wrote:
>
>> Hi everyone,
>>
>> 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
>>
>> Thanks,
>> Wasim
>>
>
--
Gaspare Boscarino, P.Eng., M.Eng., MASc.
Founder and CEO
*Theorema Systems Inc.*
www.theoremasystems.com | +1 604-765-0121
^ permalink raw reply [nested|flat] 12+ messages in thread
* Assunto: Replication lag
@ 2025-04-17 22:13 Marcus Vinicius Castro <[email protected]>
parent: Wasim Devale <[email protected]>
1 sibling, 0 replies; 12+ messages in thread
From: Marcus Vinicius Castro @ 2025-04-17 22:13 UTC (permalink / raw)
To: [email protected]; Pgsql-admin <[email protected]>; pgsql-admin
First you have to check the indexes and the query.
After that you can research on Google ways to increase the performance in database.
Best regards!
Yahoo Mail: Busque, organize e aumente sua produtividade
Em qui., 17 17e abr. 17e 2025 às 2:52, Wasim Devale<[email protected]> escreveu: Hi everyone,
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 offAnd maximum streaming delay is set to -1
Thanks,Wasim
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Replication lag
@ 2025-04-18 06:48 Laurenz Albe <[email protected]>
parent: Gaspare Boscarino, P.Eng. <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Laurenz Albe @ 2025-04-18 06:48 UTC (permalink / raw)
To: Gaspare Boscarino, P.Eng. <[email protected]>; Wasim Devale <[email protected]>; +Cc: Pgsql-admin <[email protected]>; pgsql-admin
> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Replication lag
@ 2025-04-18 10:51 Wasim Devale <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Wasim Devale @ 2025-04-18 10:51 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Gaspare Boscarino, P.Eng. <[email protected]>; Pgsql-admin <[email protected]>; pgsql-admin
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
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Replication lag
@ 2025-04-18 11:08 Laurenz Albe <[email protected]>
parent: Wasim Devale <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Laurenz Albe @ 2025-04-18 11:08 UTC (permalink / raw)
To: Wasim Devale <[email protected]>; +Cc: Gaspare Boscarino, P.Eng. <[email protected]>; Pgsql-admin <[email protected]>; pgsql-admin
On Fri, 2025-04-18 at 16:21 +0530, Wasim Devale wrote:
> So finally long running on a replica won't minimise replication lag to zero in any scenario? Correct?
I am not sure I understand that sentence correctly.
Yes, if you are running long-running queries on a standby server, that
won't minimize replication lag. But I am surprised that anyone could
imagine it would.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 12+ messages in thread
* Replication lag
@ 2025-05-23 07:13 Wasim Devale <[email protected]>
0 siblings, 3 replies; 12+ messages in thread
From: Wasim Devale @ 2025-05-23 07:13 UTC (permalink / raw)
To: pgsql-admin; Pgsql-admin <[email protected]>
Hello,
Reply wal and query execution on replica can coexists?
Golden gate in oracle has this feature that they can coexists but in
postgresql do we have any provision like this.
Please assist.
Thanks,
Wasim
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Replication lag
@ 2025-05-23 08:08 David Okeamah <[email protected]>
parent: Wasim Devale <[email protected]>
2 siblings, 0 replies; 12+ messages in thread
From: David Okeamah @ 2025-05-23 08:08 UTC (permalink / raw)
To: Wasim Devale <[email protected]>; pgsql-admin; Pgsql-admin <[email protected]>
Query Execution and Reply WAL Coexistence in PostgreSQL
Wasim,
Thanks for your question.
Yes, PostgreSQL does support concurrent WAL replay and read query execution on replicas through its hot standby feature. By setting hot_standby = on, a replica can serve read-only queries while applying WAL files from the primary via streaming replication.
However, there are a few caveats:
* Read queries on the standby may be canceled if they conflict with recovery operations. This behavior can be tuned using parameters like max_standby_streaming_delay and hot_standby_feedback.
* Unlike Oracle GoldenGate, PostgreSQL’s native logical replication is more limited in terms of conflict resolution and cross-version replication, though tools like pglogical or Debezium can bridge those gaps for more complex use cases.
Best regards,
David Okeamah
DAVID OKEAMAH,DEVELOPER
________________________________
From: Wasim Devale <[email protected]>
Sent: Friday, May 23, 2025 8:13:10 AM
To: pgsql-admin <[email protected]>; Pgsql-admin <[email protected]>
Subject: Replication lag
Hello,
Reply wal and query execution on replica can coexists?
Golden gate in oracle has this feature that they can coexists but in postgresql do we have any provision like this.
Please assist.
Thanks,
Wasim
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Replication lag
@ 2025-05-23 08:31 Dionysios-Charalampos Vythoulkas <[email protected]>
parent: Wasim Devale <[email protected]>
2 siblings, 0 replies; 12+ messages in thread
From: Dionysios-Charalampos Vythoulkas @ 2025-05-23 08:31 UTC (permalink / raw)
To: pgsql-admin; Pgsql-admin <[email protected]>; +Cc: Wasim Devale <[email protected]>
On streaming replication yes, you can perform read-only queries.
On logical replication, as far as I know you can also run write queries, but you have to be
careful to keep the data consistent.
On Παρασκευή, 23 Μαΐου 2025 10:13:10 Π.Μ. EEST Wasim Devale wrote:
> Hello,
>
> Reply wal and query execution on replica can coexists?
>
> Golden gate in oracle has this feature that they can coexists but in
> postgresql do we have any provision like this.
>
> Please assist.
>
> Thanks,
> Wasim
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Replication lag
@ 2025-05-23 09:46 Laurenz Albe <[email protected]>
parent: Wasim Devale <[email protected]>
2 siblings, 0 replies; 12+ messages in thread
From: Laurenz Albe @ 2025-05-23 09:46 UTC (permalink / raw)
To: Wasim Devale <[email protected]>; pgsql-admin; Pgsql-admin <[email protected]>
On Fri, 2025-05-23 at 12:43 +0530, Wasim Devale wrote:
> Reply wal and query execution on replica can coexists?
Yes, you can have both.
But there is the possibility of replication conflicts, which can
either delay replay of the WAL or lead to cacneled queries on the
standby.
To see why this is unavoidable in some cases, consider the following
scenario:
- on the standby, there is a long-running query on table A
- on the primary, somebody executes "DROP TABLE A"
The change gets replicated to the standby, but it clearly cannot be
replayed while the query is still running.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2025-05-23 09:46 UTC | newest]
Thread overview: 12+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-17 05:51 Replication lag Wasim Devale <[email protected]>
2025-04-17 12:14 ` Wasim Devale <[email protected]>
2025-04-17 20:28 ` Kellyn Pot'Vin-Gorman <[email protected]>
2025-04-17 22:04 ` Gaspare Boscarino, P.Eng. <[email protected]>
2025-04-18 06:48 ` Laurenz Albe <[email protected]>
2025-04-18 10:51 ` Wasim Devale <[email protected]>
2025-04-18 11:08 ` Laurenz Albe <[email protected]>
2025-04-17 22:13 ` Assunto: Replication lag Marcus Vinicius Castro <[email protected]>
2025-05-23 07:13 Replication lag Wasim Devale <[email protected]>
2025-05-23 08:08 ` David Okeamah <[email protected]>
2025-05-23 08:31 ` Dionysios-Charalampos Vythoulkas <[email protected]>
2025-05-23 09:46 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox