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

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.

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.