Subject: Patch: dumping tables data in multiple chunks in pg_dump

From: Hannu Krosing <[email-masked]>
Date: Tue, 11 Nov 2025 16:29:56 +0100

Attached is a patch that adds the ability to dump table data in multiple 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.

It will also help in case the target file system has some limitations
on file sizes (4GB for FAT, 5TB for GCS).

Currently no tests are included in the patch and also no extra
documentation outside what is printed out by pg_dump --help . Also any
pg_log_warning lines with "CHUNKING" is there for debugging and needs
to be removed before committing.

As  implemented no changes are needed for pg_restore as all chunks are
already associated with the table in .toc and thus are restored into
this table

the attached README shows how I verified it works  and the textual
file created from the directory format dump in the last step there

--
Hannu

---

From: Ashutosh Bapat <[email-masked]>
Date: Wed, 12 Nov 2025 18:29:33 +0530

Hi Hannu,

> small number of HUGE tables so parts of these can be dumped in

Have you measured speed up? Can you please share the numbers?

-- 
Best Wishes,
Ashutosh Bapat

---

From: Hannu Krosing <[email-masked]>
Date: Thu, 13 Nov 2025 19:02:43 +0100

I just ran a test by generating a 408GB table and then dumping it both ways

$ time pg_dump --format=directory -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=directory -h 10.58.80.2 -U postgres
--huge-table-chunk-pages=131072 -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.

---

From: Hannu Krosing <[email-masked]>
Date: Thu, 13 Nov 2025 19:39:26 +0100

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 = 4172 GB/h)

$ time ./pg_dump --format=directory -h 10.58.80.2 -U postgres
--huge-table-chunk-pages=131072 -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=directory -h 10.58.80.2 -U postgres
--huge-table-chunk-pages=131072 -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

---

From: Hannu Krosing <[email-masked]>
Date: Thu, 13 Nov 2025 21:24:33 +0100

Ran another test with a 53GB database where most of the data is in TOAST

CREATE TABLE just_toasted(
  id serial primary key,
  toasted1 char(2200) STORAGE EXTERNAL,
  toasted2 char(2200) STORAGE EXTERNAL,
  toasted3 char(2200) STORAGE EXTERNAL,
  toasted4 char(2200) STORAGE EXTERNAL
);

and the toast fields were added in somewhat randomised order.

Here the results are as follows

Parallelism   |   chunk size (pages)   |    time (sec)
 1        |    -         |     240
 2        |  1000    |     129
 4        |  1000    |      64
 8        |  1000    |      36
16       |  1000    |      30

 4        |  9095    |      78
 8        |  9095    |      42
16       |  9095    |      42

The reason larger chunk sizes performed worse was that they often had
one or two stragglers left behind which

Detailed run results below:

hannuk@pgn2:~/work/postgres/src/bin/pg_dump$ time ./pg_dump
--format=directory -h 10.58.80.2 -U postgres -f
/tmp/ltoastdb-1-plain.dump largetoastdb
real    3m59.465s
user    3m43.304s
sys     0m15.844s

hannuk@pgn2:~/work/postgres/src/bin/pg_dump$ time ./pg_dump
--format=directory -h 10.58.80.2 -U postgres
--huge-table-chunk-pages=9095 -j 4 -f /tmp/ltoastdb-4.dump
largetoastdb
real    1m18.320s
user    3m49.236s
sys     0m19.422s

hannuk@pgn2:~/work/postgres/src/bin/pg_dump$ time ./pg_dump
--format=directory -h 10.58.80.2 -U postgres
--huge-table-chunk-pages=9095 -j 8 -f /tmp/ltoastdb-8.dump
largetoastdb
real    0m42.028s
user    3m55.299s
sys     0m24.657s

hannuk@pgn2:~/work/postgres/src/bin/pg_dump$ time ./pg_dump
--format=directory -h 10.58.80.2 -U postgres
--huge-table-chunk-pages=9095 -j 16 -f /tmp/ltoastdb-16.dump
largetoastdb
real    0m42.575s
user    4m11.011s
sys     0m26.110s

hannuk@pgn2:~/work/postgres/src/bin/pg_dump$ time ./pg_dump
--format=directory -h 10.58.80.2 -U postgres
--huge-table-chunk-pages=1000 -j 16 -f /tmp/ltoastdb-16-1kpages.dump
largetoastdb
real    0m29.641s
user    6m16.321s
sys     0m49.345s

hannuk@pgn2:~/work/postgres/src/bin/pg_dump$ time ./pg_dump
--format=directory -h 10.58.80.2 -U postgres
--huge-table-chunk-pages=1000 -j 8 -f /tmp/ltoastdb-8-1kpages.dump
largetoastdb
real    0m35.685s
user    3m58.528s
sys     0m26.729s

hannuk@pgn2:~/work/postgres/src/bin/pg_dump$ time ./pg_dump
--format=directory -h 10.58.80.2 -U postgres
--huge-table-chunk-pages=1000 -j 4 -f /tmp/ltoastdb-4-1kpages.dump
largetoastdb
real    1m3.737s
user    3m50.251s
sys     0m18.507s

hannuk@pgn2:~/work/postgres/src/bin/pg_dump$ time ./pg_dump
--format=directory -h 10.58.80.2 -U postgres
--huge-table-chunk-pages=1000 -j 2 -f /tmp/ltoastdb-2-1kpages.dump
largetoastdb
real    2m8.708s
user    3m57.018s
sys     0m18.499s

---

From: Hannu Krosing <[email-masked]>
Date: Thu, 13 Nov 2025 21:26:46 +0100

The reason for small chunk sizes is that they are determined by main
heap table, and that was just over 1GB

largetoastdb=> SELECT format('%I.%I', t.schemaname, t.relname) as table_name,
       pg_table_size(t.relid) AS table_size,
       sum(pg_relation_size(i.indexrelid)) AS total_index_size,
       pg_relation_size(t.relid) AS main_table_size,
       pg_relation_size(c.reltoastrelid) AS toast_table_size,
       pg_relation_size(oi.indexrelid) AS toast_index_size,
       t.n_live_tup AS row_count,
       count(*) AS index_count,
       array_to_json(array_agg(json_build_object(i.indexrelid::regclass,
pg_relation_size(i.indexrelid))), true) AS index_info
  FROM pg_stat_user_tables t
  JOIN pg_stat_user_indexes i ON i.relid = t.relid
  JOIN pg_class c ON c.oid = t.relid
  LEFT JOIN pg_stat_sys_indexes AS oi ON oi.relid = c.reltoastrelid
 GROUP BY 1, 2, 4, 5, 6, 7
 ORDER BY 2 DESC, 7 DESC
 LIMIT 25;
┌─[ RECORD 1 ]─────┬─────────────────────────────────────┐
│ table_name       │ public.just_toasted                 │
│ table_size       │ 56718835712                         │
│ total_index_size │ 230064128                           │
│ main_table_size  │ 1191559168                          │
│ toast_table_size │ 54613336064                         │
│ toast_index_size │ 898465792                           │
│ row_count        │ 5625234                             │
│ index_count      │ 1                                   │
│ index_info       │ [{"just_toasted_pkey" : 230064128}] │
└──────────────────┴─────────────────────────────────────┘

---

From: Hannu Krosing <[email-masked]>
Date: Thu, 13 Nov 2025 21:34:23 +0100

Added to https://commitfest.postgresql.org/patch/6219/

---

From: Dilip Kumar <[email-masked]>
Date: Mon, 17 Nov 2025 09:45:17 +0530

> small number of HUGE tables so parts of these can be dumped in

+1 for the idea, I haven't done the detailed review but I was just
going through the patch, I noticed that we use pg_class->relpages to
identify whether to chunk the table or not, which should be fine but
don't you think if we use direct size calculation function like
pg_relation_size() we might get better idea and not dependent upon
whether the stats are updated or not?  This will make chunking
behavior more deterministic.

-- 
Regards,
Dilip Kumar
Google

---

From: Hannu Krosing <[email-masked]>
Date: Mon, 24 Nov 2025 22:02:15 +0100

The expectation was that as chunking is useful mainly in case of
really huge tables the analyze should have been run "recently enough".

Maybe we should use pg_relation_size() in case we have already
determined that the table is large enough to warrant chunking? Maybe
at least 1/2 of the requested chunk size?

My reasoning was to not put too much extra load on pg_dump in case
chunking is not required. But of course we can use the presence of a
chunking request to decide to run pg_relation_size(), assuming the
overhead won't be too large in this case.

---

From: Dilip Kumar <[email-masked]>
Date: Tue, 25 Nov 2025 10:20:01 +0530

> > behavior more deterministic.

Yeah that makes sense, we can use relpages for initial identification
and then use pg_relation_size() if relpages says the table is large
enough.

-- 
Regards,
Dilip Kumar
Google

---

From: Hannu Krosing <[email-masked]>
Date: Sat, 28 Mar 2026 11:59:18 +0100

Hi Zsolt and Dilip,

Thanks for review and useful comments!

On Tue, Feb 3, 2026 at 10:10 PM Zsolt Parragi <[email-masked]> wrote:
>
> Hello!
>
> I did some testing with this patch, and I think there are some issues
> during restoration:
>
> 1. Isn't there a possible race / scheduling mistake during restore
> because of missing dependencies? The code now prints out "TABLE DATA
> (pages %u:%u)", while the restore code checks for the explicit "TABLE
> DATA" string for dependency tracking (pg_backup_archiver.c:2013 and a
> few other places). This causes POST DATA to have no dependency on the
> table data, and can be scheduled before we load all table data.

I have resolved this by adding a second array to the reverse dependencies
mechanism in buildTocEntryArrays() for chunked dump where I collect arrays
of ids in AH->tableDataChunkIds.

For this I extracted the list management part from DumpableObject

typedef struct _DependencyList
{
DumpId    *dependencies; /* dumpIds of objects this one depends on */
int nDeps; /* number of valid dependencies */
int allocDeps; /* allocated size of dependencies[] */
} DependencyList;

And added addStandaloneDependency() based addObjectDependency()

I simplified it to always use realloc, as it can handle the NULL case

void
addStandaloneDependency(DependencyList *dobj, DumpId refId)
{
if (dobj->nDeps >= dobj->allocDeps)
{
dobj->allocDeps = (dobj->allocDeps <= 0) ? 16 : dobj->allocDeps * 2;
dobj->dependencies = pg_realloc_array(dobj->dependencies,
  DumpId, dobj->allocDeps);
}
dobj->dependencies[dobj->nDeps++] = refId;
}

And then I use AH->tableDataChunkIds in repoint_table_dependencies() to
- replace the dependency on table def with dependency on first chunk
- add the remaining cunks at the end of dependency list.

> I was able to verify the scheduling issue with an index: the INDEX
> part is scheduled too early, before all TABLE DATA completes, but then
> locking prevents it from progressing, so everything completed fine in
> the end. Even if that's guaranteed, which I'm not 100% sure of, it's
> still based on luck and not proper logic, and takes up a slot (or
> multiple), reducing parallelism.
>
> 2. Fixing the TABLE DATA strcmp checks solves the scheduling issue,
> but it's not that simple, because then it causes truncation issues
> during restore, which needs additional changes in the restore code. I
> did a quick fix for that by adding an additional condition to the
> created flag, and with that it seems to restore everything properly,
> and with proper ordering, only starting index/constraint/etc after all
> table data is completed. However this was definitely just a quick test
> fix, this needs a proper better solution.
>
> Other issues I see are more minor, but numerous:

I collect the chunk dependencies in a separate array, which
should solve the truncation issue.

Can you advise a good check to add to tap tests for verifying?

> 3. The patch still has lots of debug output (pg_log_WARNING("CHUNKING
> ...")); Is this intended? Shouldn't these be behind some verbose
> check, and maybe use info instead of warning?

This left in for easing initial reviewing. I have either removed them
or turned them into pg_log_debug()

> 4. The is_segment macro should have () around the use of tdiptr

Thanks, fixed.

> 5. There's still a 32000 magic constant, shouldn't that have some
> descriptive name / explanatory comment?

I turned this into "ctid < (pagenr+1, 0)" for clarity and
futureproofing, as it is not entirely impossible that we could have
at some point more than 32000 items per page.

> 6. formatting issues at multiple places, mostly missing spaces after
> if/while/for statements

My hope was that the pre-release automatic formatting run takes care of
this.

I will eyeball to see if I find theem, but I don't think I have a good
way to detect them all.

Suggestions very much welcome!

> 7. inconsistent error messages (not in range vs must be in range)

> 8. There's a remaining TODO that seems stale, current_chunk_start is
> already uint64

Removed.

> 9. typo: "the computed from pog_relation_size" -> "then computed from
> pg_relation_size"

Fixed.

On Thu, Feb 12, 2026 at 7:13 AM Dilip Kumar <[email-masked]> wrote:
>
> On Wed, Jan 28, 2026 at 11:00 PM Hannu Krosing <[email-masked]> wrote:
> >
> > v13 has added a proper test comparing original and restored table data
> >
> I was reviewing v13 and here are some initial comments I have
>
> 1. IMHO the commit message details about the work progress instead of
> a high level idea about what it actually does and how.
> Suggestion:
>
> SUBJECT: Add --max-table-segment-pages option to pg_dump for parallel
> table dumping.
>
> This patch introduces the ability to split large heap tables into segments
> based on a specified number of pages. These segments can then be dumped in
> parallel using the existing jobs infrastructure, significantly reducing
> the time required to dump very large tables.
>
> The implementation uses ctid-based range queries (e.g., WHERE ctid >=
> '(start,1)'
> AND ctid <= '(end,32000)') to extract specific chunks of the relation.
>
> <more architecture details and limitation if any>

SUBJECT: Add --max-table-segment-pages option to pg_dump for parallel
table dumping.

This patch introduces the ability to split large heap tables into segments
based on a specified number of pages. These segments can then be dumped in
parallel using the existing jobs infrastructure, significantly reducing
the time required to dump very large tables.

This --max-table-segment-pages number specifically applies to main table
pages which does not guarantee anything about output size.
The output could be empty if there are no live tuples in the page range.
Or it can be almost 200 GB if the page has just pointers to 1GB TOAST items.

The implementation uses ctid-based range queries (e.g., WHERE ctid >=
'(startPage,1)' AND ctid <= '(endPage+1,0)') to extract specific chunks of
the relation.

This is only effectively supported for PostgreSQL version 14+ though it does
work inefficiently on earlier versions

The patch only supports "heap" access method as others may not even have the
ctid column

> 2.
> + pg_log_warning("CHUNKING: set dopt.max_table_segment_pages to [%u]",
> dopt.max_table_segment_pages);
> + break;
>
> IMHO we don't need to place warning here while processing the input parameters

Either removed or turned to pg_log_debug()

> 3.
> + printf(_("  --max-table-segment-pages=NUMPAGES\n"
> +      "                               Number of main table pages
> above which data is \n"
> + "                               copied out in chunks, also
> determines the chunk size\n"));
>
> Check the comment formatting, all the parameter description starts
> with lower case, so better we start with "number" rather than "Number"

Fixed

> 4.
> + if (is_segment(tdinfo))
> + {
> + appendPQExpBufferStr(q, tdinfo->filtercond?" AND ":" WHERE ");
> + if(tdinfo->startPage == 0)
> + appendPQExpBuffer(q, "ctid <= '(%u,32000)'", tdinfo->endPage);
> + else if(tdinfo->endPage != InvalidBlockNumber)
> + appendPQExpBuffer(q, "ctid BETWEEN '(%u,1)' AND '(%u,32000)'",
> + tdinfo->startPage, tdinfo->endPage);
> + else
> + appendPQExpBuffer(q, "ctid >= '(%u,1)'", tdinfo->startPage);
> + pg_log_warning("CHUNKING: pages [%u:%u]",tdinfo->startPage, tdinfo->endPage);
> + }
>
> IMHO we should explain this chunking logic in the comment above this code block?

I added the comment.
I also changed the chunk end logic to "ctid < '(LastPage+1,0)'" for clarity and
future-proofing.

----
Best Regards

Hannu

---

From: Hannu Krosing <[email-masked]>
Date: Sat, 28 Mar 2026 16:32:23 +0100

The issue is that currently the value is given in "main table pages"
and it would be somewhat deceptive, or at least confusing, to try to
express this in any other unit.

As I explained in the commit message:

---------8<-------------------8<-------------------8<----------------
---------8<-------------------8<-------------------8<----------------

And I can think of no cheap and reliable way to change that equation.

I'll be very happy if you have any good ideas for either improving the
flag name, or even propose a way to better estimate the resulting dump
file size so we could give the chunk size in better units

---
Hannu

On Sat, Mar 28, 2026 at 12:26 PM Michael Banck <[email-masked]> wrote:
>
> Hi,
>
> On Tue, Jan 13, 2026 at 03:27:25PM +1300, David Rowley wrote:
> > Perhaps --max-table-segment-pages is a better name than
> > --huge-table-chunk-pages as it's quite subjective what the minimum
> > number of pages required to make a table "huge".
>
> I'm not sure that's better - without looking at the documentation,
> people might confuse segment here with the 1GB split of tables into
> segments. As pg_dump is a very common and basic user tool, I don't think
> implementation details like pages/page sizes and blocks should be part
> of its UX.
>
> Can't we just make it a storage size, like '10GB' and then rename it to
> --table-parallel-threshold or something? I agree it's bikeshedding, but
> I personally don't like either --max-table-segment-pages or
> --huge-table-chunk-pages.
>
>
> Michael

---

From: Hannu Krosing <[email-masked]>
Date: Sat, 28 Mar 2026 16:33:59 +0100

The above

"Or it can be almost 200 GB if the page has just pointers to 1GB TOAST items."

should read

"Or it can be almost 200 GB *for a single page* if the page has just
pointers to 1GB TOAST items."

---

From: Hannu Krosing <[email-masked]>
Date: Sun, 29 Mar 2026 23:49:38 +0200

Fixing a off-by-one error in copying over dependencies

---

From: Hannu Krosing <[email-masked]>
Date: Mon, 30 Mar 2026 19:32:34 +0200

Now the dependencies on chunks should also behave correctly

---

From: Zsolt Parragi <[email-masked]>
Date: Mon, 30 Mar 2026 22:32:22 +0100

Hello!

A simple test causes an assertion failure in my testing, dependency
counting still doesn't seem to work correctly:

pg_restore: >...>/pg_backup_archiver.c:5207: reduce_dependencies:
Assertion `otherte->depCount > 0' failed.

Without assertions it results in data loss.

004_pg_dump_parallel also showcases the issue in my testing.

But simple manual testing also confirms it:

1. create some data

CREATE TABLE tplain (id int UNIQUE);
INSERT INTO tplain SELECT x FROM generate_series(1,1000) x;

2. create a dump

dump with --max-table-segment-pages=2

3. try to restore

restore with --jobs=3