PgConf.Russia 2019

In this master class, I will take you step-by step through a major upgrade of PostgreSQL. Through our practice we see a lot of different PostgreSQL servers in production environment and often, almost too often teams who once made PostgreSQL their database of choice never update it following the initial installation. The are many different reasons for it, the result, however, is the same - they all miss out on the new useful features of the newer releases and reduced database performance.
The goal of my masterclass is to equip attendees with necessary tools for performing PostgreSQL upgrade. I will take you through each step of the major upgrade and will dive into each executed command. I will also explain the particular order in which I perform an upgrade and explain the consequences of not following this order or missing a particular step. We will perform an upgrade of PostgreSQL 9.0 to 11. My hope is that following this masterclass number of outdated PostgreSQL database will reduce since participants will then go back to their databases and make sure that they are running the most recent version.

So you finally have your database model for your application and you fill it in with current data. How do you keep it up to date? While INSERT may still be transparent, UPDATE and DELETE will overwrite your previous data, so you won't be able to reproduce them. Cloning the whole huge content for each minor update is not an option. For rich and complex data about hundreds of thousands of power generators in Germany and worldwide, I built a model using range data types in recent PostgreSQL which allows me to insert, update and delete data while granting the full access to the whole state of the database at any historical moment. I'll present a very simplified version of the database so the audience will be immediately able to apply it for their cases. I'll also show a few tricks in Python and Psycopg2 that will allow a whole team to prepare, review, and deploy all revisions to this database without merge conflicts. And I'll give a few ideas on how to retrieve this data efficiently.

When you are optimizing Postgres it is usually maintenance that goes by the wayside. How do we fix autovacuum? Where did all of this bloat come from? Why am I getting IO spikes? How do I get RDS to behave?! Why are commits so slow on replication? The answer to all of these questions is understanding the relationship between proper Postgres maintenance and performance. Join us for a 3 hour jaunt through the wily world of making Postgres Go!

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.