Thread: Order of Daily VACUUM, CLUSTER, REINDEX

Hey list,

I have a cron script that runs a couple cleanup commands on my
database. It runs three commands:
VACUUM ANALYZE;
CLUSTER;
REINDEX DATABASE "database";

They are run in the above order. I was wondering if there's a better
order to do this in. For example, should the ANALYZE be run _after_
the CLUSTER? Does that affect the query planner? And the same goes for
REINDEX.

I could always split up the VACUUM and ANALYZE into separate commands
as well, if order would have any effect.

I _do_ have autovacuum running. However, I like to run these commands
during a low-traffic time just so autovacuum slows the site down as
least as possible during high-load times.

Thanks!

PostgreSQL 8.4.2 on i386-portbld-freebsd8.0, compiled by GCC cc (GCC)
4.2.1 20070719  [FreeBSD], 32-bit

yes, if you really want to do it - analyze should be running following cluster, as it moves data around.
plus, with 8.4 autovacuum should do the job.

> VACUUM ANALYZE;
> CLUSTER;
> REINDEX DATABASE "database";

ANALYZE has to go after CLUSTER; and CLUSTER already
vacuums the tables (I'm not 100% sure though). CLUSTER also
reindexes the whole table, so there's no need for another REINDEX.

I think the right way of doing it would be:

CLUSTER;
ANALYZE; (no "vacuum analyze", just "analyze").

Beware though that CLUSTER and REINDEX can be very long processes,
and that CLUSTER locks the whole table... they're not tools supposed to
be used that often (since there's usually no need to run them so often).

Why do you think you need to run those commands daily?

Leonardo F escribió:
> > VACUUM ANALYZE;
> > CLUSTER;
> > REINDEX DATABASE "database";
>
> ANALYZE has to go after CLUSTER; and CLUSTER already
> vacuums the tables (I'm not 100% sure though). CLUSTER also
> reindexes the whole table, so there's no need for another REINDEX.
>
> I think the right way of doing it would be:
>
> CLUSTER;
> ANALYZE; (no "vacuum analyze", just "analyze").

Note that cluster only clusters tables that have been previously
clustered.  So you still need to vacuum tables that are not clustered.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.