PgConf.Russia 2019

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)

While doing development of one of our project we were asked to build HA database using Postgres, geographically distributed.

First our choice was obvious, we started to work with big 3 cloud providers, but soon it was quite understand that everything costs big enough for us. Also there were a bunch of incompatibilities with unsupported extensions as well as londiste replication we were heavily used.

I will talk about why we chose patroni, what types of problem we faced with and patroni's special features can dramatically simplify deploy and everyday usage.

Our company has developed a software product Live Universal Interface (LUI) is a tool to quickly create and modify standardized on-screen forms to WEB browsers without compiling code, know just enough SQL. LUI is aimed at B2B, B2G, G2C and B2C segments and intended for use in billing systems, financial management, accounting and control of production, where you must decide first and foremost functional tasks, rather than demonstrate unnecessary graphics elements.

Collective elaboration is provided by storing all elements of program code in the total database, which can be located on the corporate server or in the "cloud" on a third-party site.

Magic tricks followed by exposure (1C+PG):

• Focus number one. How to convince the accounting department to buy a new server.
• Focus number two. How to show that MS SQL is faster than PostgreSQL.
• Focus number three. How to show that PostgreSQL is faster than MS SQL Server.