MIME-Version: 1.0
From: Marcos Pegoraro <marcos@f10.com.br>
Date: Sat, 6 Dec 2025 09:18:41 -0300
Message-ID: 
 <CAB-JLwbBFNuASyEnZWP0Tck9uNkthBZqi6WoXNevUT6+mV8XmA@mail.gmail.com>
Subject: Initial COPY of Logical Replication is too slow
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000324d5a064547928f"
Archived-At: 
 <https://www.postgresql.org/message-id/CAB-JLwbBFNuASyEnZWP0Tck9uNkthBZqi6WoXNevUT6%2BmV8XmA%40mail.gmail.com>
Precedence: bulk

--000000000000324d5a064547928f
Content-Type: multipart/alternative; boundary="000000000000324d59064547928d"

--000000000000324d59064547928d
Content-Type: text/plain; charset="UTF-8"

Subscriber needs to ask publisher about tables and fields to COPY and it
uses pg_get_publication_tables for that, and it is too slow when the number
of tables is high because on every table it's subscribed it has to run this
select.
We can get the same result with a join on pg_publication_rel.

regards
Marcos

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

<div dir=3D"ltr"><div class=3D"gmail_default" style=3D"font-family:verdana,=
sans-serif;color:rgb(102,102,102)">Subscriber needs to ask publisher about =
tables and fields to COPY and it uses pg_get_publication_tables for that, a=
nd it is too slow when the number of tables is high because on every=C2=A0t=
able it&#39;s subscribed=C2=A0it has to run this select.=C2=A0</div><div cl=
ass=3D"gmail_default" style=3D"font-family:verdana,sans-serif;color:rgb(102=
,102,102)">We can get the same result with a join on pg_publication_rel.</d=
iv><div class=3D"gmail_default" style=3D"font-family:verdana,sans-serif;col=
or:rgb(102,102,102)"><br></div><div class=3D"gmail_default" style=3D"font-f=
amily:verdana,sans-serif;color:rgb(102,102,102)">regards</div><div class=3D=
"gmail_default" style=3D"font-family:verdana,sans-serif;color:rgb(102,102,1=
02)">Marcos</div></div>

--000000000000324d59064547928d--

--000000000000324d5a064547928f
Content-Type: application/octet-stream;
	name="V1-Initial COPY of Logical Replication.diff"
Content-Disposition: attachment;
	filename="V1-Initial COPY of Logical Replication.diff"
Content-Transfer-Encoding: base64
Content-ID: <f_miu99u2r0>
X-Attachment-Id: f_miu99u2r0

RnJvbSBlMmZkZmY0ZjEzZTA1YTVlOTExYTJhYjBjZTVjMzg2Zjc5NWRhY2U4IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBQZWdvcmFyb0YxMCA8bWFyY29zQGYxMC5jb20uYnI+CkRhdGU6
IFNhdCwgNiBEZWMgMjAyNSAwODo1NDowNSAtMDMwMApTdWJqZWN0OiBbUEFUQ0hdIEZ1bmN0aW9u
IHBnX2dldF9wdWJsaWNhdGlvbl90YWJsZXMgaXMgdG9vIHNsb3csIGNoYW5nZWQgdG8KIHBnX3B1
YmxpY2F0aW9uX3JlbCBhbmQgcGdfcHVibGljYXRpb24KCi0tLQogc3JjL2JhY2tlbmQvcmVwbGlj
YXRpb24vbG9naWNhbC90YWJsZXN5bmMuYyB8IDMyICsrKysrKysrKystLS0tLS0tLS0tLQogMSBm
aWxlIGNoYW5nZWQsIDE1IGluc2VydGlvbnMoKyksIDE3IGRlbGV0aW9ucygtKQoKZGlmZiAtLWdp
dCBhL3NyYy9iYWNrZW5kL3JlcGxpY2F0aW9uL2xvZ2ljYWwvdGFibGVzeW5jLmMgYi9zcmMvYmFj
a2VuZC9yZXBsaWNhdGlvbi9sb2dpY2FsL3RhYmxlc3luYy5jCmluZGV4IDZiYjBjYmVlZGFkLi4x
YmE4MjYxMzA4YyAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvcmVwbGljYXRpb24vbG9naWNhbC90
YWJsZXN5bmMuYworKysgYi9zcmMvYmFja2VuZC9yZXBsaWNhdGlvbi9sb2dpY2FsL3RhYmxlc3lu
Yy5jCkBAIC03OTksMTYgKzc5OSwxNCBAQCBmZXRjaF9yZW1vdGVfdGFibGVfaW5mbyhjaGFyICpu
c3BuYW1lLCBjaGFyICpyZWxuYW1lLCBMb2dpY2FsUmVwUmVsYXRpb24gKmxyZWwsCiAJCSAqLwog
CQlyZXNldFN0cmluZ0luZm8oJmNtZCk7CiAJCWFwcGVuZFN0cmluZ0luZm8oJmNtZCwKLQkJCQkJ
CSAiU0VMRUNUIERJU1RJTkNUIgotCQkJCQkJICIgIChDQVNFIFdIRU4gKGFycmF5X2xlbmd0aChn
cHQuYXR0cnMsIDEpID0gYy5yZWxuYXR0cykiCi0JCQkJCQkgIiAgIFRIRU4gTlVMTCBFTFNFIGdw
dC5hdHRycyBFTkQpIgotCQkJCQkJICIgIEZST00gcGdfcHVibGljYXRpb24gcCwiCi0JCQkJCQkg
IiAgTEFURVJBTCBwZ19nZXRfcHVibGljYXRpb25fdGFibGVzKHAucHVibmFtZSkgZ3B0LCIKLQkJ
CQkJCSAiICBwZ19jbGFzcyBjIgotCQkJCQkJICIgV0hFUkUgZ3B0LnJlbGlkID0gJXUgQU5EIGMu
b2lkID0gZ3B0LnJlbGlkIgotCQkJCQkJICIgICBBTkQgcC5wdWJuYW1lIElOICggJXMgKSIsCi0J
CQkJCQkgbHJlbC0+cmVtb3RlaWQsCi0JCQkJCQkgcHViX25hbWVzLT5kYXRhKTsKKyAgICAgICAg
ICAgICAgICAgICAgIAkiU0VMRUNUIENBU0UgV0hFTiBjYXJkaW5hbGl0eShyLnByYXR0cnMpIDw+
IHJlbG5hdHRzIFRIRU4gIgorCQkJCQkJInIucHJhdHRycyBFTkQgRlJPTSBwZ19jbGFzcyBjICIK
KwkJCQkJCSJMRUZUIEpPSU4gTEFURVJBTCAoU0VMRUNUIERJU1RJTkNUIHByYXR0cnMgRlJPTSAi
CisJCQkJCQkicGdfcHVibGljYXRpb25fcmVsIHIgSU5ORVIgSk9JTiBwZ19wdWJsaWNhdGlvbiBw
ICIKKwkJCQkJCSJPTiBwLm9pZCA9IHIucHJwdWJpZCBXSEVSRSBjLm9pZCA9IHIucHJyZWxpZCBB
TkQgIgorCQkJCQkJInB1Ym5hbWUgaW4gKCAlcyApKSByIE9OIFRSVUUgV0hFUkUgYy5vaWQgPSAl
dSIsCisJCQkJCQkgcHViX25hbWVzLT5kYXRhLAorCQkJCQkJIGxyZWwtPnJlbW90ZWlkKTsKIAog
CQlwdWJyZXMgPSB3YWxyY3ZfZXhlYyhMb2dSZXBXb3JrZXJXYWxSY3ZDb25uLCBjbWQuZGF0YSwK
IAkJCQkJCQkgbGVuZ3Rob2YoYXR0cnNSb3cpLCBhdHRyc1Jvdyk7CkBAIC05ODMsMTMgKzk4MSwx
MyBAQCBmZXRjaF9yZW1vdGVfdGFibGVfaW5mbyhjaGFyICpuc3BuYW1lLCBjaGFyICpyZWxuYW1l
LCBMb2dpY2FsUmVwUmVsYXRpb24gKmxyZWwsCiAJCS8qIENoZWNrIGZvciByb3cgZmlsdGVycy4g
Ki8KIAkJcmVzZXRTdHJpbmdJbmZvKCZjbWQpOwogCQlhcHBlbmRTdHJpbmdJbmZvKCZjbWQsCi0J
CQkJCQkgIlNFTEVDVCBESVNUSU5DVCBwZ19nZXRfZXhwcihncHQucXVhbCwgZ3B0LnJlbGlkKSIK
LQkJCQkJCSAiICBGUk9NIHBnX3B1YmxpY2F0aW9uIHAsIgotCQkJCQkJICIgIExBVEVSQUwgcGdf
Z2V0X3B1YmxpY2F0aW9uX3RhYmxlcyhwLnB1Ym5hbWUpIGdwdCIKLQkJCQkJCSAiIFdIRVJFIGdw
dC5yZWxpZCA9ICV1IgotCQkJCQkJICIgICBBTkQgcC5wdWJuYW1lIElOICggJXMgKSIsCi0JCQkJ
CQkgbHJlbC0+cmVtb3RlaWQsCi0JCQkJCQkgcHViX25hbWVzLT5kYXRhKTsKKyAgICAgICAgICAg
ICAgICAJCSJTRUxFQ1QgcGdfZ2V0X2V4cHIoci5wcnF1YWwsIHIucHJyZWxpZCkgRlJPTSBwZ19j
bGFzcyBjICIKKwkJCQkJCSJMRUZUIEpPSU4gTEFURVJBTCAoU0VMRUNUIERJU1RJTkNUIHBycXVh
bCwgcHJyZWxpZCBGUk9NICIKKwkJCQkJCSJwZ19wdWJsaWNhdGlvbl9yZWwgciBJTk5FUiBKT0lO
IHBnX3B1YmxpY2F0aW9uIHAgT04gIgorCQkJCQkJInAub2lkID0gci5wcnB1YmlkIFdIRVJFIHIu
cHJyZWxpZCA9IGMub2lkIEFORCAiCisJCQkJCQkicC5wdWJuYW1lIElOICggJXMgKSkgciBPTiBU
UlVFIFdIRVJFIGMub2lkID0gJXUiLAorCQkJCQkJIHB1Yl9uYW1lcy0+ZGF0YSwKKwkJCQkJCSBs
cmVsLT5yZW1vdGVpZCk7CiAKIAkJcmVzID0gd2FscmN2X2V4ZWMoTG9nUmVwV29ya2VyV2FsUmN2
Q29ubiwgY21kLmRhdGEsIDEsIHF1YWxSb3cpOwogCi0tIAoyLjUxLjAud2luZG93cy4xCgo=
--000000000000324d5a064547928f--