PGConf.Russia 2018

The speaker will share his experience of transferring high-loaded game projects that were originally developed to work with MySQL into PostgreSQL. Problems that had been expected and ones that were actually faced. Tricky bugs that were found in production servers after the migration. Solutions that were used to deal with problems. PostgreSQL features that were very useful and desirable ones that were missing.

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

Avito is the biggest classified site of Russia, and the third largest classified site in the world (after Craigslist of USA and 58.com of China). In Avito, ads are stored in PostgreSQL databases. At the same time, for many years already the logical replication is actively used. With its help, the following issues are successfully solved: the growth of data volume and growth of number of requests to it, the scaling and distribution of the load, the delivery of data to the DWH and the search subsystems, inter-base and internetwork data synchronization etc. But nothing happens "for free" - at the output we have a complex distributed system. Hardware failures can happen - it is natural - you need to be always ready for it. There is plenty of samples of logical replication configuration and lots of success stories about using it. But with all this documentation there is nothing about samples of the recovery after crashes and data corruptions, moreover there are no ready-made tools for it. Over the years of constantly using PgQ replication, we have gained extensive experience, rethought a lot, implemented our own add-ins and extensions to restore and synchronize data after crashes in distributed data processing systems. In this report, we would like to show how our experience can be shifted to a new logical replication subsystem in 10th version of PostgreSQL. In the current implementation, these are only non-trivial solutions - there is a number of issues for the community, that come down to implementing simple recovery mechanisms - as simple as configuring the replication in 10th version.

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.