PgConf.Russia 2019

I'm going to talk about emerging technologies in the area of general purpose RDBMS indexing. I will describe different approaches suitable for different workloads. We will discuss ideas from academic researches and corresponding industrial response from developers, communities, and companies. There will be the short live-coding session on creating DIY index in 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.

A major responsibility of a database engine is to convert a declarative SQL query to an efficient execution plan, employing various methods to scan and join the relations. There is always a development effort to improve this area. What clever execution plans can PostgreSQL generate, what's new in version 11 and what is in development? To name a few things, the joins are optimized by removing unneeded outer and inner joins, and reducing joins from outer and semi to inner. There is work to enable merge joins on inequality and range overlap, and to improve join selectivity estimates with multi-column statistics. When it comes to scanning a single relation, covering indexes allow to use index-only scans more often. Incremental sort and more precise estimation of sorting costs help generate better paths when sorted output is required, e.g. when using GROUP BY and ORDER BY or performing merge joins. This talk aims to give an overview of such optimizations that already exist and that are being developed now.

It's so good when database behaves predictable. When the performance is lacking, you just add CPU cores, terabytes of RAM and millions of IOPS, and everything becomes good again. But it's rather unpleasant, when server have plenty of free resources, while database is still running slow. And it's especially sad if stress testing detects no problems, while real life workload of the same volume makes your database hang.

In this talk I will consider bottlenecks of PostgreSQL, which we met in our practice, and which causes sad behavior described above. I'll also explain what can be done at user level in order to evade these bottlenecks, and what developers are planning to do in order to eliminate those bottlenecks. I'm also planning give some recipes of stress testing, which could have to evade surprises in production.