Re: Tuning

walterc(at)indiana(dot)edu (Carol Walter) writes:
> I have some questions about tuning. The PostgreSQL documentation
> says that you don't need to worry about index maintenance and tuning
> with PostgreSQL. I'm used to systems that work a lot better if they
> are periodically re-orged or re-indexed. Is it true that one need
> not be concerned with this? I'm certain that the databases must
> require some human intervention. What kind of tuning or other
> intervention are you doing. What kind of tools are available and are
> being used.

Well, there is one side to things where yes, indeed, "maintenance is
quite necessary," and that being in the area of "vacuuming."

<http://www.postgresql.org/docs/8.3/static/routine-vacuuming.html>

The need to 'reindex' or 'reorg' tables is not non-existent, however
it is needed *way* less frequently than was the case in much older
versions of PostgreSQL. e.g. - with v7.2, there were patterns of
updates that would leave portions of indexes not usable, but the issue
was rectified in ~7.4, and people have not been observing problems
relating to this former scenario.

Back when we had systems on v7.2, we had to shut down every few months
and reindex some tables in order to keep performance OK. That's no
longer the case with systems running on v8.1, and as we bring 8.3 into
production, I expect even less need for manual interventions.

If you are running VACUUM and ANALYZE often enough, and autovacuum
pretty much does so, now, then there shouldn't be much need to do
"re-orging" of the system.
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://linuxfinances.info/info/nonrdbms.html
In the name of the Lord-High mutant, we sacrifice this suburban girl
-- `Future Schlock'