Обсуждение: Add support for COPY TO in tablesync for partitioned tables.

Hi all,

After patch [1] was committed, the COPY TO command is now supported
for partitioned tables. This change updates the tablesync logic to use
COPY TO for partitioned tables as well. This change will only be
invoked when the publication is configured with
publish_via_partition_root = true;
otherwise, partitioned tables will continue to be published using
their underlying partitions by default and publishing using partitions
already use COPY TO.

[1] - https://github.com/postgres/postgres/commit/4bea91f21f61d01bd40a4191a4a8c82d0959fffe

regards,
Ajin Cherian
Fujitsu Australia

On Tue, Nov 11, 2025 at 8:39 AM Ajin Cherian <itsajin@gmail.com> wrote:
>
> After patch [1] was committed, the COPY TO command is now supported
> for partitioned tables. This change updates the tablesync logic to use
> COPY TO for partitioned tables as well.
>

In the commit message, you mentioned: "Performance tests show it's
faster than the COPY (SELECT ...) TO variant as it avoids the
overheads of query processing and sending results to the COPY TO
command.". Can you share the performance data to substantiate this
point?

--
With Regards,
Amit Kapila.

On Tue, Nov 11, 2025 at 2:19 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> In the commit message, you mentioned: "Performance tests show it's
> faster than the COPY (SELECT ...) TO variant as it avoids the
> overheads of query processing and sending results to the COPY TO
> command.". Can you share the performance data to substantiate this
> point?
>

This was based on the tests done in the original thread [1] and [2]

[1]- https://www.postgresql.org/message-id/174219852967.294107.6195385625494034792.pgcf%40coridan.postgresql.org
[2]- https://www.postgresql.org/message-id/CALdSSPi5GUx1XtVTEOmvZ73MDM9HrpzE7L_Dp55z30wfp7KMvw%40mail.gmail.com

regards,
Ajin Cherian
Fujitsu Australia

On Mon, Nov 10, 2025 at 7:37 PM Ajin Cherian <itsajin@gmail.com> wrote:
>
> On Tue, Nov 11, 2025 at 2:19 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> >
> > In the commit message, you mentioned: "Performance tests show it's
> > faster than the COPY (SELECT ...) TO variant as it avoids the
> > overheads of query processing and sending results to the COPY TO
> > command.". Can you share the performance data to substantiate this
> > point?
> >
>
> This was based on the tests done in the original thread [1] and [2]

Thank you for working on this item. I think it's a good follow-up
patch for commit 4bea91f.

Have you conducted any performance tests with logical replication
setup? I've measured normal COPY TO cases but I think it would be
worth checking how much the performance increase we can see in logical
replication setup too.

Regards,

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

On Thu, Nov 13, 2025 at 8:49 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Mon, Nov 10, 2025 at 7:37 PM Ajin Cherian <itsajin@gmail.com> wrote:
> >
> > On Tue, Nov 11, 2025 at 2:19 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
> > >
> > > In the commit message, you mentioned: "Performance tests show it's
> > > faster than the COPY (SELECT ...) TO variant as it avoids the
> > > overheads of query processing and sending results to the COPY TO
> > > command.". Can you share the performance data to substantiate this
> > > point?
> > >
> >
> > This was based on the tests done in the original thread [1] and [2]
>
> Thank you for working on this item. I think it's a good follow-up
> patch for commit 4bea91f.
>
> Have you conducted any performance tests with logical replication
> setup? I've measured normal COPY TO cases but I think it would be
> worth checking how much the performance increase we can see in logical
> replication setup too.
>
Thanks for your interest in this patch.
I've tested the same setup as mentioned in [1] but with 10 tables and
500 records each and measuring the total time it would take for all
the tablesync workers to finish sync (from log timings).
On the average:
Without patch
Tablesync time: 185.4 ms
Average COPY command times: 1.4168 ms

With patch
Tablesync time: 172.2 ms (7% improvement)
Average COPY command times: 0.633 ms

The improvement in performance is smaller as the table size increases.
There is better improvement for smaller tables.
Attaching my test scripts as well.

regards,
Ajin Cherian
Fujitsu Australia

[1] - https://www.postgresql.org/message-id/174219852967.294107.6195385625494034792.pgcf%40coridan.postgresql.org