Re: Indexes and Tables: Growth and Treatment

On Jul 13, 2004, at 6:58 PM, Tom Lane wrote:

> Try hourly vacuums. If that doesn't stem the tide, make it more often
> (or try autovacuum).

I will try autovacuum.

> Also make sure that your FSM settings are large
> enough; if they're not then no amount of plain vacuuming will keep you
> out of trouble.

I was just reading up on FSM settings today. In fact, here's the output
of a recent VACUUM VERBOSE:

INFO: free space map: 1000 relations, 11599 pages stored; 100064 total
pages needed
DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 178 kB
shared memory.

So clearly we need to increase max_fsm_pages. How is this related to
vacuuming? And is it related at all to index growth?

> With sufficiently frequent plain vacuums you really shouldn't need
> vacuum full at all.

So is the only benefit to that the extreme optimizations of disk space
it undertakes? Is there any point at which the extra compacting
actually results in a performance enhancement?

> I can't recommend an analyze frequency on what you've told us.

What more information would you need to make a recommendation?

Thanks for all the tips!

-tfo