Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vNYSo-006JT0-02 for pgsql-admin@arkaria.postgresql.org; Mon, 24 Nov 2025 15:26:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vNYSl-001TAU-0f for pgsql-admin@arkaria.postgresql.org; Mon, 24 Nov 2025 15:26:47 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vNYSk-001T9w-2O for pgsql-admin@lists.postgresql.org; Mon, 24 Nov 2025 15:26:47 +0000 Received: from mail-oa1-x2a.google.com ([2001:4860:4864:20::2a]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vNYSi-001DzB-27 for pgsql-admin@lists.postgresql.org; Mon, 24 Nov 2025 15:26:46 +0000 Received: by mail-oa1-x2a.google.com with SMTP id 586e51a60fabf-3e83c40e9dfso2762491fac.1 for ; Mon, 24 Nov 2025 07:26:45 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1763998004; x=1764602804; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=shRxldiZuFptkPrQvnkG6b3WLtvvra8+41ZRgpcGyhQ=; b=clso4FEyJCA0nJILr/fRvpWgn9YVZbnCu+T0VdlTi0wpShlkKUeDV/1jfbDmMrX+eQ Wn8E9vIJw011o8YIU+Xc43O4WSRi34wXTYnx3UgjTtjKYervtBEH7HRI11vle651QTo6 Gg2SYwn/YtQM7k0qKQv9beJtAbTcq6+R5k8/vzL20l6NmZFnWjtLDWOUHpkFrMkjVB// O3wbxaAuR3GmvOOfJpm8z9GWp7XPrm9ZlWOaUGo/xU/XnlBhyYKVlpRlrMeKQflPe4Jy kulBJooIYVr49yFl9e1Gc1gxYx26kZrVGNqUHESXU4W2RgKls8XlAIg5200NNfySu9cx QmiQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1763998004; x=1764602804; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=shRxldiZuFptkPrQvnkG6b3WLtvvra8+41ZRgpcGyhQ=; b=Xxl95xvLrsO/TYkE4/AW0RsT5pHDgC+MsR83VuoJ6GCaJLlIkkHWrYBt5XE4sHMBvm a0YJq3oSoQqgXer55Jv7eMTqr5Um0EzsKMwZSmHMGQJaGc3vDSo0EGDazl+IuzQYNHzY NggiHM0jxmGjj15YSDi8zaCkWyTkGn2hLLCgQLvF28euoPYJGpT6bHj2rNOf1iZ0O1QK Wyhy2Txr5zaqEb7hpC4kt+3gD8u4grJ+00UVnfvQ+2lsg9KP9Vj6lPrNOjmFrtIQOc/r vWjNgDof1+9TRbHlaMJu0KQu3YXdKOontHfB/orQvfja74uJ6WN8mqA7TMhT4kPBMhal w/dw== X-Forwarded-Encrypted: i=1; AJvYcCWKa5ZDpCPDh1GtDVm2cGDFTS046v24ykYcEaT2aumkMNNxrVTqJeuEEJz5Nei1XX5OanEDPxaY2fhSTA==@lists.postgresql.org X-Gm-Message-State: AOJu0Yxz88cIo4faU9lUBNk8gW6bmzTXPDHF0aujMKM/xW1cgh/dOacz ZoNdDRwWwC/bdCFDzq7wSnrnJNdowTCu8XXHy678cGj/dtTUX1TpWnxpSF7L3GA0Myrobg+F6e6 w+QMvq3gLiP9Agp9d3YdDR1qMaOMVTHfTGw== X-Gm-Gg: ASbGncuSCWzvDNEUdl9GDUsoSiF9vtYtfzh+kKp10jxF2POlEdOxDiEmYrCQotcyex6 iH/FuOXFbZcaFoPBFdRpw3hzNRqPfH1Ab48pYr1vycz9riaG5axTUDsDTgnCpzJCz4padyfdXXi 0UFYmBc6B9KLazoEqSJpsAc9HFdKCAJCQ9fJ18ur5ohLEHqHD/pQsY8+CYraQ6nH1z0/gBbTlGY BRlx33hkBUZOI2lCT9XfgNYD8Bw2aQQ4AAlRlsar8tzZ/BwYiGBEuqqga6tI5d0XIosZJD2 X-Google-Smtp-Source: AGHT+IECgyxpYIltIRrBeUTxTKml3/da8j59+l/HArFl4JZptROUBCgq11F0xj9tFfxE98VIS7j3cEioMArz47LkIR8= X-Received: by 2002:a05:6808:4f67:b0:450:d6fe:898c with SMTP id 5614622812f47-45112d25ac7mr4932651b6e.54.1763998004417; Mon, 24 Nov 2025 07:26:44 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Mon, 24 Nov 2025 10:26:33 -0500 X-Gm-Features: AWmQ_bmaN1U4AT1AYW7nhYP_G8LrA8nNbiZ0SXr0hTzBsqXtHkcMNlfojrD9HjQ Message-ID: Subject: Re: rebuild big tables with pgrepack To: Shardul Borhade , Pgsql-admin Content-Type: multipart/alternative; boundary="000000000000701eac064458cafc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000701eac064458cafc Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM Shardul Borhade wrote: > Hi Ron, > > So basically, we need to have twice the space of the table and its indexe= s > available before performing a repack, right? > > On Fri, Nov 14, 2025 at 8:47=E2=80=AFPM Ron Johnson > wrote: > >> On Fri, Nov 14, 2025 at 2:14=E2=80=AFPM ek ek wr= ote: >> >>> Hello everyone, >>> 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 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, th= e >> 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 , and butter sauce. >> Don't boil me, I'm still alive. >> lobster! >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000701eac064458cafc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Possibly=C2=A0triple, since on a test database= I noticed that a repack of a 24GB table needed not only the 24 extra GB fo= r 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=E2=80=AFAM Shard= ul Borhade <shardul@dbtune.com= > wrote:
Hi Ron,

So basically, we need to have twice= the space of the table and its indexes available before performing a repac= k, right?

On Fri, Nov 14, 2025 at 8:47=E2=80=AFPM Ron Johnson <ronljohnsonjr@gma= il.com> wrote:
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 hesitant to do such a large operat= ion in one go.
Is there an ideal or recommended way = to repack very large tables?

Ev= erything in database=C2=A0maintenance=C2=A0is circumstantial.
The basics that I'd do are:
* Verify that you hav= e enough free disk space for both the new table, the new indices and also t= he WALs generated.
* Do it during a low-activity window.
* Don't run a database backup at the same time.
* First exe= cute with=C2=A0--dry-run.
* Consider the=C2=A0--no-order option.= =C2=A0 That'll speed things up.
* And --no-analyze, though yo= u'll have to manually ANALYZE immediately afterwards.
* (I= 9;d probably disable autoanalyze=C2=A0on that table before the repack and t= hen enable it after the manual ANALYZE.)
* The --jobs option spee= ds up index rebuilds.
* Run it from cron, and redirect both stdou= t and stderr to the same log file.

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


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--000000000000701eac064458cafc--