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
