Re: Optimization

Justin,

> I am trying to understand the various factors used by Postgres to optimize.
I presently have a dual-866 Dell server with 1GB of memory. I've done the
following:

see: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
which has articles on .conf files.
(feel free to link these articles at PHPbuilder.com and elsewhere!)

> The databases I'm using have about 200,000+ news headlines with full-text
indexes (which range upwards of a few million records). They are updated
about every 5 to 10 minutes, which means I also have to run a vacuum about
once every 2 to 3 hours at least. As I get more updates obviously the
efficiency goes down. I'm trying to make the most of this system but don't
fully understand PG's optimization stuff.

Unless you're running PostgreSQL 7.1 or earlier, you should be VACUUMing every
10-15 minutes, not every 2-3 hours. Regular VACUUM does not lock your
database. You will also want to increase your FSM_relations so that VACUUM
is more effective/efficient; again, see the articles.

--
-Josh Berkus
Aglio Database Solutions
San Francisco