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(at)gmail(dot)com> wrote:

> 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(at)boringsql(dot)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.
>>
>