I recently built a new server, and decided to start keeping a local minutely updated rendering database. Before starting other tuning, I needed to create a sane PostgreSQL configuration. The default PostgreSQL configuration is designed for very small servers, while my new server was definately not small.

The hardware for the new server was an E3-1231v3 (3.4GHz base, 3.8 GHz turbo), 32GB DDR3 RAM, 8 3TB 7200 RPM drives with 1TB platters for the array, with two 256GB Crucial M550 SSDs for cache, and mirrored for a ZIL write cache. The HDDs are on a LSI 2308 built into the Supermicro motherboard and the SDDs are on SATA3 onboard.

Knowing my main use would be large OSM databases, I used my standard osm2pgsql import workload to test settings.

1
2
3
4
5
6
7
8
9
10
11

shared_buffers = 512MB
work_mem = 64MB
maintenance_work_mem = 1024MB
full_page_writes = off
wal_buffers = 16MB
checkpoint_segments = 64
checkpoint_completion_target = 0.9
random_page_cost = 2.0
cpu_tuple_cost = 0.05                   
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.2

The most unusual setting for this setup is full_page_writes = off, which is reasonably safe on a ZFS filesystem with its intent log and block checksums.

Overall, these basic adjustments brought an import with default filesystem settings down from 46 hours to 25 hours, with most of the gain in the time to build the massive GIN index on the ways table.