Differences between Postgres Pro Enterprise and PostgreSQL
1. multimaster cluster
The extension
multimasterand its support in the kernel, which are available only in the Postgres Pro Enterprise version , make it possible to build clusters of High Availability servers. After each transaction, global integrity is guaranteed (cluster-wide data integrity), i.e. on each of its nodes the data will be identical. At the same time, you can easily achieve that reading performance scales linearly with the increase in the number of nodes.In vanilla PostgreSQL, it is possible to build highly accessible clusters using streaming replication, but third-party utilities and ingenious scripts are required to identify failed nodes and restore the node after a failure.
multimastercopes with this himself, works out of the box without the use of external utilities or services. Reading zooming in vanilla
PostgreSQLpossible replication in hot standby ( Hot-standby), but with an important caveat: the application should be able to share read-onlyand read-writerequests. That is, to work on a vanilla cluster, the application may have to be rewritten: if possible, use separate connections to the database for read-only transactions, and distribute these connections across all nodes. For a cluster withmultimasterYou can write to any node, so there are no problems with the separation of connections from the database to writers and only readers. In most cases, you do not need to rewrite the application. To ensure fault tolerance, the application must be able to do
reconnect- i.e. make an attempt to restore the connection with the database in case of its violation. This applies to both the vanilla cluster and multimaster. Using logical replication in vanilla,
PostgreSQLyou can implement asynchronous bidirectional replication (for example, BDRfrom 2ndQuadrant ), but this does not provide global integrity and there is a need to resolve conflicts, and this can only be done at the application level, based on its internal logic. That is, these problems are passed on to application programmers. Ourmultimasterit provides transaction isolation itself (the repeat isolation ( Repeatable Read) and “fixed data reading” ( ) transaction isolation levels are now implemented Read Committed. During transaction fixing, all replicas will be consistent and the user application will see the same state of the database; he does not need to know what car you are requesting., who initiated the transaction, we have implemented the 3-phase commit transactions (3-phase in order to achieve this and get a predictable response time in the event of a node failure commit protocol). this mechanism is more complex than the better known the first 2-phase, so let us explain its scheme. For simplicity, depict two nodes, bearing in mind that in fact similar to the node 2 generally works even number of nodes. 
Fig. 1. Scheme of multimaster
A transaction commit request arrives at node 1 and is recorded in the node WAL. The remaining nodes of the cluster (node 2 in the diagram) receive information on data changes using the logical replication protocol and, upon receiving a request to prepare a transaction commit (
prepare transaction), apply the changes (without commit). After that, they inform the node that initiated the transaction that they are ready to commit the transaction ( transaction prepared). In the case when at least one node is not responding, the transaction is rolled back. With a positive response from all nodes, node 1 sends a message to the nodes that the transaction can be committed ( precommittransaction).This is where the difference is from a 2-phase transaction. This action may seem superfluous at first glance, but in fact this is an important phase. In the case of a 2-phase transaction, the nodes would record the transaction and report this to the 1st node that initiated the transaction. If at this moment the connection broke, then node 1, not knowing anything about the success / failure of the transaction on node 2, would have to wait for an answer until it became clear what it should do to maintain integrity: roll back or commit the transaction (or commit risking integrity). So, in the 3-phase scheme, during the 2nd phase, all nodes vote: whether to commit the transaction. If most nodes are ready to commit it, the arbiter announces to all the nodes that the transaction is committed. Node 1 commits the transaction, sends
commitby logical replication and reports the transaction fixation timestamp (it is necessary for all nodes to maintain transaction isolation for reading requests. In the future, the timestamp will be replaced by CSN- transaction fixation identifier, Commit Sequence Number). If the nodes are in the minority, then they can neither write nor read. Violation of the integrity will not occur even if the connection is broken. We have chosen the architecture
multimasterfor the future: we are engaged in the development of effective sharding. When the tables become distributed (that is, the data on the nodes will already be different), it will become possible to scale not only by reading, but also by writing, since it will not be necessary to write all the data across all nodes of the cluster in parallel. In addition, we are developing means of communication between nodes according to the protocol.RDMA(in switches InfiniBandor in devices Ethernetwhere it is RDMAsupported) when the node directly communicates to the memory of other nodes. Due to this, less time is spent on packing and unpacking network packets, and data transmission delays are small. Since nodes communicate intensively when synchronizing changes, this will give a gain in the performance of the entire cluster.2. 64-bit transaction counters
This fundamental alteration of the DBMS kernel is necessary only for heavily loaded systems, but for them it is not just desirable. She is necessary. The core
PostgreSQLtransaction counter is 32-bit, which means they cannot be counted up to more than 4 billion. This leads to problems that are solved by “freezing” - a special routine maintenance procedure VACUUM FREEZE. However, if the counter overflows too often, then the costs of this procedure turn out to be very high, and can even lead to the inability to write anything to the database. In Russia, there are now not so few corporate systems in which overflow occurs in 1 day, and bases that overflow with weekly intervals are no longer exotic. At the PGCon 2017 Developer Conferencein Ottawa, they said that some customers had meter overflows in 2-3 hours. Nowadays, people tend to put in the database the data that was previously thrown away, treating with understanding the limited capabilities of the then technology. In modern business, it is often not known in advance what data may be needed for analytics. The problem of counter overflow is called (
transaction ID wraparound), since the space of transaction numbers is looped (this is clearly explained in the article by Dmitry Vasiliev ). When overflowing, the counter is reset to zero and goes to the next round. 
Figure 2. How does the freezing of transactions fall behind by more than half a circle.
In vanilla
PostgreSQL(that is, with a known 32-bit transaction counter) something is also being done to alleviate the transaction wraparound problem. To do this, in version 9.6 (visibility map), a bit was added to the visibility map format all-frozen, which marks entire pages as frozen, so scheduled (when many old transactions accumulate) and emergency (when approaching overflow) freezes occur much faster. With the rest of the pages, the DBMS works as usual. Due to this, the overall system performance during overflow processing suffers less, but the problem has not been solved in principle. The described situation with the shutdown of the system is still not excluded, although its probability has decreased. Still, you need to carefully monitor the settings VACUUM FREEZEso that there are no unexpected subsidence of performance due to its work.Replacing 32-bit counters with 64-bit counters pushes overflow almost to infinity. The need for
VACUUM FREEZEvirtually disappears (in the current version of the frost is still used for the treatment pg_clogand pg_multixactin the case of an emergency, of which below). But the task is not solved in the forehead. If the table has few fields, and especially if these fields are integer, its volume can increase significantly (after all, each record contains the transaction numbers that generated the record and the one that deleted this version of the record, and each number now consists of 8 bytes instead of 4). Our developers have not just added 32 bits. The Postgres Pro Enterprisetop 4 bytes are not included in the record, they represent the "era" - the offset(offset)at the data page level. The era is added to the regular 32-bit transaction number in the table entries. And the tables do not swell. Now, if the system tries to write
XID, which does not fit in the range defined by the era for the page, then we must either increase the shift or freeze the whole page. But this is painlessly performed in memory. There remains a limitation in the case when the minimal XIDone that can still be claimed by data snapshots (snapshots)lags behind the one we want to write to this page by more than 2 32 . But this is unlikely. In addition, in the near future we are likely to overcome this limitation.Another problem with 32-bit counters is that overflow handling is a very complicated process. Up to version 9.5, very critical bugs were found and fixed in the corresponding code, and there are no guarantees that bugs will not appear in future versions. Our implementation of the 64-bit transaction counter has a simple and clear logic, so working with it and developing it further will be easier than fighting overflow.
Data files of systems with 64-bit counters are binary incompatible with 32-bit ones, but we have convenient utilities for converting data.
3. Pagination
In PostgreSQL, unlike most other DBMSs, there is no page-level compression (compression)
(page level compression). Only TOASTdata is compressed. If there are a lot of records in the database with relatively small text fields, then compression could reduce the size of the database several times, which would help not only save on disks, but also increase the performance of the DBMS. Analytical queries that read a lot of data from disk and do not modify them too much can be especially efficiently accelerated by reducing I / O operations. The
Postgrescommunity suggests using file systems with compression support for compression. But this is not always convenient and possible. Therefore, Postgres Pro Enterprisewe have added our own implementation of page compression. According to test results for various usersPostgres Prothe size of the database was reduced from 2 to 5 times. In our implementation, the pages are stored compressed on disk, but when they are read to the buffer, they are unpacked, so working with them in RAM occurs exactly the same as usual. Deployment of compressed data and its compression occurs quickly and practically does not increase processor load.
Since the amount of changed data during page compression can increase, we cannot always return it to its original location. We write a compressed page to the end of the file. When sequentially writing pages to disk, the overall system performance can significantly increase. This requires a file to map logical addresses to physical ones, but this file is small and the overhead is invisible.
The size of the file itself during sequential recording will increase. By launching garbage collection according to regulations or manually, we can periodically make a file more compact (defragment it) by moving all non-empty pages to the top of the file. You can collect garbage in the background (the segment is blocked, but not the whole table), and we can set the number of background processes that collect garbage.
| Compression (Algorithm) | Size (GB) | Time (sec) |
|---|---|---|
| without compression | 15.31 | 92 |
| snappy | 5.18 | 99 |
| lz4 | 4.12 | 91 |
| postgres internal lz | 3.89 | 214 |
| lzfse | 2.80 | 1099 |
| zlib (best speed) | 2.43 | 191 |
| zlib (default level) | 2.37 | 284 |
| zstd | 1.69 | 125 |
For compression, we chose the modern zstd algorithm (it was developed on Facebook ). We tried various compression algorithms, and settled on
zstd: this is the best compromise between quality and compression speed, as can be seen from the table.4. Offline transactions
Technically, the essence of an autonomous transaction is that this transaction, made from the main, parent transaction, can be committed or rolled back, regardless of the commit / rollback of the parent. An autonomous transaction is performed in its own context. If you define not an autonomous, but a regular transaction inside another (a nested transaction), then the internal one will always be rolled back if the parent one is rolled back. This behavior does not always suit application developers.
Autonomous transactions are often used where action logging or auditing is needed. For example, you need a record of an attempt to take some action in the log in a situation where a transaction is rolled back. An autonomous transaction allows you to ensure that the “sensitive” actions of employees (viewing or making changes to customer accounts) always leave traces that can be used to restore the picture in an emergency (an example on this topic will be given below).
In such as a DBMS
Oracleand DB2(not MS SQL) are defined formally autonomous transactions not as transactions, but as stand-alone units within procedures, functions, and triggers unnamed blocks. In SAP HANAalso there are autonomous transactions, but they can just be defined as transactions, and not just function blocks. AT
Oraclefor example, autonomous transactions are defined at the beginning of the block as PRAGMA AUTONOMOUS_TRANSACTION. The behavior of a procedure, function, or unnamed block is determined at the stage of their compilation and cannot be changed during execution. There
PostgreSQLis no autonomous transaction at all. They can be simulated by launching a new connection using dblink, but this translates into overhead, affects performance and is simply inconvenient. Recently, after the advent of the module pg_background, it was proposed to simulate autonomous transactions by starting background processes. But this turned out to be ineffective (we will return to the reasons below when analyzing the test results). At Postgres Pro Enterprise, we implemented autonomous transactions in the kernel
СУБД. Now they can be used both as nested autonomous transactions and in functions.In nested autonomous transactions, you can define all available
PostgreSQLisolation levels — Read Committed, Repeatable Read, and Serializable — regardless of the level of the parent transaction. For example:
All possible combinations work and give the developer the necessary flexibility. An autonomous transaction never sees the results of the parent's actions, because it has not yet been committed. The opposite depends on the level of isolation of the main. But in their relationship with a transaction that started independently, the usual isolation rules will apply.
The syntax is slightly different in functions: the keyword will throw an error. An autonomous unit in a function is defined just like this:BEGIN TRANSACTION
<..>
BEGIN AUTONOMOUS TRANSACTION ISOLATION LEVEL REPEATABLE READ
<..>
END;
END;TRANSACTIONCREATE FUNCTION <..> AS
BEGIN;
<..>
BEGIN AUTONOMOUS
<..>
END;
END;
Accordingly, the isolation level cannot be set, it is determined by the level of the parent transaction, and if it is not explicitly set, then by default.
We give an example that is considered one of the classic commercial DBMSs in the world. In some bank, customer_info table stores customer data, their debts. Let this table be inaccessible directly to the bank employee. However, they have the opportunity to check customer debts using the function available to them: Before peeping customer data, the function writes the DBMS username, customer account number and transaction time in a log table: We want the employee to be able to inquire about customer debts, but, in order not to encourage idle or malicious curiosity, we always want to see traces of his activity in the log.
CREATE TABLE customer_info(acc_id int, acc_debt int);
INSERT INTO customer_info VALUES(1, 1000),(2, 2000);CREATE OR REPLACE FUNCTION get_debt(cust_acc_id int) RETURNS int AS
$$
DECLARE
debt int;
BEGIN
PERFORM log_query(CURRENT_USER::text, cust_acc_id, now());
SELECT acc_debt FROM customer_info WHERE acc_id = cust_acc_id INTO debt;
RETURN debt;
END;
$$ LANGUAGE plpgsql;CREATE TABLE log_sensitive_reads(bank_emp_name text, cust_acc_id int, query_time timestamptz);
CREATE OR REPLACE FUNCTION log_query(bank_usr text, cust_acc_id int, query_time timestamptz) RETURNS void AS
$$
BEGIN
INSERT INTO log_sensitive_reads VALUES(bank_usr, cust_acc_id, query_time);
END;
$$ LANGUAGE plpgsql;A curious employee will execute the commands: In this case, information about his activities will be rolled back along with the rollback of the entire transaction. Since this does not suit us, we modify the logging function: Now, no matter how hard an employee tries to cover his tracks, all his views of client data will be logged. Offline transactions are the most convenient debugging tool. A dubious piece of code will have time to write a debug message before a failed transaction rolls back: In conclusion about performance. We tested our implementation of autonomous transactions in comparison with the same SQL without autonomous transactions, with a solution with a “bare” combination with connection control. The extension creates three functions:
BEGIN;
SELECT get_debt(1);
ROLLBACK;CREATE OR REPLACE FUNCTION
log_query(bank_usr text, cust_acc_id int, query_time timestamptz) RETURNS void AS
$$
BEGIN
BEGIN AUTONOMOUS
INSERT INTO log_sensitive_reads VALUES(bank_usr, cust_acc_id, query_time);
END;
END;
$$ LANGUAGE plpgsql;BEGIN AUTONOMOUS
INSERT INTO test(msg) VALUES('STILL in DO cycle. after pg_background call: '||clock_timestamp()::text);
END;dblinkdblinkpgbouncerpg_backgroundpg_background_launch(query)starts a background process background workerthat will execute the passed SQL function; pg_background_result(pid)gets the result from the process created pg_background_launch(query)and pg_background_detach(pid)detaches the background process from its creator. The code that executes the transaction is not very intuitive:
But more significantly, as expected, creating a process for each SQL is slow. From the history of pg_background creation, it is known that a fourth function was supposed to pass a new task to an already running process. In this case, the time to create the process will not be spent on every SQL, but this function is not available in the current implementation. Robert Haas , who created the first version , says: “I am skeptical of this approach [to simulate autonomous transactions usingPERFORM * FROM pg_background_result(pg_background_launch(query))
AS (result text);pg_background_run(pid, query)pg_backgroundpg_background]. Like Greg Stark, Serge Rilo, and Konstantin Pan , I believe that autonomous transactions should be performed within the same server process [backend]without relying on background processes [background_workers]. With this approach, we are unlikely to go beyond the limit of the number of background processes [max_worker_processes], and it will most likely be more efficient to work, especially when an autonomous transaction does a little work, making, say, a small diary entry. ” This is exactly how our version of autonomous transactions is implemented: the parent process remembers its context, switches to a new one, and, after the execution of the offline transaction, returns to the parent. Test results confirm Haas’s considerations , a mechanism using
pg_backgroundruns 6-7 times slower than offline transactions in Postgres Pro Enterprise. 
Fig. 3. The performance of various implementations of autonomous transactions. Tests were carried out by us on the basis of pgbech c
INSERTin the table pgbench_history. The scaling factor during the initialization of the database was 10. TPS on pure SQL was taken as 100.PS. To be continued!
PPS We will be glad to know your opinion on the relevance and possible applications of these innovations!
