public inbox for [email protected]  

help / color / mirror / Atom feed

From: Gus Spier <[email protected]>
To: Olivier Gautherot <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Ron Johnson <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Attempting to delete excess rows from table with BATCH DELETE
Date: Wed, 28 Jan 2026 05:57:09 -0500
Message-ID: <CAG8xnie28cnR7220M8G3iQ0G-L_c3WOp6ZrSOiP58M3-B=6-Zw@mail.gmail.com> (raw)
In-Reply-To: <CAJ7S9TWwFpaRD9eND1=GMmCoKPG4fDasU6LBEd=yaq2JyNqRdA@mail.gmail.com>
References: <CAG8xnif_jmRYAGUvtoQNsHA8UqGRDkwuSffO55-3wGMLQArA8g@mail.gmail.com>
	<CAKFQuwbudVTMKedFTuR+GXntRM=oZ=sCgyp1Ztg2=DUciuEbQA@mail.gmail.com>
	<[email protected]>
	<CANzqJaCydWJm8utvhLMWy62MGRYP0qdK134WB8NzXTJf=KUEPg@mail.gmail.com>
	<[email protected]>
	<CAJ7S9TWwFpaRD9eND1=GMmCoKPG4fDasU6LBEd=yaq2JyNqRdA@mail.gmail.com>

Thanks to all.

I'll give the bash loop method a try and let you know how it works out.

Regards to all,
Gus


On Wed, Jan 28, 2026 at 2:32 AM Olivier Gautherot
<[email protected]> wrote:
>
> Hi Gus!
>
> This reminds me of a costly mistake I made and you want to avoid: it was a mission critical database (say physical safety, real people) and the vacuum froze the DB for 24 hours, until I finally took it offline.
>
> If you can take it offline (and you have a couple of hours)
> - disconnect the DB
> - drop indexes (that's the killer)
> - remove unnecessary data
> - vaccuum manually (or better, copy the relevant data to a new table and rename it - this will save the DELETE above and will defragment the table)
> - rebuild indexes
> - connect the DB
>
> The better solution would be partitioning:
> - choose a metrics (for instance a timestamp)
> - create partition tables for the period you want to keep
> - copy the relevant data to the partitions and create partial indexes
> - take the DB off line
> - update the last partition with the latest data (should be a fast update)
> - truncate the original table
> - connect partitions
> - connect the DB
>
> In the future, deleting historic data will be a simple DROP TABLE.
>
> Hope it helps
> --
> Olivier Gautherot
> Tel: +33 6 02 71 92 23
>
>
> El mié, 28 de ene de 2026, 5:06 a.m., Tom Lane <[email protected]> escribió:
>>
>> Ron Johnson <[email protected]> writes:
>> > Hmm.  Must have been START TRANSACTION which I remember causing issues in DO
>> >  blocks.
>>
>> Too lazy to test, but I think we might reject that.  The normal rule
>> in a procedure is that the next command after a COMMIT automatically
>> starts a new transaction, so you don't need an explicit START.
>>
>>                         regards, tom lane
>>
>>

view thread (5+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Attempting to delete excess rows from table with BATCH DELETE
  In-Reply-To: <CAG8xnie28cnR7220M8G3iQ0G-L_c3WOp6ZrSOiP58M3-B=6-Zw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox