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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
