PGConf.Russia 2018

It is often required to asynchronously perform several transactions in a strictly defined sequence, not just a single transaction. There are several ways to achieve this, and one of the solutions available is the pgpro_scheduler module.

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.

In the last six months I have been working with a massive OLAP environment with 20TB shards, spanning around 400TB of data. Come to listen to how we make it all work, the challenges, and the skills involved. This talk has very little in common with the 10TB and Beyond talk because the data environments are very different.

We will cover analytics performance, data alignment, reasons for building extensions in C, and moving data around between servers in multiple data centers.

Jsonb is a popular data type in PostgreSQL, it provides the web developers an ability to work with ubiquitous json inside the database and use all the power of proven relational database. Fast querying of jsonb data is a challenge for database and PostgreSQL provides several options for indexing jsonb. We present the new way of efficient indexing of jsonb, based on improvement of indexing infrastructure.

It's known, that json is a greedy data type, it may contains many auxiliary data not interesting for searching and that affects the size of index. Partial index will not helps, since it filters the rows before indexing, while we are interested in extracting of parts of jsonb. Functional indexes on specific keys could introduce too big overhead. We present an improvement of indexing infrastructure, which allows to control the index behaviour by passing parameters to operator class at index creation. For example, to index a user-defined subset of jsonb it is possible to pass to operator class the powerful path expression (either jsonpath of upcoming sql/json or jspath from jsquery extension), which can be used to extract the parts of jsonb tree. That makes index more effective and reduces the overhead of its maintaining.

Another use of parameterized operator classes is to allow a user to specify parameters instead of hard coding them, for example, the GiST signature size is currently hard coded inside the implementations of several opclasses (tsvector, hstore, intarray, pg_trgm, ltree), while it is natural to use different signature length for different data to have optimal size of index and its performance.

Full text search on parts of document can be improved by passing labels to the operator class and letting him index only specified parts of document, that allow to avoid currently used recheck of the rows returned by the index.