PgConf.Russia 2019

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.

One of the most soft after features of Postgres v10 is logical replication. In this presentation we will cover what Logical Replication is, how it compares to Binary (Streaming Replication), how Logical Replication works, configuring Logical Replication, Logical Replication limitations, gotchas, security and management. We will also discuss potential deployed architectures with Logical and Binary Replication and some neat features of the underlying technology.

At the end of this presentation an attendee with a reasonable understanding of how to manage Postgres will be able to configure Logical replication for use.

During the report, I would like to share the experience of implementing BlockChain in a real business task based on 1C+PostgreSQL. Where did this task come from? From whom do we protect data with the help of technology? How to get a chain integrity report of tens of millions of records in seconds?

While doing development of one of our project we were asked to build HA database using Postgres, geographically distributed.

First our choice was obvious, we started to work with big 3 cloud providers, but soon it was quite understand that everything costs big enough for us. Also there were a bunch of incompatibilities with unsupported extensions as well as londiste replication we were heavily used.

I will talk about why we chose patroni, what types of problem we faced with and patroni's special features can dramatically simplify deploy and everyday usage.