Re: Large DB, several tuning questions: Index sizes, VACUUM, REINDEX, Autovacuum

Craig Ringer wrote:
>> ### My Issue No. 1: Index Size
>> Is such disk usage for indexes expected? What can I do to optimize? I
>> could not run yet a VACUUM on result_orig, as I hit into max_fsm_pages
>> limit
>
> You'll like 8.4 then, as you no longer have to play with max_fsm_pages.
> The fact that you're hitting max_fsm_pages suggests that you are
> probably going to be encountering table bloat.
> Of course, to get to 8.4 you're going to have to go through a dump and
> reload of doom...
Yeah, increasing max_fsm_pages and seeing what VACUUM VERBOSE tells you
afterwards is job #1, as all of the information you're getting now is
useless if VACUUM is stalled out on a giant task. It should be possible
to migrate from 8.3 to 8.4 using pg_migrator rather than doing a dump
and reload. I would recommend considering that as soon as
possible--your options are either to learn a lot about better VACUUM
practice and being diligent to make sure you never exceed it in the
future, or to switch to 8.4 and it will take care of itself.

You also need to be careful not to let the system run completely out of
disk space before doing something about this, because CLUSTER (the only
useful way to clean up after a VACUUM mistake of the magnitude you're
facing now) requires making a second copy of the live data in the table
as its method to clean things up. That option goes away once you're
really low on disk space, and if you get backed into that corner by that
you'll really be stuck.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com