pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedRegarding huge logical slot lag between source and target
19+ messages / 5 participants
[nested] [flat]
* Regarding huge logical slot lag between source and target
@ 2026-10-18 14:19 Durgamahesh Manne <maheshpostgres9@gmail.com>
0 siblings, 2 replies; 19+ messages in thread
From: Durgamahesh Manne @ 2026-10-18 14:19 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>; pgsql-general
Hi
Logical slot lag 330GB on source grows faster during the huge modifications
but it should be minimized which is not happened when load goes down.
What is the action need to take for minimizing the lag to normal state.
Source version 16
Target version 16
Sufficient resources allocated and tuned replication related parameters
Regards
Durga Mahesh
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-18 14:53 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Adrian Klaver @ 2026-10-18 14:53 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>; pgsql-general
On 10/18/26 7:19 AM, Durgamahesh Manne wrote:
> Hi
>
> Logical slot lag 330GB on source grows faster during the huge
> modifications but it should be minimized which is not happened when load
> goes down.
What does the number 330 GB represent?
Define 'huge modifications'.
Give numbers under load and when load is reduced.
>
> What is the action need to take for minimizing the lag to normal state.
> Source version 16
> Target version 16
Where are the source and target located relative to each other:
Same machine.
Same data center.
Different data centers. If so what is the network distance?
> Sufficient resources allocated and tuned replication related parameters
Given your opening sentence it looks like you do not have 'Sufficient
resources'.
Provide the numbers for resources and replication parameters.
>
>
> Regards
> Durga Mahesh
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-18 15:23 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 19+ messages in thread
From: Adrian Klaver @ 2026-10-18 15:23 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; +Cc: pgsql-general
On 10/18/26 8:08 AM, Durgamahesh Manne wrote:
When you reply, also include the mailing list. In other words Reply All.
Ccing list.
Also copy and paste information in screenshot as it did not make it into
this reply.
More comments in line.
>
>
> On Sun, 18 Oct, 2026, 20:23 Adrian Klaver, <adrian.klaver@aklaver.com
> Hi
>
> Both are in same data center.
>
> Source 128gb ram and 32 vcpus
> Target 128gb ram and 64 vcpus
>
> Source params
> Logical decoding work mem 4gB
> Max replication slots 20
> Max wal senders 35
> Max worker processes 64
> Wal keep size 48gb
> Wal level logical
> Wal sender timeout 30min
> Min wal size 16gb
> Max wal size 64gb
>
> Target params
> Min wal size 24gb
> Max worker processes 80
> Max wal size 90gb
> Wal keep size 55gb not required at target
> Max logical replication workers 50
> Max parallel workers 12
>
>
> Already set subscription with streaming and binary
Between same two machines?
Why multiple forms of replication?
>
> Huge updates on one table
Again define what a huge update is, 100,000 rows and 1,000,000 rows or
something else?
> Please what attributes needed from replication slots and stat
> replication tables .
>
>
>
> See here 3rd slot lag
>
>
> Regards
> Durga Mahesh
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-18 17:22 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Laurenz Albe @ 2026-10-18 17:22 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>; pgsql-general
On Sun, 2026-10-18 at 19:49 +0530, Durgamahesh Manne wrote:
> Logical slot lag 330GB on source grows faster during the huge modifications but it should be minimized which is not happened when load goes down.
>
> What is the action need to take for minimizing the lag to normal state.
> Source version 16
> Target version 16
> Sufficient resources allocated and tuned replication related parameters
ALTER SUBSCRIPTION sub SET (streaming = parallel);
Also, set the following parameters to generous values (as your hardware
allows):
On the publisher:
logical_decoding_work_mem
On the subscriber:
max_logical_replication_workers
max_worker_processes
max_parallel_apply_workers_per_subscription
max_sync_workers_per_subscription
Note the dependencies: max_worker_processes must accommodate for all the
worker processes.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-18 17:41 Durgamahesh Manne <maheshpostgres9@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 3 replies; 19+ messages in thread
From: Durgamahesh Manne @ 2026-10-18 17:41 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>; pgsql-general
On Sun, 18 Oct, 2026, 22:52 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
> On Sun, 2026-10-18 at 19:49 +0530, Durgamahesh Manne wrote:
> > Logical slot lag 330GB on source grows faster during the huge
> modifications but it should be minimized which is not happened when load
> goes down.
> >
> > What is the action need to take for minimizing the lag to normal state.
> > Source version 16
> > Target version 16
> > Sufficient resources allocated and tuned replication related parameters
>
> ALTER SUBSCRIPTION sub SET (streaming = parallel);
>
> Also, set the following parameters to generous values (as your hardware
> allows):
>
> On the publisher:
>
> logical_decoding_work_mem
>
> On the subscriber:
>
> max_logical_replication_workers
> max_worker_processes
> max_parallel_apply_workers_per_subscription
> max_sync_workers_per_subscription
>
> Note the dependencies: max_worker_processes must accommodate for all the
> worker processes.
>
> Yours,
> Laurenz Albe
>
Hi
I already set those params with required values to make server running
smoothly
Alter subscription also done with binary and streaming options
Finally I could not find root cause what has caused to increase of that
much lag.
No long running queries at source
No blocking at target
Generally what might be reason to huge lag in terms of hundreds which can't
go down.
This is very rare situation we face an issue like this
As final decision I resync data from scratch.
Regards
Durga Mahesh
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-18 17:46 Durgamahesh Manne <maheshpostgres9@gmail.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Durgamahesh Manne @ 2026-10-18 17:46 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>; pgsql-general
On Sun, 18 Oct, 2026, 23:11 Durgamahesh Manne, <maheshpostgres9@gmail.com>
wrote:
>
>
> On Sun, 18 Oct, 2026, 22:52 Laurenz Albe, <laurenz.albe@cybertec.at>
> wrote:
>
>> On Sun, 2026-10-18 at 19:49 +0530, Durgamahesh Manne wrote:
>> > Logical slot lag 330GB on source grows faster during the huge
>> modifications but it should be minimized which is not happened when load
>> goes down.
>> >
>> > What is the action need to take for minimizing the lag to normal state.
>> > Source version 16
>> > Target version 16
>> > Sufficient resources allocated and tuned replication related parameters
>>
>> ALTER SUBSCRIPTION sub SET (streaming = parallel);
>>
>> Also, set the following parameters to generous values (as your hardware
>> allows):
>>
>> On the publisher:
>>
>> logical_decoding_work_mem
>>
>> On the subscriber:
>>
>> max_logical_replication_workers
>> max_worker_processes
>> max_parallel_apply_workers_per_subscription
>> max_sync_workers_per_subscription
>>
>> Note the dependencies: max_worker_processes must accommodate for all the
>> worker processes.
>>
>> Yours,
>> Laurenz Albe
>>
>
>
> Hi
> I already set those params with required values to make server running
> smoothly
>
> Alter subscription also done with binary and streaming options
>
> Finally I could not find root cause what has caused to increase of that
> much lag.
>
> No long running queries at source
> No blocking at target
> Generally what might be reason to huge lag in terms of hundreds which
> can't go down.
>
> This is very rare situation we face an issue like this
>
>
> As final decision I resync data from scratch.
> Regards
> Durga Mahesh
>
Hi what is the formula to set this logical_decoding_work_mem?
I set 4Gb
When we have huge DML ops what value can be recommended.
Ram 128gb
Regards
Durga Mahesh
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-18 18:43 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
2 siblings, 0 replies; 19+ messages in thread
From: Laurenz Albe @ 2026-10-18 18:43 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>; pgsql-general
On Sun, 2026-10-18 at 23:11 +0530, Durgamahesh Manne wrote:
> On Sun, 18 Oct, 2026, 22:52 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
> > On Sun, 2026-10-18 at 19:49 +0530, Durgamahesh Manne wrote:
> > > Logical slot lag 330GB on source grows faster during the huge modifications but it should be minimized which is not happened when load goes down.
> > >
> > > What is the action need to take for minimizing the lag to normal state.
> > > Source version 16
> > > Target version 16
> > > Sufficient resources allocated and tuned replication related parameters
> >
> > [I suggested "streaming = parallel" and increasing some parameters}
>
> I already set those params with required values to make server running smoothly
>
> Alter subscription also done with binary and streaming options
>
> Finally I could not find root cause what has caused to increase of that much lag.
>
> No long running queries at source
> No blocking at target
> Generally what might be reason to huge lag in terms of hundreds which can't go down.
Perhaps DDL statements on the publisher.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-18 18:45 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Laurenz Albe @ 2026-10-18 18:45 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>; pgsql-general
On Sun, 2026-10-18 at 23:16 +0530, Durgamahesh Manne wrote:
> Hi what is the formula to set this logical_decoding_work_mem?
>
> I set 4Gb
> When we have huge DML ops what value can be recommended.
>
> Ram 128gb
I don't think there is a formula, but 4GB is pretty high.
Can you be a bit more specific about the "huge DML operations"?
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-18 19:05 Manu <manuelreyesbravo@gmail.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Manu @ 2026-10-18 19:05 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>; Adrian Klaver <adrian.klaver@aklaver.com>
Hi Durga,
> No long running queries at source
> No blocking at target
> Generally what might be reason to huge lag in terms of hundreds which
> can't go down.
> As final decision I resync data from scratch.
Before you resync: the shape you describe -- retained WAL that does not
fall even after the load stops, while the subscriber is fully caught up --
is almost always one open transaction pinning the slot's restart_lsn, and
the holder is often invisible to pg_stat_activity. A resync clears it, but
it comes back the next time the same thing happens, so it is worth finding
the holder first.
I reproduced the pattern on PostgreSQL 16.15 (publisher + subscriber, a
10M-row table, a ~4.5 GB UPDATE). What I measured:
- Any transaction that holds an XID freezes restart_lsn for as long as it
stays open -- in ANY database, on ANY table, not only the replicated one.
During that window confirmed_flush_lsn was fully caught up: the subscriber
had nothing left to apply. The "330 GB" is retained WAL, not apply lag.
- The holder need not be a running query. A transaction left idle in
transaction, and a prepared transaction (two-phase commit), both froze the
slot exactly the same way. A prepared transaction shows nothing in
pg_stat_activity -- only in pg_prepared_xacts. That matches your "no long
running queries". An inactive/orphaned slot (active = f) does the same:
it keeps every byte of WAL after its restart_lsn with no consumer at all.
- streaming = parallel does not release the pin while the transaction is
open. In my run it only shortened the catch-up after COMMIT (about 2 s vs
~40 s), because the subscriber had already applied the rows. That is
consistent with you having it set already and seeing no change to the lag.
- With many small transactions and no long-open one, the slot did not
advance during the load but caught up 15-18 s after the load stopped. So a
slot that stays frozen long after the load is gone is not that case.
Four queries to find the actual holder (run on the publisher):
-- (1) Which slot pins the WAL, and is anyone consuming it?
SELECT slot_name, active, active_pid, wal_status,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS unconfirmed,
catalog_xmin, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots ORDER BY slot_name;
-- (2) Walsender: behind decoding WAL, or waiting for the subscriber?
SELECT application_name, pid, state,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS not_yet_decoded,
pg_size_pretty(pg_wal_lsn_diff(sent_lsn, flush_lsn)) AS sent_not_confirmed,
reply_time
FROM pg_stat_replication;
-- (3) Oldest open XID in ANY database (this is what pins restart_lsn)
SELECT pid, datname, state, backend_xid, age(backend_xid) AS xid_age,
date_trunc('second', now() - xact_start) AS xact_age, left(query, 40) AS query
FROM pg_stat_activity WHERE backend_xid IS NOT NULL
ORDER BY age(backend_xid) DESC LIMIT 3;
SELECT gid, prepared, database, transaction FROM pg_prepared_xacts;
-- (4) Reorder buffer: spilled to disk, or streamed?
SELECT slot_name, spill_txns, pg_size_pretty(spill_bytes) AS spill_bytes,
stream_txns, pg_size_pretty(stream_bytes) AS stream_bytes,
total_txns, pg_size_pretty(total_bytes) AS total_bytes
FROM pg_stat_replication_slots ORDER BY slot_name;
Query (1) tells you which slot holds the WAL and whether it even has a
consumer (active, wal_status). Query (3) plus pg_prepared_xacts give the
oldest XID holder across all databases, including a prepared transaction
that pg_stat_activity will not show. In my runs the slot's catalog_xmin
matched that holder's XID, so it is a useful cross-check for locating it.
One more thing on the subscriber side: if a single transaction is larger
than logical_decoding_work_mem, the walsender streams it; below that, it
buffers the whole transaction first. Under memory pressure I also saw the
subscriber cancel the apply worker repeatedly with "terminating logical
replication worker due to timeout", which stretched the catch-up badly --
worth grepping your subscriber log for that line.
Happy to share the full test matrix if it helps.
Regards,
Manu
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-18 21:36 Durgamahesh Manne <maheshpostgres9@gmail.com>
parent: Manu <manuelreyesbravo@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Durgamahesh Manne @ 2026-10-18 21:36 UTC (permalink / raw)
To: Manu <manuelreyesbravo@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>; Adrian Klaver <adrian.klaver@aklaver.com>
On Mon, 19 Oct, 2026, 00:35 Manu, <manuelreyesbravo@gmail.com> wrote:
> Hi Durga,
>
> > No long running queries at source
> > No blocking at target
> > Generally what might be reason to huge lag in terms of hundreds which
> > can't go down.
> > As final decision I resync data from scratch.
>
> Before you resync: the shape you describe -- retained WAL that does not
> fall even after the load stops, while the subscriber is fully caught up --
> is almost always one open transaction pinning the slot's restart_lsn, and
> the holder is often invisible to pg_stat_activity. A resync clears it, but
> it comes back the next time the same thing happens, so it is worth finding
> the holder first.
>
> I reproduced the pattern on PostgreSQL 16.15 (publisher + subscriber, a
> 10M-row table, a ~4.5 GB UPDATE). What I measured:
>
> - Any transaction that holds an XID freezes restart_lsn for as long as it
> stays open -- in ANY database, on ANY table, not only the replicated one.
> During that window confirmed_flush_lsn was fully caught up: the
> subscriber
> had nothing left to apply. The "330 GB" is retained WAL, not apply lag.
>
> - The holder need not be a running query. A transaction left idle in
> transaction, and a prepared transaction (two-phase commit), both froze
> the
> slot exactly the same way. A prepared transaction shows nothing in
> pg_stat_activity -- only in pg_prepared_xacts. That matches your "no long
> running queries". An inactive/orphaned slot (active = f) does the same:
> it keeps every byte of WAL after its restart_lsn with no consumer at all.
>
> - streaming = parallel does not release the pin while the transaction is
> open. In my run it only shortened the catch-up after COMMIT (about 2 s vs
> ~40 s), because the subscriber had already applied the rows. That is
> consistent with you having it set already and seeing no change to the
> lag.
>
> - With many small transactions and no long-open one, the slot did not
> advance during the load but caught up 15-18 s after the load stopped. So
> a
> slot that stays frozen long after the load is gone is not that case.
>
> Four queries to find the actual holder (run on the publisher):
>
> -- (1) Which slot pins the WAL, and is anyone consuming it?
> SELECT slot_name, active, active_pid, wal_status,
> pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
> AS retained_wal,
> pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
> confirmed_flush_lsn)) AS unconfirmed,
> catalog_xmin, age(catalog_xmin) AS catalog_xmin_age
> FROM pg_replication_slots ORDER BY slot_name;
>
> -- (2) Walsender: behind decoding WAL, or waiting for the subscriber?
> SELECT application_name, pid, state,
> pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS
> not_yet_decoded,
> pg_size_pretty(pg_wal_lsn_diff(sent_lsn, flush_lsn)) AS
> sent_not_confirmed,
> reply_time
> FROM pg_stat_replication;
>
> -- (3) Oldest open XID in ANY database (this is what pins restart_lsn)
> SELECT pid, datname, state, backend_xid, age(backend_xid) AS xid_age,
> date_trunc('second', now() - xact_start) AS xact_age, left(query,
> 40) AS query
> FROM pg_stat_activity WHERE backend_xid IS NOT NULL
> ORDER BY age(backend_xid) DESC LIMIT 3;
> SELECT gid, prepared, database, transaction FROM pg_prepared_xacts;
>
> -- (4) Reorder buffer: spilled to disk, or streamed?
> SELECT slot_name, spill_txns, pg_size_pretty(spill_bytes) AS spill_bytes,
> stream_txns, pg_size_pretty(stream_bytes) AS stream_bytes,
> total_txns, pg_size_pretty(total_bytes) AS total_bytes
> FROM pg_stat_replication_slots ORDER BY slot_name;
>
> Query (1) tells you which slot holds the WAL and whether it even has a
> consumer (active, wal_status). Query (3) plus pg_prepared_xacts give the
> oldest XID holder across all databases, including a prepared transaction
> that pg_stat_activity will not show. In my runs the slot's catalog_xmin
> matched that holder's XID, so it is a useful cross-check for locating it.
>
> One more thing on the subscriber side: if a single transaction is larger
> than logical_decoding_work_mem, the walsender streams it; below that, it
> buffers the whole transaction first. Under memory pressure I also saw the
> subscriber cancel the apply worker repeatedly with "terminating logical
> replication worker due to timeout", which stretched the catch-up badly --
> worth grepping your subscriber log for that line.
>
> Happy to share the full test matrix if it helps.
>
> Regards,
> Manu
>
Hi Manu
Even after resyncing data from scratch
Still I am experiencing same issue.
I see logical slot lag 60Gb again after initial copy done.
offer=> \x
Expanded display is on.
offer=> SELECT slot_name, active, active_pid, wal_status,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS
retained_wal,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn))
AS unconfirmed,
catalog_xmin, age(catalog_xmin) AS catalog_xmin_age
FROM pg_replication_slots ORDER BY slot_name;
-[ RECORD 1 ]----+--------------------------------------------
slot_name | offer_ppbet_subscription
active | t
active_pid | 3869
wal_status | reserved
retained_wal | 60 GB
unconfirmed | 60 GB
catalog_xmin | 3905968151
catalog_xmin_age | 3485404
offer=> SELECT application_name, pid,
state,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) AS
not_yet_decoded, pg_size_pretty(pg_wal_lsn_diff(sent_lsn, flush_lsn)) AS
sent_not_confirmed, reply_time FROM pg_stat_replication;
-[ RECORD 1 ]------+------------------------------
application_name | walreceiver
pid | 10897
state | streaming
not_yet_decoded | 304 kB
sent_not_confirmed | 0 bytes
reply_time | 2026-10-18 21:23:08.101825+00
-[ RECORD 2 ]------+------------------------------
application_name | offer_ppbet_subscription
pid | 3869
state | catchup
not_yet_decoded | 59 GB
sent_not_confirmed | 1297 MB
reply_time | 2026-10-18 21:18:54.154215+00
*******************************************
offer=> SELECT pid, datname, state, backend_xid, age(backend_xid) AS
xid_age,date_trunc('second', now() - xact_start) AS xact_age, left(query,
40) AS query FROM pg_stat_activity WHERE backend_xid IS NOT NULL ORDER BY
age(backend_xid) DESC LIMIT 3;
pid | datname | state | backend_xid | xid_age | xact_age | query
------+---------+---------------------+-------------+---------+----------+------------------------------------------
4893 | offer | idle in transaction | 3909497296 | 22 | 00:00:00 | update
market set version=version+1,spor
4539 | offer | idle in transaction | 3909497302 | 16 | 00:00:00 | update
market set version=version+1,spor
4541 | offer | idle in transaction | 3909497309 | 9 | 00:00:00 | update
market set version=version+1,spor
*******************************************
offer=> SELECT slot_name, spill_txns, pg_size_pretty(spill_bytes) AS
spill_bytes,stream_txns, pg_size_pretty(stream_bytes) AS
stream_bytes,total_txns, pg_size_pretty(total_bytes) AS total_bytesFROM
pg_stat_replication_slots ORDER BY slot_name;
slot_name | spill_txns | spill_bytes | stream_txns | stream_bytes |
total_txns | total_bytes
----------------------------+------------+-------------+-------------+--------------+------------+-------------
offer_ppbet_subscription | 0 | 0 bytes | 0 | 0 bytes | 3808094 | 8185 MB
How to fix this permanently.
Regards
Durga Mahesh
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-19 11:43 Manu <manuelreyesbravo@gmail.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Manu @ 2026-10-19 11:43 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>; Adrian Klaver <adrian.klaver@aklaver.com>
Hi Durga,
> How to fix this permanently.
Your output rules out a pinned transaction: the idle-in-transaction sessions
have xid_age 9-22 (tiny), and restart_lsn is tracking confirmed_flush, so
nothing is stuck -- replication is simply behind by 60 GB. That is the good
news: there is no hidden holder to hunt. It is a throughput deficit, and the
fix depends on *where* the deficit is.
I reproduced your signature on 16.15 (hot-row counter, many small
transactions, walsender in catchup, sent ~= confirmed, spill/stream 0). This
workload can be limited in two different places, and your snapshot cannot
tell which one is yours:
1. The subscriber's apply worker. A single apply worker applies these small
transactions serially; when the publisher has spare CPU, that one worker
is the ceiling.
2. The publisher's walsender. On a CPU-saturated publisher the walsender
competes with your backends for CPU and cannot decode fast enough; the
backlog then does not drain while the load runs, and clears in seconds
once it stops.
Tell them apart with top: the walsender pid on the source, and the
"logical replication apply worker" on the target. Whichever sits near 100%
of a core is the limit; one well below 100% while the host is busy is the one
starved for CPU. Your sent_not_confirmed was 1297 MB (in my runs it was kB),
which suggests the subscriber side is also behind -- so apply-bound is likely,
possibly both.
Why it matters -- the lever is different:
- If the apply worker is the limit: streaming=parallel will not help. In PG16
parallel apply only covers transactions large enough to be streamed (above
logical_decoding_work_mem); a flood of tiny transactions stays on one worker
(I confirmed the count stays 1 with max_parallel_apply_workers_per_
subscription=4). The levers are to lower the commit rate reaching that
table, or to split the tables across several subscriptions so multiple apply
workers run in parallel.
- If the walsender is starved: splitting makes it worse, because each
subscription runs its own walsender and each decodes the entire WAL. Here
the lever is to reduce WAL volume or free CPU on the publisher.
Two structural notes, measured:
- Splitting into N subscriptions does raise apply throughput (more workers),
but each walsender re-decodes all the WAL (extra load on the publisher), and
a transaction touching tables in two subscriptions is no longer applied
atomically on the subscriber.
- That "update market set version=version+1" counter is about a fifth of your
WAL bytes. If the target does not need it, excluding it from the publication
frees apply capacity -- though it does not reduce the WAL the walsender still
has to decode.
One note on the "catchup" state: a logical walsender switches to "streaming"
the first time it catches up and never switches back, so "catchup" only means
it has not caught up once since it last started -- consistent with being
behind, not a separate fault.
So run top on both processes first; that tells you whether to add apply
workers (split) or to relieve the publisher. Happy to share the test details.
Regards,
Manu
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-19 13:35 Durgamahesh Manne <maheshpostgres9@gmail.com>
parent: Manu <manuelreyesbravo@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Durgamahesh Manne @ 2026-10-19 13:35 UTC (permalink / raw)
To: Manu <manuelreyesbravo@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>; Adrian Klaver <adrian.klaver@aklaver.com>
On Mon, 19 Oct, 2026, 17:13 Manu, <manuelreyesbravo@gmail.com> wrote:
> Hi Durga,
>
> > How to fix this permanently.
>
> Your output rules out a pinned transaction: the idle-in-transaction
> sessions
> have xid_age 9-22 (tiny), and restart_lsn is tracking confirmed_flush, so
> nothing is stuck -- replication is simply behind by 60 GB. That is the good
> news: there is no hidden holder to hunt. It is a throughput deficit, and
> the
> fix depends on *where* the deficit is.
>
> I reproduced your signature on 16.15 (hot-row counter, many small
> transactions, walsender in catchup, sent ~= confirmed, spill/stream 0).
> This
> workload can be limited in two different places, and your snapshot cannot
> tell which one is yours:
>
> 1. The subscriber's apply worker. A single apply worker applies these small
> transactions serially; when the publisher has spare CPU, that one worker
> is the ceiling.
> 2. The publisher's walsender. On a CPU-saturated publisher the walsender
> competes with your backends for CPU and cannot decode fast enough; the
> backlog then does not drain while the load runs, and clears in seconds
> once it stops.
>
> Tell them apart with top: the walsender pid on the source, and the
> "logical replication apply worker" on the target. Whichever sits near 100%
> of a core is the limit; one well below 100% while the host is busy is the
> one
> starved for CPU. Your sent_not_confirmed was 1297 MB (in my runs it was
> kB),
> which suggests the subscriber side is also behind -- so apply-bound is
> likely,
> possibly both.
>
> Why it matters -- the lever is different:
>
> - If the apply worker is the limit: streaming=parallel will not help. In
> PG16
> parallel apply only covers transactions large enough to be streamed
> (above
> logical_decoding_work_mem); a flood of tiny transactions stays on one
> worker
> (I confirmed the count stays 1 with max_parallel_apply_workers_per_
> subscription=4). The levers are to lower the commit rate reaching that
> table, or to split the tables across several subscriptions so multiple
> apply
> workers run in parallel.
>
> - If the walsender is starved: splitting makes it worse, because each
> subscription runs its own walsender and each decodes the entire WAL. Here
> the lever is to reduce WAL volume or free CPU on the publisher.
>
> Two structural notes, measured:
>
> - Splitting into N subscriptions does raise apply throughput (more
> workers),
> but each walsender re-decodes all the WAL (extra load on the publisher),
> and
> a transaction touching tables in two subscriptions is no longer applied
> atomically on the subscriber.
>
> - That "update market set version=version+1" counter is about a fifth of
> your
> WAL bytes. If the target does not need it, excluding it from the
> publication
> frees apply capacity -- though it does not reduce the WAL the walsender
> still
> has to decode.
>
> One note on the "catchup" state: a logical walsender switches to
> "streaming"
> the first time it catches up and never switches back, so "catchup" only
> means
> it has not caught up once since it last started -- consistent with being
> behind, not a separate fault.
>
> So run top on both processes first; that tells you whether to add apply
> workers (split) or to relieve the publisher. Happy to share the test
> details.
>
> Regards,
> Manu
>
Hi Manu
Now slog lag is very minimal.
How did you rectify when same issue you faced rather than resync data from
scratch.
Regards
Durga Mahesh
>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-19 13:49 Manu <manuelreyesbravo@gmail.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
0 siblings, 2 replies; 19+ messages in thread
From: Manu @ 2026-10-19 13:49 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>; Adrian Klaver <adrian.klaver@aklaver.com>
Hi Durga,
> Now slot lag is very minimal.
> How did you rectify when same issue you faced rather than resync data
> from scratch.
Glad it is minimal now.
To be clear, I did not hit this on a production system -- I reproduced the
pattern in a lab to find the cause, so there was nothing to "rectify" there.
But that is the useful part: this kind of lag is a throughput deficit, not
corruption. The slot's restart_lsn is simply trailing confirmed_flush, so it
drains on its own once the subscriber catches up -- when the write load eases
or the bottleneck is removed. A resync only buys you anything when there is
real divergence on the subscriber, which this is not; it just resets the
clock and the lag comes back under the same load.
So if it grows again, the order is: find the bottleneck first (top on the
walsender on the source and on the apply worker on the target -- whichever is
near 100% of a core is the limit), then apply the matching lever: lower the
commit rate reaching the hot table, split the tables across several
subscriptions so more apply workers run in parallel, or give the subscriber
more CPU/IO. No resync needed.
Regards,
Manu
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-19 14:27 Justin <zzzzz.graf@gmail.com>
parent: Manu <manuelreyesbravo@gmail.com>
1 sibling, 0 replies; 19+ messages in thread
From: Justin @ 2026-10-19 14:27 UTC (permalink / raw)
To: Manu <manuelreyesbravo@gmail.com>; +Cc: Durgamahesh Manne <maheshpostgres9@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>; Adrian Klaver <adrian.klaver@aklaver.com>
Hi Everyone
There are typically two main drivers that cause logical replication to fall
behind with no ERROR.
1. Updates are using REPLICA IDENTITY FULL This causes sequential scans to
apply the update. On a large table the logical replication worker on the
subscriber will choke. No setting will fix that—the only solution is to
change the updates to use a unique index or primary key.
2. The volume of updates across several tables is spread across multiple
sessions on the publisher, but is funneled down to a single worker on the
subscriber. This cannot be fixed with setting changes or by turning on
parallelization on the subscriber. Parallel apply is by transaction
To identify the root cause we need to peek at what logical replication is
pushing to the subscriber.
-- Copies the slot in question into a temporary slot that lives only as
long as the session that created it,
-- using the test_decoding plugin so we can read the output
SELECT * FROM pg_copy_logical_replication_slot('slot_fall_behind',
'copy_slot', true, 'test_decoding');
-- Peek at the changes
SELECT * FROM pg_logical_slot_peek_changes('copy_slot', NULL, 50);
If you see `UPDATE` rows that include all columns from the table (old and
new values), replica identity is set to FULL.
On PG 16 you cannot usefully change replica identity only on the
subscriber.
The publisher must have a suitable unique key and you must alter the
replica identity there:
ALTER TABLE <table> REPLICA IDENTITY USING INDEX <my_index>;
If the volume of updates is simply too high, you will see a large number of
individual DELETE/UPDATE/INSERT statements in separate transactions. This
prevents parallelization and the subscriber just chokes on the volume.
In that case, split the high-volume tables into separate
publications/subscriptions.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-26 04:55 Durgamahesh Manne <maheshpostgres9@gmail.com>
parent: Manu <manuelreyesbravo@gmail.com>
1 sibling, 1 reply; 19+ messages in thread
From: Durgamahesh Manne @ 2026-10-26 04:55 UTC (permalink / raw)
To: Manu <manuelreyesbravo@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>; Adrian Klaver <adrian.klaver@aklaver.com>
On Mon, Oct 19, 2026 at 7:19 PM Manu <manuelreyesbravo@gmail.com> wrote:
> Hi Durga,
>
> > Now slot lag is very minimal.
> > How did you rectify when same issue you faced rather than resync data
> > from scratch.
>
> Glad it is minimal now.
>
> To be clear, I did not hit this on a production system -- I reproduced the
> pattern in a lab to find the cause, so there was nothing to "rectify"
> there.
> But that is the useful part: this kind of lag is a throughput deficit, not
> corruption. The slot's restart_lsn is simply trailing confirmed_flush, so
> it
> drains on its own once the subscriber catches up -- when the write load
> eases
> or the bottleneck is removed. A resync only buys you anything when there is
> real divergence on the subscriber, which this is not; it just resets the
> clock and the lag comes back under the same load.
>
> So if it grows again, the order is: find the bottleneck first (top on the
> walsender on the source and on the apply worker on the target -- whichever
> is
> near 100% of a core is the limit), then apply the matching lever: lower the
> commit rate reaching the hot table, split the tables across several
> subscriptions so more apply workers run in parallel, or give the subscriber
> more CPU/IO. No resync needed.
>
> Regards,
> Manu
>
Hi Manu,
Even after resyncing data from scratch
Still I am experiencing the same issue.
I see the logical slot lag 300Gb again after the initial copy is done.
Seems we need more network bandwidth to handle this.
Here this is expected with huge update operations on a single non partition
table when the logical slot holds a huge amount of data to replicate from
source to target.
Decoding is the single threaded which is unable to handle efficiently.
No blocking and locking and long running and open transactions at both
sides.
Would it be possible to fix by doing following steps
* Increase the bandwidth
* Hash partitioning on that table
I have twenty tables listed in publication so here only one table has huge
updates but not the rest of the tables (very less DML's).
In this case splitting the tables into multiple publications is not the
permanent solution.
Even in sixteen version no improvements for replication performance
Regards,
Durga Mahesh
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-26 17:46 Manu <manuelreyesbravo@gmail.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Manu @ 2026-10-26 17:46 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>; Adrian Klaver <adrian.klaver@aklaver.com>
Hi Durga,
On Mon, Oct 26, 2026 at 10:25 AM Durgamahesh Manne
<maheshpostgres9@gmail.com> wrote:
> Decoding is the single threaded which is unable to handle efficiently.
First a correction. Earlier I read your 1297 MB sent_not_confirmed as a
sign that the subscriber was behind. I tested that and it does not
hold. With a slow subscriber, a CPU-starved walsender and a slow link
alike, sent_lsn - flush_lsn stayed under 100 MB and 98% or more of the
lag sat before sent_lsn. A walsender stops reading WAL while it cannot
send, so the "not_yet_decoded" column I gave you grows whichever side
is slow. Neither number tells you where the bottleneck is.
Decoding is the limit only if the walsender sits near 100% of one
core. In my runs it averaged under 40% in every case.
What does tell you is CPU and wait_event of the two processes, sampled
a few times while the lag grows, plus top on the same pids. On the
target:
SELECT pid, wait_event FROM pg_stat_activity
WHERE backend_type = 'logical replication worker';
and on the source the same with backend_type = 'walsender'.
- Apply worker near 100% CPU, wait_event mostly empty: the subscriber
is the limit.
- Apply worker mostly in LogicalApplyMain (waiting for data) while the
lag is large: the source side is not feeding it. Then a walsender
near 100% CPU means decoding; low CPU on a busy source host means it
is starved for CPU; low CPU on a quiet host with wait_event mostly
WalSenderWriteData means the network.
> * Increase the bandwidth
That helps only in the last case. Through a link limited to 8 MB/s the
walsender used 6% CPU and was in WalSenderWriteData 93% of the time,
and the apply worker used 17% CPU, 82% in LogicalApplyMain. Doubling
the link to 16 MB/s gave 1.86x the drain rate. With the apply worker
at 97-98% CPU, or with a starved walsender, the link is not what
limits, and more bandwidth would not change the drain rate.
> * Hash partitioning on that table
With one subscription, no: all partitions still go through one
walsender and one apply worker. With the publisher idle it drained at
0.79x the rate of the plain table, apply worker at 98% in both.
With one subscription per partition (4 here) the apply side scales:
1.85x with the publisher idle. With the publisher saturated it still
fell behind, by 40 MB/s of WAL instead of 136-152 MB/s, but mostly
because the workload itself ran at about half the rate. Each of the 4
walsenders decoded every transaction, and a transaction that touches
rows in several partitions is applied in separate pieces on the target,
not atomically. It is the same split into several publications that you
said is not a permanent solution, plus rebuilding the table.
On Justin's point about REPLICA IDENTITY FULL: in 16 the apply worker
uses a btree index on the target if one is usable. In one run
with the primary key on both sides it applied as many transactions per
second as the default identity (15,115 vs 15,060 and 15,527). Without a
usable index it scans the table for every row, so it is worth checking
which case you have.
These come from a lab with 4 CPU threads per side, one run per case and
two for the partition layouts, so take the ratios rather than the
rates. Details are in the attached measurements.txt.
Regards,
Manu
Attachments:
measurements.txt (text/plain, 5.3K) download
Logical replication lag: where is the bottleneck? -- PostgreSQL 16.15
=====================================================================
Lab measurements, one host. Not a benchmark: the host had other load,
so absolute rates are only comparable within this file. Partition
layouts were run twice in alternating rounds (a, b); the network and
REPLICA IDENTITY FULL cases once each.
Setup
-----
- PostgreSQL 16.15 (REL_16_STABLE 6ceac2e1250), -O2, no assertions.
Publisher and subscriber on one host, unix sockets.
- Publisher and pgbench pinned to 4 hardware threads, subscriber to
another 4. logical_decoding_work_mem=64MB. Subscriptions:
binary=true, streaming=on, copy_data=false.
- One hot table, 1M rows: hot(id bigint PRIMARY KEY, k bigint, v bigint,
pad text). Workload, 16 clients, autocommit, primary key unchanged:
UPDATE hot SET k = k + 1, v = :a WHERE id BETWEEN :a AND :a + 19;
About 5 kB of decoded data per transaction in every layout.
- Layouts: flat = plain table, 1 publication, 1 subscription.
hash4/1 = hash partitioned in 4, 1 publication on the root,
1 subscription. hash4/4 = 4 publications with one partition each,
4 subscriptions. publish_via_partition_root = false in all.
- Backlog built with the subscriptions disabled, then they are enabled:
"idle" = load stopped (publisher has spare CPU), "busy" = the same
load keeps running for 120 s (publisher CPU saturated).
- Every 2 s: pg_current_wal_lsn, slot confirmed_flush_lsn, sent_lsn and
flush_lsn from pg_stat_replication, CPU of the walsender(s) and apply
worker(s) from /proc. Every 0.25 s: wait_event of the walsender(s)
and apply worker(s) from pg_stat_activity (empty = "CPU").
- "consumed" = advance of confirmed_flush_lsn of the slowest slot, in
MB of WAL per second. Rows and sum(k) matched publisher vs subscriber
after every drained run.
1. Publisher idle: the single apply worker is the limit
-------------------------------------------------------
layout consumed MB/s (a / b) apply CPU walsender CPU
flat 66.0 / 68.6 97% / 98% 29% / 30%
hash4/1 52.7 / 53.6 98% / 98% 23% / 24%
hash4/4 123.2 / 126.4 365% / 375% 143% / 145%
(4 workers) (4 walsenders)
Relative to flat: hash4/1 0.79x, hash4/4 1.85x.
The subscriber had 4 hardware threads, so 4 apply workers were capped
near 4 x 95%.
Wait events, flat: apply CPU 99%; walsender WalSenderWriteData 65-70%.
2. Publisher saturated: nothing drains, the walsender gets too little CPU
------------------------------------------------------------------------
layout consumed MB/s generated MB/s net MB/s pgbench tps
flat a 46.8 182.4 -135.6 44,161
flat b 44.4 196.0 -151.6 47,541
hash4/1 a 34.4 119.8 -85.5 27,587
hash4/1 b 33.7 123.8 -90.1 28,195
hash4/4 a 59.4 99.6 -40.2 23,158
hash4/4 b 59.8 98.8 -39.0 22,993
Apply worker CPU 65-70% (flat, hash4/1), 148-151% for 4 (hash4/4).
Apply worker waiting for data (LogicalApplyMain): flat 22% / 34%,
hash4/1 31% / 32%, hash4/4 63% / 64% of probes.
Walsender CPU 28-30% (flat), 23-24% (hash4/1), about 24% each of 4.
The hash-partitioned table also lowered the publisher's own throughput
in this workload (each 20-row range touches all 4 partitions).
In hash4/4, each of the 4 slots decoded every transaction: total_txns
per slot 1,592,727 to 1,595,815 (pg_stat_replication_slots, run a).
3. Bandwidth-limited link (proxy between subscriber and publisher)
------------------------------------------------------------------
link consumed MB/s walsender apply worker
8 MB/s 10.6 6% CPU, 17% CPU,
WalSenderWriteData LogicalApplyMain 82%
93%
16 MB/s 19.7 12% CPU, 29% CPU,
WalSenderWriteData LogicalApplyMain 73%
90%
Doubling the link gave 1.86x, because here the link was the limit.
4. Where the lag shows up in pg_stat_replication
------------------------------------------------
In every run above (apply-bound, walsender-bound, link-bound), 98% or
more of the unconfirmed WAL was between pg_current_wal_lsn and sent_lsn,
and sent_lsn - flush_lsn never exceeded 96 MB. A logical walsender
advances sent_lsn as it decodes and stops reading WAL while it cannot
send (walsender.c, XLogSendLogical and ProcessPendingWrites), so a slow
link or a slow subscriber also shows up as "not yet sent".
One transaction updating all 1M rows (253 MB decoded), subscriber caught
up before it: sent_lsn - flush_lsn peaked at 54 MB with
logical_decoding_work_mem=4GB (not streamed) and at 12 MB with 64MB
(streamed, stream_txns=1).
5. REPLICA IDENTITY FULL, primary key present on both sides (one run)
--------------------------------------------------------------------
Publisher idle, flat layout: 15,115 transactions/s applied, vs 15,060
and 15,527 with the default replica identity. Decoded data per
transaction 8,520 vs 5,060 bytes. In 16 the apply worker uses a usable
btree index on the subscriber for REPLICA IDENTITY FULL
(FindUsableIndexForReplicaIdentityFull, relation.c); without one it
scans the table for every updated row.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-26 21:26 Justin <zzzzz.graf@gmail.com>
parent: Manu <manuelreyesbravo@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Justin @ 2026-10-26 21:26 UTC (permalink / raw)
To: Manu <manuelreyesbravo@gmail.com>; +Cc: Durgamahesh Manne <maheshpostgres9@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>; Adrian Klaver <adrian.klaver@aklaver.com>
Hi Manu, and Durga,
We need to identify the cause of the slow replication, guessing and
throwing solutions at the wall to see what sticks only slows the process
down.
Durga, have you looked at what the publisher is sending to the subscriber?
What is the structure of the updates/deletes?
Inserts have little to no overhead for LR.
Do you have any table triggers with ENABLE REPLICA that are set on the
subscriber that are not on the primary?
Would be nice to see the DDL of the tables being published, and the
structure of the tables on the subscriber, including indexes and triggers.
Partitioning the tables: the answer is yes, because you can break each
partition into its own publication and subscription that allows for
parallelization. There are hackish ways around having to reseed the tables.
But if you change the publisher table structure, just change the subscriber
at the same time.
I doubt bandwidth is the problem unless PG is using a NAS/SAN to hold the
database and the subscriber is using a 1gb ethernet connection and all the
network traffic goes across it.
I do not recall seeing any conversation regarding the volume of data being
replicated; only 330 GB of WAL lag. Over what period of time does this lag
occur?
I've worked on Logical Replication where the servers generated 1TB of WAL
traffic a day. The trick in these conditions is identifying which tables
required their own publication and subscriber to prevent replication lag.
LR ignores unrelated WAL entries to what it is publishing, so is the entire
database being published over a single publication?
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-27 05:05 Durgamahesh Manne <maheshpostgres9@gmail.com>
parent: Justin <zzzzz.graf@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Durgamahesh Manne @ 2026-10-27 05:05 UTC (permalink / raw)
To: Justin <zzzzz.graf@gmail.com>; +Cc: Manu <manuelreyesbravo@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>; Adrian Klaver <adrian.klaver@aklaver.com>
On Tue, Oct 27, 2026 at 2:56 AM Justin <zzzzz.graf@gmail.com> wrote:
> Hi Manu, and Durga,
>
> We need to identify the cause of the slow replication, guessing and
> throwing solutions at the wall to see what sticks only slows the process
> down.
>
> Durga, have you looked at what the publisher is sending to the subscriber?
> What is the structure of the updates/deletes?
>
> Inserts have little to no overhead for LR.
>
> Do you have any table triggers with ENABLE REPLICA that are set on the
> subscriber that are not on the primary?
>
> Would be nice to see the DDL of the tables being published, and the
> structure of the tables on the subscriber, including indexes and triggers.
>
> Partitioning the tables: the answer is yes, because you can break each
> partition into its own publication and subscription that allows for
> parallelization. There are hackish ways around having to reseed the tables.
> But if you change the publisher table structure, just change the subscriber
> at the same time.
>
> I doubt bandwidth is the problem unless PG is using a NAS/SAN to hold the
> database and the subscriber is using a 1gb ethernet connection and all the
> network traffic goes across it.
>
> I do not recall seeing any conversation regarding the volume of data being
> replicated; only 330 GB of WAL lag. Over what period of time does this lag
> occur?
>
> I've worked on Logical Replication where the servers generated 1TB of WAL
> traffic a day. The trick in these conditions is identifying which tables
> required their own publication and subscriber to prevent replication lag.
>
> LR ignores unrelated WAL entries to what it is publishing, so is the
> entire database being published over a single publication?
>
Hi
Manu and justin
Entire database being published over a single publication. Each table has a
primary key to replicate the data efficiently rather than replica identity
full which generates huge wal generation.Over 23 tables only one table has
huge updates rest of tables have very few changes to replicate
Here 4 publications with a hash option on a single non partitioned table
which has huge update ops to distribute data over slot to subscriptions.
So LR uses workers efficiently to flush the data to disk in parallel at
target.
Need to check how this works over the weekend when data load increases
Need to create hash based partitions then create publications on individual
child tables to check how this works.
I think Justin might know the first approach.
which one would be good and work efficiently 1st or 2nd.
Regards,
Durga Mahesh
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Regarding huge logical slot lag between source and target
@ 2026-10-28 01:45 Manu <manuelreyesbravo@gmail.com>
parent: Durgamahesh Manne <maheshpostgres9@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Manu @ 2026-10-28 01:45 UTC (permalink / raw)
To: Durgamahesh Manne <maheshpostgres9@gmail.com>; +Cc: Justin <zzzzz.graf@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>; Adrian Klaver <adrian.klaver@aklaver.com>
Hi Durga,
On Tue, Oct 27, 2026 at 10:35 AM Durgamahesh Manne
<maheshpostgres9@gmail.com> wrote:
> which one would be good and work efficiently 1st or 2nd.
The first, the row filters you already have. In my lab they drained a
backlog at 1.9x and 2.0x the rate of one subscription, the partitions
at 1.8x and 1.9x, and they spare you rebuilding the table.
Both share two costs: each walsender decodes all the WAL before its
filter drops the other rows (together the four used about 5x the CPU
of one, for twice the drain), and a transaction that spans filters is
applied in pieces, not atomically.
If your filters use abs() on a hash, use (hashint8(id) & 3) = 0, 1, 2
and 3 instead: a row hashing to -2147483648 makes abs() fail and stops
all four.
As Justin says, check where the time goes first (CPU and wait_event as
in my last mail): splitting only helps if the apply worker is near
100% CPU.
With 4 CPU threads per side and two runs per case, take the ratios
rather than the rates. Details in the attached measurements.txt.
Regards,
Manu
Attachments:
measurements.txt (text/plain, 5.4K) download
Row filters vs hash partitions for 4 subscriptions -- PostgreSQL 16.15
=====================================================================
Same lab, table and workload as the measurements.txt of Oct 26: one
host, publisher + pgbench on 4 hardware threads, subscriber on another
4 (2 cores each), logical_decoding_work_mem=64MB, subscriptions with
binary=true, streaming=on. One hot table of 1M rows,
hot(id bigint PRIMARY KEY, k bigint, v bigint, pad text), 16 clients:
UPDATE hot SET k = k + 1, v = :a WHERE id BETWEEN :a AND :a + 19;
Absolute rates are only comparable within this file.
Layouts
- base: plain table, 1 publication, 1 subscription.
- option 1: plain table, 4 publications, 4 subscriptions, row filter
CREATE PUBLICATION pK FOR TABLE hot WHERE ((hashint8(id) & 3) = K);
with K = 0..3.
- option 2: hash partitioned in 4, one publication per partition,
4 subscriptions.
1. Drain rate with the publisher idle
-------------------------------------
30 s of load with the subscriptions disabled, then enabled with the
load stopped. Drain = advance of confirmed_flush_lsn of the slowest
slot, MB of WAL per second. CPU = % of one CPU, averaged while
draining. Two alternating rounds (a, b).
base, round a: 68.7 MB/s; walsender 30%; apply worker 97%
base, round b: 69.5 MB/s; walsender 30%; apply worker 99%
option 1, round a: 132.0 MB/s (1.92x); walsenders 162% in total,
busiest 42%; apply workers 364% in total, busiest 92%
option 1, round b: 136.7 MB/s (1.97x); walsenders 169%, busiest 43%;
apply workers 377%, busiest 95%
option 2, round a: 127.8 MB/s (1.86x); walsenders 149%, busiest 39%;
apply workers 382%, busiest 96%
option 2, round b: 124.7 MB/s (1.79x); walsenders 152%, busiest 39%;
apply workers 377%, busiest 95%
In all of them the apply workers stayed at 92-99% CPU and filled the
4 threads of the subscriber, so about 1.9x is the ceiling of this lab,
not of the method. Every run ended with the same row count and sum(k)
on both sides.
2. Drain with the publisher saturated
-------------------------------------
15 s of backlog, then the subscriptions are enabled while the same
load keeps running for 120 s. Round a started with the CPU lane
already 22% slower than idle (another process); round b was clean and
gave the same numbers.
base, round a: consumed 34.3 MB/s, WAL generated 211.0 MB/s,
48315 commits/s on the publisher
base, round b: consumed 32.7 MB/s, generated 220.9 MB/s, 50908/s
option 1, round a: consumed 66.1 MB/s, generated 155.2 MB/s, 36694/s
option 1, round b: consumed 66.1 MB/s, generated 156.1 MB/s, 36898/s
option 2, round a: consumed 53.9 MB/s, generated 96.9 MB/s, 21775/s
option 2, round b: consumed 52.2 MB/s, generated 102.5 MB/s, 22858/s
None of the three kept up: the backlog grew in every run. Here the
walsenders compete with the load for the same 4 threads.
3. Update rate on the publisher, no subscription
-----------------------------------------------
pgbench, 16 clients, 20 s, two alternating rounds (a, b), transactions
per second.
range updates (the workload above), plain: 55756, 55240
range updates, hash partitioned in 4: 31157, 30974 (0.56x)
(a range of ids cannot be pruned to one hash partition)
single-row updates by primary key, plain: 131553, 130161
UPDATE hot SET k = k + 1, v = :a WHERE id = :a;
single-row updates by primary key, partitioned: 103092, 102573 (0.79x)
4. Updates that change the primary key across filters (option 1)
---------------------------------------------------------------
100k rows, 4 publications with the row filter above, 4 subscriptions
into the same table on the target, a trigger (ENABLE ALWAYS) on the
target logging which subscription applied each change.
One transaction adding 10000000 to every id divisible by 1000: 100
rows, 70 of them land in another filter. Applied on the target: 72
DELETEs and 73 INSERTs spread over the 4 subscriptions (the 70 moves,
the single row below twice, one plain INSERT), and 31 UPDATEs.
A single row moved and moved back, in apply order:
DELETE id 1 via s2, INSERT id 20000001 via s1,
DELETE id 20000001 via s1, INSERT id 1 via s2.
Target and publisher identical at the end (count and md5 of all rows).
Every key value belongs to exactly one filter, so all changes to one
key go through the same subscription in order.
5. Row filter with abs()
------------------------
The same test with
WHERE (abs(hashint8(id)) % 4 = K)
and one row with id 963809240, whose hashint8 is -2147483648.
abs(-2147483648) raises "integer out of range". All 4 walsenders fail
on that row (each one evaluates its filter on every row), their
subscriptions reconnect and fail again: 28 errors in about 30 s,
all 4 slots stuck at the same LSN, the row never reaches the target.
Between 0 and 2^31-1 exactly two ids hash to it: 963809240, 1784257536.
With (hashint8(id) & 3) the same row replicates.
6. From the code (REL_16_STABLE 6ceac2e1250)
--------------------------------------------
Each walsender decodes every change of the database and queues it in
its reorder buffer (decode.c:956 only checks the database,
decode.c:1001). At commit each change is passed to pgoutput
(reorderbuffer.c:1989), which skips tables not in its publication
(pgoutput.c:1476, option 2) or evaluates the row filter
(pgoutput.c:1545, option 1). So with 4 subscriptions the decoding is
done 4 times in both options; only the last check differs.
An UPDATE whose old row matches the filter and new row does not is
sent as a DELETE, and the reverse as an INSERT (pgoutput.c:1408-1424).
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2026-10-28 01:45 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-18 14:19 Regarding huge logical slot lag between source and target Durgamahesh Manne <maheshpostgres9@gmail.com>
2026-10-18 14:53 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-18 15:23 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-18 17:22 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-10-18 17:41 ` Durgamahesh Manne <maheshpostgres9@gmail.com>
2026-10-18 17:46 ` Durgamahesh Manne <maheshpostgres9@gmail.com>
2026-10-18 18:45 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-10-18 18:43 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-10-18 19:05 ` Manu <manuelreyesbravo@gmail.com>
2026-10-18 21:36 ` Durgamahesh Manne <maheshpostgres9@gmail.com>
2026-10-19 11:43 ` Manu <manuelreyesbravo@gmail.com>
2026-10-19 13:35 ` Durgamahesh Manne <maheshpostgres9@gmail.com>
2026-10-19 13:49 ` Manu <manuelreyesbravo@gmail.com>
2026-10-19 14:27 ` Justin <zzzzz.graf@gmail.com>
2026-10-26 04:55 ` Durgamahesh Manne <maheshpostgres9@gmail.com>
2026-10-26 17:46 ` Manu <manuelreyesbravo@gmail.com>
2026-10-26 21:26 ` Justin <zzzzz.graf@gmail.com>
2026-10-27 05:05 ` Durgamahesh Manne <maheshpostgres9@gmail.com>
2026-10-28 01:45 ` Manu <manuelreyesbravo@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox