public inbox for [email protected]
help / color / mirror / Atom feedFrom: Marcos Pegoraro <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: Initial COPY of Logical Replication is too slow
Date: Sat, 6 Dec 2025 09:18:41 -0300
Message-ID: <CAB-JLwbBFNuASyEnZWP0Tck9uNkthBZqi6WoXNevUT6+mV8XmA@mail.gmail.com> (raw)
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
Attachments:
[application/octet-stream] V1-Initial COPY of Logical Replication.diff (2.6K, 3-V1-Initial%20COPY%20of%20Logical%20Replication.diff)
download | inline diff:
From e2fdff4f13e05a5e911a2ab0ce5c386f795dace8 Mon Sep 17 00:00:00 2001
From: PegoraroF10 <[email protected]>
Date: Sat, 6 Dec 2025 08:54:05 -0300
Subject: [PATCH] Function pg_get_publication_tables is too slow, changed to
pg_publication_rel and pg_publication
---
src/backend/replication/logical/tablesync.c | 32 ++++++++++-----------
1 file changed, 15 insertions(+), 17 deletions(-)
diff --git a/src/backend/replication/logical/tablesync.c b/src/backend/replication/logical/tablesync.c
index 6bb0cbeedad..1ba8261308c 100644
--- a/src/backend/replication/logical/tablesync.c
+++ b/src/backend/replication/logical/tablesync.c
@@ -799,16 +799,14 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
*/
resetStringInfo(&cmd);
appendStringInfo(&cmd,
- "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 = %u AND c.oid = gpt.relid"
- " AND p.pubname IN ( %s )",
- lrel->remoteid,
- pub_names->data);
+ "SELECT CASE WHEN cardinality(r.prattrs) <> relnatts THEN "
+ "r.prattrs END FROM pg_class c "
+ "LEFT JOIN LATERAL (SELECT DISTINCT prattrs FROM "
+ "pg_publication_rel r INNER JOIN pg_publication p "
+ "ON p.oid = r.prpubid WHERE c.oid = r.prrelid AND "
+ "pubname in ( %s )) r ON TRUE WHERE c.oid = %u",
+ pub_names->data,
+ lrel->remoteid);
pubres = walrcv_exec(LogRepWorkerWalRcvConn, cmd.data,
lengthof(attrsRow), attrsRow);
@@ -983,13 +981,13 @@ fetch_remote_table_info(char *nspname, char *relname, LogicalRepRelation *lrel,
/* Check for row filters. */
resetStringInfo(&cmd);
appendStringInfo(&cmd,
- "SELECT DISTINCT pg_get_expr(gpt.qual, gpt.relid)"
- " FROM pg_publication p,"
- " LATERAL pg_get_publication_tables(p.pubname) gpt"
- " WHERE gpt.relid = %u"
- " AND p.pubname IN ( %s )",
- lrel->remoteid,
- pub_names->data);
+ "SELECT pg_get_expr(r.prqual, r.prrelid) FROM pg_class c "
+ "LEFT JOIN LATERAL (SELECT DISTINCT prqual, prrelid FROM "
+ "pg_publication_rel r INNER JOIN pg_publication p ON "
+ "p.oid = r.prpubid WHERE r.prrelid = c.oid AND "
+ "p.pubname IN ( %s )) r ON TRUE WHERE c.oid = %u",
+ pub_names->data,
+ lrel->remoteid);
res = walrcv_exec(LogRepWorkerWalRcvConn, cmd.data, 1, qualRow);
--
2.51.0.windows.1
view thread (47+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Initial COPY of Logical Replication is too slow
In-Reply-To: <CAB-JLwbBFNuASyEnZWP0Tck9uNkthBZqi6WoXNevUT6+mV8XmA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox