PostgreSQL 9.0 High Performance, by Greg Smith

I’m upgrading two work databases from PostgreSQL 9.0 to 9.1, and that means some test bulk loads. (Yes, I know about pg_upgrade, but we’re doing reloads for other reasons.) Greg Smith’s fantastic PostgreSQL 9.0 High Performance is a great help in everything related to Postgres performance, including a couple of pages on how to speed up bulk loads.

Greg’s advice is to tweak some parameters that you wouldn’t use in production, but can use for the duration of the bulk load. I wanted to make it easy to flip between standard configuration and bulk load config as I did my testing, so I aggregated the bits that were relevant to my config and stuck them at the end of my postgresql.conf. I left them commented out.

#------------------------------------------------------------------------------
# BULK LOAD ONLY
# Settings from PostgreSQL 9.0 High Performance, p. 401
#------------------------------------------------------------------------------
# maintenance_work_mem = 1GB
# checkpoint_segments = 150
# synchronous_commit = off
# fsync = off

Since they’re at the end, when I uncomment them and restart the server, they override the settings earlier in the file. Then I can do my load, and then comment them out again, without having to go all over the file to find them.

Note that I made sure to note where I got the settings from, for future reference. I don’t expect to have to do a bulk load again for at least a year, and I’ll forget by them.