Mime-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=UTF-8
Date: Fri, 02 Jan 2026 17:33:49 -0300
Message-Id: <DFEE251CWTA8.1LZ18ECCPK79K@gmail.com>
Cc: "Andrew Dunstan" <andrew@dunslane.net>, "jian he"
 <jian.universality@gmail.com>, "Tomas Vondra" <tomas@vondra.me>,
 <pgsql-hackers@postgresql.org>
Subject: Re: postgres_fdw: Use COPY to speed up batch inserts
From: "Matheus Alcantara" <matheusssilv97@gmail.com>
To: "Masahiko Sawada" <sawada.mshk@gmail.com>, "Matheus Alcantara"
 <matheusssilv97@gmail.com>
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>
 <DDV8CK8EJQQ0.DWQGFW2NOCGU@gmail.com>
 <5c52f4c7-74fa-4594-8378-53fd49f39329@dunslane.net>
 <DDWQNL7GDSXK.2OK2O16JOATAE@gmail.com>
 <DE20J06SWW40.IN0FOXQSFUEA@gmail.com>
 <CAD21AoDFisd4HYcDspqkzoM9sMofba8aBMgB0RkG8uCqt-ZQUg@mail.gmail.com>
 <DEC5ZS2XP2NS.JQ6DH0KUWNL7@gmail.com>
 <CAD21AoBhVS1R8pzvMePsp4ngRTvwOeM0qTkqFmFOj_F9LGNFuw@mail.gmail.com>
 <DEIX9LJ0S9KG.19VG2P7S0I793@gmail.com>
 <DEVDFCCDBMBS.X39YHQNM1HFJ@gmail.com>
 <CAD21AoCMNawy_72g1pc2JU0fp3X1_a_ce-ZQ6P9+YmdMMg-4Rg@mail.gmail.com>
In-Reply-To: 
 <CAD21AoCMNawy_72g1pc2JU0fp3X1_a_ce-ZQ6P9+YmdMMg-4Rg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/DFEE251CWTA8.1LZ18ECCPK79K%40gmail.com>
Precedence: bulk

On Fri Jan 2, 2026 at 5:15 PM -03, Masahiko Sawada wrote:
> +
> +   /*
> +    * Set batch_with_copy_threshold from foreign server/table options. W=
e do
> +    * this outside of create_foreign_modify() because we only want to us=
e
> +    * COPY as a remote SQL when a COPY FROM on a foreign table is execut=
ed or
> +    * an insert is being performed on a table partition. In both cases t=
he
> +    * BeginForeignInsert fdw routine is called.
> +    */
> +   fmstate->batch_with_copy_threshold =3D get_batch_with_copy_threshold(=
rel);
>
> Does it mean that we could end up using the COPY method not only when
> executing COPY FROM but also when executing INSERT with tuple
> routings? If so, how does the EXPLAIN command show the remote SQL?
>
It meas that we could also use the COPY method to insert rows into a
specific table partition that is a foreign table.

Let's say that an user execute an INSERT INTO on a partitioned table
that has partitions that are postgres_fdw tables, with this patch we
could use the COPY method to insert the rows on these partitions. On
this scenario we would not have issue with EXPLAIN output because
currently we do not show the remote SQL being executed on each partition
that is involved on the INSERT statement.

If an user execute an INSERT directly into a postgres_fdw table we will
use the normal INSERT statement as we use today.

Thanks for taking a look at this.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com