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] 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.
>