PgConf.Russia 2019

Sometimes problems arise during Postgres operation, and the faster they are identified and resolved, the happier users eventually will be. pgCenter is a set of CLI powerful utilities for troubleshooting in the "here and now" mode. In the talk I will show how to use pgCenter for efficient troubleshooting, the directions in which to search, and how to respond to certain problems, in particular how to:

• check if Postgres is in the normal state;
• identify promptly the faulty clients and stop them;
• reveal too heavy queries;
• and other tips and tricks of pgCenter.

As we all know, PostgreSQL is a classic vertically scalable database for OLTP loads. In parallel with PostgreSQL for many years there is its alternative horizontal-scalable MPP version of PostgreSQL, that is called Greenplum, sharpened for big data and OLAP workload. In my pitch I will show the internal architecture of Greenplum (distributed transactions, data sharding, partitioning with hybrid storage in external systems, column storage engines with compression, and much more), a comparison with the internal structure of PostgreSQL and the application areas of each solution are shown.

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)

Sometimes there is a great desire to return the database to the past, for a day or two or more days. The reasons are diverse, but most often one is to see what has changed. Or to see if the application behaved incorrectly after the update. Or it was just a command from the boss. The classic way everyone knows is to keep full backups and sets of WAL-logs to be able to recover to an arbitrary moment. This method is a real headache for DBAs/administrators, and it will not work quickly. Sure, there are some ways to optimize this process, but downtime is inevitable. PostgresPro offers a new way — database snapshots and the ability to return to them.