PostgreSQL tuning recommendation for large server and dataset

Question

I'm working with a PostgreSQL (8.1) server on a dedicated machine with 64 GB of RAM and a fast RAID disk. The data set itself is pretty huge -- we have several tables around 200 GB, more in the 50-100 GB range, and it's constantly growing, with vacuums happening overnight, large operations running sometime in the early morning, and then smaller operations on-demand throughout the day.

We've been running into some performance problems lately, with, say, the vacuum not finishing in time before the large operations start, and then that starts blocking for the rest of the day. We've been trying to adjust our configuration to take advantage of our resources, but we're having trouble with some of the more loosely defined parameters like work_mem. (One experiment was to raise it to 512 MB with a max_connections of 150, which has turned out to cause some issues.)

What might be some good baseline parameters to try? Once we get the configuration into a stable state, we can always start experimenting more with fine-tuning individual values, but we're not sure where our needs differ from the standard recommendations for this kind of thing.

Edit: I answered this in comments, but to make it official, we are in the process of putting together a more long-term plan which will include partitioning as well as some other re-architecturing tasks, but right now, we're trying to get the most out of what we have. I'm looking for tips that are more along the lines of "A work_mem setting of 32MB will probably serve you fairly well, but you probably won't see much improvement going over 64MB."

Answer 1

PostgreSQL 8.1 is pretty old, it reached its EOL time (vide PostgreSQL Release Support Policy). I think that newer versions (e.g. 9.0) have better performance (especially better vacuuming) and in my opinion it is first step (of course postgresql.conf and probably kernel/ulimit settings are important too).

In PostgreSQL documentation there is partitioning method described for such big (and constatly growing) tables. It might be useful solution.

from http://www.day32.com/MySQL/Meetup/Presentations/postgresql_partitioning_short.pdf

Partitioning a table is normally only worthwhile when the size of the table exceeds physical memory.

Answer 2

Given the massive amount of data and the fact that is constantly growing, no tuning will be good enough in the long run if you keep using only one server. You should start considering moving some of the tables to other servers and sharding whenever its possible (to keep it scalable). Some of them might even fit in a cloud service (ie. SimpleDB). Anyway, I dont know if sharding or a NoSQL solution might fit your needs since I dont know your data, but for many cases, with a good design, it does. A temporary solution could be to use some read slaves and/or a memcached farm, in case you have performance problems during day use.