"Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
> "John Lister" <john.lister-ps@kickstone.com> wrote:
>> I'm using 8.3.8
>
> That's recent. :-)
Thanks for the reply, wasn't sure if 8.4 had fixed anything :)
> If you have index bloat you either have some
> process has held open a database transaction for a very long time
> while the table underwent updates or deletes, or your vacuum policy
> is not aggressive enough.
>
>> my indexes seem to grow disproportionately to the size of the
>> tables, but I haven't studied it in detail yet - I was trying to
>> increase performance on a number of tables that seem to be
>> extremely bloated for some reason.
>
> Well, if they're already extremely bloated, you may need to use
> CLUSTER or some other technique to recover; but it is important to
> understand how you got into that state so you can avoid doing it
> again.
>
> By the way, how are you measuring bloat, and how extreme is it?
at the extreme case one table was 30Gb with 25Gb of indexes and after
forcing a full vacuum and reindex dropped to around 7gb each.
the stats claimed that autovacuum had run fairly recently (in fact it was
trying to run as I glomped it) and I can't see any long standing
transactions, but the db
had been up for over a year so it is possible some hung around longer than
they should.
As you say, I suspect I may have had the autovacuum settings too low so as
to avoid loading the db too much (it seems to be a delicate balance between
having autovacuum run and slowing down normal use) and have upped them a
little. but wanted to make sure that (auto)vacuum was doing what I thought
before getting more aggressive with them
Thanks
John
