pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Bloated indexes from pg_restore? (Was: Index fillfactor changed in pg9?)
5+ messages / 2 participants
[nested] [flat]
* Re: Bloated indexes from pg_restore? (Was: Index fillfactor changed in pg9?)
@ 2011-04-04 15:28 Glyn Astill <glynastill@yahoo.co.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Glyn Astill @ 2011-04-04 15:28 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: pgsql-admin
--- On Fri, 1/4/11, Glyn Astill <glynastill@yahoo.co.uk> wrote:
> --- On Fri, 1/4/11, Kenneth Marshall wrote:
> What
> > happens
> > if you run a REINDEX on both DB's to the index sizes?
> >
> > Regards,
> > Ken
> >
>
> Sorry for the lack of info there. Both are 64 bit, both
> have ext3 filesystems set up the same, the 8.4 machine is on
> kernel 2.6.26 whereas the 9.0 machine is on 2.6.32.
>
> REINDEX does indeed decreace the size. I guess the
> question is why does pg_restore create them bloated? Could
> it be the parrallel (-j) option?
>
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, taken with pg_dump as follows
pg_dump -Fc mydatabase -U postgres -h localhost -a --disable-triggers -f data-dump.gz
That appears to restore with COPY, using the following
pg_restore -U postgres --disable-triggers -c -d mydatabase data-dump.gz
I'm a bit perplexed by this
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Bloated indexes from pg_restore? (Was: Index fillfactor changed in pg9?)
@ 2011-04-04 16:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: Glyn Astill <glynastill@yahoo.co.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2011-04-04 16:02 UTC (permalink / raw)
To: Glyn Astill <glynastill@yahoo.co.uk>; +Cc: Kenneth Marshall <ktm@rice.edu>; pgsql-admin
Glyn Astill <glynastill@yahoo.co.uk> writes:
> 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?
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Bloated indexes from pg_restore? (Was: Index fillfactor changed in pg9?)
@ 2011-04-04 16:23 Glyn Astill <glynastill@yahoo.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Glyn Astill @ 2011-04-04 16:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kenneth Marshall <ktm@rice.edu>; pgsql-admin
--- On Mon, 4/4/11, Tom Lane <tgl@sss.pgh.pa.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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Bloated indexes from pg_restore? (Was: Index fillfactor changed in pg9?)
@ 2011-04-04 16:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Glyn Astill <glynastill@yahoo.co.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2011-04-04 16:32 UTC (permalink / raw)
To: Glyn Astill <glynastill@yahoo.co.uk>; +Cc: Kenneth Marshall <ktm@rice.edu>; pgsql-admin
Glyn Astill <glynastill@yahoo.co.uk> writes:
> --- On Mon, 4/4/11, Tom Lane <tgl@sss.pgh.pa.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.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Bloated indexes from pg_restore? (Was: Index fillfactor changed in pg9?)
@ 2011-04-04 19:35 Glyn Astill <glynastill@yahoo.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Glyn Astill @ 2011-04-04 19:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kenneth Marshall <ktm@rice.edu>; pgsql-admin
--- On Mon, 4/4/11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > --- On Mon, 4/4/11, Tom Lane <tgl@sss.pgh.pa.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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-04-04 19:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-04 15:28 Re: Bloated indexes from pg_restore? (Was: Index fillfactor changed in pg9?) Glyn Astill <glynastill@yahoo.co.uk>
2011-04-04 16:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-04 16:23 ` Glyn Astill <glynastill@yahoo.co.uk>
2011-04-04 16:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-04 19:35 ` Glyn Astill <glynastill@yahoo.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox