MIME-Version: 1.0
References: <CAG8xnif_jmRYAGUvtoQNsHA8UqGRDkwuSffO55-3wGMLQArA8g@mail.gmail.com>
 <CAKFQuwa96OCxiW5yLtnUVvN1U4208o9c3NvLxnL1MkojLhQcVg@mail.gmail.com>
In-Reply-To: <CAKFQuwa96OCxiW5yLtnUVvN1U4208o9c3NvLxnL1MkojLhQcVg@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Wed, 28 Jan 2026 10:38:53 -0500
Message-ID: <CAKAnmmLhL-kPtMy9+rpwbaOpEi5AsidXzO8_z5-n=iOAG+1ZUA@mail.gmail.com>
Subject: Re: Attempting to delete excess rows from table with BATCH DELETE
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Gus Spier <gus.spier@gmail.com>, pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c27bcd0649748bd7"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmLhL-kPtMy9%2BrpwbaOpEi5AsidXzO8_z5-n%3DiOAG%2B1ZUA%40mail.gmail.com>
Precedence: bulk

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

On Tue, Jan 27, 2026 at 10:31=E2=80=AFPM David G. Johnston <
david.g.johnston@gmail.com> wrote:

> Strongly encourage you to try to accomplish your goal without any delete
> commands at that scale that causes vacuuming.  Can you just create an emp=
ty
> copy and load the data to keep into it then point at the newly filled
> database?  Truncate is OK.
>

This is really the best solution, especially if most of the rows are > 75
days old. This removes 100% of your bloat, allows you to keep the old data
around in case something goes wrong, reduces WAL compared to massive
deletes, and removes the need to mess with autovacuum.


Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

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

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Jan 27, 2026 at 10:31=E2=80=AFPM =
David G. Johnston &lt;<a href=3D"mailto:david.g.johnston@gmail.com">david.g=
.johnston@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quo=
te_container"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px=
 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div>Strong=
ly encourage you to try to accomplish your goal without any delete commands=
 at that scale that causes vacuuming.=C2=A0 Can you just create an empty co=
py and load the data to keep into it then point at the newly filled databas=
e?=C2=A0 Truncate is OK.</div></blockquote><div><br></div><div>This is real=
ly the best solution, especially if most of the rows are &gt; 75 days old. =
This removes 100% of your bloat, allows you to keep the old data around in =
case something goes wrong, reduces WAL compared to massive deletes, and rem=
oves the need to mess with autovacuum.</div><div><br></div><div><br></div><=
div>Cheers,</div></div><div dir=3D"ltr" class=3D"gmail_signature"><div dir=
=3D"ltr"><div>Greg</div><div><br></div><div>--</div><div>Crunchy Data - <a =
href=3D"https://www.crunchydata.com" target=3D"_blank">https://www.crunchyd=
ata.com</a></div><div>Enterprise Postgres Software Products &amp; Tech Supp=
ort</div><div><br></div></div></div></div>

--000000000000c27bcd0649748bd7--