Postgresql 14 Is Now Available, Check The New Features

Several new capabilities in PostgreSQL 14 will assist administrators and developers in deploying data-backed applications.

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:


Sign up for your weekly dose of what's up in emerging technology.
  • 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:

1. Idle_session_timeout

2. Pg_stat_wal

3. Pg_stat_progress_copy

4. Maintenance_io_concurrency

5. REINDEX on child indexes of a partitioned table

More Great AIM Stories

Dr. Nivash Jeevanandam
Nivash holds a doctorate in information technology and has been a research associate at a university and a development engineer in the IT industry. Data science and machine learning excite him.

Our Upcoming Events

Conference, in-person (Bangalore)
Machine Learning Developers Summit (MLDS) 2023
19-20th Jan, 2023

Conference, in-person (Bangalore)
Rising 2023 | Women in Tech Conference
16-17th Mar, 2023

Conference, in-person (Bangalore)
Data Engineering Summit (DES) 2023
27-28th Apr, 2023

Conference, in-person (Bangalore)
MachineCon 2023
23rd Jun, 2023

Conference, in-person (Bangalore)
Cypher 2023
20-22nd Sep, 2023

3 Ways to Join our Community

Whatsapp group

Discover special offers, top stories, upcoming events, and more.

Discord Server

Stay Connected with a larger ecosystem of data science and ML Professionals

Subscribe to our newsletter

Get the latest updates from AIM