The PostgreSQL team has announced the release of PostgreSQL 12, the latest version of the open source relational database management system.
PostgreSQL 12 has significantly improved query performance - especially when working with large volumes of data, and has also optimized the use of disk space in general.
New features include:
- implementation of the JSON Path query language (the most important part of the SQL/JSON standard);
- optimization of execution of common table expressions (WITH);
- support for generated columns
The community also continues to work on the extensibility and reliability of PostgreSQL, developing support for internationalization, authentication capabilities, and providing easier ways to administer the system.
This release includes the implementation of an interface for pluggable storage engines, which now allows developers to create their own data storage methods.
Performance improvements
PostgreSQL 12 includes significant performance and maintenance improvements for indexing and partitioning systems.
B-tree indexes, the standard indexing type in PostgreSQL, have been optimized in version 12 for workloads that involve frequent index modifications. Using the TPC-C benchmark for PostgreSQL 12 demonstrated an average 40% reduction in space usage and an overall increase in query performance.
Queries against partitioned tables have received noticeable improvements, especially for tables consisting of thousands of partitions that require working with only limited parts of the data arrays. The performance of adding data to partitioned tables using INSERT and COPY has been improved, as well as the ability to attach a new partition without blocking queries.
PostgreSQL 12 has made additional improvements to indexing that impact overall performance, including:
- reduced overhead when generating WAL for GiST, GIN and SP-GiST index types;
- the ability to create so-called covering indexes (INCLUDE clause) on GiST indexes;
- the ability to perform βnearest neighborβ queries (k-NN search) using the distance operator (<->) and using SP-GiST indexes;
- support for collecting most-common value (MCV) statistics using CREATE STATISTICS, which helps obtain better query plans when using columns whose values ββare unevenly distributed.
JIT compilation using LLVM, introduced in PostgreSQL 11, is now enabled by default. JIT compilation improves performance when working with expressions in WHERE clauses, target lists, aggregates, and some internal operations. It is available if you have compiled PostgreSQL with LLVM or are using a PostgreSQL package that was built with LLVM enabled.
Improvements to SQL language capabilities and standard compatibility
PostgreSQL 12 introduced the ability to query JSON documents using JSON path expressions defined in the SQL/JSON standard. Such queries can leverage existing indexing mechanisms for documents stored in JSONB format to efficiently retrieve data.
Common table expressions, also known as WITH queries, can now be automatically executed using substitution in PostgreSQL 12, which in turn can help improve the performance of many existing queries. In the new version, a substitution portion of a WITH query can only be executed if it is not recursive, has no side effects, and is referenced only once in a subsequent portion of the query.
PostgreSQL 12 introduces support for "generated columns". Described in the SQL standard, this column type calculates a value based on the contents of other columns in the same table. In this version, PostgreSQL supports "stored generated columns", where the calculated value is stored on disk.
Internationalization
PostgreSQL 12 expands support for ICU collations by allowing users to define "non-deterministic collations" which can, for example, allow case-insensitive or accent-insensitive comparisons.
Authentication
PostgreSQL expands its support for strong authentication methods with several enhancements that provide additional security and functionality. This release introduces client-side and server-side encryption for authentication over GSSAPI interfaces, as well as the ability for PostgreSQL to discover LDAP servers when PostgreSQL is compiled with OpenLDAP.
Additionally, PostgreSQL 12 now supports a multi-factor authentication option. The PostgreSQL server can now require the client to provide a valid SSL certificate with the corresponding username using clientcert=verify-full, and combine this with a separate authentication method requirement (e.g. scram-sha-256).
Administration
PostgreSQL 12 introduced the ability to perform non-blocking index rebuilds using the REINDEX CONCURRENTLY command. This allows users to avoid DBMS downtime during lengthy index rebuilds.
Additionally, in PostgreSQL 12, you can enable or disable page checksums in a shutdown cluster using the pg_checksums command. Previously, page checksums, a feature that helps verify the integrity of data stored on disk, could only be enabled when the PostgreSQL cluster was initialized using initdb.
Source: linux.org.ru