PostgreSQL 8.1 is pretty old, it reached its EOL time (vide PostgreSQL Release Support Policy). I think that newer versions (e.g. 9.0) have better performance (especially better vacuuming) and in my opinion it is first step (of course postgresql.conf and probably kernel/ulimit settings are important too).
In PostgreSQL documentation there is partitioning method described for such big (and constatly growing) tables. It might be useful solution.
from http://www.day32.com/MySQL/Meetup/Presentations/postgresql_partitioning_short.pdf
Partitioning a table is normally only
worthwhile when the size of the table
exceeds physical memory.
I have hit this problem before. Below are my notes from how I fixed it
You need to fix the problem by running a vacuum, as the error conveniently hints to. In order to do this, first, shutdown postgres
sudo /sbin/service postgresql stop
Now, you'll need to go through each database and perform a vaccum with the stand-alone back-end. As the postgres user Run:
postgres -D /opt/pgsql/data/ postgres
Where /opt/pgsql/data is the path to postgres's data directory, and postgres is the name of the database you want to fix.
You will get a prompt like:
PostgreSQL stand-alone backend 8.1.18
backend>
Simply enter VACUUM at the backend> prompt.
You will now most likely get something like
WARNING: database "template1" must be vacuumed within 999407 transactions
HINT: To avoid a database shutdown, execute a full-database VACUUM in "template1".
Just keep repeating the steps, changing the DBNAME each time until you don't get the warnings anymore. When that happens, you can restart postgres and all will be right again.
Edit:
I should also mention, that after we did this process a few times; we decided to implement the autovacuum process: http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUM
Best Answer
You should never run VACUUM FULL, if that's what you are talking about.
You should run VACUUM and ANALYZE regularly, on all databases. The easiest thing to do here is to just run VACUUM ANALYZE as one command, which will do both of them - that will also be faster.
Unless, that is, you can use autovacuum. If you're on 8.3 or newer, that would be the recommendation in most cases. Then you don't end up running it unnecessarily.
You shouldn't run CLUSTER on everything in the database, just because you can. If you have specific tables, run it there. But most workloads don't need a regular CLUSTER at all - and certainly not on all tables.
The tables that you do run CLUSTER on don't need VACUUM.