|PostgreSQL 12 Released|
|Written by Kay Ewbank|
|Monday, 14 October 2019|
PostgreSQL 12 has been released with improvements to partitioning and handling of btrees, along with support for multi-column most-common-value statistics.
PostgreSQL is an open source database system that was originally created at the University of California, Berkeley. It is now maintained and developed by the PostgreSQL Global Development Group, a coalition of many companies and individual contributors. PostgreSQL runs on all major operating systems, has been ACID-compliant since 2001, and has add-ons such as the PostGIS geospatial database extender.
The developers say this release offers notable improvements to query performance, particularly over larger data sets. It also offers SQL/JSON path expression support, optimizations for how common table expression (
PostgreSQL 12 provides significant performance and maintenance enhancements to its indexing system and to partitioning. B-tree Indexes which are used as standard in PostgreSQL have been optimized to deal better with where the indexes are frequently modified.
Queries on partitioned tables have also been improved, especially tables with thousands of partitions that only need to retrieve data from a limited subset. Adding data to such tables is also faster, and there's a new option to attach a new partition to a table without blocking queries.
Elsewhere in indexing, Create Statistics now supports most-common value (MCV) statistics to help generate better query plans when using columns that are nonuniformly distributed.
The Just in Time compilation using LLVM that was added in PostgreSQL 11 is now enabled by default to improve performance in situations including the execution of expressions in Where clauses.
New in this release is the ability to run queries over JSON documents using JSON path expressions, and the ability to inline With queries. PostgreSQL 12 also introduces generated columns that have their values computed from the contents of other columns in the same table.
The final main improvement is to authentication, with the addition of support for both client and server-side encryption for authentication over GSSAPI interfaces, as well as the ability for PostgreSQL to discover LDAP servers if PostgreSQL is compiled with OpenLDAP. PostgreSQL 12 also now supports a form of multi-factor authentication whereby the server can require an authenticating client to provide a valid SSL certificate with their username, along with requiring a separate authentication method.
or email your comment to: firstname.lastname@example.org