PGConf.Russia 2018

When one faces the issues with PostgreSQL, the main suspicion falls on vacuum. Experience of Data Egret team proves how many DBAs are attacking this rake. While there are tons of information, documentation and discussions on vacuum itself, the topic is still associated with a lot of myths, tales, horror stories and misconceptions. In my talk I will try to reveal the key points concerning the inner structure of vacuum, basic approaches to its adjustment and tuning, performance monitoring, and so on.

Patroni is a Python application to create high-availability PostgreSQL clusters based on the streaming replication. It is used by Red Hat, IBM Compose, Zalando and many other companies. This tutorial will highlight Patroni architecture, provide attendees with hands-on experience of configuring high-availability PostgreSQL clusters with Patroni, describe how to take advantage of numerous additional features and give an opportunity to learn more about common mistakes related to running Patroni and its troubleshooting.

In order to take most out of the Patroni tutorial one needs a laptop with git, vagrant and virtual box installed.

Vagrant can be obtained from https://www.vagrantup.com Virtualbox is at https://www.vagrantup.com

Alternatively, one can install your Linux distribution packages (or use homebrew on Mac).

Once Vagrant and Virtualbox are installed one can run the Patroni VM by issuing the following commands:

$ git clone https://github.com/alexeyklyukin/patroni-training
$ cd patroni-training
$ vagrant up

When the setup concludes Patroni box can be accessed via ssh using vagrant ssh command.

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.

"And telling GIN from SP-GIST was quite beyond his wit, we found", said the classic. Can you? This masterclass is about not-so-often used index types (compared to conventional B-tree) which however can do a great job for you. We will look into internal mechanics of these indexes and discuss cases where they can be successfully applied. Also we will talk about some peculiarities of PostgreSQL index access. To spend time efficiently, listeners are required to have basic knowledge of PostgreSQL and should be used to read plans of simple queries.

Materials of the master class

• Slides
• Demo 1
• Demo 2
• Demo 3

Backup copy of the database with demo data can be downloaded here:

• Recovery with pg_restore (338 MB)