Fast COPY FROM based on batch insert

From: Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
To: Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>, Justin Pryzby <pryzby(at)telsasoft(dot)com>
Cc: "tsunakawa(dot)takay" <tsunakawa(dot)takay(at)fujitsu(dot)com>, Zhihong Yu <zyu(at)yugabyte(dot)com>, Amit Langote <amitlangote09(at)gmail(dot)com>, tanghy(dot)fnst(at)fujitsu(dot)com, Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>, houzj(dot)fnst(at)fujitsu(dot)com
Subject: Fast COPY FROM based on batch insert
Date: 2021-06-04 08:26:29
Message-ID: bc489202-9855-7550-d64c-ad2d83c24867@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,
We still have slow 'COPY FROM' operation for foreign tables in current
master.
Now we have a foreign batch insert operation And I tried to rewrite the
patch [1] with this machinery.

The patch (see in attachment) smaller than [1] and no changes required
in FDW API.

Benchmarking
============
I used two data sets: with a number of 1E6 and 1E7 tuples. As a foreign
server emulation I used loopback FDW links.

Test table:
CREATE TABLE test(a int, payload varchar(80));

Execution time of COPY FROM into single foreign table:
version | 1E6 tuples | 1E7 tuples |
master: | 64s | 775s |
Patch [1]: | 5s | 50s |
Current: | 4s | 42s |
Execution time of the COPY operation into a plane table is 0.8s for 1E6
tuples and 8s for 1E7 tuples.

Execution time of COPY FROM into the table partitioned by three foreign
partitions:
version | 1E6 tuples | 1E7 tuples |
master: | 85s | 900s |
Patch [1]: | 10s | 100s |
Current: | 3.5s | 34s |

But the bulk insert execution time in current implementation strongly
depends on MAX_BUFFERED_TUPLES/BYTES value and in my experiments was
reduced to 50s.

[1]
https://www.postgresql.org/message-id/flat/3d0909dc-3691-a576-208a-90986e55489f%40postgrespro.ru

--
regards,
Andrey Lepikhov
Postgres Professional

Attachment Content-Type Size
0001-Implementation-of-a-Bulk-COPY-FROM.patch text/plain 23.7 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro Horiguchi 2021-06-04 08:35:33 detailed error message of pg_waldump
Previous Message Daniel Gustafsson 2021-06-04 08:17:25 A few random typos in the docs