Initial COPY of Logical Replication is too slow

You can see how much time a subscriber will need to get all files which
were added with this.
Run first time and will create 10 thousand tables, publish them and measure
how much time to get all tables Ready on pg_subscription_rel.
Run again to add more 10 thousand tables and see that time will increase,
more tables and more time.

This is just to show that if you create a subscription with a high number
of tables it spends more time doing select on pg_get_publication_tables
than the time spent actually copying. My use case I have 50 thousand
tables, and it takes 5 seconds every time it needs to get next table to
copy.

--Create a empty publication
create publication my_pub;

--Run these 3 following anonymous blocks to create schemas, tables and add
them to publication.
--Need to have 3 blocks because I cannot create a table in a schema that is
not committed. And the same for a publication.
do $$ declare Schemas_Add integer = 100; Actual_Schema text; begin
for Actual_Schema in select 'test_'||(select
to_char(coalesce(max(substring(nspname,'test_(\d+)')::integer),0)+g,'FM00000')
from pg_namespace where nspname ~
'test_\d+') from generate_series(1,Schemas_Add) g loop
execute format('create schema %s',Actual_Schema);
end loop;
end;$$;

do $$ declare Tables_Add integer = 100; Actual_Schema text; begin
for Actual_Schema in select nspname from pg_namespace where nspname ~
'test_\d+' and
not exists(select from pg_class where relnamespace =
pg_namespace.oid) loop
for j in 1..Tables_Add loop
execute format('create table %s.test_%s as select
generate_series(1,random(0,10))::integer id;',
Actual_Schema,to_char(j,'FM00000'));
end loop;
end loop;
end;$$;

do $$ declare Schemas_To_Add text = (select string_agg(nspname,',') from
pg_namespace n where nspname ~ 'test_\d+' and
not exists(select from
pg_publication_namespace where pnnspid = n.oid)); begin
execute format('alter publication my_pub add tables in schema
%s;',Schemas_To_Add);
end;$$;

--Then you can see what was generated and go to the subscriber side to
refresh the subscription and measure time spent to synchronize.
select * from pg_Namespace where nspname ~ 'test_\d+';
select pnnspid::regnamespace, * from pg_publication_namespace;
select oid::regclass, * from pg_Class where
relnamespace::regnamespace::text ~ 'test_\d+' and relkind = 'r';

--Later just clean what you do.
drop publication my_pub;

do $$ declare Schema_Drop text; begin
for Schema_Drop in select nspname from pg_Namespace where nspname ~
'test_\d+' loop
execute format ('drop schema %s cascade;',Schema_Drop);
end loop;
end;$$;

regards
Marcos

Hi,

On Sat, Dec 6, 2025 at 4:19 AM Marcos Pegoraro <marcos@f10.com.br> wrote:

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.

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.

We can get the same result with a join on pg_publication_rel.

You changed the query not to use pg_get_publication_tables():

-                                                "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);

Simply replacing pg_get_publication_tables() with joining on
pg_publication_rel doesn't work since pg_get_publication_tables()
cares for several cases, for example where the specified columns are
generated columns and the specified table is a partitioned table etc.
Therefore the patch doesn't pass the regression tests.

I think it would make more sense to introduce a dedicated SQL function
that takes the reloid as well as the list of publications and returns
the relation's the column list and row filter expression while
filtering unnecessary rows inside the function.

Regards,

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