Talks and tutorials

There's a common delusion that constraints should never be used as they affect performance in a negative way, interfere with regular work, and are, all in all, useless. The database is commonly perceived as just a storage without any logic. I'll explain why it isn't so and what this careless approach may lead to.

Slides

The report will talk about a new tool for migrating application systems from the Oracle environment to the Postgres environment. The tool is developed on the basis of the ora2pg tool (by Gill Darold) and the domestic LUI application development tool. Talks on LUI were given at past PGConfs in 2019 and 2020:

• https://pgconf.ru/en/2019/118109
• https://pgconf.ru/en/2020/262456

Artificial intelligence, machine learning, and deep learning are intertwined capabilities that attempt to solve problems that defy traditional computational solutions — problems include fraud detection, voice recognition, and search result recommendations. While they defy simple computation, they are computationally expensive, involving computation of perhaps millions of probabilities and weights. While these computations can be done outside of the database, there are specific advantages of doing machine learning inside the database, close to where the data is stored. This presentation explains how to do machine learning inside the Postgres database.

Slides

Backups are a critical part of any enterprise database solution but they are often done poorly or skipped altogether, which can lead to data loss in the event of hardware failure or some other disaster.

In this talk we'll cover database backup best practices and how to implement them with pgBackRest, including:

• WAL archiving and retention
• Backup frequency and retention
• How to meet recovery time/point objectives
• Configuration options
• Performance considerations

Our company's educational projects are intended to facilitate learning of PostgreSQL. Last year, we focused on courses for application developers: we updated DEV1 basic course and released a brand new DEV2 course. What has changed in training materials representation, how we see the further development of the courses and what else do we have besides the courses, whether the courses for DBAs will be updated and how this will affect the certification and that's what I will talk about.

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.

It's a well-known fact, that although the database performance is great, and each query is executed in milliseconds, the overall application response time may be slow, making the users wait for a response for an extended period of time. We know that the problem is not the database, but the way the application developers communicate with the database. Specifically, we are talking about ORMs - Object-Relational Mappers. Database developers hate them, but application developers love them because they allow developing applications without any knowledge of database internals. As a result, the system performance is often unacceptably slow.

The only way to change this behavior is to provide application developers with a tool, which is as easy to use, as an ORM, but which will allow escaping the common ORM pitfalls. That's why we developed NORM - No-ORM Framework. During this presentation, we will go over examples of code from https://github.com/hettie-d/NORM repo and learn how to build "transport objects" for efficient data transfer between applications and databases

The amount of data that is handled today by Enterprises and Web companies is constantly growing. At the same time, it becomes increasingly difficult to have and synchronize several copies of data in different systems. As a result there is a demand to work with large amounts of data directly in a transactional DBMS. This requirement is often imposed by the logic of applications that need real-time results. In this talk we will consider what a universal distributed transactional DBMS can be. We will analyze such aspects as the types of load and their prioritization, dynamic resource allocation and the level of consistency. What tools in PostgreSQL can be used to build such system, what we have already done and what is still missing.

Okko is one of the largest legal online cinemas in Russia. Our catalog contains 60 000 movies, cartoons and TV series. Since its starting, the service has been visited by over 20 million users. The monthly audience is 2.8 million people. All these figures speak of a reliable high-load service.

As DBA, I will mainly talk about databases (PostgreSQL, Cassandra, Redis) that are used in the company. We'll take a closer look at PostgreSQL on the topics of high loads, monitoring, optimization, backup and recovery.

MobilityDB is a moving object database extension to PostgreSQL and PostGIS. It has types and functions for storing an querying geospatial trajectories, as first class citizens. The main type is called tgeompoint (temporal geometry point). It represents a complete movement track of a geometry point, such as a car, a bird, or a person. The function speed(tgeompoint) computes the time varying speed of the object, as a tfloat (temporal float). Similar to these examples, MobilityDB has 6 temporal types, and over 300 functions. As such, it is a function-rich platform for Mobility Data Management.

In this tutorial you will:

• learn about moving object databases
• write MobilityDB SQL queries and explore a database of geospatial trajectories
• walk through the different type, indexes, and functions of MobilityDB.

PostgreSQL has a long history in DWH. Netezza, Redshift, and Greenplum have turned specific PostgreSQL releases into DWH solutions. Nowadays, with the trends in PostgreSQL towards performance improvements (better partitioning, better statistics, JIT Compilation, etc.) and advanced PostgreSQL extensions, like the Swarm64 Data Accelerator, you can create a forward-looking, no lock-in, versatile, and reliable DWH. This talk will cover the PostgreSQL and DWH trends and touch on key arguments for choosing open source PostgreSQL for DWH.

PostGIS is a Spatial Extension to PostgreSQL database. This talk will look like reading the changelog aloud with some comments and real-world usage examples from the core developer.

PostgreSQL relies on the system collation libraries, such glibc or ICU, for text ordering. One know caveat is that when the library change its sort order for a collation, any index created using the old order is likely to be corrupted when the new version of the library is installed.

In this talk, we'll see the improvements done in PostgreSQL 14 to keep track of the collation versions, detect and fix possible index corruption due to library upgrades and the work currently being done to further improve this area.

Postgres is able to build optimal query plans for most practical cases. However, sometimes, for objective reasons, for complex queries or because of open issues in the planner itself, it can make mistakes and produce a suboptimal plan. Because of this, the execution time of such a request can increase tenfold. If the query is executed frequently, then from time to time this query takes longer than it could, and the DBMS as a whole produces a lower TPS. If the planner is able to record his mistakes and take them into account in the subsequent planning of the same query, then this will improve the characteristics of the DBMS during its operation. We present the results of the development of a PostgreSQL DBMS extension that stores the query execution history and implements the planner recommendation mechanism. We show how knowledge about previously executed queries can improve the performance of subsequent ones.

High availability solutions have become extremely popular in the past few years. They play a critical role in building reliable systems based on affordable hardware. In this presentation, we will pay attention to some of the subtle aspects of the design and maintenance of such systems. In addition, the issues of capturing changes on a HA cluster will be addressed.

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!

This talk introduces GPU version of PostGIS and GiST-Index that we have developed as a new feature of PG-Strom.

Nowadays, our devices (like mobile phones) generate geolocational data time-by-time, and it is often utilized for area-marketing, push-delivery, disaster notification, and so on. People often use GIS technology to pick up users based on their current location. Even if area definitions are complicated polygons, PostGIS functions can generate right intersections, however, it is often highly computing intensive workloads.

GPU is designed for massive parallel computing workloads, with more than thousands cores per chip. And, we have developed PG-Strom extension to run a part of SQL workloads on GPU devices. At the upcoming PG-Strom v3.0, it newly supports several PostGIS functions and GiST-index for the computing intensive geolocational workloads.

In this talk, we will introduce the technology background, usage, implementation and benchmark result of GPU version of PostGIS and GiST-Index.

Materialized view is a feature to store the results of view definition queries in DB in order to achieve faster query response. However, the data in the view gets stale after underlying tables are modified. Therefore, view maintenance is needed to keep the contents up to date. PostgreSQL has REFRESH MATERIALIZED VIEW command for updating a materialized view, but this command needs to recompute the contents from scratch, so this is not efficient in cases where only a small part of a base table is modified.

Incremental View Maintenance (IVM) is a technique to maintain materialized views efficiently, which computes and applies only the incremental changes to the materialized views instead of recomputing. This feature is required for updating materialized views rapidly but not implemented on PostgreSQL yet.

Therefore, we developed IVM on PostgreSQL and are proposing to implement this as a core feature. The patch is now under discussion on the hackers mailing list. Our implementation allows materialized views to be updated automatically and incrementally when a underlying table is modified. You don't need to write your own trigger function for updating views. As a result of continuous development, the current implementation supports some aggregates, subqueries, self-join, outer joins, and CTEs (WITH clauses) in a view definition query. The result of performance evaluation using TPC-H queries shows that our IVM implementation can update a materialized view more than 200 times faster than re-computation by REFRESH command.

In this talk, we will describe our IVM implementation and its features.

Recently landed in PostgreSQL, jsonb subscripting support doesn't look as exciting as some other improvements around jsonb. But it's user visible changes are only tip of the iceberg. How many people were involved to make it, and what decisions choices were made? How long did it take, and what are the good/bad ideas to promote a patch? These and few other questions will be our targets in this talk.

Some OS distributions do not have a pcs configuration utility to create a high availability cluster PostgreSQL. In this case, the crm utility from the crmsh package will help us. It is more difficult to use, but powerful and effective.

In my master class, I will show how to use this utility, as well as configure a failover cluster in different configurations.

PostgreSQL is a mature and robust RDBMS since it has 30 years of history. Over the year, its query optimizer has been enhanced and usually produces good query plans.

However, can it always come up with good query plans? The optimization process has to use some assumptions to produce plans fast enough. Some of those assumptions are relatively easy to check (e.g. statistics are up-to-date), some harder (e.g. correct indexes are created), and some nearly impossible (e.g. making sure that the statistic samples are representative enough even for skewed data repartition). For now, given those various caveats, DBA sometimes can't always realize easily that they miss a chance to get a meaningful performance improvement.

To help DBA to get a truly good query plan, we'll present below some tools that can help to fix some of those problems by providing a missing index adviser, looking for extended statistics to create, and row estimation error correction information to get appropriate join orders with join methods automatically.

• pg_qualstats: provides a new index and extended statistics suggestions to gather many predicate statistics on the production workload.
• pg_plan_advsr: provides alternative good query plans automatically to analyze iterative query executions information to fix estimation rows error.

In this talk, we will explain how those tools work under the hood and see what can be done, how they can work together. Also, we will mention what other tools also exist for related problems. Therefore, it will be useful for DBA who are interested in improving query performance or want to check whether current settings of indexes and statistics are adequate.

Slides

In Zabbix Server 5.0.1 the PostgreSQL monitoring plugin has become available for Zabbix Agent 2. It was developed by Postgres Professional in collaboration with Zabbix. In the presentation, we will talk about how the plugin works, discuss some options for its configuration, as well as how to add custom metrics to it.

In this talk we want to present how Microsoft team composed of people from two different teams approached the project and solved the migration issues using ora2pg and was able to prove that Postgres Single Server can perform equally well as Oracle Exadata. We will present our ways of working and also some main technical challenges that we faced including migration of BULK COLLECT’s, hierarchical queries, refcursors and others more complicated Oracle constructs.

The story about a challenging PoC that proved that Postgres can achieve the same performance as Oracle Exadata. The schema that was migrated wasn’t the simplest one you might see. It was quite the opposite. The code was loaded with dynamic queries, BULK COLLECT’s, nested loops, CONNECT BY statements, global variables and lot of dependencies. Ora2pg did a great job converting the schema but left a lot of work to do manually. Also estimates produced by the tool were highly inaccurate since the logic required not the migration but total re-architecture of the code. In this talk we want to present how Microsoft team composed of people from two different teams approached the project and solved the migration issues using ora2pg and was able to prove that Postgres Single Server can perform equally well as Oracle Exadata. We will present our ways of working and also some main technical challenges that we faced including:

• How estimates do (not) work
• How we handled BULK COLLECT’s
• Why we got rid of refcursors
• How we got stuck with testing of one the packages and how the help from a friend solved the problem
• How we handled hierarchical queries and drilling down the hierarchy

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.

In a heavily-loaded project, any change implies non-zero risks of downtime or performance degradation. We constantly see how system complexity, number of database nodes, deployments per week constantly grow. How grows the level of automatization of various activities in CI/CD pipelines, containers, Kubernetes.

Meanwhile, when we look at the topic of testing of database changes–from trivial index creation to complex, almost "surgical" operations like converting int4 PK to int8 one in a multi-terabyte database under load–here we observe an obvious lag in technology and methodology development. In the best case, the changes are verified and approved visually, and here it all depends on the level of experience and tiredness of the reviewer.

In this talk, we will look at how we (Postgres.ai) solve this problem using our solution, Database Lab:

• instant provisioning of independent thin clones of multi-terabyte databases, ready to be used for testing,
• integration with existing CI/CD solutions and workflow,
• collection of metrics that are the most useful for decision support on whether or not each database migration has to be approved (and even fully automated rejection of the most dangerous actions).

PostgreSQL provides different levels of security. This talk will cover all the available security techniques used in PostgreSQL 13. We’ll look at client-side security (LibPq, JDBC) through to server-side security. It will cover all supported authentication methods and the pros and cons of all these methods. Some of the key features of the talk are:

• Introduction to Cryptography
• SSL, TLS, GSSAPI, and OpenSSL
• Client-Side Encryption
• Securing Authentication
• Securing Data on the disk
• Securing Backup & Basebackup
• Securing Replication
• Database Roles and Privileges

It’s important to be familiar with all the security levels such as (1)network-level security (2) on-disk security (3) row-level, (4), and column level security. The talk will cover all the aspects with some real-life use cases and examples.

How do you monitor Postgres? What information can you get out of it, and to what degree does this information help to troubleshoot operational issues? What if you want/need to log all the queries? That may bring heavy trafficked databases down.

At OnGres we’re obsessed with improving PostgreSQL’s observability. So we worked together with Tetrate folks on an Envoy’s Network Filter extension for PostgreSQL, to provide and extend observability of the traffic inout a cluster infrastructure. This extension is public and open source. You can use it anywhere you use Envoy. It allows you to capture automated metrics and to debug network traffic. This talk will be a technical deep-dive into PostgreSQL’s protocol decoding, Envoy proxy filters and will cover all the capabilities of the tool and its usage and deployment in any environment.

Resources:

• https://www.envoyproxy.io/docs/envoy/latest/intro/arch_overview/other_protocols/postgres#arch-overview-postgres
• https://www.cncf.io/blog/2020/08/13/envoy-1-15-introduces-a-new-postgres-extension-with-monitoring-support/

PostgreSQL is one of the leading open-source databases. Out of the box, the default PostgreSQL configuration is not tuned for any particular workload. The default configuration is designed in such a way that PostgreSQL can run on any system using minimum resources. Consequently, a default installation of PostgreSQL does not give optimum performance on the high-performance machine because it is set up to use all available resources. PostgreSQL provides mechanisms that allow you to tune your database according to your workload and machine specification. Outside of PostgreSQL, though, we can tune the Linux kernel to allow the database load to work optimally. In this talk, we will learn how to tune some of the PostgreSQL’s parameters, and we will see the effect of that tuning, but we will focus on demonstrating how to tune Linux for better Postgres performance. As there are so many Linux kernel parameters that can be tuned to improve the performance of PostgreSQL, I will also share the results of benchmarks obtained when tuning some of the Linux parameters.