Subject: Re: Attempting to delete excess rows from table with BATCH DELETE

From: "David G. Johnston" <[email-masked]>
Date: Tue, 27 Jan 2026 20:31:22 -0700

On Tuesday, January 27, 2026, Gus Spier <[email-masked]> 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.

---

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 28 Jan 2026 10:38:53 -0500

> 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

---

From: Ron Johnson <[email-masked]>
Date: Wed, 28 Jan 2026 10:57:22 -0500

> 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).

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