MIME-Version: 1.0
References: 
 <CALAkhNUfJLU4czRKYOj28+k7h6vvdKsCN5Er8cRK+rS4vcLgsg@mail.gmail.com>
In-Reply-To: 
 <CALAkhNUfJLU4czRKYOj28+k7h6vvdKsCN5Er8cRK+rS4vcLgsg@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 14 Nov 2025 14:47:27 -0500
Message-ID: 
 <CANzqJaAbcfmmHH4mJygjK4fZYFGmzPPiFn5+RxvTeMYibbjxVw@mail.gmail.com>
Subject: Re: rebuild big tables with pgrepack
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000000e883a06439345a1"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaAbcfmmHH4mJygjK4fZYFGmzPPiFn5%2BRxvTeMYibbjxVw%40mail.gmail.com>
Precedence: bulk

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

On Fri, Nov 14, 2025 at 2:14=E2=80=AFPM ek ek <livadidrive@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?
>

Everything in database maintenance is circumstantial.

The basics that I'd do are:
* Verify that you have enough free disk space for both the new table, the
new indices and also the WALs generated.
* Do it during a low-activity window.
* Don't run a database backup at the same time.
* First execute with --dry-run.
* Consider the --no-order option.  That'll speed things up.
* And --no-analyze, though you'll have to manually ANALYZE immediately
afterwards.
* (I'd probably disable autoanalyze on that table before the repack and
then enable it after the manual ANALYZE.)
* The --jobs option speeds up index rebuilds.
* Run it from cron, and redirect both stdout and stderr to the same log
file.

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Nov 14, 2025 at 2:14=E2=80=AFPM e=
k ek &lt;<a href=3D"mailto:livadidrive@gmail.com">livadidrive@gmail.com</a>=
&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquo=
te class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px =
solid rgb(204,204,204);padding-left:1ex"><div dir=3D"auto"><div dir=3D"auto=
">Hello everyone,</div><div dir=3D"auto">I=E2=80=99m going to rebuild a 900=
GB table using pg_repack. I=E2=80=99m hesitant to do such a large operation=
 in one go.</div><div dir=3D"auto">Is there an ideal or recommended way to =
repack very large tables?</div></div></blockquote><div><br></div><div>Every=
thing in database=C2=A0maintenance=C2=A0is circumstantial.</div><div><br></=
div><div>The basics that I&#39;d do are:</div><div>* Verify that you have e=
nough free disk space for both the new table, the new indices and also the =
WALs generated.</div><div>* Do it during a low-activity window.</div><div>*=
 Don&#39;t run a database backup at the same time.</div><div>* First execut=
e with=C2=A0--dry-run.</div><div>* Consider the=C2=A0--no-order option.=C2=
=A0 That&#39;ll speed things up.</div><div>* And --no-analyze, though you&#=
39;ll have to manually ANALYZE immediately afterwards.</div><div>* (I&#39;d=
 probably disable autoanalyze=C2=A0on that table before the repack and then=
 enable it after the manual ANALYZE.)</div><div>* The --jobs option speeds =
up index rebuilds.</div><div>* Run it from cron, and redirect both stdout a=
nd stderr to the same log file.</div><div><br></div></div><span class=3D"gm=
ail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signatu=
re"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#=
39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</=
div></div></div></div></div></div>

--0000000000000e883a06439345a1--