public inbox for [email protected]
help / color / mirror / Atom feedIndex rebuilding strategy
4+ messages / 4 participants
[nested] [flat]
* Index rebuilding strategy
@ 2025-09-24 20:42 Siraj G <[email protected]>
2025-09-24 20:50 ` Re: Index rebuilding strategy Alban Hertroys <[email protected]>
2025-09-24 21:52 ` Re: Index rebuilding strategy Laurenz Albe <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: Siraj G @ 2025-09-24 20:42 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
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.
Thanks
Siraj
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Index rebuilding strategy
2025-09-24 20:42 Index rebuilding strategy Siraj G <[email protected]>
@ 2025-09-24 20:50 ` Alban Hertroys <[email protected]>
2025-09-24 23:34 ` Re: Index rebuilding strategy Ron Johnson <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: Alban Hertroys @ 2025-09-24 20:50 UTC (permalink / raw)
To: Siraj G <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
> 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).
Regards,
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Index rebuilding strategy
2025-09-24 20:42 Index rebuilding strategy Siraj G <[email protected]>
2025-09-24 20:50 ` Re: Index rebuilding strategy Alban Hertroys <[email protected]>
@ 2025-09-24 23:34 ` Ron Johnson <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Ron Johnson @ 2025-09-24 23:34 UTC (permalink / raw)
To: pgsql-general
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!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Index rebuilding strategy
2025-09-24 20:42 Index rebuilding strategy Siraj G <[email protected]>
@ 2025-09-24 21:52 ` Laurenz Albe <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2025-09-24 21:52 UTC (permalink / raw)
To: Siraj G <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>
On Thu, 2025-09-25 at 02:12 +0530, Siraj G wrote:
> What are the top pointers we should consider for index rebuild? Check its size, bloat estimate, heavy Updates/Deletes?
https://www.cybertec-postgresql.com/en/should-i-rebuild-my-postgresql-index/
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-09-24 23:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-24 20:42 Index rebuilding strategy Siraj G <[email protected]>
2025-09-24 20:50 ` Alban Hertroys <[email protected]>
2025-09-24 23:34 ` Ron Johnson <[email protected]>
2025-09-24 21:52 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox