MIME-Version: 1.0
References: 
 <CAMT0RQT_0qVxcTT6ycM20QUN-pEQ6iMLbz6gLWgLpeF0NmNOUA@mail.gmail.com>
 <CAExHW5t54GPKFbW3KLzintJ6jMMRYwb-t2Fjm4JTxEcZbGDomA@mail.gmail.com>
 <CAMT0RQTHoL8S7OonFWC_aDSC-2oX7BGBBLAQ+OOBhRPcxV2eiw@mail.gmail.com>
In-Reply-To: 
 <CAMT0RQTHoL8S7OonFWC_aDSC-2oX7BGBBLAQ+OOBhRPcxV2eiw@mail.gmail.com>
From: Hannu Krosing <hannuk@google.com>
Date: Thu, 13 Nov 2025 19:39:26 +0100
Message-ID: 
 <CAMT0RQQAH1a8kY-mx7B07Uzn3T_zeaU9detqFFtW36_k67Su+A@mail.gmail.com>
Subject: Re: Patch: dumping tables data in multiple chunks in pg_dump
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Nathan Bossart <nathandbossart@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAMT0RQQAH1a8kY-mx7B07Uzn3T_zeaU9detqFFtW36_k67Su%2BA%40mail.gmail.com>
Precedence: bulk

Going up to 16 workers did not improve performance , but this is
expected, as the disk behind the database can only do 4TB/hour of
reads, which is now the bottleneck. (408/352/*3600 =3D 4172 GB/h)

$ time ./pg_dump --format=3Ddirectory -h 10.58.80.2 -U postgres
--huge-table-chunk-pages=3D131072 -j 16 -f /tmp/parallel16.dump largedb
real    5m44.900s
user    53m50.491s
sys     5m47.602s

And 4 workers showed near-linear speedup from single worker

hannuk@pgn2:~/work/postgres/src/bin/pg_dump$ time ./pg_dump
--format=3Ddirectory -h 10.58.80.2 -U postgres
--huge-table-chunk-pages=3D131072 -j 4 -f /tmp/parallel4.dump largedb
real    10m32.074s
user    38m54.436s
sys     2m58.216s

The database runs on a 64vCPU VM with 128GB RAM, so most of the table
will be read in from the disk


On Thu, Nov 13, 2025 at 7:02=E2=80=AFPM Hannu Krosing <hannuk@google.com> w=
rote:
>
> I just ran a test by generating a 408GB table and then dumping it both wa=
ys
>
> $ time pg_dump --format=3Ddirectory -h 10.58.80.2 -U postgres -f
> /tmp/plain.dump largedb
>
> real    39m54.968s
> user    37m21.557s
> sys     2m32.422s
>
> $ time ./pg_dump --format=3Ddirectory -h 10.58.80.2 -U postgres
> --huge-table-chunk-pages=3D131072 -j 8 -f /tmp/parallel8.dump largedb
>
> real    5m52.965s
> user    40m27.284s
> sys     3m53.339s
>
> So parallel dump with 8 workers using 1GB (128k pages) chunks runs
> almost 7 times faster than the sequential dump.
>
> this was a table that had no TOAST part. I will run some more tests
> with TOASTed tables next and expect similar or better improvements.
>
>
>
> On Wed, Nov 12, 2025 at 1:59=E2=80=AFPM Ashutosh Bapat
> <ashutosh.bapat.oss@gmail.com> wrote:
> >
> > Hi Hannu,
> >
> > On Tue, Nov 11, 2025 at 9:00=E2=80=AFPM Hannu Krosing <hannuk@google.co=
m> wrote:
> > >
> > > Attached is a patch that adds the ability to dump table data in multi=
ple chunks.
> > >
> > > Looking for feedback at this point:
> > >  1) what have I missed
> > >  2) should I implement something to avoid single-page chunks
> > >
> > > The flag --huge-table-chunk-pages which tells the directory format
> > > dump to dump tables where the main fork has more pages than this in
> > > multiple chunks of given number of pages,
> > >
> > > The main use case is speeding up parallel dumps in case of one or a
> > > small number of HUGE tables so parts of these can be dumped in
> > > parallel.
> >
> > Have you measured speed up? Can you please share the numbers?
> >
> > --
> > Best Wishes,
> > Ashutosh Bapat