PgConf.Russia 2019

DBMS inside container is a nightmare for DBA. I will describe how we use containerized Postgres in Juno. What problems we faced with and how did we solve them.

As is often seen in OLAP and batch processing workloads, the more complex a query (containing many joins, filters, aggregates), the more there is a possibility of row count estimation errors, which leads to planner choosing an inefficient execution plan.

To address that problem, I developed a tool called pg_plan_advsr as a PostgreSQL extension, which corrects the estimation errors by repeatedly feeding back the information collected during query execution to the planner.

The tool has three features:

1. Automatic plan tuning by repeatedly feeding execution information to planner
2. Preserve all plans generated during plan tuning in a history table
3. Create and store optimizer hints to be able to reproduce plans generated during tuning process

I verified the effectiveness of pg_plan_advsr by enabling it when running the join order benchmark (JOB) against PG 10.4 and observed its execution time shortening to 50% of the original. Therefore, it is useful for user who would like to do plan tuning for OLAP and batch processing.

I will talk about the following things in this presentation:

• Principles behind pg_plan_advsr and its architecture
• Detailed information about the measurements done with JOB
• Possible future enhancements
• Using aqo and pg_plan_advsr together (experimental)

During the report, I would like to share the experience of implementing BlockChain in a real business task based on 1C+PostgreSQL. Where did this task come from? From whom do we protect data with the help of technology? How to get a chain integrity report of tens of millions of records in seconds?

IIn the world of programming, the creation of source code for databases "procedural extensions" is something solitary. Most DBMS offers procedural languages and "stored procedures" to create procedural extensions. In Postgres the number of supported procedural languages has already exceeded a dozen.

Traditionally, stored procedures have many applications: it is difficult to resist the attraction to perform a data operation directly in the storage, especially in the Enterprise development. This approach quickly leads to the spreading of the business logic and dramatically increases the cost of support and development of the system as a whole.

The life cycle of stored procedures makes it difficult to use standard Change Management tools and practices. It is necessary to adapt the operations upon the stored procedures to the standards of Change Management, yet staying within the comfortable development practices.

We'll look at the typical tasks of the procedural extensions development and discuss the solutions we are implementing in the Xobot IDE.