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

From: Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
To: PostgreSQL-Dev <pgsql-hackers(at)postgresql(dot)org>
Subject: [POC] Fast COPY FROM command for the table with foreign partitions
Date: 2020-06-01 09:29:23
Message-ID: 3d0909dc-3691-a576-208a-90986e55489f@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
0001-Fast-COPY-FROM-into-the-foreign-or-sharded-table.patch text/x-patch 19.9 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Masahiko Sawada 2020-06-01 11:08:50 Re: pg_dump dumps row level policies on extension tables
Previous Message Kyotaro Horiguchi 2020-06-01 09:00:01 Re: shared-memory based stats collector