Re: Index rebuilding strategy

On Wed, Sep 24, 2025 at 4:51 PM Alban Hertroys <haramrae(at)gmail(dot)com> wrote:

>
> > On 24 Sep 2025, at 22:42, Siraj G <tosiraj(dot)g(at)gmail(dot)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 differing
> collation implementations, known flaky hardware or driver issues, checksum
> 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 = 0.015
autovacuum_vacuum_scale_factor = 0.015
autovacuum_vacuum_insert_scale_factor = 0.015
autovacuum_vacuum_insert_threshold = 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.)

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