PGConf.Russia 2022

This talk is about our work on detailed vacuum workload statistics collection by the statistics collector and about problem solutions and benefits it can provide.

Logical decoding and replication slots introduced in PostgreSQL 9.4 (released in 2014) created a solid foundation for implementing built-in core logical replication in version 10 (released in 2017). Unfortunately, there are a few limitations that make logical replication not very useful in real-world scenarios. Logical decoding currently isn’t supported on the standby server, and PostgreSQL allows creating logical replication slots only on the primary server. Or in other words, logical slots are lost on failover/switchover.

Postgres hackers made many attempts to address the problem, and most of them resulted in not too much success. Although, there is one little function introduced in PostgreSQL 11 that made it possible to implement failover of logical replication slots externally.

In my talk I will tell a story of how Patroni solves the problem of logical replication slots failover without using invasive third-party extensions, dig down into some of the Postgres internals in order to prove why this approach is safe, and finally, we will discuss limitations and potential downsides of this solution.

The more tables are involved with the query, the more difficult it is for the scheduler to choose a suitable execution plan (both time and memory usage increase). How can we "tell" the planner that it is better to connect this pair of tables first, and the rest can be connected later? What if we see that a part of our query can be improved, but the optimizer does not do this. In my presentation, I will talk about managing the order of connections. I will explain how we can influence the formation of a query plan using the standard "vanilla" PostgreSQL methods.

As opposed to in-memory DBMSs, traditional disc database management systems have an inherent physical limitation. It's their storage system which is by several orders of magnitude slower than CPU operation memory, even in the case of All-Flash NVM. It becomes especially critical for the development of business analytics apps and OLAP scenarios on disc DBMS. We will consider a number how-tos for improving the performance of traditional DBMSs, and then offer a new storage-centric approach to hardware-based speed-up for disc DBMSs.