Content-Type: multipart/alternative;
 boundary="------------AmsJ7mm5gclnuC5U7EdVKUXx"
Date: Sat, 15 Nov 2025 16:33:17 -0500
MIME-Version: 1.0
Subject: Re: rebuild big tables with pgrepack
In-Reply-To: 
 <CALAkhNUfJLU4czRKYOj28+k7h6vvdKsCN5Er8cRK+rS4vcLgsg@mail.gmail.com>
Content-Transfer-Encoding: 7bit
From: pg254kl@georgiou.vip
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Message-ID: <176324240033.6.6265117135512277017.1008813004@georgiou.vip>
References: 
 <CALAkhNUfJLU4czRKYOj28+k7h6vvdKsCN5Er8cRK+rS4vcLgsg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/176324240033.6.6265117135512277017.1008813004%40georgiou.vip>
Precedence: bulk

This is a multi-part message in MIME format.
--------------AmsJ7mm5gclnuC5U7EdVKUXx
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit


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

--------------AmsJ7mm5gclnuC5U7EdVKUXx
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <br>
    <div class="moz-cite-prefix">On 11/14/25 2:14 PM, ek ek -
      livadidrive at gmail.com wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CALAkhNUfJLU4czRKYOj28+k7h6vvdKsCN5Er8cRK+rS4vcLgsg@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="auto">
        <div dir="auto">Hello everyone,</div>
        <div dir="auto">I’m going to rebuild a 900GB table using
          pg_repack. I’m hesitant to do such a large operation in one
          go.</div>
        <div dir="auto">Is there an ideal or recommended way to repack
          very large tables?</div>
      </div>
    </blockquote>
    <p>I recall almost 20 years ago when <span></span><span>Bruce </span><span>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.</span></p>
    <p>But ok, you may have your reasons, so...</p>
    <ol>
      <li>Connect to your cluster from a box with good connectivity to
        it (eg: for AWS RDS this means an EC2 instance on same VPC)</li>
      <li>Definitely run pg_repack inside a tmux session</li>
      <li>Be safe and have at least 3 x pg_total_relation_size(table)
        free space</li>
      <li>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.</li>
      <li>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.</li>
      <li>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 ;)</li>
    </ol>
    <p>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).</p>
    <pre class="moz-signature" cols="72">
-- 
regards,
Kiriakos Georgiou</pre>
  </body>
</html>

--------------AmsJ7mm5gclnuC5U7EdVKUXx--