Horton

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

Tomas Vondra <tomas@vondra.me>

View thread View on postgr.es
From: Tomas Vondra <tomas@vondra.me>
To: Masahiko Sawada <sawada.mshk@gmail.com>, Sutou Kouhei <kou@clear-code.com>, andres@anarazel.de
Cc: michael@paquier.xyz, david.g.johnston@gmail.com, tgl@sss.pgh.pa.us, zhjwpku@gmail.com, pgsql-hackers@postgresql.org
Date: 2025-11-17T17:04:46Z
Lists: pgsql-hackers

Attachments

On 11/14/25 21:19, Masahiko Sawada wrote:
> After offline discussions with Sutou-san, we believe the current APIs
> work well, particularly for text-based formats, though we still need
> to verify there are no performance regressions.

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

Attached is a script that does COPY TO/FROM with the built-in formats,
on table with 1, 10 and 100 integer columns. The data sets are between
10 and 1M rows. The table is UNLOGGED, to eliminate WAL overhead.

The attached PDF summarizes results from my ryzen machine, for master
and patched build. The final columns are comparison (i.e. copy/master)
of the timings. Values >100% are regressions (marked as red).

It seems quite "red", but it's not particularly conclusive. The
differences are mostly within 5%, and that could be caused e.g. by
changes to binary layout. And some of the cases got faster too.

It might be interesting to get results from other machines. The script
may need some adjustments, but it should be too difficult.

The other thing Andres was concerned about is "the amount of COPY
performance improvements it forecloses". I have no opinion on that, as
it depends on what improvements Andres envisioned.


regards

-- 
Tomas Vondra

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.