public inbox for [email protected]
help / color / mirror / Atom feedFrom: Olivier Gautherot <[email protected]>
To: Tom Lane <[email protected]>
Cc: Ron Johnson <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: Gus Spier <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Attempting to delete excess rows from table with BATCH DELETE
Date: Wed, 28 Jan 2026 08:32:03 +0100
Message-ID: <CAJ7S9TWwFpaRD9eND1=GMmCoKPG4fDasU6LBEd=yaq2JyNqRdA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAG8xnif_jmRYAGUvtoQNsHA8UqGRDkwuSffO55-3wGMLQArA8g@mail.gmail.com>
<CAKFQuwbudVTMKedFTuR+GXntRM=oZ=sCgyp1Ztg2=DUciuEbQA@mail.gmail.com>
<[email protected]>
<CANzqJaCydWJm8utvhLMWy62MGRYP0qdK134WB8NzXTJf=KUEPg@mail.gmail.com>
<[email protected]>
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) latest in thread
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: <CAJ7S9TWwFpaRD9eND1=GMmCoKPG4fDasU6LBEd=yaq2JyNqRdA@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