When a row is updated it is really inserted (and old copy deleted). Also,
you app may sometimes delete rows. After a while there will be much
unusable space because of all the deletes. Vacuum recovers disk space so
it can be reused. If you run 'vacuum analyse', you run the basic vacuum as
just described, but also you gather statistics that the optimiser uses when
evaluating your SQL statements. Decisisons about whether to use seq-scan
or and index are based on these statistics. Unless something changed in
recent versions, you can only run analyse as part of vacuum. Leave
postmaster running.
How often? Depends on your system (don't you hate this type of answer?).
My system is doing almost nothing when it's dark out, so running every
night is not a problem. Some databases on my system see so little
avtivity, I only vacuum weekly. I've of systems that run it several times
per day.
Frank
At 05:12 PM 2/27/02 +0200, Peter Peltonen wrote:
>Questions regarding PostgreSQL 7.1.3 (if things are different with 7.2 I'd
>like to know that too :)
>
>How often one should vacuum his db?
>
>Is vacuum --analyze for just providing information or does it somehow
>store it's info for postgresql's use (in other words: should I run it too,
>and if so, should I do it before or after the real vacuum?).
>
>Should postmaster be running or not when vacuuming?
>
>Regards,
>Peter