PostgreSQL 14 is the next major release of PostgreSQL, the most powerful open source database globally. It introduces several new features that make it easier for developers and administrators to implement data-driven applications. PostgreSQL continues to make advancements in the area of complex data types, including more accessible JSON access and support for non-contiguous data ranges. With advancements in connection concurrency, high-write workloads, query parallelism, and logical replication, this latest version continues PostgreSQL’s history of boosting high performance and distributed data workloads.
Magnus Hagander, a PostgreSQL core team member, stated that “PostgreSQL’s most recent release improves our users’ capacity to handle data workloads at scale, improves observability, and includes new capabilities that benefit application developers. PostgreSQL 14 demonstrates the global PostgreSQL community’s commitment to resolving criticism and continuing to develop innovative database software that is utilised by both large and small enterprises.”
PostgreSQL 14 introduces a number of new features and enhancements, including the following:
- OUT parameters can now be used to return data from stored procedures.
- For common table expressions, the SQL-standard SEARCH and CYCLE options have been implemented.
- Subscripting can now be used with any data type for which the notation is useful, not only arrays. Subscripting operators have been added to the jsonb and hstore types in this release.
- Multiranges have been added to range types, enabling the representation of non-contiguous data ranges.
- Parallel queries, heavily concurrent workloads, partitioned databases, logical replication, and vacuuming have all seen significant performance increases.
- Updates to B-tree indexes are more efficiently managed, resulting in less index bloat.
- When the database approaches a transaction ID wraparound condition, VACUUM automatically becomes more aggressive and skips non-essential cleanup.
- Extended statistics on expressions can now be collected, allowing for more precise planning of outcomes for complex searches.
- libpq now supports pipelined queries, which can significantly increase throughput over high-latency connections.
Heavy Workload Performance Enhancements
PostgreSQL 14 significantly improves throughput for tasks that need a large number of connections, with some evaluations indicating a 2x speedup. This version builds on previous enhancements to B-tree index management by decreasing index bloat on tables with frequently updated indexes.
PostgreSQL 14 offers the ability to pipeline queries to a database, which can greatly increase performance across high-latency connections or for workloads with a high number of tiny write operations (INSERT/UPDATE/DELETE). Due to the fact that pipeline mode is a client-side feature, you can use it with any current PostgreSQL database using either the version 14 client or a client driver created using version 14 of libpq.
Improved Support for Distributed Workloads
PostgreSQL may now stream in-progress transactions to subscribers when utilising logical replication, resulting in significant performance gains when applying big transactions to subscribers. In addition, PostgreSQL 14 improves the performance of the logical decoding system, which is used to power logical replication.
In PostgreSQL 14, foreign data wrappers, which are used to interact with federated workloads across PostgreSQL and other databases, can now make use of query parallelism. This capability is implemented in postgres fdw, the foreign data wrapper that interfaces with other PostgreSQL databases, in this release.
Conveniences and Multiranges in JSON
Since PostgreSQL 9.2, it has allowed handling JSON data, however value retrieval requires a special syntax. PostgreSQL 14 now supports subscripts for accessing JSON data, e.g., SELECT (‘”postgres”: “release”: 14 ‘::jsonb)[‘postgres’][‘release’]; now works. This conforms PostgreSQL to generally used syntax for getting information from JSON data.
Range types, which were also introduced in PostgreSQL 9.2, now support non-contiguous ranges via the addition of the “multirange” data type. A multirange is an ordered collection of non-overlapping ranges that enables developers to construct simpler queries for complex sequences of ranges.
Enhancements to Security
PostgreSQL 14 simplifies the process of assigning read-only, and write-only capabilities to users on tables, views, and schemas using the predefined roles pg read all data and pg write all data. Additionally, this version changes the default password management and authentication system for new PostgreSQL instances to the standards-compliant SCRAM-SHA-256 password management and authentication system.
PostgreSQL 14 introduces new monitoring parameters:
5. REINDEX on child indexes of a partitioned table
Subscribe to our NewsletterGet the latest updates and relevant offers by sharing your email.
Nivash has a doctorate in Information Technology. He has worked as a Research Associate at a University and as a Development Engineer in the IT Industry. He is passionate about data science and machine learning.