Re: REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes - Mailing list pgsql-hackers

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.