Re: Bloated indexes from pg_restore? (Was: Index fillfactor changed in pg9?)

--- On Mon, 4/4/11, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > --- On Mon, 4/4/11, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> wrote:
> >>> So it appears now that if I restore the
> database using
> >>> pg_restore, I end up with bloated indexes,
> which are fixed
> >>> with a vacuum full.
> >>>
> >>> The dump is a data only dump with the -Fc
> flag,
>
> >> Data only dump?  Then what is the state of the
> >> database you're restoring it into?
>
> > It's a newly created database from a schema only
> dump.
>
> So the difference is that you have initially-empty indexes
> that are
> filled incrementally, whereas an ordinary dump-and-restore
> would be
> creating fresh indexes.  Incremental filling of a
> btree is usually said
> to result in about 66% fillfactor on average, 50%
> worst-case; whereas by
> default I think we build fresh indexes at 90%
> fillfactor.  You didn't
> say how much "bloat" you were seeing, but if it's less than
> 2X I think
> this is just expected.  Unless the data is pretty
> static, it's useless
> to hope that the fill factor will stay as high as 90%
> anyway.
>

Thanks Tom.

Yeah that's exactly what I'm seeing, my indexes would be about 66% fillfactor.

I realize now, the reason I don't see this on our production machines is because I drop and recreate the indexes & constraints either side of the data restore process. I'd not bothered with any of that for my tests, and assumed the difference was due to some sort of change in 9.0.