PostgreSQL partitioning. Work In Progress

Native partitioning was introduced in PostgreSQL 10, and every new release comes with more features and optimizations to this area. Yet, there is still room for improvement.

This talk briefly compares the built-in PostgreSQL partitioning with third-party extensions (pg_pathman and pg_partman), to understand what we still don't have in the core. This talk also includes an overview of partitioning-related features that are currently being developed and aimed for PostgreSQL 14.

psycopg3: all the love between Python and PostgreSQL

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!

Deploying PostgreSQL Cluster with Query Load Balancing and Monitoring Capabilities on Kubernetes

Kubernetes is an open source container orchestration platform for automating deployment, scaling and management of application containers. Nowadays, more and more applications are being deployed in containers on Kubernetes.

There are several solutions that can help us to run a PostgreSQL cluster on Kubernetes. However, these solutions don't provide query load balancing capability. In this talk, I will show you how to combine PostgreSQL Operator with Pgpool-II to deploy a PostgreSQL cluster with query load balancing capability on Kubernetes.

Monitoring is a very important part in production environments. Although Kubernetes provides a basic way to monitor the status of a PostgreSQL cluster, this is not sufficient for managing a PostgreSQL cluster in production. An important improvement of Pgpool-II 4.2 release is the ability to output more useful statistics of the PostgreSQL cluster. In this talk, I will describe how to monitor and visualize the PostgreSQL cluster statistics in Prometheus for extensive cluster monitoring.

What's next in Logical Replication?

Logical replication has been there since 10.0 and with each release, it is getting better. This talk will start with the basic architecture of Logical replication in PostgreSQL and then cover the various ways in which it can be helpful to users.

One of the shortcomings of logical replication as compare to physical replication is that currently, it allows the transaction to be replicated only once it is committed. This can create a large apply lag on the subscriber side for long-running transactions. We will discuss the solution implemented for this problem for PostgreSQL.

We will also discuss the other major work being done in logical replication which is to allow the streaming of transactions at the prepare time. This will help us in implementing conflict-free logical replication. This can be used for scaling-out reads as well. Because of 2PC, we can ensure that on subscribers we have all the data committed on the master. Now, we can design a system where different nodes are owners of some set of tables and we can always get the data of those tables reliably from those nodes, and then one can have some external process that will route the reads accordingly.

In the end, this will cover the new enhancements, improvements related to Logical Replication in recent PostgreSQL releases.