Essential Postgres Performance Tuning
Looking at the list of kernel and postgresql.conf parameters, tuning your PostgreSQL database may seem like a daunting task. While each parameter has a role to play, in most scenarios just a handful of these end up impacting performance the most.
Instead of being a comprehensive guide on every tunable parameter in postgres and the kernel, this talk serves as a quick practical guide to tuning your PostgreSQL cluster to get the best possible performance with the least effort, with special emphasis on kernel tuning. While it is not possible to access the operating system when using DBaaS offerings, when you do have access to the underlying system, the ability to tune your kernel can significantly improve performance of the database.
Along the way we will look at the most commonly encountered performance gotchas when setting up a new PostgreSQL cluster or migrating an existing one. We will also weigh the importance and impact of performance, security and integrity so depending on your custom needs you know how far you can go for performance, having the type of data you have.
Also discussed are approaches for performance comparison and a look at automated tuning tools - the concept, requirement, feasibility, limitations, and pros and cons of such a mechanism.