MIME-Version: 1.0
References: <CAG8xnif_jmRYAGUvtoQNsHA8UqGRDkwuSffO55-3wGMLQArA8g@mail.gmail.com>
 <CAKFQuwa96OCxiW5yLtnUVvN1U4208o9c3NvLxnL1MkojLhQcVg@mail.gmail.com> <CAKAnmmLhL-kPtMy9+rpwbaOpEi5AsidXzO8_z5-n=iOAG+1ZUA@mail.gmail.com>
In-Reply-To: <CAKAnmmLhL-kPtMy9+rpwbaOpEi5AsidXzO8_z5-n=iOAG+1ZUA@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 28 Jan 2026 10:57:22 -0500
Message-ID: <CANzqJaCTto6Sd+vi_g=empKWfd0c0KBw2SUE98koMF2OMcvd3A@mail.gmail.com>
Subject: Re: Attempting to delete excess rows from table with BATCH DELETE
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000006a0c08064974cc63"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaCTto6Sd%2Bvi_g%3DempKWfd0c0KBw2SUE98koMF2OMcvd3A%40mail.gmail.com>
Precedence: bulk

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

On Wed, Jan 28, 2026 at 10:39=E2=80=AFAM Greg Sabino Mullane <htamfids@gmai=
l.com>
wrote:

> 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 em=
pty
>> 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 dat=
a
> around in case something goes wrong, reduces WAL compared to massive
> deletes, and removes the need to mess with autovacuum.
>

Looping DELETE is the Dirt Simple option when the application is writing
24x7, when there's a lot of FK dependencies, etc.  It also allows you to
throttle the process (bash sleep between DELETE statements, or only purging
a few old days per script execution and then only run the script at night).

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

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

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Jan 28, 2026 at 10:39=E2=80=AFAM =
Greg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gmai=
l.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_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 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" target=3D"_blank">david.g.johnst=
on@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote"><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid =
rgb(204,204,204);padding-left:1ex"><div>Strongly encourage you to try to ac=
complish your goal without any delete commands at that scale that causes va=
cuuming.=C2=A0 Can you just create an empty copy and load the data to keep =
into it then point at the newly filled database?=C2=A0 Truncate is OK.</div=
></blockquote><div><br></div><div>This is really the best solution, especia=
lly if most of the rows are &gt; 75 days old. This removes 100% of your blo=
at, allows you to keep the old data around in case something goes wrong, re=
duces WAL compared to massive deletes, and removes the need to mess with au=
tovacuum.</div></div></div></blockquote><div></div></div><div><br></div><di=
v>Looping DELETE is the Dirt Simple option when the application is writing =
24x7, when there&#39;s a lot of FK dependencies,=C2=A0etc.=C2=A0 It also al=
lows you to throttle the process (bash sleep between DELETE statements, or =
only purging a few old days per script execution and then only run the scri=
pt at night).</div><div><br></div><span class=3D"gmail_signature_prefix">--=
 </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Dea=
th to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m st=
ill alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></=
div></div>

--0000000000006a0c08064974cc63--