When I setup a new PostgreSQL cluster, there are several parameters I change immediately. The defaults are not suited to most modern servers (unless you are being hosted by some cheap hosting company with over allocated shared servers).

These parameters are suitable for 8.3 and above, on Linux (not sure if Windows might need other changes). In some cases the 9.0 defaults are already at the values I have specified but since most of my servers are running 8.3 I include them here anyway.

Logging

• log_filename = postgresql-%Y-%m-%d_%H%M%S.log
• log_rotation_age = 1d
• log_rotation_size = 1GB
• log_min_duration_statement = 5000 (Anything slower than 5 secs and I want to know about it, I might raise or lower this value depending on the database usage)
• log_line_prefix = %u – %d – %h – %t:
• log_lock_waits = on (This is the only way to see long locking issues without catching it in the act)
• log_temp_files = 0 (To highlight any performance issues that could be addressed)

Memory
Examples for a 32GB server dedicated to PostgreSQL.

• shared_buffers = 8GB (Using more than this generally doesn’t provide any extra benefit although I have not explicitly tested that)
• effective_cache_size = 24GB
• wal_buffers = 64kB (sometimes 1MB if there is a lot of write activity)
• work_mem = 1GB (less if there are lots of concurrent users)
• maintenance_work_mem = 1GB

Connectivity

• listen_addresses = ‘*’ (default of localhost prevents external connections, could also use a specific IP address if you have multiple interfaces)
• max_connections = 250 (depends on number of app servers, db usage etc)

Locale

• datestyle = ‘iso, dmy’ (I’m not American and so don’t like backwards dates!)

Archiving

• wal_level = archive (I always leave this on even if not being used)
• archive_mode = on (I always leave this on even if not being used)
• archive_command = ‘/var/lib/dba/db_archive_log.sh %p’ (My script has some smarts to detect if archiving has been disabled by a flag in a certain location, and can raise alerts when archiving fails.)
• checkpoint_segments = 3 (I might not change this, but will definitely consider it based on the database usage scenario)
• checkpoint_timeout = 5min (I might not change this, but will definitely consider it based on the database usage scenario)