PGConf.Russia 2023

The practice of transferring structure, logic and data from Oracle DBMS to PostgresPro DBMS. Features and main difficulties of migration. Advantages of PostgresPro in terms of porting logic.

There is set of Ansible roles for any Postgres configuration: a standalone instance; a streaming replica; patroni+etcd; stolon+etcd; patroni+consul; stolon+consul. In this talk, we will discuss why using this set of Ansible roles is better than other solutions.

Historically PostgreSQL was intended to transactional OLTP workload. This thesis is confirmed by row-based kind of storage and impossibility (or some complication) in building distributed engine of query execution based on MPP principles. However, due to extensibility of PostgreSQL core (first of all, by using of pluggable access methods) and tolerant license policy similar to BSD there were appeared new different forks and extensions allowing effective processing of big data in analytical manner.

In current talk I'm going to review the PostgreSQL fork called Greenplum and Citus and TimescaleDB extensions from system developer's perspective by comparing their common analytical engine features: column storage, data compression, distributed query execution and so on. The results of such overview will be helpful to database architects seeking PostgreSQL-based DBMS for analytical workload.

-----------------------------------------------------------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)