Re: postgres_fdw: Use COPY to speed up batch inserts

On Thu, 6 Aug 2026 at 02:33, Matheus Alcantara <matheusssilv97(at)gmail(dot)com> wrote:
>
> Hi,
>
> Attached is a new version of the patch taking a different approach from
> the earlier attempts.
>
> Off-list I discussed with Masahiko the idea of adding a dedicated COPY
> API to the FDW handler, instead of trying to bolt COPY onto the existing
> insert callbacks, and the attached patch implements that. The core
> addition is a single new optional FdwRoutine callback:
>
> void
> ExecForeignBatchCopy(EState *estate,
> ResultRelInfo *rinfo,
> TupleTableSlot **slots,
> int numSlots);
>
> When a foreign table (or a foreign-table partition) is the target of
> COPY FROM and batching is enabled, CopyFrom() hands each buffered batch
> of tuples to this callback instead of ExecForeignInsert /
> ExecForeignBatchInsert. postgres_fdw implements it by running a
> COPY ... FROM STDIN on the remote connection and streaming the rows in
> text format.
>
> I first tried the more obvious design of splitting this into
> BeginForeignCopy / ExecForeignCopy / EndForeignCopy, keeping a single
> COPY ... FROM STDIN open across the entire COPY command and closing it
> only in EndForeignCopy. That doesn't work in general because of
> connection sharing. postgres_fdw caches one connection per user
> mapping, so:
>
> - when COPY routes tuples into several foreign partitions that map to
> the same remote server (the sharding case), or
>
> - when local code runs another query on the same connection in the
> middle of the COPY (e.g. a trigger or a volatile default expression
> that reads a foreign table on the same server),
>
> we would need a second command in flight on a connection that is stuck
> in COPY_IN mode, which fails. Making each call self-contained avoids
> this entirely, the connection is always left idle between calls, so
> partitions can share it and any interleaved query just works.
>
> The trade-off is that COPY is only used when batching is enabled
> (batch_size > 1). With batching disabled we would be starting and
> finishing a COPY protocol for every single row, which is slower than a
> plain INSERT, so in that case COPY FROM keeps inserting rows one at a
> time, exactly as today. The new callback is also independent of
> ExecForeignBatchInsert, a FDW may implement either or both. COPY is
> not used when the target has AFTER ROW triggers, because COPY has no
> RETURNING to feed them, such cases fall back to the batch-insert path.
>
> This is a PoC patch to experiment the idea of having a dedicated API,
> but I think that the main advantage of this is that we can involve the
> API to be more flexible and e.g use some user custom COPY options when
> writing the remote SQL for the remote server (although this patch still
> doesn't implement this) which would be hard using the ExecForeignInsert
> API. Also, I think that we can have a BeginForeignCopy and
> EndForeignCopy to initialize states used on ExecForeignBatchCopy (e.g
> the sql COPY command).
>
> The patch is split into three parts:
>
> 0001 - Extract CopyEscapeText() so it can be reused outside COPY TO.
> 0002 - Add the ExecForeignBatchCopy callback to the FDW API (copyfrom.c,
> fdwapi.h and the FDW documentation).
> 0003 - Implement the callback in postgres_fdw.
>
> Thoughts on the API and the self-contained approach are very welcome.
>
> --
> Matheus Alcantara
> EDB: https://www.enterprisedb.com

Hi!

I noticed this thread and did some benchmarking. I did benchmark v14
and v16 patches.

In my benchmarks, I used a local VM setup and two VM in different AZ setup.

In the bench, I didn't measure pgbench inserts but instead I measured
COPY <foreign table> FROM <file> speed.

tldr: connection caching is very beneficial for COPY speed, CSV ->
TEXT format also did show a little improvement.

So, when I ran benches for v14/v16 I noticed that COPY speed was
drastically worse. With some gdb/strace/perf I figured out that
connection re-acquire in COPY is very very bad for my bench perf. So I
implemented caching for COPY: enter COPY IN mode in the beginning of
batch insert mode, then send only COPY DATA messages. Patches
attached, but they are very PoC, draft.

CREATE FOREIGN TABLE ft (id int, value int, note text) SERVER remote
OPTIONS (table_name 't', batch_size 'xxx');

For local VM

- 1 machine, 2 PostgreSQL instances (source:5532 with fdw, target:5533)
- source has foreign table `ft` pointing to target table `t`
- `COPY ft FROM 'data.tsv'` reads file on source, streams to target via fdw
- batch_size set via foreign table option
- HEAD = unpatched PostgreSQL (uses prepared INSERT with N VALUES per batch)

## Benchmark: 100K rows x 10KB strings (956MB total)

Long strings shows CSV vs TEXT parsing overhead.

```
HEAD v14 v16 v14+cache v16+cache
bs=100 11.8s 20.8s 18.8s 9.7s 7.6s
bs=1000 11.6s 21.1s 18.7s 9.9s 7.5s
bs=10000 11.8s 20.9s 18.5s 9.8s 7.5s
```

## Benchmark: 1M small rows (42MB total)

Small rows (3 int/text columns, ~42 bytes per row).

```
HEAD v14 v16 v14+cache v16+cache
bs=100 5.1s 6.1s 6.1s 1.0s 1.2s
bs=1000 3.5s 4.1s 4.1s 1.1s 1.1s
bs=10000 3.6s 4.2s 4.2s 1.1s 1.0s
```

## Benchmark: 10M rows (446MB, small strings)

```
HEAD v14 v16+cache
bs=100 51.3s 59.0s 9.8s
bs=1000 36.6s 42.5s 9.5s
bs=10000 36.8s — 9.4s
bs=100000 36.7s — 9.4s
```

v16+cache is faster than v14+cache on long strings (7.5s vs 9.7s) due to
TEXT format. On small rows they are similar (~1.0-1.2s).

=============

For two VM setups results are much scarier. A COPY of 1000000 rows
works ~ twice longer. With big batch sizes it would be 4.9s on HEAD
while with v14/v16. WIth batch size 100 it will be 36s vs 70s. I
didn't prepare the full result table for this case, just did a few
runs.

```
HEAD v14 v16 v16+cache

bs=100 35-36s 70s 70s 500-600ms
bs=1000 4.9s 9.8s (didn't measure) 500-600ms
````

By the way I noticed that batch_size is practically always no more
than 1000 in batch insert callback (even though configured to a bigger
value in relation options.). I didn't look for the exact reason.

Also I faced cancel query problems many times. With v14 or v16 `copy
ft from '/tmp/fdw_bench/copy_data_1000000.tsv';` is not cancellable
with postgres infinity looping here:

```
warning: 3353 ./malloc/malloc.c: No such file or directory
#0 0x000070d14aeb5643 in __GI___libc_free (mem=0x5cd07fa287a0) at
./malloc/malloc.c:3353
#1 0x000070d1375a86c6 in PQclear (res=<optimized out>) at fe-exec.c:776
#2 0x000070d14b06ec87 in libpqsrv_PQclear (bres=0x5cd07f9a57b0) at
../../src/include/libpq/libpq-be-fe.h:100
#3 libpqsrv_PQclear (bres=0x5cd07f9a57b0) at
../../src/include/libpq/libpq-be-fe.h:95
#4 pgfdw_get_cleanup_result (conn=conn(at)entry=0x5cd07f9faab0,
endtime=842268423525902, retrycanceltime=842268394525902,
result=result(at)entry=0x7ffc9a3f3b90,
timed_out=timed_out(at)entry=0x7ffc9a3f3b8f) at connection.c:1857
#5 0x000070d14b06ee7e in pgfdw_cancel_query_end
(conn=conn(at)entry=0x5cd07f9faab0,
endtime=endtime(at)entry=842268423525902,
retrycanceltime=retrycanceltime(at)entry=842268394525902,
consume_input=consume_input(at)entry=false) at connection.c:1642
#6 0x000070d14b06f2be in pgfdw_cancel_query (conn=0x5cd07f9faab0) at
connection.c:1594
#7 pgfdw_abort_cleanup (entry=entry(at)entry=0x5cd07f9401b8,
toplevel=toplevel(at)entry=true) at connection.c:1909
#8 0x000070d14b06fb4d in pgfdw_xact_callback (event=XACT_EVENT_ABORT,
arg=<optimized out>) at connection.c:1284
#9 0x00005cd05fd89e32 in CallXactCallbacks (event=XACT_EVENT_ABORT)
at xact.c:3900
#10 AbortTransaction () at xact.c:3015
#11 0x00005cd05fd8a6d6 in AbortCurrentTransactionInternal () at xact.c:3631
#12 AbortCurrentTransaction () at xact.c:3509
#13 0x00005cd0600aa64d in PostgresMain (dbname=<optimized out>,
username=<optimized out>) at postgres.c:4629
#14 0x00005cd0600a4dc5 in BackendMain (startup_data=<optimized out>,
startup_data_len=<optimized out>) at backend_startup.c:124
#15 0x00005cd05fffa5ab in postmaster_child_launch
(child_type=<optimized out>, child_slot=1,
startup_data=startup_data(at)entry=0x7ffc9a3f4140,
startup_data_len=startup_data_len(at)entry=24,
client_sock=client_sock(at)entry=0x7ffc9a3f4160) at launch_backend.c:268
#16 0x00005cd05fffde0a in BackendStartup (client_sock=0x7ffc9a3f4160)
at postmaster.c:3643
#17 ServerLoop () at postmaster.c:1729
#18 0x00005cd05ffff902 in PostmasterMain (argc=argc(at)entry=3,
argv=argv(at)entry=0x5cd07f904ec0) at postmaster.c:1416
#19 0x00005cd05fcea798 in main (argc=3, argv=0x5cd07f904ec0) at main.c:227
[Inferior 1 (process 83146) detached]
```

Looks like PostgreSQL FDW connection state housekeeping doesn't work
well with PGASYNC_COPY_IN.

with this diff

```
reshke(at)gp-cbdb-bench:~/postgres/contrib$ git diff postgres_fdw/connection.c
diff --git a/contrib/postgres_fdw/connection.c
b/contrib/postgres_fdw/connection.c
index edfff43426a..c34f2acd9ef 100644
--- a/contrib/postgres_fdw/connection.c
+++ b/contrib/postgres_fdw/connection.c
@@ -1784,20 +1784,21 @@ pgfdw_get_cleanup_result(PGconn *conn,
TimestampTz endtime,
{
PGresult *res;

+ TimestampTz now = GetCurrentTimestamp();
+
+ /* If timeout has expired, give up. */
+ if (now >= endtime)
+ {
+ *timed_out = true;
+ failed = true;
+ goto exit;
+ }
+
while (PQisBusy(conn))
{
int wc;
- TimestampTz now = GetCurrentTimestamp();
long cur_timeout;

- /* If timeout has expired, give up. */
- if (now >= endtime)
- {
- *timed_out = true;
- failed = true;
- goto exit;
- }
-
/* If we need to re-issue the cancel request,
do that. */
if (now >= retrycanceltime)
{
```

You will get `WARNING: could not get result of cancel request due to
timeout` instead of inf loop. This is surely not a fix, but I didn't
look closer.

PFA v14/v16 with connection caching patches, posted for reference, not
for actual review. Also posing of of many bench scripts I used,
grabbed from one of VMs

--
Best regards,
Kirill Reshke