Re: When to vacuum a table?

Hi,

From: http://www.postgresql.org/docs/7.4/interactive/sql-vacuum.html

"VACUUM reclaims storage occupied by deleted tuples. In normal
PostgreSQLoperation, tuples that are deleted or obsoleted by an update
are not
physically removed from their table; they remain present until a VACUUM is
done. Therefore it's necessary to do VACUUM periodically, especially on
frequently-updated tables."

"The "vacuum analyze" form additionally collects statistics on the
disbursion of columns in the database, which the optimizer uses when it
calculates just how to execute queries. The availability of this data can
make a tremendous difference in the execution speed of queries. This command
can also be run from cron, but it probably makes more sense to run this
command as part of your nightly backup procedure - if "vacuum" is going to
screw up the database, you'd prefer it to happen immediately after (not
before!) you've made a backup! The "vacuum" command is very reliable, but
conservatism is the key to good system management. So, if you're using the
export procedure described above, you don't need to do this extra step".

All its tables constantly manipulated (INSERT, UPDATE, DELETE) they need a
VACUUM, therefore the necessity to execute at least one time to the day
normally of dawn if its database will be very great .

[],s

Marcelo Costa
Secretaria Executiva de Educação do Pará
Amazonia - Pará - Brazil

2006/11/26, Joost Kraaijeveld <J(dot)Kraaijeveld(at)askesis(dot)nl>:
>
> Hi,
>
> Are there guidelines (or any empirical data) available how to determine
> how often a table should be vacuumed for optimum performance or is this
> an experience / trial-and-error thing?
>
> TIA
>
> --
> Groeten,
>
> Joost Kraaijeveld
> Askesis B.V.
> Molukkenstraat 14
> 6524NB Nijmegen
> tel: 024-3888063 / 06-51855277
> fax: 024-3608416
> web: www.askesis.nl
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Marcelo Costa