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
