Re: postgres_fdw: Use COPY to speed up batch inserts

On 09/09/26 17:10, Kirill Reshke wrote:
> 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.
>
Hi,

Sorry for the delay, I got busy with other patches.

This regression is very bad, I didn't check the performance when I
sent the patches.

I didn't check the patch in depth (and I don't know if you will
remember this detail since it's being a while) but how did you handle
the connection sharing that I mention previously? And also, what was
the value of batch_size of these tests?

> 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.

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