Search Postgresql Archives

Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



And the point of the REINDEX at that point (below) is to remove dead tuple keys-tids
and  reorganize those split pages back into physical order without losing the freespace.

> From: Peter Geoghegan <pg@xxxxxxx>
> Sent: July 9, 2019 1:47 PM
> Subject: Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR
>  
> On Tue, Jul 9, 2019 at 10:31 AM John Lumby <johnlumby@xxxxxxxxxxx> wrote:
> > Yes,   I see that.     But surely "making splits occur less often" is a desirable
> > objective in itself, is it not?     And I believe that a parameter to preserve the "steady-state"
> > density in high-traffic indexes would help achieve that goal,   wouldn't you agree?
> 
> Anything that reliably reduces page splits without hurting space
> utilization is well worthwhile. I can't see how what you describe
> could have that effect, though. If you expect the leaf density to be
> the same after a REINDEX, then why bother at all? There is no reason
> to think that that will be more effective than simple vacuuming.
> 
Ah,  I did not explain the idea welll enough.
The scenario (simplified) is this:
Time 0      FILLFACTORs all set to default 90%
            because we do not yet know what the steady-state density
            will turn out to be.
       {   workload runs for a few weeks  }
Time N      gather table and index stats,   discover growth and learn density.
            growth is more than autovacuum could control so
       {   ALTER INDEX ??? SET (fillfactor = AUTO); }
       {   REINDEX,   desiring to preserve current density whatever this is }
       {   workload runs for a few more weeks  }
Time 2*N    gather table and index stats,   discover little or no growth since time N.
            we have achieved steady-state in total number of pages.

Would this not work?

Cheers,   John





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux