REPACK (CONCURRENTLY) can't complete after ~105M concurrent updates/deletes

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.