From: | Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> |
---|---|
To: | Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru> |
Cc: | PostgreSQL-Dev <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [POC] Fast COPY FROM command for the table with foreign partitions |
Date: | 2020-06-02 00:02:10 |
Message-ID: | CAPmGK15mAiaU85OcvEDE=7fHA3PvA7ChP82b7bEE57Yw20rF4A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi Andrey,
On Mon, Jun 1, 2020 at 6:29 PM Andrey Lepikhov
<a(dot)lepikhov(at)postgrespro(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | David Zhang | 2020-06-02 00:05:50 | Re: Postgres Windows build system doesn't work with python installed in Program Files |
Previous Message | Jonathan S. Katz | 2020-06-01 23:17:17 | Re: Another modest proposal for docs formatting: catalog descriptions |