On Fri, Jul 30, 2010 at 11:18 PM, Gnanakumar <gnanam@zoniac.com> wrote:
> Hi,
>
> What are all the controlling factors/measures that one should consider/take
> into account, when there is an unexpected growth in disk space usage of the
> database?
>
> Some of the factors that comes to my mind immediately are:
> 1) Running autovacuum daemon aggressively
> 2) Setting/tuning appropriate value for the parameters "max_fsm_pages" and
> "max_fsm_relations"
Those are both good steps. Note that they won't reduce bloat, just prevent it.
> Is there any other factors that I need to consider apart from the above?
Look into monitoring your database for bloat etc. Esp in 8.3 and
before where blowing out the free space map is quite easy to do and it
can go unnoticed for some time. A first simple step is to email
yourself the output of vacuum verbose every night to make sure you
haven't outrun fsm. Also, fsm is pretty cheap. Where I work we use
about 2.5M entries but have our fsm set to 10M so we don't have to
worry about blowing it out overnight or anything.
Lastly, make sure your IO subsystem can keep up. If you're on the
hairy edge, then vacuum may never be able to keep up.