Schinckel.net

Postgres and Django

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

Tuning

  • shared_buffers : 25% of available RAM
  • effective_cache_size : OS disk cache size
  • work_mem : in-memory sort size

Less important

  • 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.

Hardware

  • 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.

Other

  • 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