PostgreSQL Performance Tuning

Well I have had my head buried in Postgres tuning parameters today. There are so many different options to look at and no real definitive guidelines out there, and to make matters worse you can really impede performance with incorrect tuning parameters which is probably counter productive if your interested in tuning for performance :-).

I have read a lot of blog posts and articles today and will list the ones I found most helpful below.

There is a lot of information out there that is now mostly out of date. PostgreSQL 8.3 changed the game when it came  performance tuning and a lot of the hard and fast rules that had been true for many years are now no longer true. So my one piece of advise when it comes to looking at the information out there, make sure it is relevant to the version of Postgres you are using.

The other thing to keep in mind are there are certain performance parameters that can improve performance, yet reduce reliability or time to restore objectives for your database and also the inverse of this decreased performance and increased reliability. You need to decide what your individual requirements are, it is mostly a counter balance between Performance and Reliability (reliability being anything from producing instability in the disk system by changing caching methods, through to the amount of data lost at power failure, or if using WAL log archiving how long between log writes and how often to write checkpoints). As always when changing any value that is suggested do your own research. For me I looked at the official Postgres documentation as well as just googling the setting name (ie shared_buffers) and reading information from the mail list archives and other tutorials.

Resources I used:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.