Re: Vacuuming -- again

peter(at)vfemail(dot)net writes:
> I posted a message about PostgreSQL vacuuming to this list in Feb. 2010, received some responses, but probably didn't understand what I was being told at that time.

> There's a script running on my server hosting a PostgreSQL database that does some type of vacuuming routine once each week. Specifically, the script executes this command:

> psql -d database_name -c "vacuum full verbose"

> and e-mails the verbose output to me.

FULL vacuum once a week is a pretty terrible maintenance procedure.
It locks your DB and it isn't that effective; in particular, your
indexes are probably enormously bloated by now, which is why things
are getting slower.

What you should be doing is plain (non full) vacuums on a much more
frequent schedule. Or even better, let autovacuum do it for you,
if you're on a recent enough PG version to have a decent autovacuum
built in (8.4 and up do it pretty well).

Right now, you probably need a one-time REINDEX to clean up the mess
from overuse of VACUUM FULL. You'll want to schedule that in whatever
maintenance slot you're using for the current VACUUM FULL run.

There's much more extensive discussion of this stuff in the "routine
maintenance" chapter of the PG manual. See
http://www.postgresql.org/docs/8.4/static/maintenance.html
(adjust link in the obvious spot depending on which PG release
you are running).

regards, tom lane