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 <[email protected]> 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 <[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. >> >
0001-repack-concurrent-memory-use.patch
Description: Binary data
