After a year of development new stable branch of the PostgreSQL 12 DBMS. Updates for the new branch for five years until November 2024.
Key :
- Support for "", whose values are computed based on an expression encompassing 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 computed at the moment of adding or changing data, while in the second case, it is calculated upon each read based on the current state of other columns. Currently, only stored generated columns are supported in PostgreSQL;
- The ability to execute data queries from JSON documents using , resembling and defined in the SQL/JSON standard. To enhance the efficiency of processing such expressions for documents stored in JSONB format, existing indexing mechanisms are utilized;
- The use of a JIT (Just-in-Time) compiler based on LLVM developments is enabled by default to speed up the execution of certain expressions during SQL query processing. For instance, JIT can accelerate the execution of expressions within "WHERE" blocks, in target lists, aggregate expressions, and some internal operations;
- The performance of indexing has significantly increased. B-tree indexes have been optimized for working under conditions of frequent index changes — in TPC-C tests, an overall increase in performance and a reduction in disk space consumption by an average of 40% has been observed. Overhead associated with generating the Write Ahead Log (WAL) for GiST, GIN, and SP-GiST index types has been reduced. For GiST, the ability to create covering indexes (using the INCLUDE expression) that include additional columns has been added. In the operation support for statistics on most common values (MCV) has been implemented, allowing for the generation of more optimal query plans when using unevenly distributed columns;
- The implementation of partitioning is optimized for queries involving tables with thousands of partitions, while limiting the data selection to a small subset. The performance of data addition to partitioned tables has been improved using INSERT and COPY operations, and the ability to add new partitions via 'ALTER TABLE ATTACH PARTITION' has been provided without blocking query execution;
- Support for automatic inline expansion of Common Table Expressions (, CTE) has been added, allowing the use of temporary named result sets defined using the WITH operator. Inline expansion can enhance the performance of most queries, but is currently only applicable for non-recursive CTEs;
- Support added locale 'Collation' properties that specify sorting rules and comparison methods considering the meaning of characters (for instance, when sorting numeric values, the presence of a minus sign and a decimal point is taken into account, and for comparisons, character case and accents are disregarded);
- Support for multi-factor client authentication has been added, allowing the combination of SSL certificate authentication (clientcert=verify-full) with an additional authentication method such as scram-sha-256 in pg_hba.conf;
- Support for encrypting the communication channel during authentication via , both on the client side and the server side, has been added;
- Support for defining LDAP servers based on 'DNS SRV' records has been added, provided that PostgreSQL is built with OpenLDAP;
- An operation '' has been added for rebuilding indexes without blocking write operations to the index;
- A command has been added , allowing the enabling and disabling of data page checksum verification for an existing database (previously, this operation was only supported during database initialization);
- A progress indicator for CREATE INDEX, REINDEX, CLUSTER, VACUUM FULL, and pg_checksums operations has been provided;
- A command '' has been added for connecting handlers of new storage methods optimized for various specific tasks. Currently, the only built-in access method for tables is 'heap';
- The configuration file recovery.conf has been merged with postgresql.conf. The transition indicators to recovery state after a failure now use the recovery.signal and standby.signal files.
Source: opennet.ru
