Tuesday, December 23, 2008

Tuning Your PostgreSQL Server

Lately, as I upgrade Postgres to 8.3, I've been reevaluating the configurations on the many search databases I manage. My buddy discovered this basic guide to configuring Postgres (the postgresql.conf file) -
Tuning Your PostgreSQL Server.

This guide does not provide one-size-fits-all magical answers, but it provides a good baseline for settings such as shared_buffers and effective_cache_size. Obviously, your mileage may vary - if your database is OLTP or data warehouse, or you're just loading in lots and lots of data, you'll want to understand and tweak these parameters by hand. But if you follow this guide, at least some of your memory settings will be relative to your total RAM available! Initially all the parameters are set very low - this is so when you're initializing your database on a low-end system, the init script won't conk out due to lack of memory. That would be bad.