The PostgreSQL development 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 optimized overall disk space usage.
Among the new features:
- implementation of the JSON Path query language (a key part of the SQL/JSON standard);
- optimization of common table expressions (WITH);
- support for generated columns
The community continues to work on the extensibility and reliability of PostgreSQL, enhancing support for internationalization, authentication capabilities, and providing simpler administration paths.
This release features the implementation of a pluggable storage engine interface, allowing developers to create their own data storage methods.
Performance Improvements
PostgreSQL 12 includes significant performance improvements and maintenance procedures for indexing and partitioning systems.
B-tree indexesāthe standard type of indexing in PostgreSQLāhave been optimized in version 12 for workloads that involve frequent index modifications. Benchmarks using the TPC-C test for PostgreSQL 12 demonstrated a 40% reduction in space usage and an overall increase in query performance.
Queries on partitioned tables have received notable enhancements, particularly for tables comprising thousands of partitions that involve working only with limited parts of data arrays. The performance of data insertion into partitioned tables using INSERT and COPY has been improved, along with the ability to attach a new partition without blocking executing queries.
PostgreSQL 12 has made additional enhancements in indexing that affect overall performance, including:
- reduced overhead in WAL generation for GiST, GIN, and SP-GiST index types;
- the ability to create so-called covering indexes (covering indexes, using the INCLUDE clause) on GiST indexes;
- the ability to perform k-nearest neighbor queries (k-NN search) using the distance operator () and utilizing SP-GiST indexes;
- Support for collecting statistics on the most common values (most-common value, MCV) using CREATE STATISTICS, which helps generate better query execution plans when using columns with unevenly distributed values.
JIT compilation using LLVM, introduced in PostgreSQL 11, is now enabled by default. JIT compilation enhances performance when working with expressions in WHERE clauses, target lists, aggregates, and some internal operations. It is available if you compiled PostgreSQL with LLVM or are using a PostgreSQL package built with LLVM enabled.
Improvements to SQL language capabilities and compliance with standards.
PostgreSQL 12 introduces 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 for efficient data retrieval.
Common Table Expressions (CTEs), also known as WITH queries, in PostgreSQL 12 can now automatically be executed using substitution, which can, in turn, help improve the performance of many existing queries. In the new version, part of a WITH query can be executed with substitution only if it is not recursive, has no side effects, and is referenced only once in the subsequent part of the query.
PostgreSQL 12 introduces support for "generated columns." Defined in the SQL standard, this type of column computes its value based on the contents of other columns in the same table. In this version, PostgreSQL supports "stored generated columns," where the computed value is stored on disk.
Internationalization
PostgreSQL 12 expands support for ICU collations, allowing users to define "nondeterministic collations" that can, for example, permit case-insensitive or accent-insensitive comparisons.
Authentication
PostgreSQL expands its support for reliable authentication methods through several enhancements that provide additional security and functionality. This release features client-side and server-side encryption for authentication via GSSAPI interfaces, along with PostgreSQL's ability to detect servers LDAP, if 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 introduces the ability to perform non-blocking index rebuilds using the REINDEX CONCURRENTLY command. This allows users to avoid downtime in the DBMS during lengthy index rebuilds.
Furthermore, in PostgreSQL 12, you can enable or disable page checksums in a cluster that is powered off using the pg_checksums command. Previously, page checksumsāa feature that helps verify the integrity of data stored on diskācould only be enabled at the time of cluster initialization with initdb.
Source: linux.org.ru
