PgConf.Russia 2019

In my talk I'll tell you about practices of working with Postgres in the Go-services. I’ll describe general advantages and disadvantages of the basic tools that are commonly used when working with Postgres using Go. Of course, we will touch on the nuances that need to be taken into account when your services are running inside the Kubernetes. I will also talk about Avito’s experience in providing a database of product’s developers. This presentation will be of interest to developers who want to avoid problems when working with Postgres, and will be useful to DBA who want to know what difficulties customers face in their database.

Brendan Gregg’s USE (Utilization, Saturation, Errors) method for monitoring is quite known. There’s also Tom Wilkie’s RED (Rate, Errors, Durations) method, which is suggested to be better suited to monitor services than USE. I want to talk about how we employ these methodologies when we develop our Postgres monitoring in okmeter.io.

PostgreSQL 12 Feature Freeze is scheduled for April 2019, which didn't come yet. But general shapes of upcoming release are already visible. In this talk I'll consider patches already committed to PostgreSQL 12 as well as patches, which would be committed very likely. I'll talk with special passion about SQL/JSON, Merge, pluggable table access methods and zheap.

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)