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
- 0001-Implementation-of-a-Bulk-COPY-FROM.patch (text/plain) patch 0001
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
-
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