Your wish is my command sir. I am hard of hearing so I try to transcribe talks. This one is more abbreviated than my last one.
Secrets of Postgres Performance
Measure Everything Before and After
Do not do Dumb Things
Cache Everything
Watch your query counts
Tuning Parameters
shared_buffers: set to 25% of available ram, move up/down 5%
to find sweet spot
effective_cache_size: planning hint that tells PG how much RAM it can expect for OS disk cache
work_mem: per process amount of ORDER BY space
Other Parameters
wal_buffers: Set to 16MB and forget it
checkpoint_segments: Increase to at least 10
maintenance_work_mem: 50MB for every GB of RAM
synchronous_commit: turn off with data loss risks
Disks. Lots of disks.
Not all disks are created equal. Faster disks make a huge difference.
Configuration of disks matters a lot. RAID-5 is bad. RAID-1+0 is good.
Separate out the WAL onto its own disk. Can 4x write performance.
Worry about CPU after your disk/RAID/filesystem configurations are more or less optimal.
Use pgbouncer to pool connections
Tablespaces
Consider putting archive or legacy data onto slower drives/volumes
Separate your data and indices onto different volumes if possible
This applies even on SANs
Best of both worlds with Rackspace and Cloud Connect
EBS volumes and Software RAID is best but scary on AWS.
Choice of filesystem matters. No journaled filesystems for your WAL, it is a journal.
Use XFS for maximum performance.
USE noatime!
Avoid queries in loops.
Count() is slow
Secrets of Postgres Performance
Measure Everything Before and After
Do not do Dumb Things
Cache Everything
Watch your query counts
Tuning Parameters
shared_buffers: set to 25% of available ram, move up/down 5% to find sweet spot
effective_cache_size: planning hint that tells PG how much RAM it can expect for OS disk cache
work_mem: per process amount of ORDER BY space
Other Parameters
wal_buffers: Set to 16MB and forget it
checkpoint_segments: Increase to at least 10
maintenance_work_mem: 50MB for every GB of RAM
synchronous_commit: turn off with data loss risks
Disks. Lots of disks.
Not all disks are created equal. Faster disks make a huge difference.
Configuration of disks matters a lot. RAID-5 is bad. RAID-1+0 is good.
Separate out the WAL onto its own disk. Can 4x write performance.
Worry about CPU after your disk/RAID/filesystem configurations are more or less optimal.
Use pgbouncer to pool connections
Tablespaces
Consider putting archive or legacy data onto slower drives/volumes
Separate your data and indices onto different volumes if possible
This applies even on SANs
Best of both worlds with Rackspace and Cloud Connect
EBS volumes and Software RAID is best but scary on AWS.
Choice of filesystem matters. No journaled filesystems for your WAL, it is a journal.
Use XFS for maximum performance.
USE noatime!
Avoid queries in loops.
Count() is slow