REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

Started by Radim Marekabout 2 months ago24 messageshackers
Jump to latest
#1Radim Marek
radim@boringsql.com

Hey,

specifically CC'ing Antonin as we already talked about some
squeezing/repacking problems in past.

As it's one of the features I'm looking forward to most, I did quite a lot
of testing of
REPACK (CONCURRENTLY) over the last 2 weeks. I'm happy to say I wasn't able
to hit any show stopper, no matter how much I tried to break it (although I
have some edge case scenarios for later). I ran 75+ hostile runs (lots of
them scenarios I've previously seen hurt pg_repack/pg_squeeze).

The one thing I found is a hard limit during the catch-up. I noticed the
backend memory growing with the amount of concurrent updates, and when I
pushed it with a big batch update running concurrently, REPACK
(CONCURRENTLY) got OOM-killed, and with enough memory it failed on a hard
limit instead.

As far as I can tell this came over from pg_squeeze, which applies changes
the same way. pg_repack doesn't hit this particular scenario.

To verify it I tried multiple outcomes and got to

limit outcome no of changes
1 GB backend SIGKILLed & cluster crash restart ~18.0M
4 GB backend SIGKILLed & cluster crash restart ~84.3M
8 GB ERROR: invalid memory alloc request 104,820,740
size 1677721600

To make it deterministic I paused REPACK just before catch-up (1M-row
table) and ran N full-table updates from another session, then let it go.
On a table that small REPACK would otherwise finish long before enough
changes pile up; on a big table the copy and index builds take hours, which
gives the same effect without any trick.

Memory limit is --memory / --memory-swap set the same on a docker
container, release build. All three on master on Apple Silicon; the 1 GB
case I repeated on 19beta4, both on Apple Silicon and in the same container
on amd64 VM (~17.2M and ~16.8M), so it's not master or ARM specific.

Memory increase is linear, somewhere around 50 bytes per replayed
update/delete, and no GUC caps it. Only when I treid 8 GB run I was
surprised by the fact it hits the fixed number of changes.

The surprise is that REPACK (CONCURRENTLY) can't finish with more than 105M
rows updated/deleted concurrently (rows, not statements). Imagine something
with large number of HOT updates and other adverse condition. It's not
going to affect basic use cases, but if I think about tables where I would
see REPACK (CONCURRENTLY) used as alternative to non-blocking CLUSTER
during the I/O problems due to the data collocation, this is actually quite
realistic. Over last year alone there was more than handful scenarios where
this was unfortunately peak time solution to get data sorted. While it
might be considered abuse, imo it's legitimate.

The example would be REPACK of 250 - 500 GB table (don't even get me
started on
over-indexed ones). Just this week I dealt with a 100 GB table where full
operation on managed instance would take roughly 1.5 hours, that's already
only ~19k row changes/s. Get to 5h REPACK and all it takes is ~6k row
changes/s. Not every day problem, but you know how it goes - when it
rains...

DISCLAIMER: what follows was LLM assisted. The numbers line up exactly with
the ERROR I got, but I can't claim I came up with the explanation myself.

---

Why it happens: every tuple in the new heap is inserted by the REPACK
transaction, and apply_concurrent_changes() does a CommandCounterIncrement
before each replayed UPDATE or DELETE. So each of those modifies a tuple
with our own xmin and an older cmin, which means a new combo CID per
change, kept until commit. It shows up as growth in "Combo CIDs" in
pg_log_backend_memory_contexts().

Where the ceiling comes from: combocid.c starts the array at 100 entries
and doubles it, and after 100 * 2^20 = 104,857,600 entries the next
repalloc (1,677,721,600 bytes) exceeds MaxAllocSize. That's independent
of available memory, so the limit is the same everywhere.

---

I believe this is not a stopper for REPACK (CONCURRENTLY) but given the
visibility of the feature this might be thing that migth get documented. It
will also attrack people who might not have prior experience with
concurrent repacking tools. Hence we can only hope the REPACKing is done in
sane periods, but then as written above - I definitely used pg_squeeze in
past to solve data layout issues. At the same time this might scale up to 5
GB more memory needed in times when DBAs might be already facing adverse
conditions.

Hopefully over the wekeend I'm going to publish the findings on my site (
boringsql.com) to document this behaviour under title "Sizing REPACK
(CONCURRENTLY) for busy tables".

Hope this make sense

Radim

PS: during my runs I also replicated the issue Thom Brown reported with
TOAST table.

#2shihao zhong
zhong950419@gmail.com
In reply to: Radim Marek (#1)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

Hi Radim,

Your analysis is right. On master, 2M replayed UPDATEs used about 106MB
for combo CIDs, close to your number.

Patch 0008 in Antonin's "REPACK enhancements" series [1]https://www.google.com/url?q=https://postgr.es/m/224072.1789577949@localhost&source=gmail&ust=1790462338814000&sa=E removes the
problem. It replays changes with their original XIDs and without the
per-change CommandCounterIncrement(). The same test made no combo CIDs
there. That series is aimed at 20, but I am also not sure if it is
appropriate to
say repack have performance issue in document.

I used an LLM for this as well. The numbers are from a run on my machine.

[1]: https://www.google.com/url?q=https://postgr.es/m/224072.1789577949@localhost&source=gmail&ust=1790462338814000&sa=E
https://www.google.com/url?q=https://postgr.es/m/224072.1789577949@localhost&source=gmail&ust=1790462338814000&sa=E

Thanks
Shihao

On Fri, Sep 25, 2026 03:59 PM, Radim Marek <radim@boringsql.com> wrote:

Show quoted text

Hey,

specifically CC'ing Antonin as we already talked about some
squeezing/repacking problems in past.

As it's one of the features I'm looking forward to most, I did quite a lot
of testing of
REPACK (CONCURRENTLY) over the last 2 weeks. I'm happy to say I wasn't
able to hit any show stopper, no matter how much I tried to break it
(although I have some edge case scenarios for later). I ran 75+ hostile
runs (lots of them scenarios I've previously seen hurt
pg_repack/pg_squeeze).

The one thing I found is a hard limit during the catch-up. I noticed the
backend memory growing with the amount of concurrent updates, and when I
pushed it with a big batch update running concurrently, REPACK
(CONCURRENTLY) got OOM-killed, and with enough memory it failed on a hard
limit instead.

As far as I can tell this came over from pg_squeeze, which applies changes
the same way. pg_repack doesn't hit this particular scenario.

To verify it I tried multiple outcomes and got to

limit outcome no of changes
1 GB backend SIGKILLed & cluster crash restart ~18.0M
4 GB backend SIGKILLed & cluster crash restart ~84.3M
8 GB ERROR: invalid memory alloc request 104,820,740
size 1677721600

To make it deterministic I paused REPACK just before catch-up (1M-row
table) and ran N full-table updates from another session, then let it go.
On a table that small REPACK would otherwise finish long before enough
changes pile up; on a big table the copy and index builds take hours, which
gives the same effect without any trick.

Memory limit is --memory / --memory-swap set the same on a docker
container, release build. All three on master on Apple Silicon; the 1 GB
case I repeated on 19beta4, both on Apple Silicon and in the same container
on amd64 VM (~17.2M and ~16.8M), so it's not master or ARM specific.

Memory increase is linear, somewhere around 50 bytes per replayed
update/delete, and no GUC caps it. Only when I treid 8 GB run I was
surprised by the fact it hits the fixed number of changes.

The surprise is that REPACK (CONCURRENTLY) can't finish with more than
105M rows updated/deleted concurrently (rows, not statements). Imagine
something with large number of HOT updates and other adverse condition.
It's not going to affect basic use cases, but if I think about tables where
I would see REPACK (CONCURRENTLY) used as alternative to non-blocking
CLUSTER during the I/O problems due to the data collocation, this is
actually quite realistic. Over last year alone there was more than handful
scenarios where this was unfortunately peak time solution to get data
sorted. While it might be considered abuse, imo it's legitimate.

The example would be REPACK of 250 - 500 GB table (don't even get me
started on
over-indexed ones). Just this week I dealt with a 100 GB table where full
operation on managed instance would take roughly 1.5 hours, that's already
only ~19k row changes/s. Get to 5h REPACK and all it takes is ~6k row
changes/s. Not every day problem, but you know how it goes - when it
rains...

DISCLAIMER: what follows was LLM assisted. The numbers line up exactly
with the ERROR I got, but I can't claim I came up with the explanation
myself.

---

Why it happens: every tuple in the new heap is inserted by the REPACK
transaction, and apply_concurrent_changes() does a CommandCounterIncrement
before each replayed UPDATE or DELETE. So each of those modifies a tuple
with our own xmin and an older cmin, which means a new combo CID per
change, kept until commit. It shows up as growth in "Combo CIDs" in
pg_log_backend_memory_contexts().

Where the ceiling comes from: combocid.c starts the array at 100 entries
and doubles it, and after 100 * 2^20 = 104,857,600 entries the next
repalloc (1,677,721,600 bytes) exceeds MaxAllocSize. That's independent
of available memory, so the limit is the same everywhere.

---

I believe this is not a stopper for REPACK (CONCURRENTLY) but given the
visibility of the feature this might be thing that migth get documented. It
will also attrack people who might not have prior experience with
concurrent repacking tools. Hence we can only hope the REPACKing is done in
sane periods, but then as written above - I definitely used pg_squeeze in
past to solve data layout issues. At the same time this might scale up to 5
GB more memory needed in times when DBAs might be already facing adverse
conditions.

Hopefully over the wekeend I'm going to publish the findings on my site (
boringsql.com) to document this behaviour under title "Sizing REPACK
(CONCURRENTLY) for busy tables".

Hope this make sense

Radim

PS: during my runs I also replicated the issue Thom Brown reported with
TOAST table.

#3Radim Marek
radim@boringsql.com
In reply to: shihao zhong (#2)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

Hi Shihao, thank you for the confirmation. I wasn't aware of the
enhancements series.

It's definitely not a performance issue in the doc; rathe a resource limit.
Since 19 will go with this limitation I attached a small doc patch about
this. Hope I got the use of 'other' correctly based on current version
https://www.postgresql.org/docs/19/sql-repack.html

Radim

On Sat, 26 Sept 2026 at 00:49, shihao zhong <zhong950419@gmail.com> wrote:

Show quoted text

Hi Radim,

Your analysis is right. On master, 2M replayed UPDATEs used about 106MB
for combo CIDs, close to your number.

Patch 0008 in Antonin's "REPACK enhancements" series [1] removes the
problem. It replays changes with their original XIDs and without the
per-change CommandCounterIncrement(). The same test made no combo CIDs
there. That series is aimed at 20, but I am also not sure if it is
appropriate to
say repack have performance issue in document.

I used an LLM for this as well. The numbers are from a run on my machine.

[1]
https://www.google.com/url?q=https://postgr.es/m/224072.1789577949@localhost&source=gmail&ust=1790462338814000&sa=E

Thanks
Shihao

On Fri, Sep 25, 2026 03:59 PM, Radim Marek <radim@boringsql.com> wrote:

Hey,

specifically CC'ing Antonin as we already talked about some
squeezing/repacking problems in past.

As it's one of the features I'm looking forward to most, I did quite a
lot of testing of
REPACK (CONCURRENTLY) over the last 2 weeks. I'm happy to say I wasn't
able to hit any show stopper, no matter how much I tried to break it
(although I have some edge case scenarios for later). I ran 75+ hostile
runs (lots of them scenarios I've previously seen hurt
pg_repack/pg_squeeze).

The one thing I found is a hard limit during the catch-up. I noticed the
backend memory growing with the amount of concurrent updates, and when I
pushed it with a big batch update running concurrently, REPACK
(CONCURRENTLY) got OOM-killed, and with enough memory it failed on a hard
limit instead.

As far as I can tell this came over from pg_squeeze, which applies
changes the same way. pg_repack doesn't hit this particular scenario.

To verify it I tried multiple outcomes and got to

limit outcome no of changes
1 GB backend SIGKILLed & cluster crash restart ~18.0M
4 GB backend SIGKILLed & cluster crash restart ~84.3M
8 GB ERROR: invalid memory alloc request 104,820,740
size 1677721600

To make it deterministic I paused REPACK just before catch-up (1M-row
table) and ran N full-table updates from another session, then let it go.
On a table that small REPACK would otherwise finish long before enough
changes pile up; on a big table the copy and index builds take hours, which
gives the same effect without any trick.

Memory limit is --memory / --memory-swap set the same on a docker
container, release build. All three on master on Apple Silicon; the 1 GB
case I repeated on 19beta4, both on Apple Silicon and in the same container
on amd64 VM (~17.2M and ~16.8M), so it's not master or ARM specific.

Memory increase is linear, somewhere around 50 bytes per replayed
update/delete, and no GUC caps it. Only when I treid 8 GB run I was
surprised by the fact it hits the fixed number of changes.

The surprise is that REPACK (CONCURRENTLY) can't finish with more than
105M rows updated/deleted concurrently (rows, not statements). Imagine
something with large number of HOT updates and other adverse condition.
It's not going to affect basic use cases, but if I think about tables where
I would see REPACK (CONCURRENTLY) used as alternative to non-blocking
CLUSTER during the I/O problems due to the data collocation, this is
actually quite realistic. Over last year alone there was more than handful
scenarios where this was unfortunately peak time solution to get data
sorted. While it might be considered abuse, imo it's legitimate.

The example would be REPACK of 250 - 500 GB table (don't even get me
started on
over-indexed ones). Just this week I dealt with a 100 GB table where full
operation on managed instance would take roughly 1.5 hours, that's already
only ~19k row changes/s. Get to 5h REPACK and all it takes is ~6k row
changes/s. Not every day problem, but you know how it goes - when it
rains...

DISCLAIMER: what follows was LLM assisted. The numbers line up exactly
with the ERROR I got, but I can't claim I came up with the explanation
myself.

---

Why it happens: every tuple in the new heap is inserted by the REPACK
transaction, and apply_concurrent_changes() does a CommandCounterIncrement
before each replayed UPDATE or DELETE. So each of those modifies a tuple
with our own xmin and an older cmin, which means a new combo CID per
change, kept until commit. It shows up as growth in "Combo CIDs" in
pg_log_backend_memory_contexts().

Where the ceiling comes from: combocid.c starts the array at 100 entries
and doubles it, and after 100 * 2^20 = 104,857,600 entries the next
repalloc (1,677,721,600 bytes) exceeds MaxAllocSize. That's independent
of available memory, so the limit is the same everywhere.

---

I believe this is not a stopper for REPACK (CONCURRENTLY) but given the
visibility of the feature this might be thing that migth get documented. It
will also attrack people who might not have prior experience with
concurrent repacking tools. Hence we can only hope the REPACKing is done in
sane periods, but then as written above - I definitely used pg_squeeze in
past to solve data layout issues. At the same time this might scale up to 5
GB more memory needed in times when DBAs might be already facing adverse
conditions.

Hopefully over the wekeend I'm going to publish the findings on my site (
boringsql.com) to document this behaviour under title "Sizing REPACK
(CONCURRENTLY) for busy tables".

Hope this make sense

Radim

PS: during my runs I also replicated the issue Thom Brown reported with
TOAST table.

Attachments:

t253948_3
0001-repack-concurrent-memory-use.patchapplication/octet-stream; name=0001-repack-concurrent-memory-use.patchDownload+7-1
#4Antonin Houska
ah@cybertec.at
In reply to: Radim Marek (#3)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

Radim Marek <radim@boringsql.com> wrote:

Hi Shihao, thank you for the confirmation. I wasn't aware of the enhancements series.

It's definitely not a performance issue in the doc; rathe a resource limit. Since 19 will go with this limitation I attached a small doc patch
about this. Hope I got the use of 'other' correctly based on current version https://www.postgresql.org/docs/19/sql-repack.html

It's unfortunate that REPACK is probably the only command that exercises this
combo CID limit. However, there can be other limits, e.g. REPACK being unable
to catch up if the change rate is too high, excessive usage of disk for the
decoded changes, etc.

I think that the most important thing for the user to know is that the purpose
of REPACK is to improve performance (via bloat removal and/or clustering),
whereas "failsafe VACUUM" (i.e. VACUUM with INDEX_CLEANUP set to off) should
be used to avoid the risk of XID wraparound. This kind of VACCUM is probably
much faster and less likely to fail.

I don't have a good idea about the wording right now.

--
Antonin Houska
Web: https://www.cybertec-postgresql.com

#5shihao zhong
zhong950419@gmail.com
In reply to: Antonin Houska (#4)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

Hi Antonin,

I think that the most important thing for the user to know is that the

purpose

of REPACK is to improve performance (via bloat removal and/or clustering),
whereas "failsafe VACUUM" (i.e. VACUUM with INDEX_CLEANUP set to off)

should

be used to avoid the risk of XID wraparound.

I don't have a good idea about the wording right now.

Here is a try with Fable, as v2 of Radim's patch.

It adds a paragraph to Notes. REPACK is for bloat and clustering. For
wraparound uses VACUUM, because REPACK takes much longer and can fail
late.

Thanks,
Shihao

Attachments:

t253948_5
v2-0001-doc-Document-REPACK-limits-and-point-to-VACUUM-fo.patchapplication/octet-stream; name=v2-0001-doc-Document-REPACK-limits-and-point-to-VACUUM-fo.patchDownload+25-1
#6Alvaro Herrera
alvherre@kurilemu.de
In reply to: shihao zhong (#5)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

On 2026-Sep-27, shihao zhong wrote:

Here is a try with Fable, as v2 of Radim's patch.

It adds a paragraph to Notes. REPACK is for bloat and clustering. For
wraparound uses VACUUM, because REPACK takes much longer and can fail
late.

Yeah, that sounds appropriate.

I think the original <note> paragraph is worth rewriting more deeply
though rather than just adding one more paragraph; I think some of the
things it mentions are not so relevant from the user's POV, and also I
think we can use some small changes elsewhere in the page.

What do you think of the attached? I used -U7 in `git format-patch` so
that the surrounding can be read directly from the patch.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/

Attachments:

t253948_6
v3-0001-doc-Document-REPACK-limits-and-point-to-VACUUM-fo.patchtext/x-diff; charset=utf-8Download+48-24
#7Antonin Houska
ah@cybertec.at
In reply to: Alvaro Herrera (#6)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

Álvaro Herrera <alvherre@kurilemu.de> wrote:

On 2026-Sep-27, shihao zhong wrote:

Here is a try with Fable, as v2 of Radim's patch.

It adds a paragraph to Notes. REPACK is for bloat and clustering. For
wraparound uses VACUUM, because REPACK takes much longer and can fail
late.

Yeah, that sounds appropriate.

I think the original <note> paragraph is worth rewriting more deeply
though rather than just adding one more paragraph; I think some of the
things it mentions are not so relevant from the user's POV, and also I
think we can use some small changes elsewhere in the page.

What do you think of the attached? I used -U7 in `git format-patch` so
that the surrounding can be read directly from the patch.

I thought about this documentation update last week and wasn't sure it needs
to be that exact about how much memory is consumed per changed tuple. I think
we should rather encourage users to do monitoring in genaral: besides memory,
the system can run out of disk space (REPACK w/o CONCURRENTLY also creates a
copy of the table, but it's probably not used for big tables due to the
stronger lock). Besides that, REPACK runs in a single transaction, so it might
hold the xmin horizon(s) for too long (like any other long-running
transactions). Finally, the processing of the concurrent changes might not be
fast enough, in which case the AccessExclusiveLock may be needed for
surprisingly long time.

The paragraph about REPACK vs VACUUM LGTM.

--
Antonin Houska
Web: https://www.cybertec-postgresql.com

#8Alvaro Herrera
alvherre@kurilemu.de
In reply to: Antonin Houska (#7)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

On 2026-Oct-06, Antonin Houska wrote:

I thought about this documentation update last week and wasn't sure it needs
to be that exact about how much memory is consumed per changed tuple.

True. I do want to clarify that the amount of memory used is not the
size of the tuples, though.

I think
we should rather encourage users to do monitoring in genaral: besides memory,
the system can run out of disk space (REPACK w/o CONCURRENTLY also creates a
copy of the table, but it's probably not used for big tables due to the
stronger lock). Besides that, REPACK runs in a single transaction, so it might
hold the xmin horizon(s) for too long (like any other long-running
transactions). Finally, the processing of the concurrent changes might not be
fast enough, in which case the AccessExclusiveLock may be needed for
surprisingly long time.

Hmm, I'll see what I can come up with. Note you say "it might hold the
xmin horizon like any other long-running transactions", but that's not
true for VACUUM, so I think it's worth pointing it out explicitly, since
it's a clear disadvantage of REPACK.

The paragraph about REPACK vs VACUUM LGTM.

Great, thanks for looking.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Tiene valor aquel que admite que es un cobarde" (Fernandel)

#9Alvaro Herrera
alvherre@kurilemu.de
In reply to: Antonin Houska (#7)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

On 2026-Oct-06, Antonin Houska wrote:

I thought about this documentation update last week and wasn't sure it needs
to be that exact about how much memory is consumed per changed tuple. I think
we should rather encourage users to do monitoring in genaral: besides memory,
the system can run out of disk space (REPACK w/o CONCURRENTLY also creates a
copy of the table, but it's probably not used for big tables due to the
stronger lock). Besides that, REPACK runs in a single transaction, so it might
hold the xmin horizon(s) for too long (like any other long-running
transactions). Finally, the processing of the concurrent changes might not be
fast enough, in which case the AccessExclusiveLock may be needed for
surprisingly long time.

I have revamped the reference page for REPACK rather heavily. What do
you think of this? I attach the HTML for easy reading.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/

Attachments:

t253948_9
v4-0001-Revamp-REPACK-doc-refentry-page.patchtext/x-diff; charset=utf-8Download+191-154
sql-repack.htmltext/html; charset=utf-8Download
#10Alvaro Herrera
alvherre@kurilemu.de
In reply to: Alvaro Herrera (#9)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

Actually, after going over Thom Brown's report on MVCC unsafety, I
looked again and decided to move the <warning> box and the list of
restrictions for CONCURRENTLY to also appear in the "Notes on Concurrent
Operation" subsection, instead of inside the "Parameters" section.
So here it is again, both in patch form as well as HTML.

Thanks

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"In Europe they call me Niklaus Wirth; in the US they call me Nickel's worth.
That's because in Europe they call me by name, and in the US by value!"

Attachments:

t253948_10
v5-0001-Revamp-REPACK-doc-refentry-page.patchtext/x-diff; charset=utf-8Download+265-228
sql-repack.htmltext/html; charset=utf-8Download
#11Antonin Houska
ah@cybertec.at
In reply to: Alvaro Herrera (#10)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

Alvaro Herrera <alvherre@kurilemu.de> wrote:

Actually, after going over Thom Brown's report on MVCC unsafety, I
looked again and decided to move the <warning> box and the list of
restrictions for CONCURRENTLY to also appear in the "Notes on Concurrent
Operation" subsection, instead of inside the "Parameters" section.
So here it is again, both in patch form as well as HTML.

[ Thanks for the HTML. ]

I suspect there's a thinko in the "Notes on Resources" section. Shouldn't the
first paragraph start like this?

"When the USING INDEX clause is given and an index scan is chosen, ..."

No objections regarding the "Notes on Concurrent Operation" section.

--
Antonin Houska
Web: https://www.cybertec-postgresql.com

#12Alvaro Herrera
alvherre@kurilemu.de
In reply to: Antonin Houska (#11)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

On 2026-Oct-06, Antonin Houska wrote:

I suspect there's a thinko in the "Notes on Resources" section. Shouldn't the
first paragraph start like this?

"When the USING INDEX clause is given and an index scan is chosen, ..."

Hmm, no -- this paragraph tries to explain what happens when we use the
unsorted option, _or_ we use the sorted option but we use a indexscan to
implement it. So either the VACUUM FULL, or the indexscan based
CLUSTER. In this case we only have one extra copy of the table.

The second paragraph tries to describe what happens when we use a
seqscan and sort -- that is, we have USING INDEX, but we don't scan
using that index (that is, CLUSTER that uses a sort). That requires
additional storage (the tuplestore that is sorted).

Maybe we need to reword both so that this is more obvious?

No objections regarding the "Notes on Concurrent Operation" section.

Thanks for reading!

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"I think my standards have lowered enough that now I think 'good design'
is when the page doesn't irritate the living f*ck out of me." (JWZ)

#13Radim Marek
radim@boringsql.com
In reply to: Alvaro Herrera (#12)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

Hey, what about wording

REPACK creates a temporary copy of the table and all of its indexes, so you
need free disk space at least equal to the sum of the table size and the
index sizes.

If clause `USING INDEX` is provided and the planner chooses a sequential
scan and sort, a temporary sort file is also needed, so the maximum space
requirement can reach twice the table size plus the index sizes. This
method is usually faster than an index scan, but if disk space is tight,
you might need to set `enable_sort` to `off` to force an index scan, or
omit `USING INDEX` if the table does not need to be reordered.

---

Radim

On Tue, 6 Oct 2026 at 22:05, Alvaro Herrera <alvherre@kurilemu.de> wrote:

Show quoted text

On 2026-Oct-06, Antonin Houska wrote:

I suspect there's a thinko in the "Notes on Resources" section.

Shouldn't the

first paragraph start like this?

"When the USING INDEX clause is given and an index scan is chosen, ..."

Hmm, no -- this paragraph tries to explain what happens when we use the
unsorted option, _or_ we use the sorted option but we use a indexscan to
implement it. So either the VACUUM FULL, or the indexscan based
CLUSTER. In this case we only have one extra copy of the table.

The second paragraph tries to describe what happens when we use a
seqscan and sort -- that is, we have USING INDEX, but we don't scan
using that index (that is, CLUSTER that uses a sort). That requires
additional storage (the tuplestore that is sorted).

Maybe we need to reword both so that this is more obvious?

No objections regarding the "Notes on Concurrent Operation" section.

Thanks for reading!

--
Álvaro Herrera Breisgau, Deutschland —
https://www.EnterpriseDB.com/
"I think my standards have lowered enough that now I think 'good design'
is when the page doesn't irritate the living f*ck out of me." (JWZ)

#14Antonin Houska
ah@cybertec.at
In reply to: Alvaro Herrera (#12)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

Alvaro Herrera <alvherre@kurilemu.de> wrote:

On 2026-Oct-06, Antonin Houska wrote:

I suspect there's a thinko in the "Notes on Resources" section. Shouldn't the
first paragraph start like this?

"When the USING INDEX clause is given and an index scan is chosen, ..."

Hmm, no -- this paragraph tries to explain what happens when we use the
unsorted option, _or_ we use the sorted option but we use a indexscan to
implement it. So either the VACUUM FULL, or the indexscan based
CLUSTER. In this case we only have one extra copy of the table.

ok. What I find disturbing in the first sentence is the "but" conjunction:

"... that clause is given but an index scan is chosen ..."

Shouldn't it rater be "and"?

When I read "but", I usually expect a contradiction. However I see no
contradiction between specifying the USING INDEX clause and choosing index
scan.

--
Antonin Houska
Web: https://www.cybertec-postgresql.com

#15Alvaro Herrera
alvherre@kurilemu.de
In reply to: Antonin Houska (#14)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

On 2026-Oct-07, Antonin Houska wrote:

ok. What I find disturbing in the first sentence is the "but" conjunction:

"... that clause is given but an index scan is chosen ..."

Shouldn't it rater be "and"?

When I read "but", I usually expect a contradiction. However I see no
contradiction between specifying the USING INDEX clause and choosing index
scan.

You're right, that was pretty hard to read. How about this, following
Radim's suggestion?

<para>
<command>REPACK</command> creates a temporary copy of the table and all
of its indexes, so you need free disk space at least equal to the sum
of the table size and the index sizes.
</para>

<para>
If the <literal>USING INDEX</literal> clause is given and the planner
chooses a sequential scan and sort to implement it, a temporary sort
file that uses the table size again is needed in addition to the above.
This method is usually faster than an index scan, but if the disk space
requirement is intolerable, you can disable this choice by temporarily
setting <xref linkend="guc-enable-sort"/> to <literal>off</literal>; or
omitting <literal>USING INDEX</literal> if the table doesn't need to be
reordered.
</para>

The "... that uses the table size again is needed ..." bit looks
somewhat odd to me, but I don't know how to make it better.

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
Are you not unsure you want to delete Firefox?
[Not unsure] [Not not unsure] [Cancel]
http://smylers.hates-software.com/2008/01/03/566e45b2.html

#16Alvaro Herrera
alvherre@kurilemu.de
In reply to: Alvaro Herrera (#15)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

On 2026-Oct-07, Alvaro Herrera wrote:

You're right, that was pretty hard to read. How about this, following
Radim's suggestion?

I settled on this, which seems perfectly clear:

<para>
<command>REPACK</command> creates a temporary copy of the table and all
of its indexes, so you need free disk space at least equal to the sum
of the table size and the index sizes.
</para>

<para>
If the <literal>USING INDEX</literal> clause is given and the planner
chooses a sequential scan and sort to implement it, a temporary sort
file of up to the size of the table is needed in addition to the above.
This method is usually faster than an index scan, but if the disk space
requirement is intolerable, you can disable this choice by temporarily
setting <xref linkend="guc-enable-sort"/> to <literal>off</literal>; or
omitting <literal>USING INDEX</literal> if the table doesn't need to be
reordered.
</para>

Also, the note about REPACK being one long transaction preventing useful
vacuuming work applies to both regular one and concurrent, so it must
appear in the "Notes on Resources" section rather than "Notes on
Concurrent Operation". I'll move it up a subsection.

And with that, I will push it. Further changes are still possible and
suggestions are welcome, of course.

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Cómo ponemos nuestros dedos en la arcilla del otro. Eso es la amistad; jugar
al alfarero y ver qué formas se pueden sacar del otro" (C. Halloway en
La Feria de las Tinieblas, R. Bradbury)

#17Antonin Houska
ah@cybertec.at
In reply to: Alvaro Herrera (#15)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

Alvaro Herrera <alvherre@kurilemu.de> wrote:

On 2026-Oct-07, Antonin Houska wrote:

ok. What I find disturbing in the first sentence is the "but" conjunction:

"... that clause is given but an index scan is chosen ..."

Shouldn't it rater be "and"?

When I read "but", I usually expect a contradiction. However I see no
contradiction between specifying the USING INDEX clause and choosing index
scan.

You're right, that was pretty hard to read. How about this, following
Radim's suggestion?

<para>
<command>REPACK</command> creates a temporary copy of the table and all
of its indexes, so you need free disk space at least equal to the sum
of the table size and the index sizes.
</para>

<para>
If the <literal>USING INDEX</literal> clause is given and the planner
chooses a sequential scan and sort to implement it, a temporary sort
file that uses the table size again is needed in addition to the above.
This method is usually faster than an index scan, but if the disk space
requirement is intolerable, you can disable this choice by temporarily
setting <xref linkend="guc-enable-sort"/> to <literal>off</literal>; or
omitting <literal>USING INDEX</literal> if the table doesn't need to be
reordered.
</para>

Yes, this looks well structured - first the simple case, then the "less
simple".

The "... that uses the table size again is needed ..." bit looks
somewhat odd to me, but I don't know how to make it better.

The only variant that occurs to me is

"... that creates one more copy of the table ..."

Just in case.

--
Antonin Houska
Web: https://www.cybertec-postgresql.com

#18Alvaro Herrera
alvherre@kurilemu.de
In reply to: Alvaro Herrera (#16)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

Pushed this. Thanks for the reviews!

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"El sudor es la mejor cura para un pensamiento enfermo" (Bardia)

#19Nathan Bossart
nathandbossart@gmail.com
In reply to: Alvaro Herrera (#18)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

On Wed, Oct 07, 2026 at 02:26:54PM +0200, Alvaro Herrera wrote:

Pushed this. Thanks for the reviews!

We talked about this one in today's RMT meeting.

Processing of these concurrent changes requires a fixed small amount of
memory for each tuple concurrently updated or deleted, not limited by
maintenance_work_mem; if more than about 104 million rows are
concurrently updated or deleted during the execution of REPACK, the
command fails.

This seems like a rather low limit that will definitely affect folks in the
field. The upthread discussion indicates that the "make REPACK
(CONCURRENTLY) MVCC-safe" work should fix this, but could there be an
incremental improvement that alleviates this limitation in the meantime?

--
nathan

#20Alvaro Herrera
alvherre@kurilemu.de
In reply to: Nathan Bossart (#19)
Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

On 2026-Oct-07, Nathan Bossart wrote:

We talked about this one in today's RMT meeting.

Processing of these concurrent changes requires a fixed small amount of
memory for each tuple concurrently updated or deleted, not limited by
maintenance_work_mem; if more than about 104 million rows are
concurrently updated or deleted during the execution of REPACK, the
command fails.

This seems like a rather low limit that will definitely affect folks in the
field. The upthread discussion indicates that the "make REPACK
(CONCURRENTLY) MVCC-safe" work should fix this, but could there be an
incremental improvement that alleviates this limitation in the meantime?

The limit is caused by the dynahash used in combocid.c to store the CID
mappings. An "easy" fix for this might be to mark that allocation as
using palloc_huge() somehow. I haven't actually tried that, but I
suspect it's not without its risks. (Since I didn't test it, I don't
know either if there are any other limits beyond that one.)

I'm not really sure that 105 million tuples modified concurrently during
repack is really all that small a limitation. I mean, it's not
**huge**, but it's not tiny either. If you update 1000 tuples per
second while REPACK runs, you'd need a REPACK run that takes 28 hours to
reach the limit. I guess it's definitely not impossible to hit it, if
your workload is insane enough.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/

#21shihao zhong
zhong950419@gmail.com
In reply to: Nathan Bossart (#19)
#22Radim Marek
radim@boringsql.com
In reply to: shihao zhong (#21)
#23Alvaro Herrera
alvherre@kurilemu.de
In reply to: Radim Marek (#22)
#24Nathan Bossart
nathandbossart@gmail.com
In reply to: Alvaro Herrera (#23)