Enterprise-level approach to PostgreSQL tuning: database experiments
Shared_buffers = 25% – is it too much or not enough? Or it's the right value?
How can we ensure that this – pretty much outdated – recommendation suit well our needs?
It is time to start apply enterprise-level approach to tuning postgresql.conf. Not using various blind auto-tuners or advices from old articles and blog posts, but based on the following two aspects:
- comprehensive database experiments, conducted in automated fashion, repeated multiple times in conditions as close to production as possible, and
- deep understanding of DBMS and OS internals.
Using Nancy CLI (https://gitlab.com/postgres.ai/nancy) we will consider a concrete example: infamous shared_buffers, under various circumstances, in various projects. We will try to figure out, how to optimize this settings for given infrastructure, database, and workload.