public inbox for [email protected]
help / color / mirror / Atom feedRe: Attempting to delete excess rows from table with BATCH DELETE
3+ messages / 3 participants
[nested] [flat]
* Re: Attempting to delete excess rows from table with BATCH DELETE
@ 2026-01-28 03:31 David G. Johnston <[email protected]>
2026-01-28 15:38 ` Re: Attempting to delete excess rows from table with BATCH DELETE Greg Sabino Mullane <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2026-01-28 03:31 UTC (permalink / raw)
To: Gus Spier <[email protected]>; +Cc: pgsql-general <[email protected]>
On Tuesday, January 27, 2026, Gus Spier <[email protected]> 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Attempting to delete excess rows from table with BATCH DELETE
2026-01-28 03:31 Re: Attempting to delete excess rows from table with BATCH DELETE David G. Johnston <[email protected]>
@ 2026-01-28 15:38 ` Greg Sabino Mullane <[email protected]>
2026-01-28 15:57 ` Re: Attempting to delete excess rows from table with BATCH DELETE Ron Johnson <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Greg Sabino Mullane @ 2026-01-28 15:38 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Gus Spier <[email protected]>; pgsql-general <[email protected]>
On Tue, Jan 27, 2026 at 10:31 PM David G. Johnston <
[email protected]> 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.
Cheers,
Greg
--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Attempting to delete excess rows from table with BATCH DELETE
2026-01-28 03:31 Re: Attempting to delete excess rows from table with BATCH DELETE David G. Johnston <[email protected]>
2026-01-28 15:38 ` Re: Attempting to delete excess rows from table with BATCH DELETE Greg Sabino Mullane <[email protected]>
@ 2026-01-28 15:57 ` Ron Johnson <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Ron Johnson @ 2026-01-28 15:57 UTC (permalink / raw)
To: pgsql-general <[email protected]>
On Wed, Jan 28, 2026 at 10:39 AM Greg Sabino Mullane <[email protected]>
wrote:
> On Tue, Jan 27, 2026 at 10:31 PM David G. Johnston <
> [email protected]> 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!
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-01-28 15:57 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-28 03:31 Re: Attempting to delete excess rows from table with BATCH DELETE David G. Johnston <[email protected]>
2026-01-28 15:38 ` Greg Sabino Mullane <[email protected]>
2026-01-28 15:57 ` Ron Johnson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox