PgConf.Russia 2019

Non-standard 1C cluster

• Migration of the standard 1C cluster to MPI environment - "machine to machine migration of services".
• PostgreSQL migration to GPU powered machine.

CTE Queries Usage for Business Logic

Wide usage of Common Table Expression queries considered as a core paradigm for implementing the Business Logic for high loaded web applications development based on PostgreSQL functions.

Enterprise-level approach to PostgreSQL tuning: database experiments

Shared_buffers = 25% – is it too much or not enough? Or it's the right value?

How can we ensure that this – pretty much outdated – recommendation suit well our needs?

It is time to start apply enterprise-level approach to tuning postgresql.conf. Not using various blind auto-tuners or advices from old articles and blog posts, but based on the following two aspects:

1. comprehensive database experiments, conducted in automated fashion, repeated multiple times in conditions as close to production as possible, and
2. deep understanding of DBMS and OS internals.

Using Nancy CLI (https://gitlab.com/postgres.ai/nancy) we will consider a concrete example: infamous shared_buffers, under various circumstances, in various projects. We will try to figure out, how to optimize this settings for given infrastructure, database, and workload.

Less known but worthwile PostgreSQL extensions

Postgres is known for it extensibility, which made it the universal database, that means it can meet the requirements of practically any project. Many extensions are well-known and widely used, for example, PostGIS extension - de-facto standard of open source GIS, hstore - an extension for storing and manipulation of arbitrary key/value pairs. I will talk about less known but useful PostgreSQL extensions, which provides a new functionaliy and/or improve the performance of PostgreSQL.

PostgreSQL was designed to be extensible, it provides an API to application developers to extend PostgreSQL functionality and/or improve the performance for specific data and workloads. It is important that there is no need of having expertise of core developers, and these new functionality could be added online without restarting of database server. Application developer can create various database objects, such as functions, data types, operators, indexes, and even new access methods.

I will present my choice of two extensions out of hundreds:

vops - greatly improves the performance of Postgres for OLAP queries using vector operations, pg_variables - provides session variables for storing scalars and relations, useful for generating reports on read-only replicas.