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 <[email protected]> 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. >
