Fwd: Indexes and Tables: Growth and Treatment

Matthew,

Here's some more feedback on our use of pg_autovaccum. It's clear that
it's working and that it's helping, but even after increasing our
max_fsm_pages substantially (to in excess of what vacuum verbose
suggests is needed), we're still seeing pretty a rapid increase in disk
usage.

It used to be that nightly reindexing helped substantially, but am I
wrong in thinking that the frequency of dynamic analysis is helping
keep index size down?

Anyway, the bottom line is that it still seems like a vacuum full on a
periodic basis is necessary to keep growth rates in check. We track
both table and index size, and our max table grows pretty rapidly (over
the course of a few days) from about 4 MB to about 11 MB with
pg_autovacuum running. After a vacuum full, it returns to about 4 MB.

Are there any other postgresql.conf parameters that I should be looking
at as far as allowing pg_autovacuum to work more effective in terms of
disk reclamation? Is it possible that my max_fsm_pages is still too
low?

Thanks!

-tfo

Begin forwarded message:

> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Date: July 13, 2004 6:58:24 PM CDT
> To: "Thomas F.O'Connell" <tfo(at)sitening(dot)com>
> Cc: Scott Holdren <scott(at)holdren(dot)com>, pgsql-general(at)postgreSQL(dot)org
> Subject: Re: [GENERAL] Indexes and Tables: Growth and Treatment
> (Modified by Thomas F. O'Connell)
>
> "Thomas F.O'Connell" <tfo(at)sitening(dot)com> writes:
>> It's a high turnover database, in that the applications that use it
>> perform thousands of inserts, updates, and deletes on a daily basis.
>
>> We're seeing about 5-10 GB of increased disk space used on a daily
>> basis if a vacuum (full) or reindexdb is not performed. We were doing
>> one vacuum analyze full a week with nightly vacuum analyzes.
>
> Try hourly vacuums. If that doesn't stem the tide, make it more often
> (or 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.
>
> With sufficiently frequent plain vacuums you really shouldn't need
> vacuum full at all.
>
> I can't recommend an analyze frequency on what you've told us.
>
> regards, tom lane