Hi,
We just don't seem to be getting much benefit from autovacuum. Running
a manual vacuum seems to still be doing a LOT, which suggests to me
that I should either run a cron job and disable autovacuum, or just
run a cron job on top of autovacuum.
The problem is that if I run the same query (an update query) on the
db it takes 4 - 6 times longer than on a fresh copy (dumped then
restored to a different name on the same machine/postgres). There is
clearly an issue here...
I have been thinking about strategies and am still a bit lost. Our
apps are up 24/7 and we didn't code for the eventuality of having the
db going offline for maintenance... we live and learn!
Would it be wise to, every week or so, dump then restore the db
(closing all our apps and then restarting them)? The dump is only
about 270MB, and restore is about 10mins (quite a few large indexes).
It seems that we have no real need for vacuum full (I am clutching at
straws here...), so in theory I could just vacuum/analyse/reindex and
things would be OK. Will a fresh restore be much more performant than
a fully vacuumed/analysed/reindexed db? Probably? Possibly?
I believe I understand the autovacuum docs but...
Help!
8-]
Cheers
Antoine
--
This is where I should put some witty comment.