Postgres tuning using feedback loops

I wanted to gather in one place a selection of resources that have helped me learn how to monitor and tune Postgres effectively. For optimal database performance, there's a high chance we'll have to get our hands dirty with this topic, because the default settings are on the conservative side (it has to start even on a Raspberry Pi), but with proper tuning it is impressive how far can Postgres take us.

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 I've found you can structure them into some categories. It will help a lot also to learn a bit how it works internally: I suggest reading the Postgres internals book (free) for this purpose.

Our workloads can change so we have to continuously track the internal health over time and adjust when needed, using feedback loops that let us know if we have to take any action. For this purpose, a custom Grafana dashboard based on Postgres internal metrics worked like a charm. For best practices on setting up actionable dashboards, I recommend this book. There are many readily available ones as well, and below I'll gather some options for you. Lastly, an interesting recent development is the appearance of AI agents that check metrics themselves.

Postgres wiki

• Monitoring

Monitoring tools

• postgres_ai
• pgwatch
• pg_stat_monitor
• pg_statviz
• pganalyze
• temboard

Postgres FM podcast episode

• Monitoring checklist

Agents

• Xata Agent

Blog post selection

• shared buffers/cache hit ratio
• checkpoints
• work_mem
• autovacuum
• bgwriter
• AIO
• connections
• Starting point recommendations

Hope it helps!