Christopher B. Browne's Home Page
cbbrowne@acm.org

2. QuickStart Guide to Tuning PostgreSQL

PostgreSQL often gets accused of being rather slow. In earlier versions, back in the "dark ages" of 7.0 or 6.5, that was certainly true. In modern days, this is typically the result of taking an install with all of the defaults from what is generated by the source distribution, perhaps combined with running an application already specifically tuned for optimal behaviour with some other database.

Note

The defaults are configured to be able to function on all platforms on which PostgreSQL runs, and are definitely not optimal for typical usage.

Fortunately, it is quite straightforward to achieve massive improvements by changing a very few things. Here is a sort of " top ten" list of things that should be done to improve performance, and why:

This represents a set of actions that are highly specific to PostgreSQL ; if you go through these activities, you should see substantial improvements in performance over the " default" settings.

After that, it also makes sense to look to additional techniques to tune specific queries. Typically, this involves examining the query plans for particular queries, and experimenting to see what changes improve those plans. This commonly involves adding indices that allow your queries to be efficiently selective about what records they retrieve. Evaluating the efficiency of queries is a bit of a "black art" where it simply takes time and practice to recognize the goodness of a query plan.

Google

If this was useful, let others know by an Affero rating

Contact me at cbbrowne@acm.org