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

Hello,

> > This seems like a rather low limit that will definitely affect
> > folks in the field.
>
> I am not a heavy user of pg_repack, just curious, why do we think
> 105 million is a very small number?
>

As a medium-heavy repack user over the last decade, I'd say this limit is
acceptable for now. Online repacking of a large table is rarely done
without planning, and what decides whether you hit the limit is storage
throughput (how long the run takes) times the rate of change on the table.

I put numbers on that in [1]. The budget is roughly 29k changed rows/s
divided by the run's duration in hours. Generous for a 250 GB table, tight
(circa 1.5k rows/s) for 5 TB on a modest cloud VM. But at that size a run
takes most of a day, and you are already planning around everything else
boing on hold - vacuum, disk saturation, WAL volume, etc. The change budget
is more or less "just" one more item on that list.

My point in starting this thread was to make sure people affected on v19
know the limit exists, and the fact it might attract new audience that
previously was not using repacking heavily. The worst case is losing all
that work at the very last step, quite possibly while you're already
dealing with something close to an incident. If 19 ships with it, I think
it deserves a sentence in the REPACK docs, next to the disk space note.

Radim

PS: I've been using pg_squeeze for exactly those scenarios as pg_repack has
some limitations (ironically one of them was FOR ALL TABLES without
exception when logical replication is already used). For me
personally REPACK (CONCURRENTLY) as now planned for v19 is improvement in
all operational aspects.

[1] https://boringsql.com/posts/repack-concurrently-costs/#when-the-run-is-long

> > An "easy" fix for this might be to mark that allocation as
> > using palloc_huge() somehow.
>
> The request that fails is the comboCids array in GetComboCommandId(),
> not the hash table. 8 bytes times 209,715,200 entries is the
> 1677721600 in Radim's error. I do not see a limit in the hash table.
>
> I tried repalloc_array_extended() there. With 110 million replayed updates
> master fails with that error, and the patched build completes with
> correct data. It only changes runs that fail today. From reading the
> code, the next limits are the int array size at about 1.7 billion
> entries, and then 2^32 commands.
>
> It does not lower the memory use. I measured 48 bytes for each
> change, so the limit today also caps this at about 5 GB. Without it,
> 1 billion changes need about 50 GB, and a host with less gets the
> OOM killer, not an ERROR.
>
> Experimental patch attached.
>
> Thanks,
> Shihao
>
>
>