PostgreSQL DBMS

Main article: Database Management System (DBMS)

A free object relational database management system DBMS () is a free alternative to commercial DBMS (such as,,,, and Oracle Database Microsoft SQL Server IBM DB2 Informix production DBMS Sybase) along with other free DBMS (such DBMS as MySQL Firebird). It exists in implementations for the following platforms: ,/, Win32, Linux SolarisOpenSolaris Mac OS X,, QNX 4.25, FreeBSD QNX 6.

Russian developers made a great contribution to the development of PostgreSQL. Among them can be distinguished Vadim Mikheev, Oleg Bartunov, Fedor Sigaev, Alexander Korotkov.

The largest vendor providing services for the refinement and implementation of Postgres is the American company EnterpriseDB. Since January 2015, Postgres Professional has been developing in Russia according to a similar model, providing support, training, development, and consulting services.

{{main 'Product: PostgreSQL_(
PostgresPro)}}

Functions

The functions are code blocks executed on the server, not on the DB client. Although they can be written in pure SQL, the implementation of additional logic, for example, conditional transitions and loops, goes beyond SQL itself and requires the use of some language extensions. Functions can be written using one of the following languages:

• Built-in procedural language PL/pgSQL, much like the PL/SQL language used in Oracle DBMS;
• Scripting languages - PL/Lua, PL/LOLCODE, PL/Perl, plPHP, PL/Python, PL/Ruby, PL/sh, PL/Tcl and PL/Scheme;
• Classical languages ​ ​ - C, C++, Java (through the PL/Java module);
• Statistical language R (via PL/R module).

PostgreSQL allows the use of functions that return a set of records, which can then be used in the same way as the result of a regular query.

Functions can be performed both with the rights of their creator and with the rights of the current user. Sometimes functions are identified with stored procedures, but there is a difference between these concepts.

Triggers

Triggers are defined as functions triggered by DML operations. For example, an INSERT operation can trigger a trigger that checks the added record for compliance with certain conditions. When writing functions for triggers, various programming languages ​ ​ can be used.

Triggers are associated with tables. Multiple triggers are executed in alphabetical order.

Rules and Representations

The rules mechanism (English rules) is a mechanism for creating user handlers not only of DML operations, but also of fetching operations. The main difference from the trigger mechanism is that the rules are triggered at the parsing stage of the request, before choosing the optimal execution plan and the execution process itself. Rules allow you to override the behavior of the system when performing a SQL operation on a table. A good example is the view engine implementation: when creating a view, a rule is created that determines that instead of performing a fetch to view operation, the system should perform a fetch to the base table/tables, taking into account the fetch conditions underlying the view definition. To create views that support update operations, the rules for insert, modify, and delete row operations must be defined by the user.

Indexes

PostgreSQL supports indexes of the following types: B-tree, hash, R-tree, GiST, GIN. If necessary, you can create new types of indexes, although this is far from a trivial process. Indexes in PostgreSQL have the following properties:

• it is possible to view the index not only in direct, but also in reverse order - creating a separate index for the operation of the ORDER BY construct... DESC is unnecessary;
• You can create an index over multiple columns in the table, including columns of different data types.
• indexes can be functional, that is, built not on the basis of a set of values ​ ​ of a certain column/columns, but on the basis of a set of values ​ ​ of a function of a set of values;
• indices can be partial, that is, built only on a part of the table (on some of its projection); in some cases, this helps to create much more compact indexes or achieve performance improvements by using different types of indexes for different (for example, in terms of refresh rate) parts of the table;
• A query scheduler can use multiple indexes at the same time to execute complex queries.

Multi-Version (MVCC)

PostgreSQL supports simultaneous modification of the database by several users using the Multiversion Concurrency Control (MVCC) mechanism. Thanks to this, ACID requirements are met and there is practically no need for read locks.

Full-Text Search

PostgreSQL has a built-in full-text search system that allows you to search the database for documents and sort them in a given order. The main advantages of using built-in full-text search are: close integration with DBMS (transactionality, simultaneous access, disaster recovery), scalability, wide customization options (dictionaries, parsers, etc.).

Geoinformation systems

PostGIS is an extension of the PostgreSQL DBMS designed for storage in a geographical database. PostGIS includes support for R-Tree/GiST spatial indices and geodata processing functions.

2024

Posting to Linx Cloud

The Linx Cloud provider has placed an object relational database management system PostgreSQL in DBaaS format in the cloud. The provider announced this on November 1, 2024. Read more here.

PostgreSQL 17 with SQL/JSON support

PostgreSQL Global Development Group September 30, 2024 announced the release of a major version of PostgreSQL.

PostgreSQL 17 has included a large number of improvements that improve performance, resiliency and security. Among the authors are more than 30 Russians, developers from the Postgres Professional team. The company has been sending patches to open PostgreSQL since 2015.

Among the key updates of version 17: an updated memory management system for VACUUM; SQL/JSON support; Improve query performance support for incremental backup of pg_basebackup; improvements in logical replication.

More about the changes in PostgreSQL 17:

• The ability to create incremental copies that save only changes made relative to another backup.
• Updating COPY command... FROM. The team learned to ignore errors related to incorrect format of individual column values.
• Memory structure and interface for working with string identifiers based on a compressed prefix tree (radix tree).
• Combine WAL records about cleaning and freezing a page.
• For each SLRU cache, a corresponding configuration parameter appeared (with the_buffers suffix in the name), which allows you to fine-tune the system for a specific load.
• The sslnegotiation parameter for client applications running through libpq. Thanks to the parameter, the client and the server can do without prior coordination of the use of TLS.
• SQL/JSON support.
• Built-in locale provider for C.UTF8.
• MAINTAIN privilege and predefined pg_maintain role.
• The options for the EXPLAIN command are SERIALIZE, MEMORY.
• The ability to create an event trigger on a database connection.
• A view of the pg_stat_checkpointer statistics collector.

Also added features in the MERGE team:

• You can now use an updatable view as the target relationship.
• The WHEN NOT MATCHED BY SOURCE condition to handle the rows of the target relationship that are not in the source.
• Support for RETURNING. The merge_action function will help you understand which lines have been added, changed.
• Changes have also been made to the operation of logical replication:
• Synchronize logical replication slots between the wizard and the replica.
• Migrating replication slots when updating the publishing server.
• Quickly create a logical replica from a physical replica.

The company will release updated versions of Postgres Pro based on version 17 in 2024.

We are constantly working to close the gap between the releases of PostgreSQL and Postgres Pro. Previously, we made a big comparison of mergers of changes once a year, when the next version of Open Source was released. It was quite a complicated process as there were many changes, and some of them in some cases could even contradict each other. Therefore, a few years ago, the team moved to the practice of continuous merging of changes. We do this constantly, almost daily, as soon as changes are accepted in the open version. Thanks to this approach, we have minimized the technical lag: we are releasing an updated version of Postgres Pro Standard a few days after the release of PostgreSQL, and Postgres Pro Enterprise a few weeks later, "said Ivan Panchenko, co-founder and deputy general director of Postgres Professional.

Cloud Availability T1 Cloud

T1 Cloud cloud services Three Managed Services (managed services) formats have become available to customers at once based on the current versions of database management systems Postgre, and SQL. MongoDB Redis T1 Cloud announced this on March 19, 2024. More. here

FSTEC warned of a dangerous vulnerability in the Java connector for PostgreSQL

At the end of February, FSTEC warned of the discovery of information about a dangerous vulnerability BDU:2024-01541^[1]is present in the PostgreSQL JDBC driver and allows malicious code to be executed remotely. Although according to the CVSS classification, it has an index of 10 out of 10, however, not every installation of this DBMS is subject to it.

For the error^[2] fixes have been released] in versions 42.7.2. 42.6.1. 42.5.5. 42.4.4. 42.3.9. 42.2.28 and 42.2.28.jre7, to which it is recommended to update. In addition, in the default mode of operation of the JDBC driver, it is not dangerous.

The JDBC driver is a software component that allows a Java application to generate queries to a SQL-enabled DBMS. The standard of such interaction provides communication between the application server and the database, which are parts of modern web project management (CMS) systems. Although such interaction is hidden for users by the code of the web server, nevertheless, in some versions of web applications, where the user has the ability to specify query parameters (for example, search), it is possible to transfer the corresponding parameters through the JDBC driver and cause the vulnerability to fire.

Diagram of interaction of the JDBC driver with the DBMS

However, the error appears only if you use a special query preparation mode, to enable which, in the configuration of the Java application connection with the DBMS, enter the string prefixQueryMode=simple. In this mode, to exploit the vulnerability, it is also necessary to have two parameters: integer and string - without filtering their content. With this combination of conditions, character-by-character compilation of a JDBC query leads to the formation of two minuses (sign "-") of the sequence "-," with which the comment begins in SQL. As a result, the attacker has the opportunity to escape a legal request and execute his own, transmitted in the second, text parameter.

In fact, this is a new type of SQL injection that allows you to execute your queries in a vulnerable DBMS.

An example of the code that triggers the vulnerability, given on the manufacturer's website

In small web projects built on Java application servers, this error will most likely not occur because they use the default connection settings. It is worth paying attention to the presence of such an error in complex web projects, where DevSecOps pipelines are now being increasingly implemented. If they are based on PostgreSQL, then it is worth updating the corresponding dependencies and installing a new version of the DBMS.

The compensatory measures recommended by FSTEC are as follows: using the default query mode; Do not use the value of the prefixQueryMode=simple parameter when configuring connection properties. use of firewalls to limit the possibility of remote access. In addition, it is worth installing a WAF in front of the web server, which can detect and filter dangerous commands contained in user requests.

A dangerous vulnerability has been discovered in PostgreSQL, which allows you to execute commands on behalf of the developer

On February 12, 2024, the FSTEC NOS entered information about a critical vulnerability in the PostgreSQL BDU:2024-01121^[3] database^[4]which allows a remote violator to execute arbitrary commands on behalf of the developer. According to CVSS, this vulnerability has a value of 8 out of 10.

An exploit has been published for the vulnerability that runs on versions up to 16, 15.6, 14.11, 13.14 and 12.18. In the specified versions (except 16), which were released on February 8, the vulnerability has been fixed, so it is recommended to install the appropriate fixes as soon as possible.

^[5] vulnerability] was discovered in the REFRESH MATERIALIZED VIEW CONCURRENTLY function, which is accessed with a special request to execute extraneous code on behalf of the creator of the corresponding object. In particular, this can be done using a specially prepared CREATE RULE request, which allows you to run a vulnerable function with the commands necessary for an attacker.

PostgreSQL is a popular DBMS in Russia. According to the Netlas.io service, about 17.6 thousand installations were found. True, Russia in this list is in fifth place after the United States (106 thousand), Poland (37.6 thousand), Germany (37.5 thousand) and the Netherlands (19.1 thousand). In total, 367.4 thousand different versions of PostgreSQL are installed around the world, a significant part of which may be vulnerable. This creates the danger of a massive attack using an already published exploit.

PostgreSQL distribution map for countries around the world according to the search engine Netlas.io

It should be noted that not all of the specified installations are delivered from the main DBMS distribution. There are several commercial versions of the database in which their developers should check the update. In addition, PostgreSQL itself, as a rule, is included in Linux server distributions, and its updates should also be released by the developers of the corresponding OS.

This work can be done as soon as possible, but if updates are still not released for your version, then FSTEC employees recommend standard compensatory measures that minimize the availability of the base from the outside and the possibility of exploiting the vulnerability. They specifically include: minimizing user privileges; disabling or deleting unused user accounts; Use web application layer firewall to limit remote access Use virtual private networks to organize remote access.

2023

PostgreSQL 16

On September 15, 2023, it became known that after 11 months of development, an updated stable branch of the PostgreSQL 16 DBMS was published. Updates for this branch will be released within five years until November 2028. Support for PostgreSQL 11.x, the oldest of the supported branches, will be discontinued on November 9th.

𝓲

helpnetsecurity.com

As reported, the main changes affected the following:

• The logical replication mechanism, which allows you to translate changes made to the database during the addition, deletion or update of records to another server, is enhanced by the ability to replicate changes from a standby server. For example, in the case of a high load on the primary server, the standby server can be used to transfer changes to other secondary systems.
• Support for bidirectional logical table replication has been added, allowing you to synchronize changes to two tables on different servers. The replication capabilities added to PostgreSQL 16 allow you to create configurations with several active servers (active-active mode), on which you can simultaneously perform "INSERT," "UPDATE," "DELETE" operations.
• Optimized logical replication performance. For example, it is possible to use parallel processors when subscribers use large transactions. Added the ability to use B-tree indexes on the subscriber side for tables without primary keys, which allows you to do without sequential scanning when searching for records. The initial synchronization of some tables in binary format is optimized.
• Extended logical replication access controls. For example, a predefined role "pg_create_subscription" has been added to allow users to create new subscriptions.
• Added support for load balancing on the side of clients using the libpq standard library. Balancing allows the client to connect to different hosts, which can be selected both in a specific and random order. If the host is unavailable, an attempt is made to connect to another server. After the connection is established, requests within the session are sent to the selected server.
• Optimization of the query scheduler has been made:
  • Parallelization of "FULL JOIN" and "RIGHT JOIN" table joining operations is provided.
  • Optimized use of "RIGHT JOIN" and "OUTER JOIN" operations to find rows that are not in the attached table ("anti-join").
  • Optimized optimization plan performance for queries that use aggregate functions in conjunction with DISTINCT or ORDER BY expressions.
  • Incremental sorting is used to optimize SELECT DISTINCT queries.
  • Window functions have been optimized.
  • Increased the speed of loading data in batch mode using the "COPY" operation, among other things, the optimization also affected the simultaneous execution of several "COPY" operations. In some situations, acceleration can be as high as 300%.
  • Optimized VACUUM strategy to reduce the number of situations that require the entire table to be locked.
  • On systems with architectures x86 ARM , the use of vector processor instructions (SIMD) is implemented to optimize ASCII processing of -strings, operations with data in JSON format and performing searches in arrays and subtransactions.

• Support for the "IS JSON" operation is implemented to check the type of JSON expression defined in the SQL:2023 standard. Added JSON_ARRAY () and JSON_ARRAYAGG () functions to create JSON arrays.
• It is allowed to use underscores in numbers to increase the visibility of digital literals. For example, "SELECT... WHERE a > 1_000_000".
• The ability to specify hexadecimal, binary and octal literals has been added. For example, "SELECT 0x1538, 0o12450, 0b1010100111001."
• The " \bind "command has been added to the psql utility, which allows you to generate parameterized queries and use variables in queries. For example, "SELECT $1:: int + $2:: int\bind 1 2\g."
• Support for the properties of the "Collation" locale has been expanded, allowing you to set sorting rules and mapping methods taking into account the meaning of the characters (for example, when sorting digital values, the presence of a minus and a dot before a number and different types of spelling are taken into account, and when comparing, the case of characters and the presence of an accent are not taken into account). By default, it now builds with ICU Collation instead of libc locale.
• Enhanced capabilities for monitoring DBMS performance. Added service representation "pg_stat_io" with statistics reflecting the load on the I/O system by various backends (background worker, autovacuum, etc.) and objects.
• A field with the last scan time of the table or index has been added to the pg_stat_all_tables service view.
• The auto_explain module has support for logging values ​ ​ passed to parameterized requests. Optimized the accuracy of the query tracking algorithm used in pg_stat_statements and pg_stat_activity views.
• In pg_hba.conf and pg_ident.conf files containing access and authentication settings, it is possible to use regular expressions for user names and databases, as well as add the directives "include," "include_if_exists" and "include_dir" to include the contents of other files with settings.
• Additional parameters have been added to manage client connection protection: require_auth, to set the authentication parameters allowed when connecting to the server; sslrootcert="system" to use the CA root certificate store provided by the client operating system. Added support for the Kerberos delegated credentials mechanism, which can be used for authenticated connection to external services using the postgres_fdw^[6] modules^[7]16

Creating a utility for easy migration from Oracle software to Russian DBMS

In mid-June 2023 Postgres Professional , it introduced a utility for easy migration ON Oracle from to Russian. PostgreSQL DBMS According to the developers, the program allows you to increase the percentage of correct code conversion in automatic mode, which will help customers save up to 50% of migration time and reduce migration costs from Oracle. More. here

RooX UIDM Compatibility

The company RooX on April 24, 2023 announced the compatibility of the management authentication and authorization system RooX UIDM with the open DBMS Postgre SQL and its assembly from the Postgres Pro Russian company. Postgres Professional More. here

Support by SearchInform CIB system

SearchInform presented an update to the DLP system - the solution implements support for the freely distributed PostgreSQL and its domestic analogue Postgres Pro. This became known on January 17, 2023. Read more here.

2022

In the world rating of contributors in the 15th version of PostgreSQL - three Russian companies

Three at once Russian companies entered the world rating of developers DBMS open source with PostgreSQL, prepared and published at the end of March 2023 by the company. EDB This was announced on April 27, 2023 by TAdviser representatives. Postgres Professional

Thus, the Russian company Postgres Professional managed to take second place in the ranking. The other two domestic developers - Kontur and Arenadata - are located in 17 and 33 places, respectively. Read more here.

World ranking of open source DBMS developers PostgreSQL according to EDB
Russians - in second place in the world ranking of PostgreSQL developers

PostgreSQL support in SearchInform FileAuditor

SearchInform"" December 1, 2022 released an update to the DCAP system SearchInform FileAuditor"," now all information file about operations, access rights and the results of document classification are recorded database in Postgre. SQL More. here

Navicon MDM Compatibility

and System Integrator the developer Navicon announced on November 7, 2022, the finalization of the master management system. data Navicon MDM Now the solution is compatible with open DBMS PostgreSQL and its assemblies from the Russian companies, in particular, and. Postgres Pro Arenadata Postgres Navicon customers will be able to manage master data without buying from licenses major vendors. More. here

PostgreSQL 14.4 with index corruption correction

A corrective release of the PostgreSQL 14.4 DBMS has been formed, which fixes a serious problem, under certain circumstances leading to invisible corruption of data in indexes when executing the commands "CREATE INDEX CONCURRENTLY" and "REINDEX CONCURRENTLY." This became known on June 17, 2022. Indexes created using these commands may not take into account some records, which will lead to the skipping of individual rows when making SELECT queries involving problematic indexes.

To determine whether the B-tree indexes are corrupted, you can use the command "pg_amcheck --heapallindexed DBname." If errors are detected or the commands "CREATE INDEX CONCURRENTLY" and "REINDEX CONCURRENTLY" are used in previous releases with other types of indexes (GiST, GIN, etc.), after upgrading to version 14.4, it is recommended to re-index with the "reindexdb --all" utility or the "REINDEX CONCURRENTLY index_name" command.

The problem is only affected by the 14.x branch, which included optimizations that exclude certain transactions related to the "CREATE INDEX CONCURRENTLY" and "REINDEX CONCURRENTLY" execution during the VACUUM operation. As a result of the application of these optimizations, indexes created in CONCURRENTLY mode did not get some tuples in heap memory that were updated or trimmed during the creation of^[8] index^[9].

Elimination of the vulnerability. Issue pg_ivm 1.0

On May 12, 2022, it became known that corrective updates were formed for all supported PostgreSQL branches: 14.3, 13.7, 12.11, 11.16 and 10.22. The 10.x branch is approaching the end of the support period (updates will be formed until November 2022). The release of updates for the 11.x branch will last until November 2023, 12.x - until November 2024, 13.x - until November 2025, 14.x - until November 2026.

In current versions, more than 50 fixes have been proposed vulnerability and the CVE-2022-1552 associated with the ability to bypass the isolation of the execution of privileged operations Autovacuum, REINDEX, CREATE INDEX, REFRESH MATERIALIZED VIEW, CLUSTER and pg_amcheck, which Attacking has the authority to create non-objects temporary in any scheme, storages can achieve the execution of arbitrary SQL functions with superuser rights in time the execution of the above operations by the privileged user affecting the attacker's object. Including exploitation of the vulnerability can occur during automatic cleaning when bases executing the autovacuum handler.

If it is not possible to perform the update as a workaround to block the problem, you can disable autovacuum and do not perform the REINDEX, CREATE INDEX, REFRESH MATERIALIZED VIEW and CLUSTER operations as a privileged user, or run the pg_amcheck utility or restore content from a backup created by the pg_dump utility. The execution of VACUUM is considered safe, as is the application of any command operation if the objects being processed belong to trustworthy users.

Other changes include updating the JIT code for working with LLVM 14, allowing the use of databases.schema.table templates in psql utilities, pg_dump and pg_amcheck, fixing problems causing damage to GiST indices over ltree columns, incorrect rounding of epoch values extracted from interval data, malfunction of the scheduler when using asynchronous remote queries, incorrect sorting of table rows when using a CLUSTER expression on indexes with expression-based keys, loss of data at emergency termination immediately after the construction of the sorted GiST index, interlock when deleting a partitioned index, race state between DROP TABLESPACE and checkpoint.

Additionally, the release of the pg_ivm 1.0 extension with the implementation of IVM (Incremental View Maintenance) support for PostgreSQL 14 can be noted. IVM offers an alternative way to update materialized views, more effective if the changes affect a small portion of the view. IVM allows you to instantly update materialized views by applying only incremental changes to them, without recalculating the view produced using the REFRESH MATERIALIZED VIEW operation^[10]

2020

Availability for Docsvision 5.5.2

On October 30, 2020, it became known that an updated version of the Docsvision platform was released, finalized in the field of performance, stable operation and scalability and supporting work with PostgreSQL and Postgres Pro DBMS. Now support for domestic DBMS is included in the official release of the 5.5.2 platform. Read more here.

Experience of using the PostgreSQL Open Source database in an enterprise environment

In September 2020, Jet Infosystems"" reported on experience in Open Source databases PostgreSQL the corporate environment. More. here

Hybrid Transaction Acceleration Processing for PostgreSQL Acceleration

On February 11, 2020, it became known that the company Swarm64 introduced Russia in its solution Hybrid Transaction Acceleration Processing with support for the programmable accelerator Intel Arria 10 for FPGA acceleration with DBMS. open source PostgreSQL More. here

2019

Inclusion in DeviceLock products

On October 22, 2019, it became known that DeviceLock included support for Postgres Pro and PostgreSQL in its products. Read more here.

Launching the PostgreSQL DBMS Certification Program

On May 21, 2019, Postgres Professional announced the launch of the PostgreSQL DBMS certification program.

The certification program provides for three levels with increasing qualifications:

1. "Professional"
2. "Expert"
3. "Master"

To obtain a certificate, you must be tested at the Postgres Professional office and score a passing score. The materials for training can be the author's Postgres Professional courses available on the site, as well as regularly read in certified training centers. More than 500 people become participants in the courses every year.

The test for the first level - "Professional" - includes 50 questions on the basics of PostgreSQL administration and lasts 75 minutes. Since each release of PostgreSQL has its own administration features, certification is related to a specific version of the DBMS. For example, for May 2019, a test is available for the 10th version of PostgreSQL DBA1-10. For those who have passed the PostgreSQL 10 knowledge test and who want to confirm their skills for the 11th version in the future, it will be enough to undergo a short additional test focused on product differences.

To obtain an Expert level certificate, you will need to successfully pass three tests:

1. DBA2-10 (PostgreSQL configuration and monitoring)
2. DBA3-10 (PostgreSQL backup and replication)
3. QPT-10 (query optimization)

And
going to the "Master" level involves performing practical tasks for working with PostgreSQL. Postgres Professional plans to launch a certification program for application developers on PostgreSQL.

Ivan Panchenko commented on the launch of the certification program:

Postgres specialists are becoming more and more popular in the Russian market, which is confirmed by the data of personnel agencies. In such a situation, uniform standards and criteria are needed to assess the level of knowledge. In many ways, our certification program has been a response to the needs of customers and partners interested in an independent tool for assessing and improving the skills of their employees. 'Ivan Panchenko, Deputy CEO of Postgres Professional '

Live Universal Interface Compatibility

On April 15, 2019, FORS Telecom announced the appearance in the ecosystem of software and tools compatible with the open platform Postgres Pro/PostgreSQL of a designer of user web interfaces to databases - Live Universal Interface (LUI). Read more here.

TerraLink xDE Compatibility

March 12, 2019 TerraLink announced that he TerraLink xDE supports OC families Linux DBMS and PostgreSQL. More. here

2018

Inclusion of Postgres Professional co-founder Alexander Korotkov in the list of PostgreSQL DBMS committers

In June 2018, the list of committers (developers who contribute to the development of the code) DBMS PostgreSQL was replenished with a third Russian. The list of the main committers of the PostgreSQL kernel included Alexander Korotkov, co-founder and head of development of the Russian company. Postgres Professional

2017

Version 10 documentation localized for Russia

On October 11, 2017, Postgres Professional announced the translation of PostgreSQL 10.0 documentation into Russian. The materials are available in Html, epub and pdf formats. The total volume of text is almost 2,600 pages.

In addition to documentation in Russian, Russian PostgreSQL users also have 24/7 technical support, assistance in migrating from other DBMSs to PostgreSQL, training courses and technical conferences.

In the near future, Postgres Professional plans to release an updated version of the Russian DBMS Postgres Pro Standard based on PostgreSQL 10.0.

PostgreSQL 10

On October 5, 2017, PostgreSQL version 10 was released. In general, with each version released once a year, PostrgeSQL receives capabilities that expand the scope of effective DBMS application.

The main innovations:

• Logical replication: some parts of this mechanism have been added to PostgreSQL for quite some time, and in this version logical replication has become fully available to users. It can be used to selectively replicate individual tables to another server, which can also execute both reading and writing requests. Servers participating in replication can run different versions of PostgreSQL, allowing cluster upgrades with minimal downtime.
• Declarative partitioning eliminates the need for the administrator to manually define the hierarchy of tables, create triggers and integrity constraints.
• Parallel execution of requests has become possible for scanning bitmaps and indexes, for merging connections and subqueries in addition to those features that appeared in the previous version.
• Synchronous replication, taking into account the quorum, allows you to capture changes if they are confirmed by the required number of arbitrary replicas.
• SCRAM authentication is a more cryptographic version of the previously used MD5 authentication.

In total, according to the developers, version 10 included more than 100 changes and improvements, some of which were made by Postgres Professional.

Integration with Ethereum

On September 14, 2017, the Russian company Postgres Professional announced the creation of a prototype of the Posthereum extension to integrate the fully functional PostgreSQL DBMS with the Ethereum blockchain platform, designed to register transactions with any type of assets based on a smart contract system. According to the company, large Russian banks, corporations and government agencies working with PostgreSQL DBMS will be able to combine databases with Ethereum-based blockchain applications with the help of this development. Read more here.

2016

PostgreSQL 9.6

On September 29, 2016, the developer community introduced a stable branch of the PostgreSQL 9.6 DBMS. Updates for it 9.6 will be released within five years, until September 2021.

PostgreSQL 9.6 Logo, (2016)

Main additions

• supports parallel Sequential Scan, join, and data aggregation. When parallelizing, the operation is divided into parts and each part is disassembled by a separate handler, after which the results of each handler are combined, which significantly increases the processing speed of the request on systems with a large number of processor cores. The gain is noticeable for resource-intensive queries, such as regular expression mapping. For example, in texts, when parallelization is enabled, some queries are executed up to 32 times faster^[11].
• synchronous replication mode "synchronous_commit=remote_apply" in which the primary node waits for confirmation of the use of data on the standby node before closing the transaction, which allows for consistency of reads from the database in any part of the cluster;
• possibility of creating cluster configurations including several spare units replicated in synchronous mode. This feature can be used to create several completely identical load balancing nodes;
• The postgres_fdw module, which allows you to logically merge database content from several servers, added support for merge operations (join) and sorting requests, as well as the execution of UPDATE and DELETE operations on an external server. In particular, the module can be used for the initial processing of requests in PostgreSQL with translation of requests to external databases, which can be served by other DBMS;
• API for creating "hot" backups, in which the backup label is not written to the data directory, but returned as a result of the pg_stop_backup function (), which allows you to protect against problems in the event of a crash during a backup;
• reduced the negative impact on the operation of large tables of the "autovacuum" operation, due to the exclusion of "refreezing" operations of old data;
• a subsystem is implemented to display the progress of operations, for example, information on the time to the completion of VACUUM is organized;
• The full-text search system has added phrase search tools implemented through the new ‹ operator - › or ‹ DISTANCE ›, which determines the distance between words (for example, you can select phrases in which the word "A" is separated from "B" by a given number of words). Together with the new flexible search options, the new feature can be used to create hybrid search systems that combine search by relational data, JSON and full-text indexes;
• Added system views and functions: pg_stat_wal_receiver, pg_visibility, pg_config, pg_blocking_pids, g_notification_queue_usage
• Adding add-ons now supports cascading dependencies.
• The pg_basebackup module now supports parallelization of replication operations and slots.
• The\ev and\sv commands have been added to the psql utility to edit views. It is possible to specify several instances of the "-c" and "-f" options. Added mode\crosstabview to display query results as a grid, similar to spreadsheets;
• added a pg_visibility debug module that provides Visibility Map tools, which tracks which elements are visible for all active transactions;
• support for the expression "ALTER TABLE ADD COLUMN... IF NOT EXISTS "to add a column only if it is not in the table;
• The ability to create GIN indexes with any maintenance_work_mem value
• a system view of the pg_config that reflects the parameters of the DBMS assembly;

Comparing Tibero and PostgreSQL

Corrective release of all branches

On February 11, 2016, the PostgreSQL developer community announced the release of corrective updates for all supported PostgreSQL branches: 9.5.1, 9.4.6, 9.3.11, 9.2.15 and 9.1.20, which fixed two vulnerabilities, introduced a portion of bug fixes, added support for Python 3.5 in PL/Python and provided the ability to share Python2 and Python3 in one database^[12].

Support for the 9.0.x branch has been discontinued. Release of updates for the branch:

• 9.1 extended until September 2016.
• 9.2 extended until September 2017,
• 9.3 extended until September 2018,
• 9.4 extended until December 2019,
• 9.5 extended until January 2021.

The first of the vulnerabilities (CVE-2016-0773) manifests itself in the regular expression engine and can lead to a backend crash when parsing regular expressions with characters outside the Unicode range (systems in which user input is used to generate a regular expression are affected).

The second vulnerability (CVE-2016-0766) is present in the PL/Java engine and allows you to elevate your privileges when working with the database.

PostgreSQL 9.5

On January 7, 2016, it became known about the release of the stable branch of the PostgreSQL 9.5 DBMS. The release of updates for the 9.5 branch will be supported until January 2021^[13].

Changes

• "UPSERT" functionality implemented through the new expression "INSERT... ON CONFLICT DO NOTHING/UPDATE, "which allows you to handle the situation that data cannot be added via" INSERT, "for example, due to a violation of the uniqueness conditions or an invalid value of one of the fields. Instead of displaying an error, you can now ignore the execution of the operator or change the data associated with the key field (that is, if the record already exists, instead of INSERT, run UPDATE);
• Row-Level Security (RLS). Users' access to data in the table can now be delimited at the level of individual rows, for example, you can prevent a certain category of users from viewing the rows in which data added by another user is stored. To activate RLS, use the "ALTER TABLE tablename ENABLE ROW LEVEL SECURITY" directive, and then set access rules using the "CREATE POLICY" expression;
• BRIN indices (Block Range Index), which allow you to index very large tables in an ultra-compact manner, without using traditional B-trees. The essence of BRIN indices boils down to dividing a common index into blocks, each of which contains index data only for a certain range of values. In the test, this method turned out to be about twice as slow as b-trees when performing data sampling operations, but 3-4 times faster when creating and updating an index, and also took up significantly less disk space (64 KB versus 28 MB);
• New functions and operators for the JSONB data type. To change the values in a JSONB document, you can now do without retrieving and redefining the entire document, thanks to the jsonb_set () function. The functions json_strip_nulls (deleting attributes containing NULL values) and jsonb_pretty (output in formatted JSON) are also added. Added operator '| |' to join two JSONB values;
• A pg_rewind tool that greatly simplifies the process of restoring fault-tolerant configurations after switching to a standby server. After the main server returns to service, the task arises of synchronizing its state with the spare server that continued to work, which managed to accumulate its portion of changes. The utility pg_rewind tries to restore the state of the primary server via the WAL transaction log, sorting through them from the moment shortly before the failure, determining the changed data and transferring only the changed blocks, which allows you to do without restoring a full copy from the running backup server.
• Significantly optimized in-memory sorting and hashing rates. Thanks to the use of a new method for sorting string values ​ ​ and numbers, it was possible to increase the speed of creating indexes up to 20 times, and reduce the time for queries requiring sorting large amounts of data by 2-12 times;
• Added support for the TABLESAMPLE expression, which allows you to generate a sample over an incomplete amount of data from large tables, without performing resource-intensive sorting operations on the entire table. For example, the query "SELECT * FROM test TABLESAMPLE SYSTEM (10)" will generate an output that covers only 10% of the test table. Several algorithms are available to weed out values during the partial sampling process;
• Improved scaling on systems with a large number of processor cores and RAM. For example, on a system with 24 CPU cores and 496 GB of RAM in the EnterpriseDB test with a load of 64 simultaneous connections, PostgreSQL 9.5 showed a performance increase of 96% compared to PostgreSQL 9.4;
• Automated management of transaction log size. The ability to exclude the reflection of tables in the transaction log (ALTER TABLE... SET LOGGED / UNLOGGED);
• Analytical capabilities "GROUPING SETS," "CUBE" and "ROLLUP," which allow you to form an output with a grouping by a set of fields and calculate the number of combinations of different categories;
• Improved replication and fault tolerance. A mechanism for tracking the state of replication execution has been added, including methods for determining the cause of individual changes during logical replication;
• Multiple improvements have been made to the Foreign Data Wrappers engine, including the expression "IMPORT FOREIGN SCHEMA," which allows you to automate the import of all related external tables for existing tables with the selected server label. In addition, it is possible to inherit external tables in local tables and vice versa, for example, "CREATE local_customers () inherits (remote.customers);"
• The -j option has been added to the vacuumdb utility, which allows you to run VACUUM in several simultaneously executed threads.

2015

Concurrent computing infrastructure in PostgreSQL

On May 4, 2015, it became known about the adoption of changes tree DBMS in the PostgreSQL source code with the implementation of the parallel computing infrastructure^[14]

It provides:

• Convenient procedures for coordinating the launch and completion of parallel workflows;
• Synchronization of various internal states (GUCs, combined CID mapping, transaction snapshots) between the leader of the parallel work group and directly parallelized workflows;
• Limiting the invocation of various operations that can lead to incorrect changes in the conditions of active parallelization;
• Delivering notifications to the client via ErrorResponse, NoticeResponse, and NotifyResponse messages from concurrent handlers.

Postgres-XL on EcoServer - Alternative for Data Center

On August 13, 2015, it became known that the tests of the Postgres-XL database management system on the EcoServer servers were completed.

Postgres-XL - DBMS c open source for large data analysis, used in banking and business analytics. The launch DBMS of Postgres-XL on servers, according "Ricore" to the company's management, Ricore enables Russian customers to use a software solution for information processing on an affordable domestic hardware platform and thereby reduce dependence on imported IT solutions.

"Adaptation of the free ON to the needs of Russian companies is the niche in which domestic IT developers can successfully work," said the Boris Ivanov vice president. "Our "Ricore" software and hardware solution, consisting of an energy efficient EcoServer DBMS Postgres-XL server, is an excellent alternative for use by various domestic organizations, including in the banking sector, which previously used expensive database management software from foreign vendors."

Postgres-XL DBMS is focused on creating real-time cluster transaction processing systems, working with large databases for analyzing large data sets. The Postgres-XL code is distributed under a free Mozilla license. Optimization of processes in Postgres-XL occurs by horizontal scaling and mass parallel computing.

PostgreSQL 9.5 Beta Announcement

On October 8, 2015, the PostgreSQL DBMS development community introduced the beta version of PostgreSQL 9.5 DBMS.

Among the changes in this version:

• "UPSERT" functionality implemented through the new expression "INSERT... ON CONFLICT DO NOTHING/UPDATE, "which allows you to handle the situation that data cannot be added via" INSERT, "for example, due to a violation of the uniqueness conditions or an invalid value of one of the fields. Instead of displaying an error, you can now ignore the execution of the operator or change the data associated with the key field (that is, if the record already exists, instead of INSERT, run UPDATE);
• BRIN indices (Block Range Index), which allow you to index very large tables in an ultra-compact manner, without using traditional B-trees. The essence of BRIN indices boils down to dividing a common index into blocks, each of which contains index data only for a certain range of values. In the test, this method turned out to be about twice as slow as b-trees when performing data sampling operations, but 3-4 times faster when creating and updating an index, and also took up significantly less disk space (64 KB versus 28 MB);
• Row-Level Security (RLS). Users' access to data in the table can now be delimited at the level of individual rows, for example, you can prevent a certain category of users from viewing the rows in which data added by another user is stored. To activate RLS, use the "ALTER TABLE tablename ENABLE ROW LEVEL SECURITY" directive, and then set access rules using the "CREATE POLICY" expression;
• New functions and operators for the JSONB data type. To change the values in a JSONB document, you can now do without retrieving and redefining the entire document, thanks to the jsonb_set () function. The functions json_strip_nulls (deleting attributes containing NULL values) and jsonb_pretty (output in formatted JSON) are also added. Added operator '| |' to join two JSONB values;
• A pg_rewind tool that greatly simplifies the process of restoring fault-tolerant configurations after switching to a standby server. After the main server returns to service, the task arises of synchronizing its state with the spare server that continued to work, which managed to accumulate its portion of changes. The utility pg_rewind tries to restore the state of the primary server via the WAL transaction log, sorting through them from the moment shortly before the failure, determining the changed data and transferring only the changed blocks, which allows you to do without restoring a full copy from the running backup server.
• Significant optimizations in the speed of sorting and hashing in memory. Thanks to the use of a new method of sorting string values ​ ​ and numbers, it was possible to increase the indexing speed by three times;
• Automated management of transaction log size. The ability to exclude the reflection of tables in the transaction log (ALTER TABLE... SET LOGGED / UNLOGGED);
• Analytical capabilities "GROUPING SETS," "CUBE" and "ROLLUP," which allow you to form an output with a grouping by a set of fields and calculate the number of combinations of different categories;
• Improved replication and fault tolerance;
• Multiple enhancements to the Foreign Data Wrappers engine, including the expression "IMPORT FOREIGN SCHEMA," which automates the import of all associated external tables for existing tables with the selected server label. In addition, it is possible to inherit external tables in local tables and vice versa, for example, "CREATE local_customers () inherits (remote.customers);"
• The "-j" option has been added to the vacuumdb utility, which allows you to run VACUUM in several simultaneously executed threads;
• Scaling improvements have been made on systems with a large number of processor cores and RAM.

PostgreSQL supports query parallelization

On November 12, 2015, the development community announced the adoption of 9.6 changes in the experimental branch, on the basis of which the PostgreSQL release will be formed, with the implementation of parallelizing Sequential Scan operations used to enumerate values ​ ​ in the case of sampling by non-indexed fields or when manipulating the contents of fields^[15].

Enumerating into several parallel threads will significantly increase the speed of enumeration of data on systems with a large number of processor cores. The gain is especially noticeable for resource-intensive queries, such as regular expression mapping.

Executing the test query "select * from pgbench_accounts where filler like '% a%'" normally takes 743 ms, while parallelizing in four streams gives a result of 213 ms.

When parallelizing, the scan operation is divided into parts and each part is disassembled by a separate handler, after which the results of each handler are combined.

Performance comparison with licensed DBMS

On May 18, 2015, Indigo IT"" reported the results of comparative testing of the performance of the most popular in the market SOUP DBMS - in individual open-source test kits DBMS PostgreSQL it was three times better and. MS SQL Oracle DataBase

Testing was carried out in order to monitor new technologies and implement the technological development plan for 2015.

Andrey Chernogorov, CEO, Indigo IT"" noted: "Today, the most popular in the IT market are DBMS MS SQL and. Oracle DataBase At the same time, in a number of key opportunities, it is not inferior to them, and in some places it is superior, DBMS with open codes, PostgreSQL which opens up wide prospects for its use as part of the import substitution program. "

For testing, the company's specialists prepared test data sets identical for all DBMS. The object of the tests was a database of 1 TB, consisting of 1 million business facilities. The duration of testing for each DBMS is 10 hours.

It was attended by the latest versions of the most popular customers Indigo IT"": DBMS

• Microsoft SQL Server 2014,
• Oracle DataBase 12c
• Open DBMSostgreSQL 9.4.

A total of 5 test kits were performed:

• Creating hard-to-structure documents
• Updating hard-to-structure documents
• search for documents,
• writing the file to the database
• retrieving a file from the database.

Test results, 2015

By the time spent in each of the test sets shown in the table is meant the average of all sets (ms). Testing was carried out on servers with Intel Xeon Ye5 v3 processors with 128 GB of RAM.

As a result of load testing on two out of five test sets (creating difficult structured documents, updating difficult structured documents), PostgreSQL 9.4 performed almost three times better than competitors. In other tests (searching for documents, writing and receiving files from the database), the test participants showed almost the same results.

Andrey Chernogorov announced the company's intention to recommend to customers, for the company's solutions, PostgreSQL is not only because this DBMS is distributed free of charge, but because it demonstrates higher performance indicators on Indigo IT solutions. He expressed confidence that this will become an argument in favor of the transition to domestic and open decisions within the framework of the import substitution program announced by the President of Russia.

2010

PostgreSQL 9.4

The support of this version of DBMS open source with PostgreSQL for the widespread JSON data exchange format is aimed at the growing market for non-relational NoSQL data warehouses and especially the popular one. DBMS MongoDB

The first beta of PostgreSQL 9.4 introduced a number of new features focused on the rapidly expanding web application market, many of which require fast storage and a selection of large amounts of user data.

PostgreSQL 9.4 supports the JSON (JavaScript Simple Object Notation) format, which quickly gained popularity when organizing data exchange between various systems, including using the REST (Representational State Transfer) protocol. The success of the MongoDB documentary database is largely due to the growing popularity of JSON.

The structured PostgreSQL format for storing data according to JSON specifications (JSONB) eliminates the need to restructure the document before entering it into the database. As a result, PostgreSQL swallows documents as quickly as MongoDB, while continuing to meet the ACID (atomicity, consistency, isolation, durability - atomicity, consistency, isolation and reliability) requirements that are imposed on storing information in databases. In addition, PostgreSQL supports a complete set of index services, functions and operators to efficiently manipulate JSON data.

Previous versions of PostgreSQL also supported JSON, but JSON documents were saved in text format, as a result of which their recording and sampling operations took much longer.

PostgreSQL has received a number of new features:

• A new API for decoding data from a replication stream opens the way for independent software developers to create faster replication systems.
• The new Materialized Views feature, called "simultaneous update," allows you to update final reports on the fly.
• The Alter System Set function will help administrators modify the PostgreSQL configuration file directly from the SQL command line.

A number of functions and features have been added, including Dynamic Background Workers, array manipulation and table functions, and overall performance has been increased.

PostgreSQL 9.3

PostgreSQL 9.3 implements a number of mechanisms that allow you to exchange information with other databases and data stores. Foreign Data Wrapper modules, which appeared in version 9.1 and previously only allowed reading data from other systems, now provide the ability to write. It supports both relational tables and semi-structured information from NoSQL systems. A driver has also been created for the DBMS that allows you to link two different copies of PostgreSQL itself to each other and provides accelerated transaction execution between them.

Other features include enhanced JSON support and the ability to create arbitrary background server modules with unlimited access to PostgreSQL data. An example is the Mongres module, which automatically translates MongoDB queries into PostgreSQL format.

An automatic view update has been implemented and a utility has been added that allows you to back up large databases in parallel. Measures have been taken to improve the reliability of the DBMS. Fast Failover allows you to switch work from the master database to a copy in less than a second. You can check page checksums to help diagnose hard drive failures.

PostgreSQL 9.2

Developers DBMS open source with PostgreSQL continue to optimize it for enterprise use: the new version can be executed on those servers with up to 64 processor cores, while the previous maximum was 16. PostgreSQL 9.2 also supports JSON queries, so web application developers can access the DBMS as a key-value NoSQL store using familiar tools like jQuery.

As for performance, the system in the new version is capable of processing up to 350 thousand read requests per second, four times more than Postgres 9.1. And the maximum recording speed increased five times, reaching 14 thousand operations per second.

Other features include an exclusive index search mechanism that speeds up some queries by 20 times, and the ability to store and poll ranges of data of a given type; as the developers explain, this feature gives greater flexibility than other relational RDBMS, where such problems are solved using two columns.

Postgres has improved lock management to speed up large workloads. The Postgres 9.2 build is also released for HP-UX operating systems running on Itanium servers.

PostgreSQL 9.1

PostgreSQL Global Development Group introduced a new version of the PostgreSQL 9.1.0 database server. The main changes in the new version:

• Synchronous replication in a distributed database
• Support for external tables, that is, reading files outside the database as tables (as long as writing is not supported)
• Support for COLLATE clause to select character sorting (collation)
• Support for server extensions
• full isolation of serial transactions (in previous versions, some anomalies may have occurred, which have now been eliminated); the transaction isolation algorithm now matches the Repeatable read level;
• The ability to create non-scanned tables using the UNLOGGED option in the CREATE TABLE command.
• The ability to change data (INSERT/UPDATE/DELETE) in the WITH clause.
• GiST indices now provide a quick search for nearby neighbors;
• Added support for SELinux and the SECURITY LABEL command
• added a number of new server programming features using PL/Python.

Almost a year has passed since the release of version 9.0, from the moment of the last update in branch 9. * (9.0.4) - about 4 months, and from the moment of the release of the first and last release candidate (9.1 RC1) - 20 days. The server is distributed under its own license, similar to the BSD license and approved by the Open Source Initiative.

PostgreSQL 9.0

The developers of the open database management system PostgreSQL released in September 2010 the first release candidate of the Postrgesql 9.0 system, which implements all the functions prepared for release in the ninth version of this popular DBMS. The binary version of Postgresql 9.0 pre-build is currently available in the public domain and everyone can test the new capabilities of this development before transferring production servers working with real information to it.

Also in the ninth version, it became possible to replicate information from binary logs, corresponding to the Hot Stanby Databases mechanism in Oracle Database. The developers did not ignore the growing popularity of cloud or SaaS systems. Now the DBMS is optimized for working in a virtual machine environment, supports a mechanism for fast data cloning, as well as the ability to replicate information from a single master server to a large number (more than a hundred) slave servers. Also, the new release fully supports memory addressing capabilities in 64-bit versions of Windows.

Notes