From: Rui DeSousa <rui.desousa@icloud.com>
Message-Id: <69C14B4B-0DED-442A-BD74-3CABA4DD64BD@icloud.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_7030AABF-5F10-4F07-8177-63EAD713DA5F"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.8\))
Subject: Re: Schedule pg_repack job with pg_cron
Date: Wed, 7 Aug 2024 15:29:15 -0400
In-Reply-To: 
 <CAKFm-3Coe2SxvdnvO=Gd2yTUWd9svrtcMc5ij-VHCP1AOA+mPQ@mail.gmail.com>
Cc: Sathish Reddy <sathishreddy.postgresql@gmail.com>,
 pgsql-admin@lists.postgresql.org,
 Keith <keith@keithf4.com>,
 keith.fiske@crunchydata.com,
 Laurenz Albe <laurenz.albe@cybertec.at>
To: jacob ndinkwa <jndinkwa@gmail.com>
References: 
 <CA+M9Y+DfTmOqqeua1U+7jWPiovpHjmG7AZ-UfcacDueiRo2Vyg@mail.gmail.com>
 <CAKFm-3Coe2SxvdnvO=Gd2yTUWd9svrtcMc5ij-VHCP1AOA+mPQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/69C14B4B-0DED-442A-BD74-3CABA4DD64BD%40icloud.com>
Precedence: bulk


--Apple-Mail=_7030AABF-5F10-4F07-8177-63EAD713DA5F
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8


> On Aug 7, 2024, at 9:24 AM, jacob ndinkwa <jndinkwa@gmail.com> wrote:
>=20
> To schedule a pg_repack job using pg_cron on an Amazon RDS for =
PostgreSQL instance, you need to follow a few steps. However, it=E2=80=99s=
 important to note that pg_cron is only supported on certain versions of =
Amazon RDS, and pg_repack is also a separate extension that must be =
installed and enabled.
>=20
>=20

Is scheduling pg_repack just a bad idea and just introducing just more =
bloat? Why not just tune auto vacuum?

80/20 rule=E2=80=A6 most schemas are going to have their large/hot =
tables, etc and data has a natural life cycle.  If you have a heathy =
application then bloat is not an issue as free space is used by new =
tuples.  Each database has a data flow to it depending on the maturity =
and nature of the application/database. Exiting tuples make room for new =
tuples, etc.

If your have to vacuum full / pg_repack your tables on a scheduled bases =
then I think there is something very wrong with your application.

Pg_repack will do more harm in the long run.  i.e. the entire time =
pg_repack is running xmin is frozen thus creating more bloat everywhere =
else!=20

Bloat is overrated; especially in a transaction system where all your =
data access patterns should be well defined and not doing full table =
scans. Just focus on identifying bloated indexes periodically and =
rebuilding those.  There should be no need to vacuum full tables under =
normal circumstances.=

--Apple-Mail=_7030AABF-5F10-4F07-8177-63EAD713DA5F
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"word-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;" class=3D""><br =
class=3D""><div><br class=3D""><blockquote type=3D"cite" class=3D""><div =
class=3D"">On Aug 7, 2024, at 9:24 AM, jacob ndinkwa &lt;<a =
href=3D"mailto:jndinkwa@gmail.com" class=3D"">jndinkwa@gmail.com</a>&gt; =
wrote:</div><br class=3D"Apple-interchange-newline"><div class=3D""><meta =
charset=3D"UTF-8" class=3D""><p class=3D"MsoNormal" style=3D"caret-color: =
rgb(0, 0, 0); font-style: normal; font-variant-caps: normal; =
font-weight: 400; letter-spacing: normal; text-align: start; =
text-indent: 0px; text-transform: none; white-space: normal; =
word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: =
none; margin: 0in 0in 8pt; line-height: 18.399999618530273px; font-size: =
12pt; font-family: Aptos, sans-serif;">To schedule a pg_repack job using =
pg_cron on an Amazon RDS for PostgreSQL instance, you need to follow a =
few steps. However, it=E2=80=99s important to note that pg_cron is only =
supported on certain versions of Amazon RDS, and pg_repack is also a =
separate extension that must be installed and enabled.</p><br =
class=3D"Apple-interchange-newline"></div></blockquote></div><br =
class=3D""><div class=3D""><div class=3D"">Is scheduling pg_repack just =
a bad idea and just introducing just more bloat? Why not just tune auto =
vacuum?</div><div class=3D""><br class=3D""></div><div class=3D"">80/20 =
rule=E2=80=A6 most schemas are going to have their large/hot tables, etc =
and data has a natural life cycle. &nbsp;If you have a heathy =
application then bloat is not an issue as free space is used by new =
tuples. &nbsp;Each database has a data flow to it depending on the =
maturity and nature of the application/database. Exiting tuples make =
room for new tuples, etc.</div><div class=3D""><br class=3D""></div><div =
class=3D"">If your have to vacuum full / pg_repack your tables on a =
scheduled bases then I think there is something very wrong with your =
application.</div><div class=3D""><br class=3D""></div><div =
class=3D"">Pg_repack will do more harm in the long run. &nbsp;i.e. the =
entire time pg_repack is running xmin is frozen thus creating more bloat =
everywhere else!&nbsp;</div><div class=3D""><br class=3D""></div><div =
class=3D"">Bloat is overrated; especially in a transaction system where =
all your data access patterns should be well defined and not doing full =
table scans. Just focus on identifying bloated indexes periodically and =
rebuilding those. &nbsp;There should be no need to vacuum full tables =
under normal circumstances.</div></div></body></html>=

--Apple-Mail=_7030AABF-5F10-4F07-8177-63EAD713DA5F--