PgConf.Russia 2019

It's so good when database behaves predictable. When the performance is lacking, you just add CPU cores, terabytes of RAM and millions of IOPS, and everything becomes good again. But it's rather unpleasant, when server have plenty of free resources, while database is still running slow. And it's especially sad if stress testing detects no problems, while real life workload of the same volume makes your database hang.

In this talk I will consider bottlenecks of PostgreSQL, which we met in our practice, and which causes sad behavior described above. I'll also explain what can be done at user level in order to evade these bottlenecks, and what developers are planning to do in order to eliminate those bottlenecks. I'm also planning give some recipes of stress testing, which could have to evade surprises in production.

JSONB in PostgreSQL has interesting properties for design and development of business systems with heavy domain, helping to fight against complexity and variability. We will discuss the trade-offs of the JSONB approach. Using an open source project for Health IT - fhirbase we will:

• load synthetic medical data into PostgreSQL
• learn how to search and index this data (gin, jsquery, json-knife)
• use JSON aggregation to build complex queries (GraphQL)
• see how this data can be modified and validated
• discuss architecture consequences of JSONB usage

Technical requirements:

• docker
• docker-compose

Detailed prerequisites

In my talk I'll tell you about practices of working with Postgres in the Go-services. I’ll describe general advantages and disadvantages of the basic tools that are commonly used when working with Postgres using Go. Of course, we will touch on the nuances that need to be taken into account when your services are running inside the Kubernetes. I will also talk about Avito’s experience in providing a database of product’s developers. This presentation will be of interest to developers who want to avoid problems when working with Postgres, and will be useful to DBA who want to know what difficulties customers face in their database.

My presentation is about writing extensions in Postgres. I have written pg_threads that implements simplified POSIX thread API inside Postgres database. It adds a new powerful abstraction giving database developers new opportunities for writing parallel code thus taking advantage of multicore CPUs. There is an extra API for transactional and non-transactional IPC between threads. I also have an example application that takes advantage of this new API that scales linearly even across 2 nodes. The presentation is with live working demo using vagrant project with 2 VMs running Ubuntu and 2 Postgres 11 databases.