public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Index rebuilding strategy
Date: Wed, 24 Sep 2025 19:34:03 -0400
Message-ID: <CANzqJaAB+2JBOzu7s--1tQSSbwa4vwx71iTH8EN96nA+TR8XzQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAC5iy60ZstfyqPK2tmWLD_QdPTd-0FMfZHHKtyZsOSGZDjJRgw@mail.gmail.com>
<[email protected]>
On Wed, Sep 24, 2025 at 4:51 PM Alban Hertroys <[email protected]> wrote:
>
> > On 24 Sep 2025, at 22:42, Siraj G <[email protected]> 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!
view thread (4+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Index rebuilding strategy
In-Reply-To: <CANzqJaAB+2JBOzu7s--1tQSSbwa4vwx71iTH8EN96nA+TR8XzQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox