[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
- 0001-Fast-COPY-FROM-into-the-foreign-or-sharded-table.patch (text/x-patch)
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
-
Allow batch insertion during COPY into a foreign table.
- 97da48246d34 16.0 landed
-
postgres_fdw: Disable batch insertion when there are WCO constraints.
- 82593b9a3d5f 16.0 cited
-
Enforce foreign key correctly during cross-partition updates
- ba9a7e392171 15.0 cited
-
Fix permission checks on constraint violation errors on partitions.
- 6214e2b22804 14.0 cited
-
Fix compilation of uuid-ossp
- 525e60b74299 14.0 cited