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

Hi Andrey,

On Mon, Jun 1, 2020 at 6:29 PM Andrey Lepikhov
<a.lepikhov@postgrespro.ru> wrote:
> 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.

Great!

> A couple of questions:
> 1. Can this feature be interesting for the PostgreSQL core or not?

Yeah, I think this is especially useful for sharding.

> 2. If this is a useful feature, is the correct way chosen?

I think I also thought something similar to this before [1].  Will take a look.

Thanks!

Best regards,
Etsuro Fujita

[1] https://www.postgresql.org/message-id/23990375-45a6-5823-b0aa-a6a7a6a957f0%40lab.ntt.co.jp