PGConf.Russia 2023

My presentation "Explaining the Postgres Query Optimizer" covers the details of query optimization, optimizer statistics, joins, and indexes. This talk covers 40 other operations the optimizer can choose to handle complex queries, large data sets, and to enhance performance. These include merge append, gather, memoize, and hash aggregate. It explains their purpose and shows queries that can generate these operations.

This is a new talk; draft slides are at https://momjian.us/main/writings/pgsql/beyond.pdf

Since PostgreSQL 9.5 logical replication has replication origin infrastructure for replication progress tracking. Now 16th version is coming with feature which made bi-directional replication setups possible using said infrastructure. In earliest reviews it got a nickname of "native multimaster" for cluster, interconnected with said replication, bearing some resemblance to PostgresPro's Multimaster. We're going to figure out degree of it and explain insides of them. Talk expects you to have a basic knowledge of WAL insides and it's role in PostgreSQL functioning.

-----------------------------------------------------------QUERY PLAN--------------------------------------------------------------
Hash Join
  Hash Cond: (Subject = GEQO)
  -> Hash Join
        Hash Cond: (**Optimizer task = choose the best query execution plan**)
        -> Seq Scan on **The number of potential plans grows exponentially as the number of tables in a query increases**
        -> Hash
              -> Seq Scan on **PostgreSQL solves this problem by using the genetic optimizer (GEQO)**
  -> Hash
        -> Seq Scan on **Topics of the report:**
              Filter: (**(What is GEQO)** AND **(Pros and cons)** AND **(How it works)**)
(10 rows)

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.