Re: Call closed: Almost happy ending (from "Data files became huge with no apparent reason" thread)

En Wed, 4 Sep 2002 11:55:06 +1000
Martijn van Oosterhout <kleptog(at)svana(dot)org> escribió:

> On Tue, Sep 03, 2002 at 05:40:25PM +0200, Dario Fumagalli wrote:
> > As I stated in an earlier post, i vacuumed was about 2 - 3 weeks earlier
> > (my vacation duration).
>
> Seriously, setup a vacuum to run daily. The time wasted by scanning overly
> large tables and using inexact statistics far exceeds a once-per-day delay
> caused by a vacuum.

I say run a light VACUUM (standard VACUUM on 7.2) several times a day;
it doesn't lock tables so you don't have any downtime. If you reach
steady state (i.e. tables do not grow), you don't even need VACUUM FULL,
but it's probably sane to do it every so often.

Remember that you can run ANALYZE separate from VACUUM. It's important
to ANALYZE if the statistics change on your tables, so the optimizer can
choose good plans.

> I actually had a thought last night. REINDEX recreates the index, probably
> with *all* rows in the table, whether or not they are still active. Thus, to
> get maximum effect from REINDEX, you need to VACUUM first.

No. The reindexing is an exclusive operation -- that is, no other
transaction can be using the index at the same time. For that reason,
there's only one version of each tuple that is valid, and only that
version is indexed.

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"El destino baraja y nosotros jugamos" (A. Schopenhauer)