PGConf.Russia 2022

About 7 years ago, I moved from Windows-only development lead by an irresistible desire to add some parts to PostgreSQL. In this talk, I would like to cover things that were not obvious to me when I started working with the source code, the PG build and testing system. I'm going to talk about the simplest issues - routine IDE tasks, navigation, build and release process, and similar stuff. What I will share isn't the one and only truth. Some topics might relate to dummy-level problems :) I would be glad if other developers also shared the secrets of their development life. We can arrange a discussion of grep vs IDE :)

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.

Previously, we have explained the internals of Avito, discussed, where and how we store your classifieds, and how they appear on the search results. In the recent 3-4 a lot has changed in Avito. We got rid of logical replication, stopped using standby servers for reads, removed nearly all stored procedures and our custom failover solution, migrated all our classifieds into a sharded DBMS, switched from the monolith app to microservices. I'll explain why we made such decisions, list some of the problems we encountered and describe the current state of our development process.

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 .