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>
In-Reply-To: <731539.1769573201@sss.pgh.pa.us>
From: Olivier Gautherot <ogautherot@gautherot.net>
Date: Wed, 28 Jan 2026 08:32:03 +0100
Message-ID: <CAJ7S9TWwFpaRD9eND1=GMmCoKPG4fDasU6LBEd=yaq2JyNqRdA@mail.gmail.com>
Subject: Re: Attempting to delete excess rows from table with BATCH DELETE
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Ron Johnson <ronljohnsonjr@gmail.com>, 
	"David G. Johnston" <david.g.johnston@gmail.com>, Gus Spier <gus.spier@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000333cec06496dbd27"
Archived-At: <https://www.postgresql.org/message-id/CAJ7S9TWwFpaRD9eND1%3DGMmCoKPG4fDasU6LBEd%3Dyaq2JyNqRdA%40mail.gmail.com>
Precedence: bulk

--000000000000333cec06496dbd27
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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=C3=A9, 28 de ene de 2026, 5:06=E2=80=AFa.m., Tom Lane <tgl@sss.pgh.pa=
.us> escribi=C3=B3:

> Ron Johnson <ronljohnsonjr@gmail.com> 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
>
>
>

--000000000000333cec06496dbd27
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"auto"><div><div>Hi Gus!</div><div dir=3D"auto"><br></div><div d=
ir=3D"auto">This reminds me of a costly mistake I made and you want to avoi=
d: it was a mission critical database (say physical safety, real people) an=
d the vacuum froze the DB for 24 hours, until I finally took it offline.</d=
iv><div dir=3D"auto"><br></div><div dir=3D"auto">If you can take it offline=
 (and you have a couple of hours)</div><div dir=3D"auto">- disconnect the D=
B</div><div dir=3D"auto">- drop indexes (that&#39;s the killer)</div><div d=
ir=3D"auto">- remove unnecessary data</div><div dir=3D"auto">- vaccuum manu=
ally (or better, copy the relevant data to a new table and rename it - this=
 will save the DELETE above and will defragment the table)</div><div dir=3D=
"auto">- rebuild indexes</div><div dir=3D"auto">- connect the DB</div><div =
dir=3D"auto"><br></div><div dir=3D"auto">The better solution would be parti=
tioning:</div><div dir=3D"auto">- choose a metrics (for instance a timestam=
p)</div><div dir=3D"auto">- create partition tables for the period you want=
 to keep</div><div dir=3D"auto">- copy the relevant data to the partitions =
and create partial indexes</div><div dir=3D"auto">- take the DB off line</d=
iv><div dir=3D"auto">- update the last partition with the latest data (shou=
ld be a fast update)</div><div dir=3D"auto">- truncate the original table</=
div><div dir=3D"auto">- connect partitions</div><div dir=3D"auto">- connect=
 the DB</div><div dir=3D"auto"><br></div><div dir=3D"auto">In the future, d=
eleting historic data will be a simple DROP TABLE.</div><div dir=3D"auto"><=
br></div><div dir=3D"auto">Hope it helps</div><div data-smartmail=3D"gmail_=
signature"><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr">--<=
/div><div dir=3D"ltr">Olivier Gautherot</div><div dir=3D"ltr">Tel:=C2=A0+33=
 6 02 71 92 23=C2=A0<br><div><br></div></div></div></div></div></div></div>=
<br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">El mi=
=C3=A9, 28 de ene de 2026, 5:06=E2=80=AFa.m., Tom Lane &lt;<a href=3D"mailt=
o:tgl@sss.pgh.pa.us" target=3D"_blank" rel=3D"noreferrer">tgl@sss.pgh.pa.us=
</a>&gt; escribi=C3=B3:<br></div><blockquote class=3D"gmail_quote" style=3D=
"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Ron Johnson=
 &lt;<a href=3D"mailto:ronljohnsonjr@gmail.com" rel=3D"noreferrer noreferre=
r" target=3D"_blank">ronljohnsonjr@gmail.com</a>&gt; writes:<br>
&gt; Hmm.=C2=A0 Must have been START TRANSACTION which I remember causing i=
ssues in DO<br>
&gt;=C2=A0 blocks.<br>
<br>
Too lazy to test, but I think we might reject that.=C2=A0 The normal rule<b=
r>
in a procedure is that the next command after a COMMIT automatically<br>
starts a new transaction, so you don&#39;t need an explicit START.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
<br>
<br>
</blockquote></div></div></div>

--000000000000333cec06496dbd27--