PGConf.Russia 2022

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.

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.

The volume of data stored in Postgres Pro databases is growing rapidly. In some organizations, such databases have already exceeded tens of terabytes in size. The existing approaches to backups don't ensure timely backup and restore of data in such cases. We have supplemented the well-known and well-tested Postgres Pro backup tools with functionality that will allow users to backup and restore large databases. In addition to that, we have created our solution with the convenience of DBAs in mind.

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.