PGConf.Russia 2022

The talk covers the use of PostgreSQL, LUI and LUI4ORA2PG for building an analytical reporting system.

The talk tackles the following topics:

• migration from an Oracle environment;
• application of JSON functions;
• how the temporary tables helped us;
• our own means for load testing and bottlenecks detection;
• how to make beautiful GeoJSON format maps to display diagrams on them;
• installation and testing of the system on an "Elbrus" computer;
• what became an obstacle or was missing when using PostgreSQL.

The history of growth of the Live Universal Interface (LUI) web application development tool and the LUI4ORA2PG migration tool can be found in our previous presentations at PGConf conferences:

https://pgconf.ru/2019/118109 ;

https://pgconf.ru/201911/264095 ;

https://pgconf.ru/2020/262456 ;

https://pgconf.ru/2021/288310 .

The release cycle for version 15 is nearing completion. At the time of the presentation, the complete list of changes will not be known. But for sure there will be something to talk about.

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.