PGConf.Russia 2018

PostgreSQL looks very competitive with other mainstream databases on OLTP workload (execution of large number of simple queries). But on OLAP queries, requiring processing of larger volumes of data, DBMS-es oriented on analytic queries processing can provide an order of magnitude better speed. The following factors limit Postgres OLAP performance:

• Unpacking tuple overhead (tuple_deform)
• Interpretation overhead (Postgres executor has to interpret query execution plan)
• Abstraction penalty (support of abstract data types)
• Pull model overhead (operators are pulling tuples from heap page one-by-one, resulting numerous repeated accesses to the page)
• MVCC overhead (extra per-tuple storage + visibility check cost)

All this issues can be solved using vectorized executor, which proceed bulk of values at once. In this presentation I will show how vector operations can be implemented in Postgres as standard Postgres extension, not affecting Postgres core. The approach is based on introducing special types: tile types, which can be used instead of normal (scalar) types and implement vector operations. Postgres extension mechanism, such as UDT (user-defined type), FDW (foreign data wrappers), executor hooks are used to let users work with vectorized tables almost in the same way as with normal tables. But more than 10 times faster because of vector operations.

This talk will compare architectural decisions that are made in PostgreSQL vs. ORACLE and will provide a closer look at the following components of both DBMSs:

1. The ins and outs of the working DBMS, its processes and their function
2. Structures that DBMS manages
3. Durability mechanics of each respective DBMS
4. MVCC design and database restoration options
5. Storage of data on the physical media

Each architectural decision will be evaluated based on the experience with DBMS of choice, ease of administration and future improvement possibilities.

This review will demonstrate the notable strengths of PostgreSQL as an open-source DBMS compared to the commercial solution in many cases.

This talk will be interesting for:

• PostgreSQL users, as it will allow to take a closer look into an alternative DBMS;
• PostgreSQL administrators, that will be able to see huge administration possibilities that ORACLE offers and that could be adopted in PostgreSQL;
• PostgreSQL hackers, as Postgres is being actively developed and this talk will review new development segments;
• Those who are willing to migrate from ORACLE (or any other commercial DBMS) into an open-source project, as this talk will show the features of PostgreSQL compared to the commercial product.

Rambler & Co is a lot of publications, services and projects. Appear new and grow existing. This environment requires a reliable, fault-tolerant, scalable, automated system.

I'll tell you about the structure of our PostgreSQL SaaS, what tools and technologies we use. Quorum of 3 Data Centers. A single entry point for clients based on dynamic routing. Emergency switching of the primary server. Transparent scaling for reading. Create a replica without load on the cluster. Transparent transfer of PostgreSQL cluster to other servers. Update dev environment from prod for development. Backup with compression and the use of multiple CPUs on the side of the database, the restoration of one database from basebackup. Monitoring of sql queries.