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).

This talk will likely be of interest to network providers who can enhance their cool features by adding something to the syntax to simplify administration, as well as anyone interested in machine learning. We will explore the SQL parsing mechanism, what, where, and how to modify it in order to introduce new syntax constructs. Additionally, I will demonstrate the capabilities of machine learning on tabular data, all within the database, using the new syntax.

Last year, we introduced built-in fault tolerance support in Postgres Pro Enterprise through BiHA. Our solution allows you to deploy a fault-tolerant Postgres cluster where, in the event of a failure of the primary node, a new primary node (leader) is automatically selected.

However, this brings up the issue of redirecting traffic to the new leader. This can be solved using our Proxima extension or an external TCP proxy server. Both solutions needed to periodically query the BiHA cluster to determine the primary node.

As an alternative, the latest version of BiHA introduced the ability to register custom functions that will be triggered by events such as leader change, node addition/removal, and others. We call this mechanism user callbacks. In this presentation, we’ll explain how the callbacks are implemented and discuss their usage.