MIME-Version: 1.0
References: 
 <CALAkhNUfJLU4czRKYOj28+k7h6vvdKsCN5Er8cRK+rS4vcLgsg@mail.gmail.com>
 <176324240033.6.6265117135512277017.1008813004@georgiou.vip>
In-Reply-To: <176324240033.6.6265117135512277017.1008813004@georgiou.vip>
From: ek ek <livadidrive@gmail.com>
Date: Wed, 19 Nov 2025 22:59:54 +0300
Message-ID: 
 <CALAkhNUM-_L4_4h0x6Yu-i+JTFS5JkpvMrgd9SrACsTN_WRRVA@mail.gmail.com>
Subject: Re: rebuild big tables with pgrepack
To: pg254kl@georgiou.vip
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f37e130643f80628"
Archived-At: 
 <https://www.postgresql.org/message-id/CALAkhNUM-_L4_4h0x6Yu-i%2BJTFS5JkpvMrgd9SrACsTN_WRRVA%40mail.gmail.com>
Precedence: bulk

--000000000000f37e130643f80628
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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

On Sun, Nov 16, 2025, 00:33 <pg254kl@georgiou.vip> wrote:

>
> On 11/14/25 2:14 PM, ek ek - livadidrive at gmail.com wrote:
>
> Hello everyone,
> I=E2=80=99m going to rebuild a 900GB table using pg_repack. I=E2=80=99m h=
esitant 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 client=
s
> 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 blo=
cking
>    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 y=
ou
>    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 enviro=
nment
>    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 job=
s
> more efficient, not to mention the smaller indices should help with bette=
r
> use of your cache (if your partition strategy can segregate old unused da=
ta
> from newer used data).
>
>
> --
> regards,
> Kiriakos Georgiou
>
>

--000000000000f37e130643f80628
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto">These recommendations were very helpful for me, thank you=
 all.</div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D=
"ltr" class=3D"gmail_attr">On Sun, Nov 16, 2025, 00:33  &lt;pg254kl@georgio=
u.vip&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin=
:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><u></u>

 =20
   =20
 =20
  <div>
    <br>
    <div>On 11/14/25 2:14 PM, ek ek -
      livadidrive at <a href=3D"http://gmail.com" target=3D"_blank" rel=3D"=
noreferrer">gmail.com</a> wrote:<br>
    </div>
    <blockquote type=3D"cite">
     =20
      <div dir=3D"auto">
        <div dir=3D"auto">Hello everyone,</div>
        <div dir=3D"auto">I=E2=80=99m going to rebuild a 900GB table using
          pg_repack. I=E2=80=99m hesitant to do such a large operation in o=
ne
          go.</div>
        <div dir=3D"auto">Is there an ideal or recommended way to repack
          very large tables?</div>
      </div>
    </blockquote>
    <p>I recall almost 20 years ago when=C2=A0<span></span><span>Bruce </sp=
an><span>Momjian
        was educating us on PostgreSQL (using Sybase ASE, but migrating
        to PostgreSQL), we discussed the table and index rebuilding
        mania.=C2=A0 I never forgot the &quot;we have clients that haven=
9;t rebuilt
        anything for years and they run just fine&quot; haha.=C2=A0 OK, som=
etimes
        if you have very &quot;hot&quot; tables its warranted, but the poin=
t that
        stayed with me is that it&#39;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.=C2=A0 By default (no -k) pg_repack will wai=
t
        on blocking backends and on a busy table eventually timeout.=C2=A0
        Decisions decisions.=C2=A0 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.=C2=A0 lz4 is blazingly fast with low cpu cost.</li>
      <li>I don&#39;t like running anything heavy handed on production
        without practicing/testing first.=C2=A0 If things go bad and
        management asks &quot;did you test this?&quot; the answer should al=
ways be
        &quot;yes&quot;.=C2=A0 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.=C2=A0 The cache churn can be an issue if
        your workload depends on a hot cache.=C2=A0 Again, definitely run
        during the calmest window, but test under load ;)</li>
    </ol>
    <p>At 900GB I&#39;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=C2=A0parti=
tion
      strategy can segregate old unused data from newer used data).</p>
    <pre cols=3D"72">
--=20
regards,
Kiriakos Georgiou</pre>
  </div>

</blockquote></div>

--000000000000f37e130643f80628--