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 .

In this presentation, we'll talk about the newest SQL/JSON features committed to PostgreSQL 15. We'll explain how to use them, where they can help and why they make a difference. Is PostgreSQL JSON implementation fully compliant with SQL:2016? In the meantime, the next generation of the SQL standard is being developed. What will the new standard include, and how will Postgres respond to it?

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 plv8 extension is very underestimated, though it can help simplify development for a variety of tasks. It's been a year since we started using this extension in our projects. The main reason for its low popularity is the complexity of building. Moreover, building plv8 requires a lot of traffic and disc space for temporary files. That's why we decided to build plv8 and provide ready-made binaries freely to anyone interested, not just as an offering for our customers. First, we made the binaries available on GitHub. Then we created a Docker image for Postgres with pre-installed plv8 based on Debian. It looked too heavy to us, and we decided to check Alpine, however, building it for Alpine appeared to be more complicated than we expected. I'll explain what difficulties we faced and how we managed to overcome them. I will also list the tasks that can be solved with plv8 and provide advice on how to make the development process more convenient. I will also share the links to our Docker images for the latest Postgres 13 and Postgres 14 releases, that are compact and can be used for any types of tasks.