From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Bill Moran <wmoran(at)collaborativefusion(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Index bloat of 4x |
Date: | 2007-01-17 15:42:51 |
Message-ID: | 20070117154251.GG26080@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Bill Moran wrote:
>
> We just did a bunch of maintenance on one of our production databases that
> involved a lot of alter tables and moving records about and the like.
>
> Afterwards, I did a vacuum full and analyze to get the database back on
> track -- autovac maintains it under normal operations.
>
> Today I decided to run reindex during a slow period, and was shocked to
> find the database size drop from 165M to 30M. Keep in mind that the
> 165M is after vacuum full. So, apparently, there was 135M of index bloat?
> That seems a little excessive to me, especially when the docs claim that
> reindexing is usually not necessary.
It's been said that vacuum full does not fix index bloat -- in fact,
it's a problem it worsens. However, I very much doubt that it would be
this serious. I guess the question is, how large was the index *before*
all the alter tables?
I'd expect that it was the ALTER TABLEs that caused this much index
growth, which VACUUM FULL was subsequently unable to fix.
I don't expect you kept a log of index sizes throughout the operation
however :-(
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
From | Date | Subject | |
---|---|---|---|
Next Message | David Goodenough | 2007-01-17 16:04:10 | SELECT INTO TEMPORARY problem |
Previous Message | Jeremy Haile | 2007-01-17 15:18:38 | Diagnosing deadlock / connection hang |