public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ron Johnson <[email protected]>
To: Shardul Borhade <[email protected]>
To: Pgsql-admin <[email protected]>
Subject: Re: rebuild big tables with pgrepack
Date: Mon, 24 Nov 2025 10:26:33 -0500
Message-ID: <CANzqJaB_mC-ArHTg1KBPvf8azEHaw+yfbJci5DX3kPPF2uefBQ@mail.gmail.com> (raw)
In-Reply-To: <CA+xwDugHp=jmn-xqaL1cJj4UK7h7hv4PcyvkmF0NTQ8YdE7Law@mail.gmail.com>
References: <CALAkhNUfJLU4czRKYOj28+k7h6vvdKsCN5Er8cRK+rS4vcLgsg@mail.gmail.com>
	<CANzqJaAbcfmmHH4mJygjK4fZYFGmzPPiFn5+RxvTeMYibbjxVw@mail.gmail.com>
	<CA+xwDugHp=jmn-xqaL1cJj4UK7h7hv4PcyvkmF0NTQ8YdE7Law@mail.gmail.com>

Possibly *triple*, since on a test database I noticed that a repack of a
24GB table needed not only the 24 extra GB for the new copy, but also
generated 23GB of lz4-compressed WAL files in the pgbackrest archive.

Of course, if that 900GB table is mostly empty, you'll only need triple the
"actually used" space.

On Mon, Nov 24, 2025 at 6:26 AM Shardul Borhade <[email protected]> wrote:

> Hi Ron,
>
> So basically, we need to have twice the space of the table and its indexes
> available before performing a repack, right?
>
> On Fri, Nov 14, 2025 at 8:47 PM Ron Johnson <[email protected]>
> wrote:
>
>> On Fri, Nov 14, 2025 at 2:14 PM ek ek <[email protected]> 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?
>>>
>>
>> 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.
>>
>> --
>> Death to <Redacted>, and butter sauce.
>> Don't boil me, I'm still alive.
>> <Redacted> lobster!
>>
>

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

view thread (5+ messages)

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: <CANzqJaB_mC-ArHTg1KBPvf8azEHaw+yfbJci5DX3kPPF2uefBQ@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