PgConf.Russia 2019

Analysis of troublesome queries as a means of recurrent refactoring of 1C code

1. Principles of searching for troublesome queries in PostgreSQL.
2. Evaluation of hypothetical indexes and their impact on query plans.
3. The most common errors in 1C-programming.
4. Basic methods of code refactoring, taking into account the features of PostgreSQL.
5. Storing analytical information from the PostgreSQL log to assess the quality of refactoring

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.