MIME-Version: 1.0
References: <CAG8xnif_jmRYAGUvtoQNsHA8UqGRDkwuSffO55-3wGMLQArA8g@mail.gmail.com>
In-Reply-To: <CAG8xnif_jmRYAGUvtoQNsHA8UqGRDkwuSffO55-3wGMLQArA8g@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 27 Jan 2026 22:57:56 -0500
Message-ID: <CANzqJaBcHnScWQjsVLoNg3OiQqWh3RcNEyNxwFV65Ps6PErQNw@mail.gmail.com>
Subject: Re: Attempting to delete excess rows from table with BATCH DELETE
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000075de4e06496abf49"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBcHnScWQjsVLoNg3OiQqWh3RcNEyNxwFV65Ps6PErQNw%40mail.gmail.com>
Precedence: bulk

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

On Tue, Jan 27, 2026 at 10:22=E2=80=AFPM Gus Spier <gus.spier@gmail.com> wr=
ote:

> Environment: AWS RDS Aurora for PostgreSQL 15 hosting tables that
> support scientific research. The development environment predominantly
> uses JPA with Hibernate.
>
> Years of neglect have allowed mission tables to accumulate hundreds of
> millions of rows of excess data. The developers and the customer
> decided we must delete all rows older than 75 days. Table partitioning
> was briefly considered but discarded because of the effort needed to
> refactor the codebase.
>
> I proposed the straight-forward course of action: delete by batches
> from the victim tables.
>
> The solution seemed obvious:
>
> For candidate tables:
> - Determine timestamp column iwith the most relevant value and call it
> the Discriminator.
> - Delete any row whose Discriminator value is 60 days older than the
> Discriminator, with a LIMIT of 50000,
> - Get the results of the batch by querying GET DIAGNOSTICS. That value
> held aside for later use.
> - COMMIT the delete transaction
> - Loop to the top and continue deleting batches until there are no
> more rows older than 60 days.
>
> - Before running a test, I ensure that the Discriminator column is indexe=
d
>
> I write a procedure to accomplish all this work but it persists in
> returning a error to the effect that a COMMIT is not valid in a block
> tht tries to DELETE data.
>
> Has anybody seen this before?


Yup, when putting the COMMIT in a DO block, which isn't allowed.


> Is there a solution?
>

Loop using bash.  In a similar case like this, I first looked for the
oldest day's data in the tables, then did something like this bash
pseudo-code:
StopDate=3D$(date -d'60 days ago')
DeleteDay=3D$1
export PGHOST=3Dfoo.example.com
export PGDATABASE=3Dbar
while [[ "$DeleteDay <=3D "$StopDate" ]]; do
    psql -Xc "DELETE FROM blarge WHERE txn_date > '$DeleteDay' + INTERVAL
'1' DAY;"
    DeleteDay=3D$(date -d "$DeleteDay + 1 day" +"%Y-%m-%d")
done

Using that method, I developed a fast and automated monthly archive process
which exported and then deleted from 120 tables.

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

--00000000000075de4e06496abf49
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:22=E2=80=AFPM =
Gus Spier &lt;<a href=3D"mailto:gus.spier@gmail.com">gus.spier@gmail.com</a=
>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px=
 solid rgb(204,204,204);padding-left:1ex">Environment: AWS RDS Aurora for P=
ostgreSQL 15 hosting tables that<br>
support scientific research. The development environment predominantly<br>
uses JPA with Hibernate.<br>
<br>
Years of neglect have allowed mission tables to accumulate hundreds of<br>
millions of rows of excess data. The developers and the customer<br>
decided we must delete all rows older than 75 days. Table partitioning<br>
was briefly considered but discarded because of the effort needed to<br>
refactor the codebase.<br>
<br>
I proposed the straight-forward course of action: delete by batches<br>
from the victim tables.<br>
<br>
The solution seemed obvious:<br>
<br>
For candidate tables:<br>
- Determine timestamp column iwith the most relevant value and call it<br>
the Discriminator.<br>
- Delete any row whose Discriminator value is 60 days older than the<br>
Discriminator, with a LIMIT of 50000,<br>
- Get the results of the batch by querying GET DIAGNOSTICS. That value<br>
held aside for later use.<br>
- COMMIT the delete transaction<br>
- Loop to the top and continue deleting batches until there are no<br>
more rows older than 60 days.<br>
<br>
- Before running a test, I ensure that the Discriminator column is indexed<=
br>
<br>
I write a procedure to accomplish all this work but it persists in<br>
returning a error to the effect that a COMMIT is not valid in a block<br>
tht tries to DELETE data.<br>
<br>
Has anybody seen this before?</blockquote><div><br></div><div>Yup, when put=
ting the COMMIT in a DO block, which isn&#39;t allowed.</div><div>=C2=A0</d=
iv><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bord=
er-left:1px solid rgb(204,204,204);padding-left:1ex"> Is there a solution?<=
br></blockquote><div><br></div><div>Loop using bash.=C2=A0 In a similar cas=
e like this, I first looked for the oldest day&#39;s data in the tables, th=
en did something like this bash pseudo-code:</div><div><font face=3D"monosp=
ace">StopDate=3D$(date -d&#39;60 days ago&#39;)</font></div><div><font face=
=3D"monospace">DeleteDay=3D$1</font></div><div><div><font face=3D"monospace=
">export PGHOST=3D<a href=3D"http://foo.example.com">foo.example.com</a></f=
ont></div><div><font face=3D"monospace">export PGDATABASE=3Dbar</font></div=
></div><div><font face=3D"monospace">while [[ &quot;$DeleteDay &lt;=3D &quo=
t;$StopDate&quot; ]]; do</font></div><div><span style=3D"font-family:monosp=
ace">=C2=A0 =C2=A0 psql -Xc &quot;DELETE FROM </span>blarge<span style=3D"f=
ont-family:monospace"> </span>WHERE<span style=3D"font-family:monospace"> t=
xn_date &gt; &#39;$DeleteDay&#39; + INTERVAL &#39;1&#39; DAY;&quot;</span><=
/div><div><font face=3D"monospace">=C2=A0 =C2=A0 DeleteDay=3D$(date -d &quo=
t;$DeleteDay + 1 day&quot; +&quot;%Y-%m-%d&quot;)<br></font></div><div><fon=
t face=3D"monospace">done</font></div><div><br></div><div>Using that method=
, I developed a fast and automated monthly archive process which exported a=
nd then deleted from 120 tables.</div><div><br></div></div><span class=3D"g=
mail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signat=
ure"><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>

--00000000000075de4e06496abf49--