MIME-Version: 1.0
In-Reply-To: <CAG8xnif_jmRYAGUvtoQNsHA8UqGRDkwuSffO55-3wGMLQArA8g@mail.gmail.com>
References: <CAG8xnif_jmRYAGUvtoQNsHA8UqGRDkwuSffO55-3wGMLQArA8g@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Tue, 27 Jan 2026 20:31:22 -0700
Message-ID: <CAKFQuwa96OCxiW5yLtnUVvN1U4208o9c3NvLxnL1MkojLhQcVg@mail.gmail.com>
Subject: Re: Attempting to delete excess rows from table with BATCH DELETE
To: Gus Spier <gus.spier@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000cb43ae06496a5fd0"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwa96OCxiW5yLtnUVvN1U4208o9c3NvLxnL1MkojLhQcVg%40mail.gmail.com>
Precedence: bulk

--000000000000cb43ae06496a5fd0
Content-Type: text/plain; charset="UTF-8"

On Tuesday, January 27, 2026, Gus Spier <gus.spier@gmail.com> wrote:

> 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.
>
>
Strongly encourage you to try to accomplish your goal without any delete
commands at that scale that causes vacuuming.  Can you just create an empty
copy and load the data to keep into it then point at the newly filled
database?  Truncate is OK.

Daily trimming going forward would be less problematic at least.

David J.

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

On Tuesday, January 27, 2026, Gus Spier &lt;<a href=3D"mailto:gus.spier@gma=
il.com">gus.spier@gmail.com</a>&gt; wrote:<br><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex=
">Environment: AWS RDS Aurora for PostgreSQL 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>
</blockquote><div><br></div><div>Strongly encourage you to try to accomplis=
h your goal without any delete commands at that scale that causes vacuuming=
.=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><div><=
br></div><div>Daily trimming going forward would be less problematic at lea=
st.</div><div><br></div><div>David J.</div><div><br></div>

--000000000000cb43ae06496a5fd0--