MIME-Version: 1.0
References: <CAB-JLwbBFNuASyEnZWP0Tck9uNkthBZqi6WoXNevUT6+mV8XmA@mail.gmail.com>
 <CAD21AoA6i2ui8FMZeuU_KxX4t-fM8G==zTW2Dp6-goujttrpew@mail.gmail.com> <CAB-JLwZpp=7c9_r0beWWJxRh2BS_2Vvth8UDv7H57DBeaqggVg@mail.gmail.com>
In-Reply-To: <CAB-JLwZpp=7c9_r0beWWJxRh2BS_2Vvth8UDv7H57DBeaqggVg@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Mon, 26 Jan 2026 12:30:03 -0800
Message-ID: <CAD21AoDT3sL2COprsRumM9zEpL1Bk5VWboK4V2mRnjGua8xfeA@mail.gmail.com>
Subject: Re: Initial COPY of Logical Replication is too slow
To: Marcos Pegoraro <marcos@f10.com.br>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="0000000000006b0b000649506120"
Archived-At: <https://www.postgresql.org/message-id/CAD21AoDT3sL2COprsRumM9zEpL1Bk5VWboK4V2mRnjGua8xfeA%40mail.gmail.com>
Precedence: bulk

--0000000000006b0b000649506120
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Jan 19, 2026 at 9:44=E2=80=AFAM Marcos Pegoraro <marcos@f10.com.br>=
 wrote:
>
> Em sex., 19 de dez. de 2025 =C3=A0s 22:59, Masahiko Sawada <sawada.mshk@g=
mail.com> escreveu:
>>
>> Yeah, if we pass a publication that a lot of tables belong to to
>> pg_get_publication_tables(), it could take a long time to return as it
>> needs to construct many entries.
>
>
> Well, I don't know how to help but I'm sure it's working badly.
> Today I added some fields on my server, then seeing logs I could see how =
slow this process is.
>
> duration: 2213.872 ms  statement: SELECT DISTINCT  (CASE WHEN (array_leng=
th(gpt.attrs, 1) =3D c.relnatts)   THEN NULL ELSE gpt.attrs END)  FROM pg_p=
ublication p,  LATERAL pg_get_publication_tables(p.pubname) gpt,  pg_class =
c WHERE gpt.relid =3D 274376788 AND c.oid =3D gpt.relid   AND p.pubname IN =
( 'mypub' )
>
> 2 seconds to get the list of fields of a table is really too slow.
> How can we solve this ?

After more investigation of slowness, it seems that the
list_concat_unique_oid() called below is quite slow when the database
has a lot of tables to publish:

    relids =3D GetPublicationRelations(pub_elem->oid,
                                     pub_elem->pubviaroot ?
                                     PUBLICATION_PART_ROOT :
                                     PUBLICATION_PART_LEAF);
    schemarelids =3D GetAllSchemaPublicationRelations(pub_elem->oid,
                                                     pub_elem->pubviaroot ?
                                                     PUBLICATION_PART_ROOT =
:
                                                     PUBLICATION_PART_LEAF)=
;
    pub_elem_tables =3D list_concat_unique_oid(relids, schemarelids);

This is simply because it's O(n^2), where n is the number of oids in
schemarelids in the test case. A simple change would be to do sort &
dedup instead. With the attached experimental patch, the
pg_get_publication_tables() execution time gets halved in my
environment (796ms -> 430ms with 50k tables). If the number of tables
is not large, this method might be slower than today but it's not a
huge regression.

In the initial tablesync cases, it could be optimized further in a way
that we introduce a new SQL function that gets the column list and
expr of the specific table. This way, we can filter the result by
relid at an early stage instead of getting all information and
filtering by relid as the tablesync worker does today, avoiding
overheads of gathering system catalog scan results.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

--0000000000006b0b000649506120
Content-Type: application/octet-stream; name="sort_and_dedup.patch"
Content-Disposition: attachment; filename="sort_and_dedup.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mkvm61950>
X-Attachment-Id: f_mkvm61950

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NhdGFsb2cvcGdfcHVibGljYXRpb24uYyBiL3NyYy9i
YWNrZW5kL2NhdGFsb2cvcGdfcHVibGljYXRpb24uYwppbmRleCA5YTQ3OTFjNTczZS4uMmVhM2Q4
ZGQ5YTggMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL2NhdGFsb2cvcGdfcHVibGljYXRpb24uYwor
KysgYi9zcmMvYmFja2VuZC9jYXRhbG9nL3BnX3B1YmxpY2F0aW9uLmMKQEAgLTExODMsNyArMTE4
MywxMCBAQCBwZ19nZXRfcHVibGljYXRpb25fdGFibGVzKFBHX0ZVTkNUSU9OX0FSR1MpCiAJCQkJ
CQkJCQkJCQkJCQkJcHViX2VsZW0tPnB1YnZpYXJvb3QgPwogCQkJCQkJCQkJCQkJCQkJCVBVQkxJ
Q0FUSU9OX1BBUlRfUk9PVCA6CiAJCQkJCQkJCQkJCQkJCQkJUFVCTElDQVRJT05fUEFSVF9MRUFG
KTsKLQkJCQlwdWJfZWxlbV90YWJsZXMgPSBsaXN0X2NvbmNhdF91bmlxdWVfb2lkKHJlbGlkcywg
c2NoZW1hcmVsaWRzKTsKKworCQkJCXB1Yl9lbGVtX3RhYmxlcyA9IGxpc3RfY29uY2F0KHJlbGlk
cywgc2NoZW1hcmVsaWRzKTsKKwkJCQlsaXN0X3NvcnQocHViX2VsZW1fdGFibGVzLCBsaXN0X29p
ZF9jbXApOworCQkJCWxpc3RfZGVkdXBsaWNhdGVfb2lkKHB1Yl9lbGVtX3RhYmxlcyk7CiAJCQl9
CiAKIAkJCS8qCg==
--0000000000006b0b000649506120--