Besides many other interesting topics, Opengeo’s PostGIS tutorial discusses “Tuning PostgreSQL for Spatial”.
The following values are recommended for production environments:
- shared_buffers: 75 % of database memory (500 MB)
- work_mem: 16 MB
- maintenance_work_mem: 128 MB
- wal_buffers: 1 MB
- checkpoint_segments: 6
- random_page_cost: 2.0
- seq_page_cost: 1.0
All of these configuration parameters can edited in the database configuration file, C:\Documents and Settings\%USER\.opengeo\pgdata\%USER. This is a regular text file and can be edited using Notepad or any other text editor. An easier way of editing this configuration is by using the built-in “Backend Configuration Editor”. In pgAdmin, go to File > Open postgresql.conf…. It will ask for the location of the file, and navigate to C:\Documents and Settings\%USER\.opengeo\pgdata\%USER.
The changes will not take effect until the server is restarted.