At 04:06 PM 1/20/2011, Tom Lane wrote:
>peter@vfemail.net writes:
>> I posted a message about PostgreSQL vacuuming to this list in Feb. 2010, received some responses, but probably
didn'tunderstand 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
eachweek. 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
>
>--
>Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-novice
Confession time. I'm not running PostgreSQL 8.anything. I'm still running PostgreSQL version 7.4.3 on a Dell
PowerEdgeSC400 using FreeBSD 4.10. Do all of the concepts you relate still apply? I think I did a REINDEX operating a
coupleof months ago. Will a:L
psql -d database_name -c "reindex"
command do the trick to reindex everything?
Thank you for your patience.
-------------------------------------------------
This message sent via VFEmail.net
http://www.vfemail.net
$14.95 Lifetime accounts! 15GB disk! No bandwidth quotas!