Re: Database Optimization and Peformance

joe(dot)mcclintock(at)antics(dot)com (Joe McClintock) writes:
> My concern is this, doing a backup and restore does not seem an
> appropriate way manage database fragmentation and performance. The
> documentation I have read indicates that vacuum, analyze reindex are
> the tools to use to de-fragment and optimize the database. In my
> case they did not work and reindexing made query performance
> slightly worse. Am I missing something? As the database grows, will
> I need to essentially rebuild the db on a regular basis?

It oughtn't be necessary.

It seems quite plausible that there are a few tables that are not
being vacuumed nearly often enough.

If you have tables where large portions are modified
(updated/deleted), then you need to run VACUUM quite frequently,
otherwise such tables will be overrun with dead space.

We have tables that we run VACUUM on every five minutes because they
change really heavily. (200-300 tuples, where we do an update to a
tuple every time a customer is billed.)
--
"cbbrowne","@","ntlug.org"
http://cbbrowne.com/info/finances.html
Why does sour cream have an expiration date?