PgConf.Russia 2019

Python may not be the fastest programming language on the CPU, but its fast and easy development saves a lot of costs between the keyboard and the chair. Since database clients spend most of their time waiting for a response from the database server, Python's asynchronous functionality available in the recent versions (3.5+) may help to optimize the application's runtime considerably by working on something else while server's response is being prepared. The asynchronous interface between Python and PostgreSQL is called "asyncpg". In the workshop we'll explore this library and write a short application using some of its useful features.

Wide usage of Common Table Expression queries considered as a core paradigm for implementing the Business Logic for high loaded web applications development based on PostgreSQL functions.

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.

One of the most soft after features of Postgres v10 is logical replication. In this presentation we will cover what Logical Replication is, how it compares to Binary (Streaming Replication), how Logical Replication works, configuring Logical Replication, Logical Replication limitations, gotchas, security and management. We will also discuss potential deployed architectures with Logical and Binary Replication and some neat features of the underlying technology.

At the end of this presentation an attendee with a reasonable understanding of how to manage Postgres will be able to configure Logical replication for use.