Practical cases for query optimization in PostgreSQL

In my opinion, every DBA has to deal with "heavy" queries. The question is whether we can do something at all to speed up such query. Is it possible to optimize such queries, or maybe it doesn't make sense? For example, rewriting such query may take much longer than getting any performance gain from this rewritten query. During this talk, I'll tackle several approaches to speeding up queries, and demonstrate some practical examples of optimizations that I use at work.

Use cases for the latest pg_probackup version

The developers of pg_probackup will explain how to work with a variety of pg_probackup commands. They will also guide you through making FULL and incremental backups in DELTA, PAGE, and PTRACK modes. Configurations of backup policies for different fields from a hundred of simple DBMSs to large e-commerce and fintech setups will also be demonstrated.

How we switched Oracle for PostgreSQL for a client, before it became mainstream

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.

RTT, RTO, RPO and synchronous replication

How does network latency affect performance when using synchronous replication? If a primary node fails, how long will our cluster not be reachable? How much data can be lost? Will there be any anomalies when reading from standby? We are designing our network architecture, give us a minimum RTT value for cluster solution! Many DBAs have to face these issues when designing and implementing cluster solutions. In this short talk we will tell how to quickly estimate RTT, RTO, RPO values, with the help of which Open Source tools one can test cluster solution and experimentally verify the design parameters, which metrics values can be used as a guide in design.