MIME-Version: 1.0
From: "Crimean (gmail)" <crimean.celica@gmail.com>
Date: Fri, 5 Jun 2026 12:11:19 +0200
Message-ID: 
 <CACq0ybaVS8v2ouZfSJuSHX9bvgt=sJkwj=OYTzuv9UVxJN95fw@mail.gmail.com>
Subject: Logical replication initialization time depends dramatically on the
 publication "schema" size
To: pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000077144d06537ee27b"
Archived-At: 
 <https://www.postgresql.org/message-id/CACq0ybaVS8v2ouZfSJuSHX9bvgt%3DsJkwj%3DOYTzuv9UVxJN95fw%40mail.gmail.com>
Precedence: bulk

--00000000000077144d06537ee27b
Content-Type: text/plain; charset="UTF-8"

Hello,

I would like to report a scalability issue I observed during logical
replication initialization.

The problem is not that replication fails or behaves incorrectly. However,
initialization time appears to increase significantly as the number of
tables in a publication grows.

While investigating, I noticed the following query being executed:

SELECT DISTINCT
       (CASE WHEN (array_length(gpt.attrs, 1) = c.relnatts)
             THEN NULL
             ELSE gpt.attrs
        END)
FROM pg_publication p,
     LATERAL pg_get_publication_tables(p.pubname) gpt,
     pg_class c
WHERE gpt.relid = 236073
  AND c.oid = gpt.relid
  AND p.pubname IN ('p1');

According to AWS Performance Insights, this query shows an average latency
of more than 130 ms and approximately 113,799 buffer hits per execution
when the publication contains around 10,000 tables.

As a result, the subscription initialization rate degrades from roughly 28
tables/second with 500 tables in the publication to about 8 tables/second
with 10,000 tables.

In my environment, replication itself works correctly, but initialization
time increases substantially as publication size grows. Unfortunately, some
of my deployments contain very large schemas, with over 1 million tables on
the publisher side, making this behavior a practical problem.

I can provide PostgreSQL version information, execution plans, and
reproduction details if needed.

Thank you for your time.

Best regards,
Sergii

--00000000000077144d06537ee27b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><p class=3D"gmail-isSelectedEnd">Hello,</p><p class=3D"gma=
il-isSelectedEnd">I would like to report a scalability issue I observed dur=
ing logical replication initialization.</p><p class=3D"gmail-isSelectedEnd"=
>The problem is not that replication fails or behaves incorrectly. However,=
 initialization time appears to increase significantly as the number of tab=
les in a publication grows.</p><p class=3D"gmail-isSelectedEnd">While inves=
tigating, I noticed the following query being executed:</p><pre dir=3D"ltr"=
><code dir=3D"ltr">SELECT DISTINCT
       (CASE WHEN (array_length(gpt.attrs, 1) =3D c.relnatts)
             THEN NULL
             ELSE gpt.attrs
        END)
FROM pg_publication p,
     LATERAL pg_get_publication_tables(p.pubname) gpt,
     pg_class c
WHERE gpt.relid =3D 236073
  AND c.oid =3D gpt.relid
  AND p.pubname IN (&#39;p1&#39;);</code></pre><p class=3D"gmail-isSelected=
End">According to AWS Performance Insights, this query shows an average lat=
ency of more than 130 ms and approximately 113,799 buffer hits per executio=
n when the publication contains around 10,000 tables.</p><p class=3D"gmail-=
isSelectedEnd">As a result, the subscription initialization rate degrades f=
rom roughly 28 tables/second with 500 tables in the publication to about 8 =
tables/second with 10,000 tables.</p><p class=3D"gmail-isSelectedEnd">In my=
 environment, replication itself works correctly, but initialization time i=
ncreases substantially as publication size grows. Unfortunately, some of my=
 deployments contain very large schemas, with over 1 million tables on the =
publisher side, making this behavior a practical problem.</p><p class=3D"gm=
ail-isSelectedEnd">I can provide PostgreSQL version information, execution =
plans, and reproduction details if needed.</p><p class=3D"gmail-isSelectedE=
nd">Thank you for your time.</p><p>Best regards,<br>Sergii</p></div>

--00000000000077144d06537ee27b--