Thread: Vacuum is needed or not?

Hi,

How do I know vacuum is need or not? I mean, are there any query to know status of statistics, tables, indexes, etc?
(7.4.7)
Our database size bigger than just go and do it. :(

Thanks,
Szabek

On 1/5/06, Szabolcs BALLA <szballa@confinsystems.com> wrote:
> Hi,
>
> How do I know vacuum is need or not? I mean, are there any query to know
> status of statistics, tables, indexes, etc?
> (7.4.7)
> Our database size bigger than just go and do it. :(
>
> Thanks,
> Szabek

VACUUM is needed at least every billion transactions...

execute it in a script periodically... if you are in doubt if you need
VACUUM then you need it urgently, execute it right now!!!

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

On 1/5/06, Szabolcs BALLA <szballa@confinsystems.com> wrote:
>
>
VACUUM is needed at least every billion transactions...

execute it in a
> script periodically... if you are in doubt if you need
VACUUM then you need
> it urgently, execute it right now!!!

>
> yes, and i running every weekend a full analyze, but actually i think it's
> need again, but just for feel i couldn't stop the server.
> I need evidence. (oldspice rulez ;))
>
> Szabek

a plain VACUUM (without full) is enough in most cases... and you don't
need to stop the server for a plain VACUUM...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Since periodic VACUUM runs are needed anyway for the reasons described earlier, it's unlikely that any table would not be vacuumed for as long as a billion transactions. But to help administrators ensure this constraint is met, VACUUM stores transaction ID statistics in the system table pg_database. In particular, the datfrozenxid column of a database's pg_database row is updated at the completion of any database-wide VACUUM operation (i.e., VACUUM that does not name a specific table). The value stored in this field is the freeze cutoff XID that was used by that VACUUM command. All normal XIDs older than this cutoff XID are guaranteed to have been replaced by FrozenXID within that database. A convenient way to examine this information is to execute the query

SELECT datname, age(datfrozenxid) FROM pg_database;

--
"Adopting the position that you are smarter than an automaticoptimization algorithm is generally a good way to achieve lessperformance, not more" - Tom Lane.