Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Wed, 29 Oct 2025 21:28:41 -0300
Message-Id: <DDV8CK8EJQQ0.DWQGFW2NOCGU@gmail.com>
Subject: Re: postgres_fdw: Use COPY to speed up batch inserts
From: "Matheus Alcantara" <matheusssilv97@gmail.com>
To: "jian he" <jian.universality@gmail.com>, "Matheus Alcantara"
 <matheusssilv97@gmail.com>
Cc: "Tomas Vondra" <tomas@vondra.me>, <pgsql-hackers@postgresql.org>
References: <DDIZJ217OUDK.2R5WE4OGL5PTY@gmail.com>
 <af99c828-ef24-4355-92d5-b5f631cda9c9@vondra.me>
 <CAFY6G8ePwjT8GiJX1AK5FDMhfq-sOnny6optgTPg98HQw7oJ0g@mail.gmail.com>
 <DDP9DDKVIYQ0.2LTDOCAWGG9M6@gmail.com>
 <CACJufxGgnX91Kzp-kJ03uZp2-sAPtuOpxxjhcigN72Z7BcYRZA@mail.gmail.com>
 <DDQRIXD8K0Z4.YO9AP5L8F4TI@gmail.com>
 <CACJufxFXhAuFDfQS=N0nCaFFTi08__cfwh6E7LhDuuJiAEo=Ww@mail.gmail.com>
In-Reply-To: 
 <CACJufxFXhAuFDfQS=N0nCaFFTi08__cfwh6E7LhDuuJiAEo=Ww@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/DDV8CK8EJQQ0.DWQGFW2NOCGU%40gmail.com>
Precedence: bulk

On Wed Oct 29, 2025 at 12:10 AM -03, jian he wrote:
> On Sat, Oct 25, 2025 at 2:27=E2=80=AFAM 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 slo=
wer
> than CIM_MULTI, I think.
> We should do performance tests for the case where the COPY statement is l=
imited
> 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.
>=20
I've benchmarked using buildtype=3Drelease with Dcassert=3Dfalse and
buildtype=3Ddebug with Dcassert=3Dtrue 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=3Ddebug=20
Dcassert=3Dtrue
    tps =3D 13.596754

Batch using COPY
batch_size: 100
buildtype=3Ddebug=20
Dcassert=3Dtrue
    tps =3D 11.650642=20

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

Batch using INSERT
batch_size: 100
buildtype=3Drelease=20
Dcassert=3Dfalse
    tps =3D 28.333161=20


Batch using COPY
batch_size: 100
buildtype=3Drelease=20
Dcassert=3Dfalse
    tps =3D 18.499420


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

--
Matheus Alcantara