public inbox for [email protected]
help / color / mirror / Atom feedFrom: ek ek <[email protected]>
To: [email protected]
Cc: Pgsql-admin <[email protected]>
Subject: Re: rebuild big tables with pgrepack
Date: Wed, 19 Nov 2025 22:59:54 +0300
Message-ID: <CALAkhNUM-_L4_4h0x6Yu-i+JTFS5JkpvMrgd9SrACsTN_WRRVA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CALAkhNUfJLU4czRKYOj28+k7h6vvdKsCN5Er8cRK+rS4vcLgsg@mail.gmail.com>
<[email protected]>
These recommendations were very helpful for me, thank you all.
On Sun, Nov 16, 2025, 00:33 <[email protected]> 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
>
>
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: rebuild big tables with pgrepack
In-Reply-To: <CALAkhNUM-_L4_4h0x6Yu-i+JTFS5JkpvMrgd9SrACsTN_WRRVA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox