Re: VACUUM vs. REINDEX

On Fri, 7 Jul 2006, William Scott Jordan wrote:

> Hi all!
>
> Can anyone explain to me what VACUUM does that REINDEX doesn't? We have a
> frequently updated table on Postgres 7.4 on FC3 with about 35000 rows which
> we VACUUM hourly and VACUUM FULL once per day. It seem like the table still
> slows to a crawl every few weeks. Running a REINDEX by itself or a VACUUM
> FULL by itself doesn't seem to help, but running a REINDEX followed
> immediately by a VACUUM FULL seems to solve the problem.
>
> I'm trying to decide now if we need to include a daily REINDEX along with our
> daily VACUUM FULL, and more importantly I'm just curious to know why we
> should or shouldn't do that.
>
> Any information on this subject would be appreciated.

William,

If you're having to VACUUM FULL that often, then it's likely your FSM settings
are too low. What does the last few lines of VACUUM VERBOSE say? Also, are
you running ANALYZE with the vacuums or just running VACUUM? You still need
to run ANALYZE to update the planner statistics, otherwise things might slowly
grind to a halt. Also, you should probably consider setting up autovacuum and
upgrading to 8.0 or 8.1 for better performance overall.

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954