PGConf.Russia 2025

Diagnostic Methods: Analyzing the Issue of Long CREATE DATABASE Execution

This talk covers two cases of long CREATE DATABASE execution in PostgreSQL 11, which, in a Corosync/Pacemaker cluster configuration, caused node failover due to a monitoring timeout from the clustering software. The first case is related to a large shared buffers size, while the second involves the use of the WITH OWNER option during database creation and a large pg_shdepend system table (a feature of using the jBPM software).

In this talk, we will discuss what character encodings and collations are. I will explain the issues that can arise from sorting (COLLATION) in databases and show how these problems can be addressed using the COLLATION PROVIDER = ICU feature in PostgreSQL.

This talk explores the challenges we encountered — and solved — while migrating massive databases from Oracle to PostgreSQL.

One of the most complex aspects of this process was rebuilding foreign keys (FKs). To overcome these challenges, we had to dive deep into the internal workings of FK creation and validation.

Key topics include:

Does ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY have an execution plan?

Can an ordinary user influence this process?

What locks are applied during FK creation and validation?

Also, we’ll introduce a first-principles method for investigating performance issues, applying it to a real-world FK creation bottleneck.

Although we often don't like to talk about it, database administration carries with it a large amount of stress. This is particularly true when we have to take on late night support duties. In this talk you will learn: 1. The basics of the physiology of stress. This is not a physiology lecture, but as engineers we need enough to build a mental model. 2. What kinds of mistakes we make when under stress. 3. How to measure stress in your own life and recognize it in others. 4. What tools are helpful to manage short- and long-term stressors. Managing short-term stress is important in avoiding becoming overwhelmed when problems occur, but managing longer-term stressors is just as important in avoiding problems in the first place.