Horton

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

Masahiko Sawada <sawada.mshk@gmail.com>

View thread View on postgr.es
From: Masahiko Sawada <sawada.mshk@gmail.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Sutou Kouhei <kou@clear-code.com>, pgsql-hackers@postgresql.org
Date: 2024-10-10T22:55:34Z
Lists: pgsql-hackers 
On Tue, Oct 8, 2024 at 8:34 PM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Mon, Oct 07, 2024 at 03:23:08PM -0700, Masahiko Sawada wrote:
> > In the benchmark, I've applied the v20 patch set and 'master' in the
> > result refers to a19f83f87966. And I disabled CPU turbo boost where
> > possible. Overall, v20 patch got a similar or better performance in
> > both COPY FROM and COPY TO compared to master except for on MacOS.
> > I'm not sure that changes made to master since the last benchmark run by
> > Tomas and Suto-san might contribute to these results.
>
> Don't think so.  FWIW, I have been looking at the set of tests with
> previous patch versions around v7 and v10 I have done, and did notice
> a similar pattern where COPY FROM was getting slightly better for text
> and binary.  It did not look like only noise involved, and it was
> kind of reproducible.  As long as we avoid the function pointer
> redirection for the per-row processing when dealing with in-core
> formats, we should be fine as far as I understand.  That's what the
> latest patch set is doing based on a read of v21.

Yeah, what v21 patch is doing makes sense to me.

>
> > I'll try to investigate the performance regression that happened on MacOS.
>
> I don't have a good explanation for this one.  Did you mount the data
> folder on a tmpfs and made sure that all the workloads were
> CPU-bounded?

Yes, I used tmpfs and workloads were CPU-bound.

>
> > I think that other performance differences in my results seem to be within
> > noises and could be acceptable. Of course, it would be great if others
> > also could try to run benchmark tests.
>
> Yeah.  At 1~2% it could be noise, but there are reproducible 1~2%
> evolutions.  In the good sense here, it means.

In real workloads, COPY FROM/TO operations would be more disk I/O
bound. I think that 1~2% performance differences that were shown in
CPU-bound workload would not be a problem in practice.

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

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.