PGConf.Russia 2018

Many of us know that it is MVCC that provides concurrency access to data in many relational databases that guarantee transactions consistency and isolation. But only deep understanding of the implementation of this mechanism in PostgreSQL allows us to better understand the processes in the database, to design the application logic and table structures to be the most effective in a high-load world. We'll take one of the processes in our product to understand how MVCC is implemented in PostgreSQL and we'll explain one of the peculiarity when seemingly unrelated activities can affect each other.

PostGIS is a spatial extension to PostgreSQL that enables spatial datatypes, access methods and a set of functions to perform geometric operations on them.

Typically PostGIS is used to select a small subset of a big static dataset. In this talk I'll cover issues that arise when working with big dynamic data flows, and ways to resolve them, on examples that we've met developing Juno ride sharing service backend.

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.