Re: postgres_fdw: Use COPY to speed up batch inserts

On 2025-10-29 We 8:28 PM, Matheus Alcantara wrote:
> 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(at)gmail(dot)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.
>

I think it's probably worth finding out why COPY is so much worse in the
presence of triggers. Is there something we can do to improve that, at
least so it's no worse?

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com