The PostgreSQL Global Development Group announces that the first beta release of PostgreSQL 14 is now available for download. This release contains previews of all features that will be available in the final release of PostgreSQL 14, though some details of the release could change before then.
You can find information about all of the features and changes found in PostgreSQL 14 in the release notes:
https://www.postgresql.org/docs/14/release-14.html
In the spirit of the open source PostgreSQL community, we strongly encourage you to test the new features of PostgreSQL 14 in your systems to help us eliminate bugs or other issues that may exist. While we do not advise you to run PostgreSQL 14 Beta 1 in your production environments, we encourage you to find ways to run your typical application workloads against this beta release.
Your testing and feedback will help the community ensure that the PostgreSQL 14 release upholds our standards of delivering a stable, reliable release of the world's most advanced open source relational database. Please read more about our beta testing process and how you can contribute:
https://www.postgresql.org/developer/beta/
PostgreSQL 14 Feature Highlights
Performance
PostgreSQL 14 continues the trend of recent releases in providing performance benefits to workloads of all sizes.
This release has significant improvements in transaction throughput for PostgreSQL systems that have large numbers of connections to the database, regardless if they are in an active or idle state.
PostgreSQL 14 also contains additional gains in reducing B-tree indexing overhead, including reducing the bloat on tables with frequently updated indexes. GiST indexes can now presort data during their build process, allowing both for faster index creation and smaller indexes. SP-GiST indexes now support covering indexes that allow users to add additional nonsearchable columns to the index through the INCLUDE clause.
There are many improvements to query parallelism in PostgreSQL 14. In addition to overall performance improvements for parallel sequential scans, the RETURN QUERY directive in PL/pgSQL can now execute queries with parallelism. REFRESH MATERIALIZED VIEW can now use query parallelism as well.
PostgreSQL 14 also introduces the ability to leverage query parallelism when querying remote databases using foreign data wrappers. The PostgreSQL foreign data wrapper, postgres_fdw, added support for this in PostgreSQL 14 when the async_capable flag is set. postgres_fdw also supports bulk inserts and can import table partitions using IMPORT FOREIGN SCHEMA and can now execute TRUNCATE on foreign tables.
This release also has several improvements to the partitioning system, including performance gains when updating or deleting rows on tables where only a few partitions are affected. In PostgreSQL 14, partitions can now be detached in a non-blocking manner using the ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY command.
Incremental sorting, introduced in the previous release, can now be used by window functions in PostgreSQL 14. This new release adds more capabilities to extended statistics, which can now be applied to expressions.
PostgreSQL has supported compression on its "oversized data" columns (i.e. the TOAST system) for decades, but this release adds the ability to now choose to use LZ4 compression for column compression.
Data Types + SQL
Building upon its existing support for range types, PostgreSQL 14 adds the new multirange type that lets you specify an ordered list of noncontiguous ranges, e.g.:
SELECT datemultirange( daterange('2021-07-01', '2021-07-31'), daterange('2021-09-01', '2021-09-30'), daterange('2021-11-01', '2021-11-30'), daterange('2022-01-01', '2022-01-31'), daterange('2022-03-01', '2022-04-07') );
The new multirange type supports the existing operations available to range types.
PostgreSQL 14 now adds a general subscripting framework for retrieving information in nested objects. For example, you can now retrieve nested info in the JSONB data type using subscript syntax, e.g.:
SELECT ('{ "this": { "now": { "works": "in postgres 14!" }}}'::jsonb)['this']['now']['works'];
PostgreSQL 14 also adds support for OUT parameters in stored procedures, and allows for the GROUP BY clause to use the DISTINCT keyword to remove duplicate GROUPING SET combinations.
For recursive common table expressions (WITH queries), PostgreSQL 14 adds the syntactic conveniences of SEARCH and CYCLE to help with ordering and cycle detection respectively.
There is also the new date_bin function in PostgreSQL 14 that can align timestamps with specified intervals, a technique known as "binning."
Administration
PostgreSQL 14 makes numerous improvements to VACUUM, with optimizations geared towards indexes. Autovacuum now analyzes partitioned tables and can propagate information about row counts to parent tables. There are also performance gains in ANALYZE that can be controlled with maintenance_io_concurrency parameter.
PostgreSQL 14 includes numerous improvements on what information can be monitored, including the ability to track progress on COPY using the pg_stat_progress_copy view. This release lets you track WAL activity from the pg_stat_wal view, and inspect replication slot statistics from the pg_stat_replication_slots view.
There are several new parameters in PostgreSQL 14 to help manage connections. These include idle_session_timeout, which can close idle connections after the specified period, and client_connection_check_interval parameter that lets PostgreSQL cancel long running queries if a client disconnects.
The REINDEX command can now process all of the child indexes of a partitioned table, and PostgreSQL 14 adds the pg_amcheck utility to help check for data corruption.
Replication & Recovery
PostgreSQL 14 adds numerous performance benefits for logical replication, including the ability to stream in-progress transactions to subscribers instead of waiting for them to be completed. The ALTER SUBSCRIPTION makes it easier to add/remove publications using the new ADD/DROP PUBLICATION syntax.
There are performance improvements in PostgreSQL 14 to how PostgreSQL starts up when in crash recovery, and you can now use pg_rewind on a PostgreSQL instance that is in standby mode.
Security
PostgreSQL 14 adds the ability to give users universal "read only" and "write only" privileges on tables/views/sequences through the use of the pg_read_all_data and pg_write_all_data predefined roles, respectively.
This release also defaults to using SCRAM-SHA-256 for password management on new PostgreSQL instances. Additionally, the clientcert parameter in the pg_hba.conf must now use either the values of verify-ca or verify-full instead of the legacy values.
PostgreSQL 14 can use a certificate's "distinguished name" (DN) for certificated-based authentication with clientname=DN parameter in the pg_hba.conf file.
Additional Features
Many other new features and improvements have been added to PostgreSQL 14, some of which may be as or more important to your use case than what is mentioned above. Please see the release notes for a complete list of new and changed features:
https://www.postgresql.org/docs/14/release-14.html
Testing for Bugs & Compatibility
The stability of each PostgreSQL release greatly depends on you, the community, to test the upcoming version with your workloads and testing tools in order to find bugs and regressions before the general availability of PostgreSQL 14. As this is a Beta, minor changes to database behaviors, feature details, and APIs are still possible. Your feedback and testing will help determine the final tweaks on the new features, so please test in the near future. The quality of user testing helps determine when we can make a final release.
A list of open issues is publicly available in the PostgreSQL wiki. You can report bugs using this form on the PostgreSQL website:
https://www.postgresql.org/account/submitbug/
Beta Schedule
This is the first beta release of version 14. The PostgreSQL Project will release additional betas as required for testing, followed by one or more release candidates, until the final release in late 2021. For further information please see the Beta Testing page.
Links
