tech

PostgreSQL Performance Tuning

postgresqldatabaseperformance

PostgreSQL is incredibly capable out of the box, but a few tweaks make a big difference.

Quick Wins

  • work_mem: increase for complex sorts (default 4MB is very conservative)
  • shared_buffers: 25% of RAM is a good starting point
  • effective_cache_size: tell Postgres how much cache the OS provides

Query Tuning

Always start with EXPLAIN ANALYZE. Sequential scans are not evil — they are correct for small tables. The problem is sequential scans on tables with millions of rows that should use an index.

Add indexes based on actual query patterns, not theoretical ones.