Horton

Re: Make COPY format extendable: Extract COPY TO format implementations

Sutou Kouhei <kou@clear-code.com>

View thread View on postgr.es
From: Sutou Kouhei <kou@clear-code.com>
To: tomas@vondra.me
Cc: sawada.mshk@gmail.com, andres@anarazel.de, michael@paquier.xyz, david.g.johnston@gmail.com, tgl@sss.pgh.pa.us, zhjwpku@gmail.com, pgsql-hackers@postgresql.org
Date: 2025-12-02T02:39:57Z
Lists: pgsql-hackers

Attachments

Hi,

In <c36d218a-bb38-42b9-9076-cb75b8984a39@vondra.me>
  "Re: Make COPY format extendable: Extract COPY TO format implementations" on Mon, 17 Nov 2025 18:04:46 +0100,
  Tomas Vondra <tomas@vondra.me> wrote:

> I got pinged about this patch off-list. I won't have capacity to do a
> proper review, anytime soon, but I got a bit of time to do a simple
> benchmark (which seems useful as that was one of the concerns in this
> thread, it seems).

Thanks!!!

I also do the same condition benchmark on my Mac mini:

Machine:

* Apple M1 (8 core)
* Memory: 16GB
* macOS Sequoia (15.6)

Parameters:

* N integer columns: 1 10 100
* N rows: 10 100 1000 10000 10000 100000 1000000
* Formats: text csv binary
* Operations: FROM TO
* Patches: 0001 0002 0003 0004 0005 0006

I ran 5 times for each parameter set and choose the median
elapsed time.

I used
https://gitlab.com/ktou/pg-bench/-/blob/main/copy-format-extendable/run.sh
. I attach it.

I measured 6 times. See the attached mac-mini-result-${N}.{csv,pdf}.

A PDF has 3 columns. They are text, csv and binary formats
from left to right.

1st row uses COPY FROM and 0001 patch.
2nd row uses COPY TO and 0001 patch.

3rd row uses COPY FROM and 0002 patch.
4th row uses COPY TO and 0002 patch.

...

Each heatmap visualizes (${elapsed_time_patch} /
${elapsed_time_master}) * 100. 100 > (red) means slower and
100 < (blue) means faster.

It seems that they don't show any reproducible trends.

For example, the binary cases in mac-mini-result-2.pdf show
that patched cases are always slower but the binary cases in
mac-mini-result-{1,6}.pdf show that most patched cases are
faster. The binary cases in mac-mini-result-{1,5,6}.pdf show
that 0006 patch is slower than 0005 patch but the binary
cases in mac-mini-result-{3,4}.pdf don't show it.

Another example, the text cases in mac-mini-result-1.pdf
show that patched cases are always slower but the text cases
in mac-mini-result-1.pdf show that most patched cases are
faster.

I hope that these numbers help to proceed this proposal.


Thanks,
-- 
kou

Commits

  1. Refactor Copy{From|To}GetRoutine() to use pass-by-reference argument.

  2. Refactor COPY FROM to use format callback functions.

  3. Refactor COPY TO to use format callback functions.

  4. Another try to fix BF failure introduced in commit ddd5f4f54a.

  5. Revert "Refactor CopyReadAttributes{CSV,Text}() to use a callback in COPY FROM"

  6. Improve COPY TO performance when server and client encodings match

  7. Simplify signature of CopyAttributeOutCSV() in copyto.c

  8. Revert "Refactor CopyAttributeOut{CSV,Text}() to use a callback in COPY TO"

  9. Refactor CopyAttributeOut{CSV,Text}() to use a callback in COPY TO

  10. Refactor CopyReadAttributes{CSV,Text}() to use a callback in COPY FROM

  11. Add progress reporting of skipped tuples during COPY FROM.

  12. pgbench: Add \syncpipeline

  13. meson: Make gzip and tar optional

  14. Export the external file reader used in COPY FROM as APIs.