Cloud PostgreSQL in Ozon: what's under the hood?
My talk covers PostgreSQL infrastructure in Ozon: - how do we tune virtual machines (KVM); - which version control system we have created; - which drivers we make and how it relates to fault tolerance and load balancing; - how do we make "hot" upgrades for the parameters of our virtual machines.
Видео доступно только участникам мероприятия, выполнившим вход в личный кабинет
Alexander Liubushkin ООО "ФОРС Телеком"Rustam Abdrakhimov ООО "ФОРС Телеком"
Experience in using Live Universal Interface (LUI) and PostgreSQL in creating an analytical reporting system
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:
Alexander Kukushkin Zalando SE
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.
Vadim Yatsenko Tantor Labs
PostgreSQL has a number of peculiarities that you need to take into account not only while maintaining your database but also when designing your database schema. Experienced PostgreSQL are well aware of vacuuming process. On the web one can find tons of materials covering its internals, configurations and monitoring. Many valuable talks about vacuum were given at numerous conferences. However, we still face the common wraparound problem when the maximum possible number of transactions (xid) is reached. It happens even on databases that are relatively small in size. In my presentation, I will share a customer case that looks interesting to me. A chain of mistakes made at different stages of the database's life cycle once caused a disaster. The database fully stopped for one week, we detected a wraparound and spotted corrupted blocks. Maintenance was problematical, and we spent sleepless nights in search of a solution. We managed to achieve a local win as we finally restored the database, but it's not the end of this story, which makes it even more interesting.