Server Configuration Tuning

Learning Focus

Use this lesson to understand the PostgreSQL settings that most often affect performance and how to tune them safely.

Concept Overview

Server tuning is workload-dependent. Start with:

• the slow queries you identified
• evidence from plans and metrics

Then adjust settings deliberately.

---

High-Impact Settings (Conceptual)

Setting	What it affects	Typical direction
shared_buffers	cache for data pages	increase carefully
work_mem	memory per sort/hash operation	increase for complex queries
maintenance_work_mem	vacuum/index builds	increase for maintenance
max_connections	concurrency limit	keep reasonable; use pooling
effective_cache_size	planner estimate of OS cache	set to realistic value
autovacuum settings	bloat/stats	tune for churny tables

---

Practical Workflow

1. Measure baseline (query times, CPU, I/O, locks).
2. Change one setting at a time.
3. Re-run representative workloads.
4. Keep a rollback plan.

---

Autovacuum Notes

Autovacuum is critical at scale:

• it reclaims dead tuples
• it updates statistics
• it enables index-only scans

If autovacuum is not keeping up, you will see bloat and unstable plans.

---

Common Mistakes & Troubleshooting

Mistake	What happens	Fix
Setting work_mem too high	Memory spikes under concurrency	Consider connection count and query mix
Increasing connections without pooling	Context switching and memory waste	Use a pooler, keep max_connections sane
Ignoring autovacuum	Bloat and slow queries	Monitor and tune autovacuum

---

Best Practices

• Tune based on measurement, not defaults.
• Use connection pooling.
• Keep autovacuum healthy.
• Document configuration changes.

---

Quick Reference

SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;

What's Next

• Continue to 19. Backup and Recovery
• Or return to Performance Optimization Overview