Migrating Oracle to PostgreSQL in a high-load system with microservice architecture

A report on our experience of implementing PostgreSQL instead of Oralce in a highly loaded system with a microservice architecture that processes several terabytes of data. Let's talk about how we went from piloting Greenplum to moving to several interacting PosgreSQL databases with different load profiles, and about the problems we encountered in the process.

Multi-transactions and possible issues with them

In this presentation, we cover multi-transactions, explain when they appear and how they can be monitored and checked. We also tackle the possible issue with multi-transaction ID wraparound and the problem of file absence in pg_multixact/offsets when restoring from a backup.

Heterogeneous distributed system - as a way to safely migrate from MSSQL Server to PostgreSQL, as well as reduce sanctions risks

This report is primarily intended for companies, specifically their IT departments, that are running Russian systems 1C 8.x and have the ability to work with both MSSQL Server and PostgreSQL. We live in a unique time when having a heterogeneous IT system (a system with a distributed architecture where each database instance runs under different databases and/or has a different data structure) is justified both economically and in terms of potential risks. On the one hand, we store data and use databases with predictable behavior and open source code, regardless of the political situation. On the other hand, with this approach, we take advantage of all the benefits (primarily performance) of a powerful database supported by the largest vendor, albeit a hostile state to us. Now is the time to assess risks with the necessary level of paranoia. After all, data can be corrupted not only at the level of storage logic, but also by the method of additional "hidden" calls of Delete/Update constructs at the engine level of the database. Therefore, it is now relevant not only to monitor the performance of the IT system, but also to audit data and their timely integrity. It is necessary to implement data verification procedures, it is necessary to implement procedures for closing periods, procedures for fault-tolerant storage. And accordingly, to provide for various threat models in data recovery procedures. The report presents options for countering such threats and scenarios for the most seamless transition of large databases to PostgreSQL, since it is precisely for such databases that the problem of translating to a new DBMS is particularly acute.

Crazy things you can do with PostgreSQL Indexes

Of the relational databases, PostgreSQL is fairly unique in the indexing capabilities it offers. While most of us are familiar with the use of indexes to speed performance when filtering on columns, PostgreSQL indexes can do far more than this.

This talk will focus on cases where difficult problems were solved through the creative use of indexes. Each of these cases is from an episode in my career.

In this presentation, you will learn:

• The general index access methods PostgreSQL supports out of the box
• How the planner uses indexes (only on a high level)
• Functional indexes
• Creative use of Index Only scans.

This is a talk for all audiences. The talk is primarily for beginner to intermediate users, but should have enough information for more advanced users to get some insight or inspiration from the talk as well.