System Tuning for PostgreSQL DBAs
System tuning adjusts server-wide resources: memory settings such as shared_buffers and work_mem, I/O and checkpoint behaviour, connection handling with pooling, partitioning for large tables, and monitoring. It complements query tuning on the PostgreSQL DBA path, because a well-written query still suffers if memory, connections or disks are misconfigured.
Stage: Performance · Part of the PostgreSQL DBA roadmap · Last reviewed 19 November 2026
What to learn in System Tuning
- Set shared_buffers, effective_cache_size and work_mem from available RAM with reasoning
- Tune checkpoint settings to smooth out write spikes
- Put PgBouncer in front of a database that sees many short connections
- Partition a large time-series table by range and drop old partitions
- Monitor with pg_stat_activity and pg_stat_database and feed an exporter into dashboards
System Tuning interview questions
Why is setting work_mem very high risky?
work_mem is allocated per sort or hash operation, per plan node, not per connection, so a complex query across many connections can use several multiples of it and exhaust RAM. Set it conservatively and raise it per session or role for specific heavy reports.
Why use a connection pooler such as PgBouncer when PostgreSQL already accepts connections?
Each connection is a separate backend process with memory and scheduling overhead, so thousands of connections hurt throughput. A pooler keeps a small number of server connections and multiplexes many clients, though transaction pooling restricts session-level features such as advisory locks and temporary tables.
Where System Tuning fits in the PostgreSQL DBA roadmap
System Tuning belongs to the Performance stage (intermediate level), which takes about 7 hours. Make it fast.
Other units in this stage
Back to the full PostgreSQL DBA roadmap