Skip to content
January 25, 2011 / Steven Pousty

Quick note on Tuning Postrgres

A while ago I wrote this in the online doc for postgres after getting some help on IRC. I Had trouble finding my own comments again but then doing a vanity search research I found the comments – putting them here so it is easier for me to find.

I would love to see more hints or other peoples experience. Postgresql tuning is one of the least explained pieces of Postgresql.

So without further ado – here are some notes for tuning Postgres when your machine has more than 4 gigs of RAM (which is most servers now).

From AndrewSN on the IRC Channel here is the config setting for a Unix like machine with RAM >= 4g

shared_buffers = 5% to 10% of physical RAM

effective_cache_size = 50%-75% of physical RAM (unless you’re using 8.1 or older, in which case 75%-100% of physical RAM)

wal_buffers = 8M

maintenance_work_mem = 384M (or so, there’s not usually much to gain from going higher)

work_mem = this one is harder, since it depends a lot more on the query workload than the others; 16M to 64M might be a reasonable starting point
the more concurrent connections you have doing complex queries, the smaller that needs to be

max_fsm_pages is determined more by the on-disk size of your db than the size of the server. estimate or measure the on-disk db size, divide by 16k, and use that as a starting point

if you use temp tables a lot, a modest increase in temp_buffers may help if you have a lot of RAM.

checkpoint_segments = 32 ?
checkpoint_segments is worth increasing if you have a heavy write load and don’t mind a lot of disk space used for pg_xlog. It does slow down recovery after a crash or unclean shutdown.

I personally increase default_statistics_target to 100 by default regardless of the size of the server


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: