Last Updated: February 25, 2016
·
10.23K
· creaktive

Making a decent postgresql.conf, like a boss

This tip is not about high-end performance tuning at all, instead, I'll focus on reasonably optimized settings compatible with the actual hardware running the PostgreSQL server.
That's because default PostgreSQL installations from almost every distributor have quite handicapped factory settings.
Fortunately, there is pgtune, a Python script that acts like a PostgreSQL configuration wizard:

pgtune works by taking an existing postgresql.conf file as an input, making changes to it based on the amount of RAM in your server and suggested workload, and output a new file
For instance:

python pgtune \
    -i /etc/postgresql/9.2/main/postgresql.conf \
    -o postgresql.conf \
    --memory=8589934592 \
    --type=Desktop \
    --connections=10

(on Linux, RAM amount is obtained automatically)
And here is the diff between the original and generated config:

556a557,568
> #---------------------------------------------
> # pgtune wizard run on 2012-12-18
> # Based on 8388608 KB RAM in the server
> #---------------------------------------------
> 
> default_statistics_target = 100
> maintenance_work_mem = 512MB
> effective_cache_size = 2GB
> work_mem = 128MB
> wal_buffers = 1536kB
> shared_buffers = 512MB
> max_connections = 10

P.S. - for some serious tweaking business, check PostgreSQL Performance Tuning by Christopher Browne.