Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xIWCD-00000003jsF-0iRS for pgsql-general@arkaria.postgresql.org; Sun, 18 Oct 2026 19:05:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xIWCB-00000003ora-0fPc for pgsql-general@arkaria.postgresql.org; Sun, 18 Oct 2026 19:05:23 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xIWCA-00000003orS-31C6 for pgsql-general@lists.postgresql.org; Sun, 18 Oct 2026 19:05:22 +0000 Received: from mail-dy1-x132c.google.com ([2607:f8b0:4864:20::132c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xIWC8-00000002gUv-1M6q for pgsql-general@lists.postgresql.org; Sun, 18 Oct 2026 19:05:21 +0000 Received: by mail-dy1-x132c.google.com with SMTP id 5a478bee46e88-363cd582f45so21049eec.0 for ; Sun, 18 Oct 2026 12:05:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1792350319; x=1792955119; darn=lists.postgresql.org; h=mime-version:content-transfer-encoding:content-type:references :in-reply-to:message-id:date:subject:cc:to:from:from:to:cc:subject :date:message-id:reply-to:content-type; bh=GyrLfF6twCF+3jyZBq2jy3Lu6trJ77Gf8OVxIDLlOko=; b=F4T7qC9a52duta/jn/0oqmXDuID5VCLqHLyqosEW5cV5F/Ap7xPxny/NU4NAx1jth6 EsiwovbFQb243mhhwaFLzPUu7cL7/JlbEZXDBWc3t8BoWh1gpBgn3r7Cx6anxTgPE7F8 phj8Nv4CGigkUqQmewpeh1RISu4OVIjrAP12d4nImNmAJT+ZoOGolLrv3KIZfQ1wlVJA dSrZkU/7NB0blikwf0ga+RnPSy9cKDcHaaa80iutUsej94ldYN5GjJVuqCLMo2x2YVCW cuflyqADxBGb4qCXSQ2J5bFV+LBCzJq0HaNKfW14nUtsgQuBdQY0d3QRLbfuaHacCEeN unTA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1792350319; x=1792955119; h=mime-version:content-transfer-encoding:content-type:references :in-reply-to:message-id:date:subject:cc:to:from:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to :content-type; bh=GyrLfF6twCF+3jyZBq2jy3Lu6trJ77Gf8OVxIDLlOko=; b=WKjzsI/tFJ1vaLvQFQqLT17lYlcjFSQ3pG0Dhtv6Ej5ffKJgsd2FFZC6elBoJSqUUo XVnMSWatB5c1bpG7EZUySxWmDYFmVPaYFTrL0lyTzy82uaMaDbbhGn7Z4dDwmeM3aL6z npjGO2ETfPOMX2vr2qgNk/CwzzgZ+gcBVYQqRRoPR6U5Hq4g84uRw/dwgaT2Wwjf1/WL IHi+QWkso71xnQcB0h84t0IGxqub8xujjERWBKZtcs30j1Hmmx7eDnYIxUDTu4DOjjhM uBctuCr7yug/VCI/p+vVBD+2HLfxfT4ZbZhJcj2dzy9P9/5GMdc7IIe9S1spO1pUN87c YUEg== X-Gm-Message-State: AFq9FYLfrb5BXEhrrwIyvHQXiKJSdmwuiSYy+Q48fuXFevz4ectMQMnd +KKeedZ/BlwYZp/CjnDbT1NQaCFzuygG6EQ/2yNUaANAtWgMafye/nOM X-Gm-Gg: AYBFou3Ya2lBlAqbmBP1wa34mm/nYnzJAoC3FGasw4vmKSUwIJcCTPeHHn1/j7A/aje nzvSn91fHWpL1FTzUrqETpPiGkjZrV2RhYz+YbaCEOKxwnUb95+e5uZhLK5L2oD7DR9k5t+BqmO Ugn74AIdL5SqRoQvCRQ5iLY6igjI1XHD9BAwdb5TO1AecQDQuvBBHIGEOCmViuw2FSsNIHR8aTs 645/T/5f5vYtTSmf5ykII1wnYAbJJObGAPY7ZoQfGIObmg99LXlSDcrSxe1Jy38Vw9X+XNm+QfE jNzT1rIwFBvLji85MPoMgANrNPKfft6EpnWYw/VwoGv1ojUjVQ+99T1AelWqZfg250wjDOs0QRM XgWD1YI2mmwrPu9WkHwFHjFiwXzqjj6D4rvnld1BwOu1wChWCut746V9RK1qdRKmFrhAmfnSMOI BJjLKzy5Pf4c5Dl0KBB6VtQC7/l8ZyI97+R9QfH20teelLb7FTDMSHQRU2u9LIQsQfgO0Dlvzoa ECjnM5LjADUV3NNghuryHBeAIApMWTykvmInj3R/45JxgXhcT1jqZYdqNKASb9BjICrvtkCrjc4 pTbviuTJUnQhnwOWPy6TlTzAggCgrX6P+LAh X-Received: by 2002:a05:7300:51e5:b0:35f:ea17:d171 with SMTP id 5a478bee46e88-361a107bbfemr14650320eec.2.1792350318252; Sun, 18 Oct 2026 12:05:18 -0700 (PDT) Received: from 192.168.1.109 ([2803:3c90:cee:ea00:5c3c:7e70:29e3:7a55]) by smtp.gmail.com with ESMTPSA id 5a478bee46e88-360826875b3sm27301713eec.21.2026.10.18.12.05.16 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Sun, 18 Oct 2026 12:05:17 -0700 (PDT) From: Manu To: Durgamahesh Manne Cc: pgsql-general , Laurenz Albe , Adrian Klaver Subject: Re: Regarding huge logical slot lag between source and target Date: Sun, 18 Oct 2026 16:05:11 -0300 Message-ID: <179235031169.464394.79170838645883572@gmail.com> In-Reply-To: References: Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 =3D f) does the same: it keeps every byte of WAL after its restart_lsn with no consumer at all. - streaming =3D 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_l= sn)) 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_confi= rmed, 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