Thread

Re: postgres_fdw: Use COPY to speed up batch inserts

Matheus Alcantara <matheusssilv97@gmail.com> — 2025-10-30T00:28:41Z

On Wed Oct 29, 2025 at 12:10 AM -03, jian he wrote:
> On Sat, Oct 25, 2025 at 2:27 AM Matheus Alcantara
> <matheusssilv97@gmail.com> wrote:
>>
>> On this new version I also added some regress tests on postgres_fdw.sql
>>
>
> In the CopyFrom function, we have the CopyInsertMethod, CIM_SINGLE is slower
> than CIM_MULTI, I think.
> We should do performance tests for the case where the COPY statement is limited
> to use CIM_SINGLE.
>
> You can use triggers to make COPY can only use the CIM_SINGLE copymethod.
> for example:
> create function dummy() returns trigger as $$ begin return new; end $$
> language plpgsql;
> create trigger dummy
>     before insert or update on batch_table_3
>     for each row execute procedure dummy();
>
> My local tests show that when batch_size is greater than 2, COPY performs faster
> than batch inserts into a foreign table, even though COPY can only use
> CIM_SINGLE.
> However, my tests were done with an enable-assert build, since I
> encountered issues compiling the release build.
>
> anyway, I am sharing my test script.
> 
I've benchmarked using buildtype=release with Dcassert=false and
buildtype=debug with Dcassert=true and in both cases I've got a worst
performance when using the COPY for batching insert into a a foreign
table with a trigger. See the results (best of 4 runs).

Batch using INSERT
batch_size: 100
buildtype=debug 
Dcassert=true
    tps = 13.596754

Batch using COPY
batch_size: 100
buildtype=debug 
Dcassert=true
    tps = 11.650642 

--------------------

Batch using INSERT
batch_size: 100
buildtype=release 
Dcassert=false
    tps = 28.333161 


Batch using COPY
batch_size: 100
buildtype=release 
Dcassert=false
    tps = 18.499420


It seems to me that we need to disable the COPY usage when the foreign
table has triggers enabled.

--
Matheus Alcantara