PGConf.Russia 2018

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.

Pluggable storages is hot subject in PostgreSQL development. The period of heated debates about whether we need them is over. Skepticism about pluggable storages, based on concern that they may be source of inconsistent behavior, was weakened after criticism of PostgreSQL MVCC implementation from Uber side. It became widely understandable that pluggable storages are needed at least for an alternative MVCC implementation. And that is one of way-points for pluggable storages interface design.

At the moment, work on pluggable storages is in the practical stage. There is a thread is pgsql-hackers where few people are developing patchset and several people are doing review.

This talk will cover following subjects:

• overview of pluggable storages interface;
• changes in PostgreSQL core required to implement this interface;
• current and potential implementations of pluggable storages including heap with undo-log and in-memory OLTP engine;
• current state of patchset and prospective of its commit
• further development of interface allowing more possibilities in pluggable storages (columnar, index-organized, LSM and so on).

Last year we announced Skala-SR / Postgres Pro database machine with a hardware and software support for remote direct memory access (RDMA) as a key feature. The first appliances have been already installed at customer sites, and even with the first version customers got some constructions, that were impossible without RDMA and CPU offload (got with Mellanox networking). However, capabilities of this equipment are much wider, and this talk is dedicated to current works and prospective developments for this topic.

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.