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@bowt.ie>
> 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@hotmail.com> 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