PostgreSQL interview question · Most asked
What do shared_buffers, work_mem and effective_cache_size control?
Summary
shared_buffers is PostgreSQL's own page cache (commonly about 25% of RAM); effective_cache_size is only a planner hint of how much data the OS cache plus shared buffers can hold (often 50-75% of RAM); work_mem is the memory each sort or hash operation may use before spilling to disk.
Longer Answer
work_mem is per operation, per query, per connection: a complex query with five sorts across 100 connections could use 500 x work_mem. Keep the global value modest and raise it per session or role for reporting queries (SET work_mem = '256MB'). Spills show as external merge Disk in EXPLAIN ANALYZE and as temp files in pg_stat_database. maintenance_work_mem speeds up VACUUM and index builds.
Key Points
- shared_buffers ~25% RAM.
- effective_cache_size is a planner hint.
- work_mem is per operation; raise per session.
- Watch temp-file spills.
More most-asked PostgreSQL questions
- How does MVCC work in PostgreSQL?
- JSON or JSONB in PostgreSQL, and how do you index JSONB?
- What does VACUUM do, and how does autovacuum work?
- How do you read EXPLAIN ANALYZE output in PostgreSQL?
- What isolation levels does PostgreSQL provide, and how do they behave?
- What index types does PostgreSQL support, and when do you use each?
- Which schema changes take heavy locks in PostgreSQL, and how do you avoid outages during migrations?
- Why do PostgreSQL deployments need connection pooling, and how do PgBouncer's modes differ?