MIME-Version: 1.0
References: <CAG8xnif_jmRYAGUvtoQNsHA8UqGRDkwuSffO55-3wGMLQArA8g@mail.gmail.com>
 <CAKFQuwbudVTMKedFTuR+GXntRM=oZ=sCgyp1Ztg2=DUciuEbQA@mail.gmail.com>
 <729685.1769572320@sss.pgh.pa.us> <CANzqJaCydWJm8utvhLMWy62MGRYP0qdK134WB8NzXTJf=KUEPg@mail.gmail.com>
 <731539.1769573201@sss.pgh.pa.us> <CAJ7S9TWwFpaRD9eND1=GMmCoKPG4fDasU6LBEd=yaq2JyNqRdA@mail.gmail.com>
 <CAG8xnie28cnR7220M8G3iQ0G-L_c3WOp6ZrSOiP58M3-B=6-Zw@mail.gmail.com> <CANzqJaC-u_kkwHK5jKQZ6zedUYDXXFq7CX8Y4_zh0ZB0QMtO5Q@mail.gmail.com>
In-Reply-To: <CANzqJaC-u_kkwHK5jKQZ6zedUYDXXFq7CX8Y4_zh0ZB0QMtO5Q@mail.gmail.com>
From: Gus Spier <gus.spier@gmail.com>
Date: Wed, 28 Jan 2026 10:39:00 -0500
Message-ID: <CAG8xniexsPtem2+5KpsAv_J-qO2dtD1tkhgcsD_f7H6xnrbPmw@mail.gmail.com>
Subject: Re: Attempting to delete excess rows from table with BATCH DELETE
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Olivier Gautherot <ogautherot@gautherot.net>, Tom Lane <tgl@sss.pgh.pa.us>, 
	"David G. Johnston" <david.g.johnston@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAG8xniexsPtem2%2B5KpsAv_J-qO2dtD1tkhgcsD_f7H6xnrbPmw%40mail.gmail.com>
Precedence: bulk

Good thoughts. Thanks, Ron!


On Wed, Jan 28, 2026 at 10:02=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail.c=
om> wrote:
>
> More thoughts:
> 1. In cases where records are huge (bytea storing images) I added an inne=
r hourly loop.
> 2. Disable autovaccum on the table you're purging, then run pg_repack on =
it and re-enable autovacuum.
> 3. pg_repack --no-order is a lot faster than having it order by the PK.  =
(You might want it ordered by an indexed date field, though.)
>
> On Wed, Jan 28, 2026 at 5:57=E2=80=AFAM Gus Spier <gus.spier@gmail.com> w=
rote:
>>
>> 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=E2=80=AFAM Olivier Gautherot
>> <ogautherot@gautherot.net> wrote:
>> >
>> > Hi Gus!
>> >
>> > This reminds me of a costly mistake I made and you want to avoid: it w=
as a mission critical database (say physical safety, real people) and the v=
acuum 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 a=
nd rename it - this will save the DELETE above and will defragment the tabl=
e)
>> > - 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 upd=
ate)
>> > - 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=C3=A9, 28 de ene de 2026, 5:06=E2=80=AFa.m., Tom Lane <tgl@sss.p=
gh.pa.us> escribi=C3=B3:
>> >>
>> >> Ron Johnson <ronljohnsonjr@gmail.com> writes:
>> >> > Hmm.  Must have been START TRANSACTION which I remember causing iss=
ues 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
>> >>
>> >>
>
>
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!