MIME-Version: 1.0
References: <CAC5iy60ZstfyqPK2tmWLD_QdPTd-0FMfZHHKtyZsOSGZDjJRgw@mail.gmail.com>
 <8E8DB597-41BA-4E6F-A792-13B8AA9238D2@gmail.com>
In-Reply-To: <8E8DB597-41BA-4E6F-A792-13B8AA9238D2@gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 24 Sep 2025 19:34:03 -0400
Message-ID: <CANzqJaAB+2JBOzu7s--1tQSSbwa4vwx71iTH8EN96nA+TR8XzQ@mail.gmail.com>
Subject: Re: Index rebuilding strategy
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000089cc69063f947dbf"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAB%2B2JBOzu7s--1tQSSbwa4vwx71iTH8EN96nA%2BTR8XzQ%40mail.gmail.com>
Precedence: bulk

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

On Wed, Sep 24, 2025 at 4:51=E2=80=AFPM Alban Hertroys <haramrae@gmail.com>=
 wrote:

>
> > On 24 Sep 2025, at 22:42, Siraj G <tosiraj.g@gmail.com> wrote:
> >
> > Hello Experts!
> >
> > What are the top pointers we should consider for index rebuild? Check
> its size,  bloat estimate, heavy Updates/Deletes?
> >
> > Please highlight the best practices.
>
> I think just any pointers of corruption, really. OS updates with differin=
g
> collation implementations, known flaky hardware or driver issues, checksu=
m
> discrepancies if you have those turned on and get any of those.
>
> The need to rebuild indices should be quite rare. Regular vacuuming and
> analysing should take care of most of the need, with much of those
> happening automatically anyway (could need some tuning though).
>

I drop the scale factors down to 1.5% and the insert threshold to 500.  The
application we run seems to like that.  YMMV, of course.

autovacuum_analyze_scale_factor =3D 0.015
autovacuum_vacuum_scale_factor =3D 0.015
autovacuum_vacuum_insert_scale_factor =3D 0.015
autovacuum_vacuum_insert_threshold =3D 500

Also, I repack tables when abs(correlation) gets below 60% (which
eventually happens on tables where the oldest records are regularly
deleted).  That rebuilds the indices for you.

(Why don't I partition those tables?  1. It's a 3rd party application;
thus, they control the schema. 2. Partitioning by date means adding a date
field to the PK, which means the PK really isn't a PK anymore.)

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Sep 24, 2025 at 4:51=E2=80=AFPM A=
lban Hertroys &lt;<a href=3D"mailto:haramrae@gmail.com">haramrae@gmail.com<=
/a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><block=
quote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1=
px solid rgb(204,204,204);padding-left:1ex"><br>
&gt; On 24 Sep 2025, at 22:42, Siraj G &lt;<a href=3D"mailto:tosiraj.g@gmai=
l.com" target=3D"_blank">tosiraj.g@gmail.com</a>&gt; wrote:<br>
&gt; <br>
&gt; Hello Experts!<br>
&gt; <br>
&gt; What are the top pointers we should consider for index rebuild? Check =
its size,=C2=A0 bloat estimate, heavy Updates/Deletes?<br>
&gt; <br>
&gt; Please highlight the best practices.<br>
<br>
I think just any pointers of corruption, really. OS updates with differing =
collation implementations, known flaky hardware or driver issues, checksum =
discrepancies if you have those turned on and get any of those.<br>
<br>
The need to rebuild indices should be quite rare. Regular vacuuming and ana=
lysing should take care of most of the need, with much of those happening a=
utomatically anyway (could need some tuning though).<br></blockquote><div><=
br></div><div></div></div><div>I drop the scale factors down to 1.5% and th=
e insert threshold to=C2=A0500.=C2=A0 The application we run seems to like =
that.=C2=A0 YMMV, of course.</div><div><br></div><div><font face=3D"monospa=
ce">autovacuum_analyze_scale_factor =3D 0.015<br>autovacuum_vacuum_scale_fa=
ctor =3D 0.015<br>autovacuum_vacuum_insert_scale_factor =3D 0.015</font></d=
iv><div><font face=3D"monospace">autovacuum_vacuum_insert_threshold =3D 500=
</font></div><div><br></div><div>Also, I repack tables when abs(correlation=
) gets below 60% (which eventually happens on tables where the oldest recor=
ds=C2=A0are regularly deleted).=C2=A0 That rebuilds the indices for you.</d=
iv><div><br></div><div>(Why don&#39;t I partition those tables?=C2=A0 1. It=
&#39;s a 3rd party application; thus, they control the schema. 2. Partition=
ing by date means adding a date field to the PK, which means the PK really =
isn&#39;t a PK anymore.)</div><div><br></div><span class=3D"gmail_signature=
_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=
=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me=
, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></=
div></div></div></div>

--00000000000089cc69063f947dbf--