Main reference for tuning:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
Most importantly you should make sure you have a way of measuring performance first, and then when you try new changes try them one at a time, gather data (3-5 samples are a good start, you might need to run tests a few times to get take into account cache priming) then compare with old settings.
The most important principle is, the less disk IO the better. So if we have the correct indexing, it works less to find an answer. If you can cache data into RAM, that even better, as the disk IO will be less frequent.
So focus on:
- shared_buffers - set it to a portion of the RAM free on the host. (like 1/2 or 3/4 if the memory is huge, preserve 512-1024 as a guide)
- work_mem - increasing it to say 16 MB or so is recommended
Use show all to see the current settings for your DB, and make changes in your postgresql.conf file.
Check the OS scheduler (either deadline or noop is recommended):
$ cat /sys/class/block/sde/queue/scheduler
noop anticipatory [deadline] cfq
Check your disk IO with iostat -m 5 ... you want to make sure none of your disks are pegged (usually most spinning disks can do up to 60 MBps if they are fast, RAID devices and SSD disks are usually faster). If we can determine there is a lot of IO on one disk, then we need to reduce that disk IO by either increasing cache (shared_buffers) or something else.
Some other ideas:
- Make sure you have autovacuum switched on and its running periodically: http://www.postgresql.org/docs/9.2/static/routine-vacuuming.html
- Periodic re-indexing can help with performance and storage usage also: http://www.postgresql.org/docs/9.2/static/routine-reindex.html