How we improved the industrial-grade Platform V Pangolin DBMS in version 6.1
My name is Mikhail Gelemeev, I am the leader of the Platform V Pangolin support team at SberTech.
Platform V Pangolin — relational database management system. It is based on the freely distributed version of PostgreSQL and contains a number of improvements to ensure compliance with increased requirements for data security, availability, reliability, and ease of use. Our product helps to obtain the functionality of a relational DBMS, including the creation of high availability clusters, data backup, removal and restoration of backup copies.
In January we released a new version – Platform V Pangolin 6.1. appeared in it updates for working with large amounts of data. In short, working with sections has become easier and faster: access to data in partitioned tables is cheaper, and you can flexibly create unique ones for them global indexes. Now you can prevent high CPU and RAM consumption user session, this improves the availability of the service. We have also added a diagnostic tool for current activity for a detailed understanding of session processes, so the work of the DBMS becomes more transparent.
In the article I will tell you more about each of the improvements. They can be divided into two parts: for users and for administrators/infrastructure.
User updates
I'll start with the advantages of the updated product for our users.
Global indexes for partitioned tables
Global indexes for partitioned tables physically cover many subtables at once and provide a uniqueness constraint based on a set of attributes that do not contain a partition key.
Anyone who has used partitioned tables has sooner or later encountered limitations in the implementation of the partitioning mechanism, for example:
You cannot create a unique index on a field or fields unless they contain a partition key. Therefore, it is necessary to control the uniqueness of fields throughout the table at the application level, and this is not always possible.
The index is always partitioned, just like the main table. This results in redundant reads (roughly proportional to the number of partitions) in cases where the query does not filter on the partition key. The result is a lot of reads on simple queries and increased query execution time.
IN version 6.1 we have added the ability to create global indexes. This functionality removes the described limitations:
Allows you to create and use global unique constraints on fields of partitioned tables other than the fields of the partition key.
Significantly increases the efficiency of searching for records in partitioned tables using a unique key, including those that do not contain the partitioning key.
It is important that during design and development we took into account the experience of operating solutions of Western colleagues. For example, in Platform V Pangolin, when a section is detached, that is, when ALTER TABLE DETACH PARTITION is called, the index remains available for use. More details can be found from documentation.
Increasing the bit size of transaction identifiers (XIDs) to 64 bits
Increasing the bit size of transaction identifiers (XID) to 64 bits helps eliminate the problem of achieving XID wraparound and the associated limitations and operational nuances.
Currently, in the original PostgreSQL kernel, 32 bits are allocated for the transaction counter, which covers only 4 billion transactions (XID). To avoid exhaustion of transaction numbers, a loop counter scheme is used.
Many years ago, at the design stage of PostgreSQL, 4 billion transactions were enough. But now in a loaded system it is quite possible to exhaust the counter in a few days. The 64-bit transaction counter will almost never overflow. Even with a load of 2^32 transactions per day (~50,000 TPS), the interface between the past and the future will be reached in 2^31 days (5.9 million years). The advantages of such a counter are as follows:
Ability to avoid incoming write requests from being terminated under high transaction load conditions if there is a long-lived transaction holding the transaction horizon (xmin). For example, with a 32-bit counter, a 13-hour OLAP query after 12 hours would cause 50,000 TPS of OLTP queries to be denied service. At this point, the age of the next writing transaction would have reached 2^31 in relation to the transaction horizon held by the OLAP query. The transaction horizon prevents autovacuum from freezing row versions younger than the transaction horizon to advance the counter. As a result, a new number for the write transaction is not allocated, and Platform V Pangolin fails OLTP queries to prevent data loss.
The 64-bit counter removes the restriction on the 2^31 age limit of a new transaction relative to the horizon, so the problem does not arise.
The ability to reduce the risk of performance degradation during intense transaction loads by configuring the freezing procedure to be launched more rarely and scheduling it for a period with less load.
plpgsql_check and pldebugger extensions
Adding the plpgsql_check and pldebugger extensions helps you debug and monitor pl/pgsql procedures and functions.
pldebugger — an extension that provides an API for executing functions with the ability to stop at a given point, step-by-step execution, and view variable values. The extension is integrated with solutions from pg_admin and dbeaver (the utilities provide a graphical interface for development using extension capabilities), and also allows you to use API functions directly from psql, since in fact these are ordinary SQL functions.
plpgsql_check is a full-fledged static code analyzer for PL\pgSQL for PostgreSQL. The extension provides a set of capabilities for analyzing and profiling functions and procedures written in the PL\pgSQL language. In addition, it analyzes the SQL inside procedures and finds errors that would not normally be found when executing a CREATE PROCEDURE/FUNCTION command. You can control the levels of many warnings and prompts. You can also add PRAGMA markers to disable or enable many aspects that allow you to hide known messages. Or remind you to return to them for a deeper analysis.
plsql-http extension
Our experience suggests that using a DBMS as a universal machine for solving any problem has a downside and does not always lead to the expected results. At the same time, in some cases, mechanisms are needed to solve specific problems without expanding the infrastructure used and adding external services and components.
In such cases, the plsql-http extension can be useful – an HTTP client at the DBMS level. The extension allows you to access REST services from SQL queries.
Message queue support
Message queue support includes the pgq, pgq-coop, and pgqd daemon extensions. A queue is a data structure with sequential access to objects using a first-in, first-out method. Most often, message queues are used for services to interact with each other, perform pending operations, etc. There are specialized solutions for organizing queues: RabbitMQ, Platform V Synapse and others.
If you have a task for which the queuing mechanism is perfect, but there is no way to use a separate service, you can use the pgq extension.
Admin/Infrastructure Updates
Let's see what's new for administrators and infrastructure.
Ability to control the consumption of computing resources (CPU, RAM)
One of the common cases in the work of a database administrator is when a poorly written user query leads to memory overflow on the server and subsequent restart of PostgreSQL instance processes due to OOM Killer.
In version Platform V Pangolin 6.1 it became possible control the consumption of computing resources (CPU, RAM) client sessions. Now, when trying to execute a non-optimally composed query, it is possible to provide protection against unavailability or degradation of functioning if the expected consumption of DBMS resources by any of the session processes is exceeded.
In addition, it is now possible to diagnose cases of non-optimally composed queries not only by execution time, but also by resource consumption.
Session trace
Every DBMS administrator has faced the need to see what a session does.
Session trace is a convenient mechanism for turning on/off debugging information, which allows you to view the application point-by-point with maximum detail of interaction with the DBMS server without using the system log and audit. The tool allows you to obtain the information necessary for analysis depending on the selected level of detail, for example:
executable statements;
binding variables;
locks such as LWLock and Lock;
resources consumed by all operations.
The trace report can be obtained in a clear, structured form in a text file or hypertext format.
Adaptation of a migration tool from original PostgreSQL to Platform V Pangolin 6.1
The modified pg_upgrade utility allows you to migrate from the original PostgreSQL to Platform V Pangolin. The improvement will be relevant for solving the problems of migrating from the original PostgreSQL to Platform V Pangolin at existing stands, without transferring data through third-party tools. In other words, by “converting” the database, which is often necessary when disk and server resources are limited.
Support for Alt 10 and Astra Linux 1.7 operating systems
Now the full list of supported OS looks like this:
SberLinux 8;
Alt 8 SP;
Alto 9;
Alto 10;
RED OS 7.3;
Astra Linux 1.7;
RHEL 7;
RHEL 8.
Conclusion
We looked at the main updates that appeared in the new version of Platform V Pangolin. They allow you to work with large volumes of data even easier and more convenient. Starting with release 6.1, the product is released in various editions (Enterprise, Standard, Trial). You can read more about this here.
And the best part is that now users don't have to pay for functionality they don't use. The distribution kit can be purchased with the necessary set of functionality, including for intended use, for example, in conjunction with 1C: Enterprise.
