PGConf.Russia 2025

A brief presentation on various extensions that can assist with optimization.

t’s well known that pg_upgrade is the go-to tool for fast PostgreSQL upgrades. However, even with this tool, there are cases where the upgrade process takes far longer than expected.

In our case, upgrading a PostgreSQL database with 350,000 tables meant either waiting 3.5 hours or finding a better approach. By digging into the pg_upgrade source code, we discovered a way to speed up the process significantly. In this talk, we’ll share how we did it.

In this talk, we’ll:

• Share our experience of reworking the ECM system and platform that was originally built on C.Net + MS SQL for Windows to run on Linux + Postgres, while maintaining the option to install and operate on Windows + MS.

• Discuss migrating clients to new platforms with data migration.

• Share insights from operating the system with a high-load client (PG servers with 192 cores and 3 TB of RAM).

The focus of the presentation is on the challenges of building an application solution on PostgreSQL, specifically a high-load analytical data warehouse. Using the case of the company EVRAZ, we will demonstrate the impact of applying the Data Vault methodology on PostgreSQL and Greenplum databases for developing a unified production performance system.

We will discuss the difficulties and solutions, showcasing query plans for tasks such as updating directories and handling large objects, along with optimization examples. We will highlight coding nuances, problems related to populating the model, and issues with querying from the model.

This session will be useful for those planning to use or already facing challenges with the Data Vault methodology and performance issues in DWH on the open-source stack. We will compare technical implementation options for the Business Vault model layer, considering the specifics of PostgreSQL and Greenplum.

We will also cover 5 real problems that arise when operating a DWH and their solutions:

1. Transferring Business Vault object assembly logic from PostgreSQL to Greenplum.
2. Slower ETL performance when building the current state of Business Vault in PostgreSQL.
3. Slower Data Lineage construction in PostgreSQL and Greenplum.
4. Slow satellite queries in Greenplum.
5. Slow queries with "IN" or "OR" in the Business Vault layer.