I wanted to gather in one place a selection of resources that have helped me make a mental model of how to tune Postgres effectively. For optimal database performance, there's a high chance we'll have to get our hands dirty with this task, because its default settings are on the conservative side (it has to start even on a Raspberry Pi).
Given the number of individual settings you can configure, the task might seem daunting at first sight, but luckily there are great guides out there, as well as you can categorize them. It will help a lot to learn a bit how it works internally: I suggest reading the Postgres internals book (free) for this purpose.
Our workloads can change, or we move to a different hardware, so in order to track the internal health over time and adjust when needed, I had great results at a former workplace with setting up a custom Grafana dashboard based on Postgres internal metrics. For best practices on setting up dashboards, I recommend this book.
The list below is a work in progress, I will add to it the moment I remember one, or replace one if I believe the quality is higher.
- shared buffers/cache hit ratio
- checkpoint tuning
- work_mem / memory in general
- autovacuum tuning
- bgwriter: 1 and 2
- AIO
- starting point, recommended settings: 1
Top comments (0)