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 1xIlmb-00000003smm-0Jn4 for pgsql-general@arkaria.postgresql.org; Mon, 19 Oct 2026 11:44:01 +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 1xIlmZ-00000006ixF-35Rz for pgsql-general@arkaria.postgresql.org; Mon, 19 Oct 2026 11:43:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xIlmZ-00000006ix5-1fyQ for pgsql-general@lists.postgresql.org; Mon, 19 Oct 2026 11:43:59 +0000 Received: from mail-pj1-x102e.google.com ([2607:f8b0:4864:20::102e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1xIlmV-00000002yF6-3nGw for pgsql-general@lists.postgresql.org; Mon, 19 Oct 2026 11:43:58 +0000 Received: by mail-pj1-x102e.google.com with SMTP id 98e67ed59e1d1-3ac7a053626so427105a91.0 for ; Mon, 19 Oct 2026 04:43:55 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1792410234; x=1793015034; 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=vod0z7Lctn6uRB24XJ4EOJYSCyDcw7l46g1ZO5aIqdU=; b=LOScNi1Bh7B5xOK9w58mk1C7Q0hEsrjjaUQgTCqzBuH/1rAH5hWxjQgPx9Qiw7DY0t dLBUo1zSMJnF1/jZox94RMMHEm8mZr7Ic9dF7aJ6B5eWvore4SVitUbUgypXPaljr85J iuybUiw6DaAkBZvvwUU8e/1JkxeTp2QGcraLGnFpy/Is4Lxj5ZUgLJttVrETCGyPa+Tp GAcDUr1cggwf648z/KjtfkMDFwp6L2wRFEh4Xut5gC3xs6foQQ9XuPt9uKCDhUjrErJa gPdzgdPAiwvFsDZeQgaZKoEUuQQAo/gbEcdhcjmidva+00BhDbqkPjG61vrojkD/cp9n g9qw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20260707; t=1792410234; x=1793015034; 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=vod0z7Lctn6uRB24XJ4EOJYSCyDcw7l46g1ZO5aIqdU=; b=o+zMGM7K0l0pjbCYSKevHd5qUODZx1FRTzV6+IHBNfLE/IJPMhh2iB+QavF+5xVWCy Oj3rdegU1yuRHWqUPezJwp9uPcVjt3DVSLJA4yoh409FXj6tGS6tba+pcmJNq86TaF47 Am2yScPo8FJhmoPrAEJ0LjISMZ4fv8lxXYfVewlKDicCwItNB7OrYT/jesAC1WJU4GMR 3ndnu2PvBpu/PxsnDT+RheL0ZcriAsECnlbmede1Rwp4DacsrOQ/XbfaQHijglfmZJ0P l4tAyHeQMaebZdbw1zeLdEtJ2h5CV4Kd6JxZzk1KYfvK90XRl1ZU6sT1AdxH/hWYRHgQ mW3Q== X-Gm-Message-State: AFq9FYL5uincIlgNZU1uSBNA54i+7MxB3qtL3wwmesdh4m3bnqYN08d0 sSz5axV4uOpBwIPMQHQTDOJD+k3mG5T8kVM9QOtyiM4PvJN/xtCu38QT3rf/YhCr X-Gm-Gg: AYBFou3SISvOW7hQmB/k6aFEBwXb+rgm1RriXTGL4mtNIhlfCgirRVG+GY4jYXw6fXP cROenhDy1mGuewOeLZ1ZLae7fwV6w5r+3fcpn+AX+GTnOqSwehAIA+8f29PCwztN05wV4z/61U5 XzQOPgjmFkKcJtFjCoH3xAincpUjXBJ657SG69eMB1w1zOsP9GOQNKDR1IwrOsviCVKQiuh1beT TwRWLSotE+lhnOfd5k8If2LXnVo7f/SVytysu/RzGF4H5aut1/Ha6OHQyvEKLSVp5BzdTmSPCud q6FIm+YzpohBIBzNu+OMY8gLoDZapPUA95EkbOPtu5Dn0VPIGcGbgmqFXrUE0H1GkwLEk9mz+Rm K0KXaEBkRPsbtiByzfWr0OMvrsxsnh/uivTnwoLRBbzSFfRPs3Fb1fGUBj+IKZaK2qQU4+koTEo OOD5CUiM+clQykZJRnsx5pV1/mrhW3YUVsjj6UDbC7gr7BUl5GtBdvnDA0K399Vo5NqtQvyPv8x /IJAfjvZisSt+t5hutCZn87wGM0aSCBtglSvylYAnPwTLsx4nhatOMkxNj8P+HL/875YM7irRwf z+8Cw+aCl+obwO7nyfH8Y/X4Nw== X-Received: by 2002:a17:90b:164b:b0:3aa:ff60:9bb8 with SMTP id 98e67ed59e1d1-3acef167affmr10582524a91.3.1792410233341; Mon, 19 Oct 2026 04:43:53 -0700 (PDT) Received: from 192.168.1.109 ([2803:3c90:cee:ea00:5c3c:7e70:29e3:7a55]) by smtp.gmail.com with ESMTPSA id 5a478bee46e88-36082a7b33bsm58707161eec.24.2026.10.19.04.43.51 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Mon, 19 Oct 2026 04:43:52 -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: Mon, 19 Oct 2026 08:43:48 -0300 Message-ID: <179241022816.412020.16417709797375727622@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, > 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 ~=3D 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=3Dparallel will not help. In PG= 16 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=3D4). 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=3Dversion+1" counter is about a fifth of yo= ur 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 sti= ll 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