Frank Wiles gave a great talk Secrets of PostgreSQL Performance
Don’t do dumb things
- Dedicate a single server to your database
- Only fetch what you need
Do smart things
- cache everything
- limit number of queries
- shared_buffers : 25% of available RAM
- effective_cache_size : OS disk cache size
- work_mem : in-memory sort size
- wal_buffers : set to 16MB
- checkpoint_segments : at least 10
- maintenance_work_mem : 50MB for every GB of RAM
Can also transactionally turn on grouping of transactions.
- As much RAM as you can afford - fit whole db if you can.
- Faster disks.
- Disk speed is important
- RAID5 is bad
- RAID-1+0 is good
- WAL on own disk → 4x write performance
- CPU speed - unlikely to be the limiting factor.
- use pg_bouncer to pool connections
- use tablespaces to move tables/indexes onto other disks
- ie, indexes on fastest disk
- stuff that might run in background and hit only specific tables that are not used by other bits