PGConf.Russia 2018

If you honestly evaluate most of our business applications, you will see that they first collect and import the data into a database and then send the same data in the opposite direction.

What if we don't build an ORM wall between the application and the database, but try using the symbiosis of their strong points and special features instead?

I will tell you how we use PostgreSQL and Clojure for building data-intensive medical applications. We will cover the following topics:

• functional relational programming
• jsonb for modeling complex data domains
• functional indexes and json-knife extension for jsonb search
• graphql implementation on PostgreSQL
• logical replication for building reactive integrations
• asynchronous JDBC-free connector to PostgreSQL on netty

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.

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.

In the pitch I will talk about the most important nuances of deployment and operations of the distributed analytical open-source database based on PostgreSQL - Greenplum. I will analyze the typical mistakes in its use, give the best practices and warn about bottlenecks.