Re: rebuild big tables with pgrepack

These recommendations were very helpful for me, thank you all.

On Sun, Nov 16, 2025, 00:33 <pg254kl(at)georgiou(dot)vip> wrote:

>
> On 11/14/25 2:14 PM, ek ek - livadidrive at gmail.com wrote:
>
> Hello everyone,
> I’m going to rebuild a 900GB table using pg_repack. I’m hesitant to do
> such a large operation in one go.
> Is there an ideal or recommended way to repack very large tables?
>
> I recall almost 20 years ago when Bruce Momjian was educating us on
> PostgreSQL (using Sybase ASE, but migrating to PostgreSQL), we discussed
> the table and index rebuilding mania. I never forgot the "we have clients
> that haven't rebuilt anything for years and they run just fine" haha. OK,
> sometimes if you have very "hot" tables its warranted, but the point that
> stayed with me is that it's usually unnecessary.
>
> But ok, you may have your reasons, so...
>
> 1. Connect to your cluster from a box with good connectivity to it
> (eg: for AWS RDS this means an EC2 instance on same VPC)
> 2. Definitely run pg_repack inside a tmux session
> 3. Be safe and have at least 3 x pg_total_relation_size(table) free
> space
> 4. Make sure you understand the -k (--no-kill-backend) and
> --wait-timeout options. By default (no -k) pg_repack will wait on blocking
> backends and on a busy table eventually timeout. Decisions decisions.
> Definitely run when things are calm.
> 5. If your default toast compression is not lz4, and this table uses
> TOAST, consider changing it to lz4 prior to this pg_repack. lz4 is
> blazingly fast with low cpu cost.
> 6. I don't like running anything heavy handed on production without
> practicing/testing first. If things go bad and management asks "did you
> test this?" the answer should always be "yes". If this is your first time
> pg_repacking such table, make sure you first do it on a staging environment
> under heavy load during a performance run. The cache churn can be an issue
> if your workload depends on a hot cache. Again, definitely run during the
> calmest window, but test under load ;)
>
> At 900GB I'd start considering partitioning, it will make maintenance jobs
> more efficient, not to mention the smaller indices should help with better
> use of your cache (if your partition strategy can segregate old unused data
> from newer used data).
>
>
> --
> regards,
> Kiriakos Georgiou
>
>