PgConf.Russia 2019

Several real cases from those who stopped the migration to PostgreSQL.

You just set up your first PostgreSQL cluster, created a database schema, loaded some data, did some fine tuning of configuration. Now you want to make your cluster highly available. Unfortunately, PostgreSQL doesn't offer built-in automatic failover, but luckily for us, there are plenty of external tools for that. As a next logical step you start choosing a tool, and... you already doing it wrong, because first you have to define SLA, RTO, and RPO. In this talk I am going to cover most of the common mistakes people do when setting up a highly available cluster.

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:

1. comprehensive database experiments, conducted in automated fashion, repeated multiple times in conditions as close to production as possible, and
2. 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.

At the end of 2018, I've got a request to extend the syntax of the ltree contrib. I'm finalizing the patch and going to speak about:

• the current state of the extension,
• the extended syntax, and
• the process of development and testing the extension.