Fast COPY FROM based on batch insert

Andrei Lepikhov <a.lepikhov@postgrespro.ru>

From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
To: Etsuro Fujita <etsuro.fujita@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>
Cc: "tsunakawa.takay" <tsunakawa.takay@fujitsu.com>, Zhihong Yu <zyu@yugabyte.com>, Amit Langote <amitlangote09@gmail.com>, tanghy.fnst@fujitsu.com, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Pg Hackers <pgsql-hackers@postgresql.org>, Tomas Vondra <tomas.vondra@enterprisedb.com>, houzj.fnst@fujitsu.com
Date: 2021-06-04T08:26:29Z
Lists: pgsql-hackers

Attachments

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

Commits

  1. Allow batch insertion during COPY into a foreign table.

  2. postgres_fdw: Disable batch insertion when there are WCO constraints.

  3. Enforce foreign key correctly during cross-partition updates

  4. Fix permission checks on constraint violation errors on partitions.

  5. Fix compilation of uuid-ossp