find out if autovacuum is running for your Postgres database
Monitoring Postgres autovacuum
Postgres does all updates on a database Copy-on-Write. That means, on every update on a row, additional space is needed on the filesystem. If noone is using an old entry anymore, the space allocated by these data is not freed automatically, nor will it be available for postgres to store new data. To get the space back, you have to vacuum, luckily there is an autovacuum in postgres. Autovacuum is an extra deamon that will run vacuum from time to time. It will not run a vacuum full, thus it will never free disk space, but postgres is able to reuse the already allocated space and thus the datafiles will not grow infinitely.
The cumbersome part is to check if autovacuum is working, since there is no way to ensure that it will do. First you can check, if autovacuum is configured to run. You might think setting "autovacuum = on" would be enough, it's not, since it will only work with some version depending statistics enabled.
Potstgres >=8.2
SHOW autovacuum; SHOW track_counts;
Prior versions:
SHOW autovacuum; SHOW stats_start_collector; SHOW stats_row_level;
Next is to check, if the autovacuum daemon is running. There is one postgres process that will be named "postgres: autovacuum launcher process". If you are running solaris, ps will not show you that name, instead it will show the original process cmdline, since it is ignoring updates on argv[0] made by processes at runtime. You can help your self with a little loop.
for PGPR in `/usr/bin/pgrep postgres`; do pargs -a $PGPR; done | grep 'argv\[0' | sed 's/ *$//'
An more easy and generic way is to look into the logfile, if you've not already deleted the log containing the startup messages, you'll find the following line there:
LOG: autovacuum launcher started
You can also monitor the activity of autovacuum on the database. However, it will only show you activities if autovacuum had done something, thus on an fresh installed database it is very unlikely to see any activity.
postgres=# SELECT relname,last_autovacuum from pg_stat_user_tables;
relname | last_autovacuum
-------------------------+-----------------
my_table | 2011-05-02 05:05:11.78106+02
my_other_table | 2011-05-03 09:21:18.850887+02
unlikely_for_update |
If you select pg_stat_all_tables instead of pg_stat_user_tables you will also see vacuums for internal tables.