After a year of development new stable branch of PostgreSQL 12 DBMS. Updates for the new branch for five years until November 2024.
All :
- Added support for“, whose value is calculated based on an expression covering the values of other columns in the same table (similar to views, but for individual columns). Generated columns can be of two types - stored and virtual. In the first case, the value is calculated at the time of adding or changing data, and in the second case, on each read, based on the current state of other columns. Only stored generated columns are currently supported in PostgreSQL;
- Added the ability to query data from JSON documents using resembling and defined in the SQL/JSON standard. To improve the efficiency of processing such expressions for documents stored in JSONB format, existing indexing mechanisms are used;
- By default, the use of a JIT compiler (Just-in-Time) based on LLVM developments is enabled to speed up the execution of some expressions during the processing of a SQL query. For example, JIT is useful for speeding up expressions inside WHERE blocks, target lists, aggregate expressions, and some internal operations;
- Significantly improved indexing performance. B-tree indexes are optimized to work in conditions of frequent change of indexes - in the TPC-C tests, there is an overall performance increase and a reduction in disk space consumption by an average of 40%. Reduced write-ahead log (WAL) overhead for GiST, GIN, and SP-GiST index types. Added the ability to create wrapper indexes (through an INCLUDE statement) for GiST that include additional columns. In operation support for Most Typical Values (MCV) statistics to generate more optimal query plans when using non-uniformly distributed columns;
- The implementation of partitioning (partitioning) is optimized for queries covering tables with thousands of partitions, but limited to fetching a limited subset of data. The performance of adding data to partitioned tables using the INSERT and COPY operations has been increased, and the ability to add new partitions through the "ALTER TABLE ATTACH PARTITION" without blocking query execution has been provided;
- Added support for automatic inline expansion of common table expressions (, CTE) that allow the use of temporary named result sets specified using the WITH clause. Inline deployment improves the performance of most queries, but so far it is only used for non-recursive CTEs;
- Added support properties of the “Collation” locale, which allows you to set sorting rules and collation methods taking into account the meaning of characters (for example, when sorting digital values, the presence of a minus and a dot in front of the number and different types of spelling are taken into account, and when comparing, the case of characters and the presence of an accent mark are not taken into account) ;
- Added support for multi-factor client authentication, in which pg_hba.conf for authentication can combine SSL certificate authentication (clientcert=verify-full) with an additional authentication method, such as scram-sha-256;
- Added support for encryption of the communication channel when authenticating through , both on the client side and on the server side;
- Added support for defining LDAP servers based on "DNS SRV" records if PostgreSQL is built with OpenLDAP;
- Added operation "» to rebuild indexes without blocking writes to the index;
- Team added , which allows for the existing database to enable and disable the checksum check of data pages (previously this operation was supported only during the initialization of the database);
- Provided a progress indicator for the CREATE INDEX, REINDEX, CLUSTER, VACUUM FULL and pg_checksums operations;
- Added command "» to connect handlers for new table storage methods optimized for various specific tasks. Currently, the only built-in table access method is "heap";
- The recovery.conf configuration file has been merged into postgresql.conf. As indicators of the transition to a state of recovery after a failure, now use recovery.signal and standby.signal files.
Source: opennet.ru