PGConf.Online 2021

Консенсус, Postgres, Мультимастер

Postgres Pro Multimaster is Postgres extension (and a set of core patches) providing high availability (HA) with strong consistency and read scalability. It forms symmetric shared-nothing cluster synchronously replicating the data and automatically performing disaster recovery. During the last year we've put significant efforts ensuring and proving that consistency is preserved in all scenarios. The new version, which will be released as part of Postgres Pro Enterprise 13 uses Paxos algorithm for determining transaction outcome and custom protocol governing the recovery process; we used TLA+ model checker to verify its correctness. I'll tell how these things work and why in some cases multimaster may be an attractive alternative to the traditional streaming replication based HA deployments.

multimaster is now open source, available at https://github.com/postgrespro/mmts

To make the talk less narrow specialized and more appealing to the wide audience, in the first part I will shed some light on how generally modern DBMSs (mostly so-called NewSQL) handle fault tolerance. In particular,

• what is a strongly consistent DBMS and the associated overhead;
• what is distributed consensus, Paxos, Raft;
• how they help here;

I won't do an attempt to explain any algorithms line-by-line; it would be hardly useful given the time frames and there is a lot of literature available anyway. The goal is rather to waymark the field and get you a bit comfortable there.

Postgres и искусственный интеллект в современном мире

Artificial intelligence, machine learning, and deep learning are intertwined capabilities that attempt to solve problems that defy traditional computational solutions — problems include fraud detection, voice recognition, and search result recommendations. While they defy simple computation, they are computationally expensive, involving computation of perhaps millions of probabilities and weights. While these computations can be done outside of the database, there are specific advantages of doing machine learning inside the database, close to where the data is stored. This presentation explains how to do machine learning inside the Postgres database.

Управление данными подвижных объектов с MobilityDB

MobilityDB is a moving object database extension to PostgreSQL and PostGIS. It has types and functions for storing an querying geospatial trajectories, as first class citizens. The main type is called tgeompoint (temporal geometry point). It represents a complete movement track of a geometry point, such as a car, a bird, or a person. The function speed(tgeompoint) computes the time varying speed of the object, as a tfloat (temporal float). Similar to these examples, MobilityDB has 6 temporal types, and over 300 functions. As such, it is a function-rich platform for Mobility Data Management.

In this tutorial you will:

• learn about moving object databases
• write MobilityDB SQL queries and explore a database of geospatial trajectories
• walk through the different type, indexes, and functions of MobilityDB.

Повреждение данных: как его избежать, обнаружить и обеспечить восстановление

PostgreSQL databases can become corrupted for a variety of reasons, including hardware failure, software failure, and user error. In this talk, I’ll talk about some of my experiences with database corruption. In particular, I’ll mention some of the things which seem to be common causes of database corruption, such as procedural errors taking or restoring backups; some of the ways that database corruption most often manifests when it does occur, such as errors indicating inconsistencies between a table and its indexes or a table and its toast table; and a little bit about techniques that I have seen used to repair databases or recover from corruption, including some experiences with pg_resetxlog. This talk will be based mostly on my experiences working with EnterpriseDB customers; I hope that it will be useful to hackers from the point of view of thinking about possible improvements to PostgreSQL, and to end users from the point of view of helping them avoid, diagnose, and cope with corruption.