[POC] Fast COPY FROM command for the table with foreign partitions

Andrei Lepikhov <a.lepikhov@postgrespro.ru>

From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
To: PostgreSQL-Dev <pgsql-hackers@postgresql.org>
Date: 2020-06-01T09:29:23Z
Lists: pgsql-hackers

Attachments

Hi, hackers!

Currently i see, COPY FROM insertion into the partitioned table with 
foreign partitions is not optimal: even if table constraints allows can 
do multi insert copy, we will flush the buffers and prepare new INSERT 
query for each tuple, routed into the foreign partition.
To solve this problem i tried to use the multi insert buffers for 
foreign tuples too. Flushing of these buffers performs by the analogy 
with 'COPY .. FROM STDIN' machinery as it is done by the psql '\copy' 
command.
The patch in attachment was prepared from the private scratch developed 
by Arseny Sher a couple of years ago.
Benchmarks shows that it speeds up COPY FROM operation:
Command "COPY pgbench_accounts FROM ..." (test file contains 1e7 tuples, 
copy to three partitions) executes on my laptop in 14 minutes without 
the patch and in 1.5 minutes with the patch. Theoretical minimum here 
(with infinite buffer size) is 40 seconds.

A couple of questions:
1. Can this feature be interesting for the PostgreSQL core or not?
2. If this is a useful feature, is the correct way chosen?

-- 
Andrey Lepikhov
Postgres Professional
https://postgrespro.com
The Russian Postgres Company

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