From: | John Lumby <johnlumby(at)hotmail(dot)com> |
---|---|
To: | Peter Geoghegan <pg(at)bowt(dot)ie> |
Cc: | pgsql general <pgsql-general(at)postgresql(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR |
Date: | 2019-07-09 18:27:47 |
Message-ID: | BYAPR06MB55596AEDE781F2EC8F7F2137A3F10@BYAPR06MB5559.namprd06.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)bowt(dot)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(at)hotmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Naresh g | 2019-07-09 18:30:32 | Advice on Best BCP Plan |
Previous Message | John Lumby | 2019-07-09 18:22:41 | Re: REINDEX : new parameter to preserve current average leaf density as new implicit FILLFACTOR |