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.

Storing binary data in database tables is sometimes a good solution for a particular project. But sometimes, due to changes in conditions or insufficient consideration of decisions, such storage is becoming a real nightmare. If there is an understanding of how and where to place these data, the transition to the new solutions are often very hard, often require modification in the application code and downtime the system for migration. The presentation is a particular solution of such problems. Our extension allows to move binary data from database to the storage Ceph and not only. And does it seamless for the applications.

Apart from its main purpose of scheduling tasks, pgpro_scheduler can also deal with chained transactions. It can be used in various scenarios of asynchronous data processing.

This tutorial demonstrates pgpro_scheduler features that ensure secure processing of chained transactions. We'll be using cryptocurrency transactions as an example.

pgpro_scheduler is included into Postgres Pro Enterprise as an extension.

It’s 3am. Your phone rings. PostgreSQL is down, you need to promote a replica to master. Why the h**l isn’t this automatic?

If you thought of this before, you want automatic High Availability (HA). Don’t miss this talk! We will enter the world of Modern PostgreSQL HA.

Good news, there are several new, “modern” solutions for PostgreSQL HA. However, there are several solutions and it's not easy to pick one. Most require non-trivial setups, and there are many small caveats about HA like how to provide entry points to the application, HA correctness, HA vs. read scaling, external dependencies, interaction with cloud environments, and so forth.

Join this talk to master PostgreSQL HA and how to deploy it on current times.