Re: Attempting to delete excess rows from table with BATCH DELETE - Mailing list pgsql-general

From Ron Johnson
Subject Re: Attempting to delete excess rows from table with BATCH DELETE
Date
Msg-id CANzqJaCTto6Sd+vi_g=empKWfd0c0KBw2SUE98koMF2OMcvd3A@mail.gmail.com
Whole thread Raw
In response to Re: Attempting to delete excess rows from table with BATCH DELETE  (Greg Sabino Mullane <htamfids@gmail.com>)
List pgsql-general
On Wed, Jan 28, 2026 at 10:39 AM Greg Sabino Mullane <htamfids@gmail.com> wrote:
On Tue, Jan 27, 2026 at 10:31 PM 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 empty 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.

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!

pgsql-general by date:

Previous
From: Gus Spier
Date:
Subject: Re: Attempting to delete excess rows from table with BATCH DELETE
Next
From: Adrian Klaver
Date:
Subject: Re: Index (primary key) corrupt?