MIME-Version: 1.0
References: 
 <CA+M9Y+DfTmOqqeua1U+7jWPiovpHjmG7AZ-UfcacDueiRo2Vyg@mail.gmail.com>
 <CAKFm-3Coe2SxvdnvO=Gd2yTUWd9svrtcMc5ij-VHCP1AOA+mPQ@mail.gmail.com>
 <69C14B4B-0DED-442A-BD74-3CABA4DD64BD@icloud.com>
 <CANzqJaDnbxuMCV1vF+GfuA=bFWuMo+45cnQK1H4AW3YBqi23eg@mail.gmail.com>
 <d6e421ef-5490-4e14-a278-aa6fe462377c@gmx.net>
In-Reply-To: <d6e421ef-5490-4e14-a278-aa6fe462377c@gmx.net>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 8 Aug 2024 06:26:22 -0400
Message-ID: 
 <CANzqJaD-v-ipsWcHY8HV51aWci3SomCf5gN0X2D1nwY3yqL-tg@mail.gmail.com>
Subject: Re: Schedule pg_repack job with pg_cron
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000082a37061f297602"
Archived-At: 
 <https://www.postgresql.org/message-id/CANzqJaD-v-ipsWcHY8HV51aWci3SomCf5gN0X2D1nwY3yqL-tg%40mail.gmail.com>
Precedence: bulk

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

On Thu, Aug 8, 2024 at 6:17=E2=80=AFAM <shammat@gmx.net> wrote:

>
> Ron Johnson schrieb am 07.08.2024 um 21:39:
> > Part of a properly-maintained system is *regularly* archive/purging
> > (whether that be dropping date-based partitions, or deleting old
> > data from unpartitioned tables or tables partitioned by something
> > other than a date).
> >
> > For example, I gave a list of tables (all intertwined via FK
> > constraints) to the application support people, and they returned
> > the list stating how many weeks or months of data to retain in each
> > table.  Every Saturday night a cron job goes through and deletes the
> > old data from, and then "manually" vacuum-analyzes them.
>
>
> If the application will then insert new data after the cleanup, Postgres
> will re-use the free space that the delete "created". So depending
> on the speed of inserts, you might not really gain that much.
>

Eh?  The whole point of VACUUM is to ensure that the existing allocated
disk space is available for new records?

Or did you think that I do a VACUUM FULL on those tables?  (No; I
definitely don't do that, though I *occasionally* CLUSTER *some* of the
tables to make range queries more efficient.)

--=20
Death to America, and butter sauce.
Iraq lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Aug 8, 2024 at 6:17=E2=80=AFAM &l=
t;<a href=3D"mailto:shammat@gmx.net">shammat@gmx.net</a>&gt; wrote:<br></di=
v><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1=
ex"><br>
Ron Johnson schrieb am 07.08.2024 um 21:39:<br>
&gt; Part of a properly-maintained system is *regularly* archive/purging<br=
>
&gt; (whether that be dropping date-based partitions, or deleting old<br>
&gt; data from unpartitioned tables or tables partitioned by something<br>
&gt; other than a date).<br>
&gt;<br>
&gt; For example, I gave a list of tables (all intertwined via FK<br>
&gt; constraints) to the application support people, and they returned<br>
&gt; the list stating how many weeks or months of data to retain in each<br=
>
&gt; table.=C2=A0 Every Saturday night a cron job goes through and deletes =
the<br>
&gt; old data from, and then &quot;manually&quot; vacuum-analyzes them.<br>
<br>
<br>
If the application will then insert new data after the cleanup, Postgres<br=
>
will re-use the free space that the delete &quot;created&quot;. So dependin=
g<br>
on the speed of inserts, you might not really gain that much.<br>
</blockquote></div><br clear=3D"all"><div>Eh?=C2=A0 The whole point of VACU=
UM is to ensure that the existing allocated disk space is available for new=
 records?</div><div><br></div><div>Or did you think that I do a VACUUM FULL=
 on those tables?=C2=A0 (No; I definitely don&#39;t do that, though I <i>oc=
casionally</i>=C2=A0CLUSTER <i>some</i>=C2=A0of the tables to make range qu=
eries more efficient.)</div><div><br></div><span class=3D"gmail_signature_p=
refix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D=
"ltr">Death to America, and butter sauce.<div>Iraq lobster!</div></div></di=
v></div>

--000000000000082a37061f297602--