PGConf.Online 2021

psycopg3: как Питон полюбил Постгрес

Python is today one of the most used programming languages in the world: simple to learn and to use and ready to interface to any known service and protocol. psycopg2 is the most used PostgreSQL driver for Python: it offers good performance and makes the communication between the language and the database as smooth as possible.

Python has evolved enormously in the past years and its first-class support for async programming is changing the way new programs are written. PostgreSQL has evolved too: a new generation of the driver is needed to make the most of all the features it has to offer.

psycopg3 is the new generation of the most used Python-PostgreSQL adapter: it offers a familiar interface and smooth upgrade path, but behind the scenes it is engineered to obtain the best performance from the database and the language: async programming, prepared statements, binary parameters.

psycopg3 is also experimenting with innovative JSONB support and query pipelining! Come and discover the forefront of the research between your most loved language and database!

Управление PostgreSQL с помощью Ansible

Ansible is an open-source configuration management and deployment tool, which can be used to manage servers and software installations. This talk will briefly cover Ansible itself, and then explain how Ansible is used to install and configure PostgreSQL on a server. Examples will round up the talk.

Продвинутые техники pg_upgrade

When it comes to performing postgres upgrades between major versions the command line utility pg_upgrade is the most popular method used today. But as with all things wonderful, there are caveats. One of the more critical issues is what to do when there's a failure. The purpose of this talk is to present those little covered "tricks" of the trade that allows one to improve and enhance the upgrade experience.

We'll start out by reviewing its basic mode operation. We'll then learn what makes it so darn fast when upgrading multi-terabyte datacluster in a matter of minutes. Finally, you will be introduced to those feared failures and how to deal with them with confidence and certainty.

Herein is a summary of the topics in this talk:

• How pg_upgrade works: the big picture
• About pgupgrade (comand line invocation)
  • arguments and options

• The steps performing an upgrade
• About the replication ROLE
  • with replication
  • with login

• Upgrade options: COPY vs Hard Link
• Post-upgrade
  • about performance
  • aboutanalyze
  • repack
  • reindexing

• When something goes wrong at the point of no return (or is it?)
• Upgrading the REPLICA
  • default method: pg_basebackup
  • the cool method:
    • - leveraging rsync
    caveat; about vacuum wraparound

JSONB изнутри

JSONB is a popular data type in Postgres, and there is demand from users to improve its performance. In particular, we want to optimize a typical pattern of using jsonb as a storage for relatively short metadata and big blobs, which is currently highly inefficient. We will discuss several approaches to jsonb improvement and present the results of our experiments.