Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Use "COPY table TO" for partitioned tables in initial table synchronization.

  2. Support COPY TO for partitioned tables.

  1. Add support for COPY TO in tablesync for partitioned tables.

    Ajin Cherian <itsajin@gmail.com> — 2025-11-11T03:08:52Z

    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

  2. Re: Add support for COPY TO in tablesync for partitioned tables.

    Amit Kapila <amit.kapila16@gmail.com> — 2025-11-11T03:18:58Z

    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.

  3. Re: Add support for COPY TO in tablesync for partitioned tables.

    Ajin Cherian <itsajin@gmail.com> — 2025-11-11T03:37:01Z

    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

  4. Re: Add support for COPY TO in tablesync for partitioned tables.

    Masahiko Sawada <sawada.mshk@gmail.com> — 2025-11-12T21:49:22Z

    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

  5. Re: Add support for COPY TO in tablesync for partitioned tables.

    Ajin Cherian <itsajin@gmail.com> — 2025-11-14T03:17:31Z

    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

  6. Re: Add support for COPY TO in tablesync for partitioned tables.

    Masahiko Sawada <sawada.mshk@gmail.com> — 2025-11-19T22:56:07Z

    On Thu, Nov 13, 2025 at 7:17 PM Ajin Cherian <itsajin@gmail.com> wrote:
>
> 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.
>

Thank you for the test! I've also done some performance tests and got
similar results.

The patch is pretty simple and looks good to me. I'll push the patch,
barring objections.

Regards,

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

  7. Re: Add support for COPY TO in tablesync for partitioned tables.

    Masahiko Sawada <sawada.mshk@gmail.com> — 2025-11-20T23:09:36Z

    On Wed, Nov 19, 2025 at 2:56 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Thu, Nov 13, 2025 at 7:17 PM Ajin Cherian <itsajin@gmail.com> wrote:
> >
> > 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.
> >
>
> Thank you for the test! I've also done some performance tests and got
> similar results.
>
> The patch is pretty simple and looks good to me. I'll push the patch,
> barring objections.

Pushed.


Regards,

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