Skip to content
RED SAGSkills
mediumDatabase Administrator interview question

How would you tune the main PostgreSQL memory settings on a new server?

What the interviewer wants to hear

  • shared_buffers around 25 percent of RAM as a start
  • effective_cache_size tells the planner how much OS cache to expect, around 50 to 75 percent
  • work_mem is per sort or hash operation per session, so keep it modest globally
  • maintenance_work_mem higher for vacuum and index builds
  • Consider connection count and measure with monitoring, not guesses

Model answer

I start from the total RAM and the expected number of connections. shared_buffers is usually about 25 percent of RAM, because PostgreSQL also benefits from the OS page cache. effective_cache_size does not allocate memory; it tells the planner how much cache is likely available, so I set it to around 50 to 75 percent of RAM. work_mem is the tricky one: it applies to each sort or hash in each query, so I keep it modest globally, for example 16 to 64 MB, and raise it per role or session for reporting jobs. maintenance_work_mem can be higher, like 1 to 2 GB, to speed up vacuum and index builds, keeping autovacuum workers in mind. Then I watch temp file usage, cache hit ratio and memory headroom, and use a pooler so connection counts stay sane.

Can you answer it under time pressure?

Take a timed medium round and get scored on every answer.

Start mock interview

More medium Database Administrator questions