The PostgreSQL development team has announced the release of PostgreSQL 12, the latest version of the open-source relational database management system.
\nPostgreSQL 12 has significantly improved query performance\u2014especially when working with large volumes of data\u2014and has optimized overall disk space usage.<\/p>\n
Among the new features:<\/p>\n
The community continues to work on the extensibility and reliability of PostgreSQL, enhancing support for internationalization, authentication capabilities, and providing simpler administration paths.<\/p>\n
This release features the implementation of a pluggable storage engine interface, allowing developers to create their own data storage methods.<\/p>\n
Performance Improvements<\/strong><\/p>\n PostgreSQL 12 includes significant performance improvements and maintenance procedures for indexing and partitioning systems.<\/p>\n B-tree indexes\u2014the standard type of indexing in PostgreSQL\u2014have 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.<\/p>\n 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.<\/p>\n PostgreSQL 12 has made additional enhancements in indexing that affect overall performance, including:<\/p>\n 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.<\/p>\n Improvements to SQL language capabilities and compliance with standards.<\/strong><\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n Internationalization<\/strong><\/p>\n PostgreSQL 12 expands support for ICU collations, allowing users to define \"nondeterministic collations\" that can, for example, permit case-insensitive or accent-insensitive comparisons.<\/p>\n Authentication<\/strong><\/p>\n 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<\/a> LDAP, if PostgreSQL is compiled with OpenLDAP.<\/p>\n Additionally, PostgreSQL 12 now supports a multi-factor authentication option. The PostgreSQL server can now require the client to provide a valid SSL<\/a>-certificate with the corresponding username using clientcert=verify-full, and combine this with a separate authentication method requirement (e.g., scram-sha-256).<\/p>\n\n