PGConf.Online 2021

High-Performance PostgreSQL

PostgreSQL is one of the leading open-source databases. Out of the box, the default PostgreSQL configuration is not tuned for any particular workload. The default configuration is designed in such a way that PostgreSQL can run on any system using minimum resources. Consequently, a default installation of PostgreSQL does not give optimum performance on the high-performance machine because it is set up to use all available resources. PostgreSQL provides mechanisms that allow you to tune your database according to your workload and machine specification. Outside of PostgreSQL, though, we can tune the Linux kernel to allow the database load to work optimally. In this talk, we will learn how to tune some of the PostgreSQL’s parameters, and we will see the effect of that tuning, but we will focus on demonstrating how to tune Linux for better Postgres performance. As there are so many Linux kernel parameters that can be tuned to improve the performance of PostgreSQL, I will also share the results of benchmarks obtained when tuning some of the Linux parameters.

Speed up your fast text search queries with RUM index

Fast text search queries can be made even faster with indexing on lexemes inside compound records of tsvector format. RUM index is an open-source PostgreSQL extension. It represents a big improvement of GIN index and it can index lexemes with additional information e.g. tsvector lexeme weight-mark. So it can support tsvector capabilities more.

Until recently it was needed to recheck results of weight-containing queries by table. My modification (2020) is to make the processing of this kind of queries index-only and therefore much faster.

Also, I will describe and provide benchmarks for different usage cases for fast text search. We'll see how RUM index can improve the performance and compare it with PostgreSQL internal GIN index.

Shardman - the native approach to sharding in PostgreSQL

The amount of data that is handled today by Enterprises and Web companies is constantly growing. At the same time, it becomes increasingly difficult to have and synchronize several copies of data in different systems. As a result there is a demand to work with large amounts of data directly in a transactional DBMS. This requirement is often imposed by the logic of applications that need real-time results. In this talk we will consider what a universal distributed transactional DBMS can be. We will analyze such aspects as the types of load and their prioritization, dynamic resource allocation and the level of consistency. What tools in PostgreSQL can be used to build such system, what we have already done and what is still missing.

Inside JSONB

JSONB is a popular data type in Postgres, and there is demand from users to improve its performance. In particular, we want to optimize a typical pattern of using jsonb as a storage for relatively short metadata and big blobs, which is currently highly inefficient. We will discuss several approaches to jsonb improvement and present the results of our experiments.