PGConf.Russia 2023

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.

With the rise of NoSQL databases, a number of falsehoods have flourished regarding how to choose a database engine. This talk focuses specifically on Redis and PostgreSQL, and why one might choose one or the other.

At small scales, we can often get by thinking of database servers as black boxes, but as we scale, the internals and architecture become more and more important. This talk focuses on behavior of these systems at scale and under load.

In this presentation you will learn:

• How Redis and PostgreSQL differ architecturally
• How differences in architecture affect scalability and performance
• Cases here Redis is the clear winner
• Cases where PostgreSQL is the clear winner

Additionally, some notes will be offered in terms of where PostgreSQL can improve in to compete with the sorts of workloads that generally favor Redis.

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.

The history of import substitution did not start last year after well-known events. Its launch dates back to 2014. It was from this year that state and near-state companies began to think of switching to the so called "recommended software". One of these companies approached us back in 2020 with a project to move from Oracle to PostgreSQL. This project was designed to solve the accumulated architectural problems (imperfect storage system for telemetry data, the DBMS itself worked inside a virtual machine), and optimize the use of disk space (make space in the main storage, debug saving archived data, ensure correct backup). Since the customer's system should have worked uninterrupted 24/7, it was necessary to switch from one DBMS to another "seamlessly" without downtime, with simultaneous operation of both to ensure step-by-step translation of subsystems and the ability to control the correctness of the data. And, of course, the work had to be completed as quickly as possible.

In the report we will discuss how we managed to solve this case.