PGConf.Russia 2021

Intel® Xeon® Scalable Gen 3 - new commands have been added to speed up the database: vector bit manipulation instructions to enable data compression without losses, vector instructions to make TLS protocols faster and SGX enclaves for secure code execution. We will also discuss the new generation of persistent memory Intel® Optane™ PMem 200 series. We will explain what these new technologies including oneAPI tools can give to the PostgreSQL project community

We maintain large PostgreSQL databases with the total data volume exceeding 180 TB. Every instance in the cluster gets a workload of 15 thousand requests per second. These circumstances certainly impose limitations on the classical approach to updating database structures. The cost of a DBA error gets higher, as even a small error can lead to a sleepless night) In my talk, I will cover our existing limitations for changes in the production environment, non-classical DBMS behavior under heavy workloads, and will discuss PostgreSQL in general.

In OZON, we work on PostgreSQL as a service. Currently, we have thousands of clusters. We know about the important questions software developers ask, and the most crucial ones are as follows: how to understand the database's workload and why this query is getting slow? In this presentation, I will explain how we help our developers answer these questions by simply having a look at our dashboard.

Many application developers prefer not to have logic on the DB side (in functions) as there are no suitable software development tools, especially for development teams. In the plv8 case, the situation worsens as this function contains both SQL and JavaScript code, and popular IDEs have no support for such a symbiosis. At this tutorial, I will introduce my development named "Plv8 Framework", which considerably simplifies the creation of code on plv8.

The gist is as follows: the JS code that is executed on the DB side can run on the developer's local computer by using node.js, which works on the same v8 interpreter. The plv8.execute special function is replaced by a function from the pg-native npm library, which calls the outer DBMS. I will demonstrate a set of tools chosen by me that enables the following:
- writing JS code in the IDE you prefer and enjoy syntax highlight;
- code debugging in real time (with breakpoint, watch, etc.);
- writing of auto-tests (unit-tests), with a variety of options: Postgres, SQLite, mocks;
- deploying your code in the DBMS;
- usage of additional npm packages (the issue is that all code of the plv8-based function should be included in the function's body, in one file).

You can use this tool regardless of the programming language that you use for the backend. However, it becomes more flexible if you use languages with static typing (like Java, C#, etc.). For the tasks where the backend is an intermediate layer between the frontend and DBMS, logic (or its part) can be placed in plv8/js with dynamic typing, which will simplify the development process.

In addition to the development of new functions on plv8, the framework provides a set of ready-made functions for CRUD operations. These functions are universal, they aren't tied to a certain database structure, and they can work in any project. If you use them, you can do less backend development, in some projects to a very significant extent.

The installation of the plv8 extension is the most complicated part of working with it. However, I have good news: my colleagues prepared Docker files and Docker images for PostgreSQL 13 with pre-installed plv8! So your start with plv8 will be super simple: you need to deploy the Docker container using just one command!
Docker file: PostgreSQL 13 + plv8 v2.13.15
Demo project for you to participate in the tutorial
For the tutorial, you need to install the following:
Node.js (the most desirable is LTS)
IDE for JS (i.e., free Visual Studio Code)
GraphQL Playground