Re: Database growing. Need autovacuum help.

In response to Henrik <henke(at)mac(dot)se>:
>
> We are running a couple of 8.3.1 servers and the are growing a lot.
>
> I have the standard autovacuum settings from the 8.3.1 installation
> and we are inserting about 2-3 million rows every night and cleaning
> out just as many every day.

Is this a batch job? If so, autovac might not be your best friend
here. There _are_ still some cases where autovac isn't the best
choice. If you're doing a big batch job that deletes or updates a
bunch of rows, you'll probably be better off making a manual vacuum
the last step of that batch job. Remember that you can vacuum
individual tables.

> The database size rose to 80GB but after a dump/restore its only 16GB
> which shows that there where nearly 65GB bloat in the database.

Does it keep growing beyond 80G? While 65G may seem like a lot of bloat,
it may be what your workload needs as working space. I mean, you _are_
talking about shifting around 2-3 million rows/day.

Crank up the logging. I believe the autovac on 8.3 can be configured
to log exactly what tables it operates on ... which should help you
determine if it's not configured aggressively enough.

Some other things to do to improve your situation are to isolate exactly
_what_ is bloating. Use pg_relation_size() to get a list of the sizes
of all DB objects right after a dump/restore and when the database is
bloated and compare to see what's bloating the most. Don't forget to
check indexes as well. If rebuilding a single index nightly will take
care of your bloat, that's not bad. Unusual, but it does happen under
some workloads ... you might be able to adjust the index fill factor
to improve things as well.

If it's just a single table that's bloating, a VACUUM FULL or CLUSTER
of that table alone on a regular schedule might take care of things.
If your data is of a FIFO nature, you could benefit from the old trick
of having two tables and switching between them on a schedule in order
to truncate the one with stale data in it.

Hope some of these ideas help.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023