Keep seldom-used settings handy in your configuration files

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.

Advertisements

Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: