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