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!
