On Mon, 19 Oct, 2026, 00:35 Manu, <[email protected]> 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
