PGConf.Russia 2022

SPQR is a lightweight OLTP sharding solution written in Go. In this talk, I'm going to discuss design decisions that led us through FDW-based sharding, CustomNode-based sharding, C-implemented lightweight query routing, and finally to SPQR design.

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.

Our talk reviews the modern tools for administering PostgreSQL. It also covers the DBMS's performance bottlenecks and provides approaches to resolving the related issues. The presentation tackles the problems that occur while Oracle to Postgres migrations including the following: performance tuning tools (the analogues of AWR and ASH); monitoring tools (the analogues of Cloud Control); ensuring high availability and reliability of the database (grid infrastructure analogues); known "database performance bottlenecks"; an overview of options for technical support options SLAs.

Many books end with a wedding, but the reader has no idea about the future life of the heroes except that they lived happily ever after. In 2019, we successfully migrated distributed our railway application from Oracle 11g SE to vanilla PostgreSQL 11.9. But our story did not end with this successful migration - life went on, and sometimes we got startled because of "surprises". We encountered a number of problems, some of which were solved by reorganizing the data, some disappeared after we changed our stored procedures, and some got resolved after tuning the PostgreSQL parameters. Solving our problems would be impossible without the logging and profiling system built into our DB application. Our talk covers the examples of successful detection and resolving of the performance issues that occurred in our PostgreSQL-based application.