PostgreSQL 14, the latest version of the popular open source database, has arrived, with capabilities focused on performance, complex data types, and distributed data workloads.Published on September 30, PostgreSQL 14 can be downloaded from postgresql.org.For workloads that use many connections, the PostgreSQL 14 upgrade has achieved a doubling of performance on some benchmarks. Heavy workloads, and workloads with many small write operations, also benefit from the new ability to pipeline queries to a database, which can boost performance over high-latency connections. This client-side feature can be used with any modern PostgreSQL database with the version 14 client or a client driver built with version 14 of libpq.For distributed workloads, use of logical replication can stream in-progress transactions to subscribers, with performance benefits for applying large transactions. And foreign data wrappers, for working with federated workloads across PostgreSQL and other databases, now can leverage query parallelism in PostgreSQL 14.Elsewhere in PostgreSQL 14:
Query planning and execution have been enhanced. Improved support for query parallelism includes better performance of parallel sequential scans. Queries using nested loop joins may benefit through additional caching in PostgreSQL 14.
Range types now support contiguous ranges through a “multirange” data type. A multirange is an ordered list of ranges that do not overlap, allowing developers to write simpler queries to work with complex range sequences.
JSON data can be accessed using subscripts.
Performance has been improved in the vacuuming system for database maintenance. Optimizations include reducing the overhead of B-trees.
Stored procedures allowing for transaction control in a block of code now can return data by using OUT parameters.
Extended statistics now can be used for expressions.
Assigning read-only and write-only privileges to users on tables, views, and schemas has been made easier through pg_read_all_data and pg_write_all_data predefined roles.
Standards-compliant SCRAM-SHA-256 password management and authentication now is the default on new instances of PostgreSQL.
Copyright © 2021 IDG Communications, Inc.
Source link