After almost a year of development the release of the new version of the stable branch was announced of the DBMS PostgreSQL 14 whose updates for the new branch will be published for five years until November 2026.
For those who are still unfamiliar with PostgreSQL, you should know that it is also known as Postgres and this is a relational database management system (RDBMS) free, open source, which aims to offer a database based on extensibility and compliance with technical standards.
It is designed to handle a variety of workloads, from simple machines to data warehouses or web services with many concurrent users.
What’s new in PostgreSQL 14?
In this new version the range definition type family has been expanded with new “multiple range” types that allow define ordered lists of ranges of values that do not overlap. In addition to each existing range type, its own multiple range type is proposed. The use of new types simplifies the design of queries that manipulate complex sequences of ranges.
Too capabilities expanded for distributed configurations which include multiple PostgreSQL servers. In implementing logical replication, it was possible to stream transactions in progress, which can significantly improve replication performance of large transactions. In addition, the logical decoding of the data arriving during logical replication has been optimized.
further added support for working on the client side (implemented in libpq) Transporter mode transmission requests to significantly speed up the scenarios of a database associated with the implementation of a large number of small write operations (INSERT / UPDATE / DELETE) due to sending next request without waiting for the result of the above. The mode also helps speed up work on connections with long delays in package delivery.
The external data container mechanism (postgres_fdw) to connect external tables has added support for parallel query processing, which is currently only applicable when connecting to other PostgreSQL servers. Postgres_fdw also adds support for adding data to external tables in batch mode and the ability to import partitioned tables by specifying the “IMPORT FOREIGN SCHEMA” directive.
What’s more, optimizations were made to the implementation of the VACUUM operation (garbage collection and packing disk storage), added “emergency mode” to skip nonessential wrapper operations if transaction ID wrapper conditions are created and reduced overhead when processing B-Tree indexes. The execution of the “ANALYZE” operation, which collects statistics on the operation of the database, has been significantly accelerated.
On the other hand, it is also highlighted that tools have been expanded to monitor the operation of the DBMS, then I know added views to track command progress “COPY”, statistics about replication slots and WAL transaction log activity.
In PostgreSQL 14 we can also find that added the ability to customize the compression method used in the TOAST system, which is responsible for storing large data such as blocks of text or geometric information. In addition to the pglz compression method, TOAST can now use the LZ4 algorithm.
Have been added query scheduler optimizations to improve parallel query processing and to improve the performance of the simultaneous execution of sequential record scans, the execution of queries in parallel in PL / pgSQL using the command “RETURN QUERY” and the execution of queries in parallel in “REFRESH MATERIALIZED VIEW”.
Of the other changes that stand out:
- Additional caching support has been implemented to improve the performance of nested circular joins (join).
- Optimizations have been made to improve the performance of heavily loaded systems that handle a large number of connections. In some tests, the performance has doubled.
- Performance of B-tree indexes has been improved and an issue with index growth when tables are frequently updated has been resolved.
- Now extended statistics can be used to optimize expressions and incremental sorts can be used to optimize window functions.
Finally sIf you are interested in knowing more about it, you can check the details In the following link.