RE: Initial COPY of Logical Replication is too slow

From: "Hayato Kuroda (Fujitsu)" <kuroda(dot)hayato(at)fujitsu(dot)com>
To: 'Masahiko Sawada' <sawada(dot)mshk(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Cc: Jan Wieck <jan(at)wi3ck(dot)info>, "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: RE: Initial COPY of Logical Replication is too slow
Date: 2026-03-26 12:46:27
Message-ID: OS9PR01MB12149620E338A567BF8FBD14CF556A@OS9PR01MB12149.jpnprd01.prod.outlook.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

> I ran the performance testing independently for the 0001 patch. Overall
> performance looked
> very nice, new function spent O(1) time based on the total number of tables.
> It seems good enough.

...and I tested 0002 as well with the same settings, and the trend was the same as 0001.

Both 0001 and 0002 were applied and below SQL was run, which was same was what
tablesync worker would try:

```
SELECT DISTINCT
(CASE WHEN (array_length(gpt.attrs, 1) = c.relnatts)
THEN NULL ELSE gpt.attrs END)
FROM pg_get_publication_tables(ARRAY['pub'], 16535) gpt,
pg_class c
WHERE c.oid = gpt.relid;
```

And below is the result. Each cell shows the execution time of the SQL. HEAD
column is the case when [1] was done. 0001 column is the case for [2].
Looks like the SQL used by 0002 looks slightly faster, which is same as the
expectation. JOIN was removed once.

Total tables HEAD [ms] 0001 [ms] 0001 + 0002 [ms]
50 5.77 4.19 3.74
500 15.75 4.28 3.76
5000 120.39 4.22 3.79
50000 1741.89 4.60 4.11
500000 73287.16 4.95 4.38

Attached graph visualized the table.

[1]:
```
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 = 17885 AND c.oid = gpt.relid
AND p.pubname IN ( 'pub' );
```

[2]:
```
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, 16535) gpt,
pg_class c
WHERE c.oid = gpt.relid
AND p.pubname IN ( 'pub' );
```

Best regards,
Hayato Kuroda
FUJITSU LIMITED

Attachment Content-Type Size
image/png 30.4 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Srinath Reddy Sadipiralla 2026-03-26 13:19:53 Re: Adding REPACK [concurrently]
Previous Message Álvaro Herrera 2026-03-26 12:37:19 Re: pg_publication_tables: return NULL attnames when no column list is specified