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
