public inbox for [email protected]
help / color / mirror / Atom feedPostgreSQL 14 press release draft
3+ messages / 2 participants
[nested] [flat]
* PostgreSQL 14 press release draft
@ 2021-09-21 02:19 Jonathan S. Katz <[email protected]>
2021-09-21 07:16 ` Re: PostgreSQL 14 press release draft Laurenz Albe <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Jonathan S. Katz @ 2021-09-21 02:19 UTC (permalink / raw)
To: PostgreSQL Advocacy <[email protected]>
Hi,
Attached is a copy of the first draft for the PG14 press release.
This brings together highlights of many of the features in the upcoming
PostgreSQL 14 release while providing context on their significance.
With the plethora of new features coming in PostgreSQL 14, it is
challenging to highlight them all, but the idea is to give a glimpse of
what to expect in the new release.
Feedback on the release is welcome. However, please provide your
feedback on the release no later than **Thu, Sep 23, 2021 @ 18:00 UTC**.
Thanks,
Jonathan
The PostgreSQL Global Development Group today announced the release of
[PostgreSQL 14](https://www.postgresql.org/docs/14/release-14.html), the latest
version of the world’s [most advanced open source database](https://www.postgresql.org/).
PostgreSQL 14 brings a variety of features that help developers and
administrators to deploy their data-backed applications. PostgreSQL continues to
add innovations on complex data types, including more conveniences for accessing
JSON and support for noncontiguous ranges of data. This latest release also adds
to PostgreSQL's trend on improvements for high performance and distributed
data workloads, with advances in support for connection concurrency, high-write
workloads, query parallelism and logical replication.
_Hold for quote_
[PostgreSQL](https://www.postgresql.org), an innovative data management system
known for its reliability and robustness, benefits from over 25 years of open
source development from a [global developer community](https://www.postgresql.org/community/)
and has become the preferred open source relational database for organizations
of all sizes.
### JSON Conveniences and Multiranges
PostgreSQL has supported manipulating [JSON](https://www.postgresql.org/docs/14/datatype-json.html)
data since the release of PostgreSQL 9.2, though retrieval of values used a
unique syntax. PostgreSQL 14 now lets you [access JSON data using subscripts](https://www.postgresql.org/docs/14/datatype-json.html#JSONB-SUBSCRIPTING), e.g. a query like `SELECT ('{ "postgres": { "release": 14 }}'::jsonb)['postgres']['release'];`
now works. This aligns PostgreSQL with commonly recognized syntax for
retrieving information from JSON data. The subscripting framework added to
PostgreSQL 14 can be generally extended to other nested data structures, and is
also applied to the `hstore` data type in this release.
[Range types](https://www.postgresql.org/docs/14/rangetypes.html), also first
released in PostgreSQL 9.2, now have support for noncontiguous ranges through
the introduction of the "[multirange](https://www.postgresql.org/docs/14/rangetypes.html#RANGETYPES-BUILTIN)";.
A multirange is an ordered list of ranges that are nonoverlapping, which allows
for developers to write simpler queries for dealing with complex sequences of
ranges. The range types native to PostgreSQL (dates, times, numbers) support
multiranges, and other data types can be extended to use multirange support.
### Performance Improvements for Heavy Workloads
PostgreSQL 14 provides a significant throughput boost on workloads that use many
connections, with some benchmarks showing a 2x speedup. This release continues
on the recent improvements the overall management of B-tree indexes by reducing
index bloat on tables with [frequently updated indexes](https://www.postgresql.org/docs/14/btree-implementation.html#BTREE-DELETION).
PostgreSQL 14 introduces the ability to [pipeline queries](https://www.postgresql.org/docs/14/libpq-pipeline-mode.html)
to a database, which can significantly improve performance over high latency
connections or for workloads with many small write (`INSERT`/`UPDATE`/`DELETE`)
operations. As this is a client-side feature, you can use pipeline mode with any
modern PostgreSQL database so long as you use the version 14 client.
### Enhancements for Distributed Workloads
Distributed PostgreSQL databases stand to benefit from PostgreSQL 14. When using
[logical replication](https://www.postgresql.org/docs/current/logical-replication.html),
PostgreSQL can now stream in-progress transactions to subscribers, with
significant performance benefits for applying large transactions on subscribers.
PostgreSQL 14 also adds several other performance enhancements to the logical
decoding system that powers logical replication.
[Foreign data wrappers](https://www.postgresql.org/docs/14/sql-createforeigndatawrapper.html),
used to work with federated workloads across PostgreSQL and other databases, can
now leverage query parallelism in PostgreSQL 14. This release implements this
ability in the [`postgres_fdw`](https://www.postgresql.org/docs/14/postgres-fdw.html),
the foreign data wrapper that interfaces with other PostgreSQL databases.
In addition to supporting query parallelism, `postgres_fdw` can now also bulk
insert data on foreign tables and import table partitions with the
[`IMPORT FOREIGN SCHEMA`](https://www.postgresql.org/docs/14/sql-importforeignschema.html)
directive.
### Administration and Observability
PostgreSQL 14 extends its performance gains to its [vacuuming](https://www.postgresql.org/docs/14/routine-vacuuming.html)
system. This release includes optimizations for reducing overhead from B-trees
indexes and now allows autovacuum to analyze partitioned tables and propagate
information to its parents. [`ANALYZE`](https://www.postgresql.org/docs/14/sql-analyze.html),
used to collect database statistics, now runs significantly faster in
PostgreSQL 14 due to its own performance improvements.
The choice of compression for PostgreSQL's [TOAST](https://www.postgresql.org/docs/14/storage-toast.html)
system, which is used to store larger data like blocks of text or geometries,
can [now be configured](https://www.postgresql.org/docs/14/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION).
PostgreSQL 14 adds LZ4 compression for TOAST columns while retaining support for
`pglz` compression.
PostgreSQL 14 adds several new features to help with monitoring and
observability, including the ability to [track the progress of `COPY` commands](https://www.postgresql.org/docs/14/progress-reporting.html#COPY-PROGRESS-REPORTING),
[write-ahead-log (WAL) activity](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW),
and [statistics on replication slots](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-SLOTS-VIEW).
Enabling [`compute_query_id`](https://www.postgresql.org/docs/14/runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID)
lets you uniquely track a query through several PostgreSQL systems, including
[`pg_stat_activity`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW),
[`EXPLAIN VERBOSE`](https://www.postgresql.org/docs/14/sql-explain.html), and
through several logging functions.
### SQL Performance, Conformance, and Convenience
Query planning and execution benefit from enhancements in PostgreSQL 14. This
release includes several improvements to PostgreSQL's query parallelism support,
including better performance of parallel sequential scans, the ability for
[`PL/pgSQL`](https://www.postgresql.org/docs/14/plpgsql.html) to execute
parallel queries when using the `RETURN QUERY` directive, and enabling
[`REFRESH MATERIALIZED VIEW`](https://www.postgresql.org/docs/14/sql-refreshmaterializedview.html)
to execute parallel queries. Additionally, queries that use nested loop joins
may see performance benefits through additional caching that is added in
PostgreSQL 14.
The [extended systems](https://www.postgresql.org/docs/14/planner-stats.html#PLANNER-STATS-EXTENDED)
includes many improvements in PostgreSQL 14, including the ability to apply
extend statistics on expressions. Additionally,
[window functions](https://www.postgresql.org/docs/14/functions-window.html) can
now benefit from incremental sorts, a feature that was introduced in
[PostgreSQL 13](https://www.postgresql.org/about/news/postgresql-13-released-2077/).
[Stored procedures](https://www.postgresql.org/docs/14/sql-createprocedure.html),
which allow for transaction control in a block of code, can now return data by
using `OUT` parameters.
PostgreSQL 14 introduces the ability to "bin", or align, timestamps to a
particular interval using the [`date_bin`](https://www.postgresql.org/docs/14/functions-datetime.html#FUNCTIONS-DATETIME-BIN)
function. This release also adds the SQL conforming
[`SEARCH`](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-SEARCH)
and [`CYCLE`](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-CYCLE)
directives to help with ordering and cycle detection for recursive
[common table expressions](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-RECURSIVE).
### Security Enhancements
PostgreSQL 14 makes it convenient to assign read-only and write-only privileges
to users on tables, views, and schemas using the `pg_read_all_data` and
`pg_write_all_data` [predefined roles](https://www.postgresql.org/docs/14/predefined-roles.html).
Additionally, this release now makes the standard compliant
[`SCRAM-SHA-256`](https://www.postgresql.org/docs/14/auth-password.html) password
management and authentication system the default on new PostgreSQL instances.
### About PostgreSQL
[PostgreSQL](https://www.postgresql.org) is the world's most advanced open
source database, with a global community of thousands of users, contributors,
companies and organizations. Built on over 30 years of engineering, starting at
the University of California, Berkeley, PostgreSQL has continued with an
unmatched pace of development. PostgreSQL's mature feature set not only matches
top proprietary database systems, but exceeds them in advanced database
features, extensibility, security, and stability.
### Links
* [Download](https://www.postgresql.org/download/)
* [Release Notes](https://www.postgresql.org/docs/14/release-14.html)
* [Press Kit](https://www.postgresql.org/about/press/)
* [Security Page](https://www.postgresql.org/support/security/)
* [Versioning Policy](https://www.postgresql.org/support/versioning/)
* [Follow @postgresql on Twitter](https://twitter.com/postgresql)
Attachments:
[text/plain] release.en.md (9.5K, ../../[email protected]/2-release.en.md)
download | inline:
The PostgreSQL Global Development Group today announced the release of
[PostgreSQL 14](https://www.postgresql.org/docs/14/release-14.html), the latest
version of the world’s [most advanced open source database](https://www.postgresql.org/).
PostgreSQL 14 brings a variety of features that help developers and
administrators to deploy their data-backed applications. PostgreSQL continues to
add innovations on complex data types, including more conveniences for accessing
JSON and support for noncontiguous ranges of data. This latest release also adds
to PostgreSQL's trend on improvements for high performance and distributed
data workloads, with advances in support for connection concurrency, high-write
workloads, query parallelism and logical replication.
_Hold for quote_
[PostgreSQL](https://www.postgresql.org), an innovative data management system
known for its reliability and robustness, benefits from over 25 years of open
source development from a [global developer community](https://www.postgresql.org/community/)
and has become the preferred open source relational database for organizations
of all sizes.
### JSON Conveniences and Multiranges
PostgreSQL has supported manipulating [JSON](https://www.postgresql.org/docs/14/datatype-json.html)
data since the release of PostgreSQL 9.2, though retrieval of values used a
unique syntax. PostgreSQL 14 now lets you [access JSON data using subscripts](https://www.postgresql.org/docs/14/datatype-json.html#JSONB-SUBSCRIPTING), e.g. a query like `SELECT ('{ "postgres": { "release": 14 }}'::jsonb)['postgres']['release'];`
now works. This aligns PostgreSQL with commonly recognized syntax for
retrieving information from JSON data. The subscripting framework added to
PostgreSQL 14 can be generally extended to other nested data structures, and is
also applied to the `hstore` data type in this release.
[Range types](https://www.postgresql.org/docs/14/rangetypes.html), also first
released in PostgreSQL 9.2, now have support for noncontiguous ranges through
the introduction of the "[multirange](https://www.postgresql.org/docs/14/rangetypes.html#RANGETYPES-BUILTIN)".
A multirange is an ordered list of ranges that are nonoverlapping, which allows
for developers to write simpler queries for dealing with complex sequences of
ranges. The range types native to PostgreSQL (dates, times, numbers) support
multiranges, and other data types can be extended to use multirange support.
### Performance Improvements for Heavy Workloads
PostgreSQL 14 provides a significant throughput boost on workloads that use many
connections, with some benchmarks showing a 2x speedup. This release continues
on the recent improvements the overall management of B-tree indexes by reducing
index bloat on tables with [frequently updated indexes](https://www.postgresql.org/docs/14/btree-implementation.html#BTREE-DELETION).
PostgreSQL 14 introduces the ability to [pipeline queries](https://www.postgresql.org/docs/14/libpq-pipeline-mode.html)
to a database, which can significantly improve performance over high latency
connections or for workloads with many small write (`INSERT`/`UPDATE`/`DELETE`)
operations. As this is a client-side feature, you can use pipeline mode with any
modern PostgreSQL database so long as you use the version 14 client.
### Enhancements for Distributed Workloads
Distributed PostgreSQL databases stand to benefit from PostgreSQL 14. When using
[logical replication](https://www.postgresql.org/docs/current/logical-replication.html),
PostgreSQL can now stream in-progress transactions to subscribers, with
significant performance benefits for applying large transactions on subscribers.
PostgreSQL 14 also adds several other performance enhancements to the logical
decoding system that powers logical replication.
[Foreign data wrappers](https://www.postgresql.org/docs/14/sql-createforeigndatawrapper.html),
used to work with federated workloads across PostgreSQL and other databases, can
now leverage query parallelism in PostgreSQL 14. This release implements this
ability in the [`postgres_fdw`](https://www.postgresql.org/docs/14/postgres-fdw.html),
the foreign data wrapper that interfaces with other PostgreSQL databases.
In addition to supporting query parallelism, `postgres_fdw` can now also bulk
insert data on foreign tables and import table partitions with the
[`IMPORT FOREIGN SCHEMA`](https://www.postgresql.org/docs/14/sql-importforeignschema.html)
directive.
### Administration and Observability
PostgreSQL 14 extends its performance gains to its [vacuuming](https://www.postgresql.org/docs/14/routine-vacuuming.html)
system. This release includes optimizations for reducing overhead from B-trees
indexes and now allows autovacuum to analyze partitioned tables and propagate
information to its parents. [`ANALYZE`](https://www.postgresql.org/docs/14/sql-analyze.html),
used to collect database statistics, now runs significantly faster in
PostgreSQL 14 due to its own performance improvements.
The choice of compression for PostgreSQL's [TOAST](https://www.postgresql.org/docs/14/storage-toast.html)
system, which is used to store larger data like blocks of text or geometries,
can [now be configured](https://www.postgresql.org/docs/14/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION).
PostgreSQL 14 adds LZ4 compression for TOAST columns while retaining support for
`pglz` compression.
PostgreSQL 14 adds several new features to help with monitoring and
observability, including the ability to [track the progress of `COPY` commands](https://www.postgresql.org/docs/14/progress-reporting.html#COPY-PROGRESS-REPORTING),
[write-ahead-log (WAL) activity](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW),
and [statistics on replication slots](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-SLOTS-VIEW).
Enabling [`compute_query_id`](https://www.postgresql.org/docs/14/runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID)
lets you uniquely track a query through several PostgreSQL systems, including
[`pg_stat_activity`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW),
[`EXPLAIN VERBOSE`](https://www.postgresql.org/docs/14/sql-explain.html), and
through several logging functions.
### SQL Performance, Conformance, and Convenience
Query planning and execution benefit from enhancements in PostgreSQL 14. This
release includes several improvements to PostgreSQL's query parallelism support,
including better performance of parallel sequential scans, the ability for
[`PL/pgSQL`](https://www.postgresql.org/docs/14/plpgsql.html) to execute
parallel queries when using the `RETURN QUERY` directive, and enabling
[`REFRESH MATERIALIZED VIEW`](https://www.postgresql.org/docs/14/sql-refreshmaterializedview.html)
to execute parallel queries. Additionally, queries that use nested loop joins
may see performance benefits through additional caching that is added in
PostgreSQL 14.
The [extended systems](https://www.postgresql.org/docs/14/planner-stats.html#PLANNER-STATS-EXTENDED)
includes many improvements in PostgreSQL 14, including the ability to apply
extend statistics on expressions. Additionally,
[window functions](https://www.postgresql.org/docs/14/functions-window.html) can
now benefit from incremental sorts, a feature that was introduced in
[PostgreSQL 13](https://www.postgresql.org/about/news/postgresql-13-released-2077/).
[Stored procedures](https://www.postgresql.org/docs/14/sql-createprocedure.html),
which allow for transaction control in a block of code, can now return data by
using `OUT` parameters.
PostgreSQL 14 introduces the ability to "bin", or align, timestamps to a
particular interval using the [`date_bin`](https://www.postgresql.org/docs/14/functions-datetime.html#FUNCTIONS-DATETIME-BIN)
function. This release also adds the SQL conforming
[`SEARCH`](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-SEARCH)
and [`CYCLE`](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-CYCLE)
directives to help with ordering and cycle detection for recursive
[common table expressions](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-RECURSIVE).
### Security Enhancements
PostgreSQL 14 makes it convenient to assign read-only and write-only privileges
to users on tables, views, and schemas using the `pg_read_all_data` and
`pg_write_all_data` [predefined roles](https://www.postgresql.org/docs/14/predefined-roles.html).
Additionally, this release now makes the standard compliant
[`SCRAM-SHA-256`](https://www.postgresql.org/docs/14/auth-password.html) password
management and authentication system the default on new PostgreSQL instances.
### About PostgreSQL
[PostgreSQL](https://www.postgresql.org) is the world's most advanced open
source database, with a global community of thousands of users, contributors,
companies and organizations. Built on over 30 years of engineering, starting at
the University of California, Berkeley, PostgreSQL has continued with an
unmatched pace of development. PostgreSQL's mature feature set not only matches
top proprietary database systems, but exceeds them in advanced database
features, extensibility, security, and stability.
### Links
* [Download](https://www.postgresql.org/download/)
* [Release Notes](https://www.postgresql.org/docs/14/release-14.html)
* [Press Kit](https://www.postgresql.org/about/press/)
* [Security Page](https://www.postgresql.org/support/security/)
* [Versioning Policy](https://www.postgresql.org/support/versioning/)
* [Follow @postgresql on Twitter](https://twitter.com/postgresql)
[application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PostgreSQL 14 press release draft
2021-09-21 02:19 PostgreSQL 14 press release draft Jonathan S. Katz <[email protected]>
@ 2021-09-21 07:16 ` Laurenz Albe <[email protected]>
2021-09-22 14:18 ` Re: PostgreSQL 14 press release draft Jonathan S. Katz <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2021-09-21 07:16 UTC (permalink / raw)
To: Jonathan S. Katz <[email protected]>; PostgreSQL Advocacy <[email protected]>
On Mon, 2021-09-20 at 22:19 -0400, Jonathan S. Katz wrote:
> The [extended systems](https://www.postgresql.org/docs/14/planner-stats.html#PLANNER-STATS-EXTENDED)
> includes many improvements in PostgreSQL 14, including the ability to apply
> extend statistics on expressions. Additionally,
> [window functions](https://www.postgresql.org/docs/14/functions-window.html) can
> now benefit from incremental sorts, a feature that was introduced in
> [PostgreSQL 13](https://www.postgresql.org/about/news/postgresql-13-released-2077/).
"The extended systems" sounds weird to me, and the link points to extended statistics.
Did you mean to say "Extended statistics include many ..."?
But then the repetition jars.
Perhaps:
[Extended statistics](https://www.postgresql.org/docs/14/planner-stats.html#PLANNER-STATS-EXTENDED)
can be used for expressions in PostgreSQL v14. Additionally,
[window functions](https://www.postgresql.org/docs/14/functions-window.html) can
now benefit from incremental sorts, a feature that was introduced in
[PostgreSQL 13](https://www.postgresql.org/about/news/postgresql-13-released-2077/).
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PostgreSQL 14 press release draft
2021-09-21 02:19 PostgreSQL 14 press release draft Jonathan S. Katz <[email protected]>
2021-09-21 07:16 ` Re: PostgreSQL 14 press release draft Laurenz Albe <[email protected]>
@ 2021-09-22 14:18 ` Jonathan S. Katz <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Jonathan S. Katz @ 2021-09-22 14:18 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; PostgreSQL Advocacy <[email protected]>
On 9/21/21 3:16 AM, Laurenz Albe wrote:
> On Mon, 2021-09-20 at 22:19 -0400, Jonathan S. Katz wrote:
>> The [extended systems](https://www.postgresql.org/docs/14/planner-stats.html#PLANNER-STATS-EXTENDED)
>> includes many improvements in PostgreSQL 14, including the ability to apply
>> extend statistics on expressions. Additionally,
>> [window functions](https://www.postgresql.org/docs/14/functions-window.html) can
>> now benefit from incremental sorts, a feature that was introduced in
>> [PostgreSQL 13](https://www.postgresql.org/about/news/postgresql-13-released-2077/).
>
> "The extended systems" sounds weird to me, and the link points to extended statistics.
>
> Did you mean to say "Extended statistics include many ..."?
> But then the repetition jars.
>
> Perhaps:
>
> [Extended statistics](https://www.postgresql.org/docs/14/planner-stats.html#PLANNER-STATS-EXTENDED)
> can be used for expressions in PostgreSQL v14. Additionally,
> [window functions](https://www.postgresql.org/docs/14/functions-window.html) can
> now benefit from incremental sorts, a feature that was introduced in
> [PostgreSQL 13](https://www.postgresql.org/about/news/postgresql-13-released-2077/).
Thanks, I mostly followed this suggestion. Please see attached for
updated draft, which also incorporates feedback from -hackers.
As a reminder, please provide any feedback on the press release no later
than **Thu, Sep 23, 2021 @ 18:00 UTC**.
Thanks,
Jonathan
The PostgreSQL Global Development Group today announced the release of
[PostgreSQL 14](https://www.postgresql.org/docs/14/release-14.html), the latest
version of the world’s [most advanced open source database](https://www.postgresql.org/).
PostgreSQL 14 brings a variety of features that help developers and
administrators to deploy their data-backed applications. PostgreSQL continues to
add innovations on complex data types, including more conveniences for accessing
JSON and support for noncontiguous ranges of data. This latest release also adds
to PostgreSQL's trend on improvements for high performance and distributed
data workloads, with advances in support for connection concurrency, high-write
workloads, query parallelism and logical replication.
_Hold for quote_
[PostgreSQL](https://www.postgresql.org), an innovative data management system
known for its reliability and robustness, benefits from over 25 years of open
source development from a [global developer community](https://www.postgresql.org/community/)
and has become the preferred open source relational database for organizations
of all sizes.
### JSON Conveniences and Multiranges
PostgreSQL has supported manipulating [JSON](https://www.postgresql.org/docs/14/datatype-json.html)
data since the release of PostgreSQL 9.2, though retrieval of values used a
unique syntax. PostgreSQL 14 now lets you [access JSON data using subscripts](https://www.postgresql.org/docs/14/datatype-json.html#JSONB-SUBSCRIPTING), e.g. a query like `SELECT ('{ "postgres": { "release": 14 }}'::jsonb)['postgres']['release'];`
now works. This aligns PostgreSQL with commonly recognized syntax for
retrieving information from JSON data. The subscripting framework added to
PostgreSQL 14 can be generally extended to other nested data structures, and is
also applied to the `hstore` data type in this release.
[Range types](https://www.postgresql.org/docs/14/rangetypes.html), also first
released in PostgreSQL 9.2, now have support for noncontiguous ranges through
the introduction of the "[multirange](https://www.postgresql.org/docs/14/rangetypes.html#RANGETYPES-BUILTIN)";.
A multirange is an ordered list of ranges that are nonoverlapping, which allows
for developers to write simpler queries for dealing with complex sequences of
ranges. The range types native to PostgreSQL (dates, times, numbers) support
multiranges, and other data types can be extended to use multirange support.
### Performance Improvements for Heavy Workloads
PostgreSQL 14 provides a significant throughput boost on workloads that use many
connections, with some benchmarks showing a 2x speedup. This release continues
on the recent improvements to the overall management of B-tree indexes by
reducing index bloat on tables with [frequently updated indexes](https://www.postgresql.org/docs/14/btree-implementation.html#BTREE-DELETION).
PostgreSQL 14 introduces the ability to [pipeline queries](https://www.postgresql.org/docs/14/libpq-pipeline-mode.html)
to a database, which can significantly improve performance over high latency
connections or for workloads with many small write (`INSERT`/`UPDATE`/`DELETE`)
operations. As this is a client-side feature, you can use pipeline mode with any
modern PostgreSQL database so long as you use the version 14 client.
### Enhancements for Distributed Workloads
Distributed PostgreSQL databases stand to benefit from PostgreSQL 14. When using
[logical replication](https://www.postgresql.org/docs/current/logical-replication.html),
PostgreSQL can now stream in-progress transactions to subscribers, with
significant performance benefits for applying large transactions on subscribers.
PostgreSQL 14 also adds several other performance enhancements to the logical
decoding system that powers logical replication.
[Foreign data wrappers](https://www.postgresql.org/docs/14/sql-createforeigndatawrapper.html),
which are used for working with federated workloads across PostgreSQL and other
databases, can now leverage query parallelism in PostgreSQL 14. This release
implements this ability in the [`postgres_fdw`](https://www.postgresql.org/docs/14/postgres-fdw.html),
the foreign data wrapper that interfaces with other PostgreSQL databases.
In addition to supporting query parallelism, `postgres_fdw` can now also bulk
insert data on foreign tables and import table partitions with the
[`IMPORT FOREIGN SCHEMA`](https://www.postgresql.org/docs/14/sql-importforeignschema.html)
directive.
### Administration and Observability
PostgreSQL 14 extends its performance gains to the [vacuuming](https://www.postgresql.org/docs/14/routine-vacuuming.html)
system, including optimizations for reducing overhead from B-Trees.
[`ANALYZE`](https://www.postgresql.org/docs/14/sql-analyze.html), used to
collect database statistics, now runs significantly faster in PostgreSQL 14 due
to its own performance improvements.
Compression for PostgreSQL's [TOAST](https://www.postgresql.org/docs/14/storage-toast.html)
system, which is used to store larger data like blocks of text or geometries,
can [now be configured](https://www.postgresql.org/docs/14/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION).
PostgreSQL 14 adds LZ4 compression for TOAST columns while retaining support for
`pglz` compression.
PostgreSQL 14 adds several new features to help with monitoring and
observability, including the ability to [track the progress of `COPY` commands](https://www.postgresql.org/docs/14/progress-reporting.html#COPY-PROGRESS-REPORTING),
[write-ahead-log (WAL) activity](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW),
and [statistics on replication slots](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-SLOTS-VIEW).
Enabling [`compute_query_id`](https://www.postgresql.org/docs/14/runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID)
lets you uniquely track a query through several PostgreSQL systems, including
[`pg_stat_activity`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW),
[`EXPLAIN VERBOSE`](https://www.postgresql.org/docs/14/sql-explain.html), and
through several logging functions.
### SQL Performance, Conformance, and Convenience
Query planning and execution benefit from enhancements in PostgreSQL 14. This
release includes several improvements to PostgreSQL's query parallelism support,
including better performance of parallel sequential scans, the ability for
[`PL/pgSQL`](https://www.postgresql.org/docs/14/plpgsql.html) to execute
parallel queries when using the `RETURN QUERY` directive, and enabling
[`REFRESH MATERIALIZED VIEW`](https://www.postgresql.org/docs/14/sql-refreshmaterializedview.html)
to execute parallel queries. Additionally, queries that use nested loop joins
may see performance benefits through additional caching that is added in
PostgreSQL 14.
[Extended statistics](https://www.postgresql.org/docs/14/planner-stats.html#PLANNER-STATS-EXTENDED)
can now be used in PostgreSQL 14 for expressions. Additionally,
[window functions](https://www.postgresql.org/docs/14/functions-window.html) can
now benefit from incremental sorts, a feature that was introduced in
[PostgreSQL 13](https://www.postgresql.org/about/news/postgresql-13-released-2077/).
[Stored procedures](https://www.postgresql.org/docs/14/sql-createprocedure.html),
which allow for transaction control in a block of code, can now return data by
using `OUT` parameters.
PostgreSQL 14 introduces the ability to "bin", or align, timestamps to a
particular interval using the [`date_bin`](https://www.postgresql.org/docs/14/functions-datetime.html#FUNCTIONS-DATETIME-BIN)
function. This release also adds the SQL conforming
[`SEARCH`](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-SEARCH)
and [`CYCLE`](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-CYCLE)
directives to help with ordering and cycle detection for recursive
[common table expressions](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-RECURSIVE).
### Security Enhancements
PostgreSQL 14 makes it convenient to assign read-only and write-only privileges
to users on tables, views, and schemas using the `pg_read_all_data` and
`pg_write_all_data` [predefined roles](https://www.postgresql.org/docs/14/predefined-roles.html).
Additionally, this release now makes the standard compliant
[`SCRAM-SHA-256`](https://www.postgresql.org/docs/14/auth-password.html) password
management and authentication system the default on new PostgreSQL instances.
### About PostgreSQL
[PostgreSQL](https://www.postgresql.org) is the world's most advanced open
source database, with a global community of thousands of users, contributors,
companies and organizations. Built on over 30 years of engineering, starting at
the University of California, Berkeley, PostgreSQL has continued with an
unmatched pace of development. PostgreSQL's mature feature set not only matches
top proprietary database systems, but exceeds them in advanced database
features, extensibility, security, and stability.
### Links
* [Download](https://www.postgresql.org/download/)
* [Release Notes](https://www.postgresql.org/docs/14/release-14.html)
* [Press Kit](https://www.postgresql.org/about/press/)
* [Security Page](https://www.postgresql.org/support/security/)
* [Versioning Policy](https://www.postgresql.org/support/versioning/)
* [Follow @postgresql on Twitter](https://twitter.com/postgresql)
Attachments:
[text/plain] release.en.md (9.3K, ../../[email protected]/2-release.en.md)
download | inline:
The PostgreSQL Global Development Group today announced the release of
[PostgreSQL 14](https://www.postgresql.org/docs/14/release-14.html), the latest
version of the world’s [most advanced open source database](https://www.postgresql.org/).
PostgreSQL 14 brings a variety of features that help developers and
administrators to deploy their data-backed applications. PostgreSQL continues to
add innovations on complex data types, including more conveniences for accessing
JSON and support for noncontiguous ranges of data. This latest release also adds
to PostgreSQL's trend on improvements for high performance and distributed
data workloads, with advances in support for connection concurrency, high-write
workloads, query parallelism and logical replication.
_Hold for quote_
[PostgreSQL](https://www.postgresql.org), an innovative data management system
known for its reliability and robustness, benefits from over 25 years of open
source development from a [global developer community](https://www.postgresql.org/community/)
and has become the preferred open source relational database for organizations
of all sizes.
### JSON Conveniences and Multiranges
PostgreSQL has supported manipulating [JSON](https://www.postgresql.org/docs/14/datatype-json.html)
data since the release of PostgreSQL 9.2, though retrieval of values used a
unique syntax. PostgreSQL 14 now lets you [access JSON data using subscripts](https://www.postgresql.org/docs/14/datatype-json.html#JSONB-SUBSCRIPTING), e.g. a query like `SELECT ('{ "postgres": { "release": 14 }}'::jsonb)['postgres']['release'];`
now works. This aligns PostgreSQL with commonly recognized syntax for
retrieving information from JSON data. The subscripting framework added to
PostgreSQL 14 can be generally extended to other nested data structures, and is
also applied to the `hstore` data type in this release.
[Range types](https://www.postgresql.org/docs/14/rangetypes.html), also first
released in PostgreSQL 9.2, now have support for noncontiguous ranges through
the introduction of the "[multirange](https://www.postgresql.org/docs/14/rangetypes.html#RANGETYPES-BUILTIN)".
A multirange is an ordered list of ranges that are nonoverlapping, which allows
for developers to write simpler queries for dealing with complex sequences of
ranges. The range types native to PostgreSQL (dates, times, numbers) support
multiranges, and other data types can be extended to use multirange support.
### Performance Improvements for Heavy Workloads
PostgreSQL 14 provides a significant throughput boost on workloads that use many
connections, with some benchmarks showing a 2x speedup. This release continues
on the recent improvements to the overall management of B-tree indexes by
reducing index bloat on tables with [frequently updated indexes](https://www.postgresql.org/docs/14/btree-implementation.html#BTREE-DELETION).
PostgreSQL 14 introduces the ability to [pipeline queries](https://www.postgresql.org/docs/14/libpq-pipeline-mode.html)
to a database, which can significantly improve performance over high latency
connections or for workloads with many small write (`INSERT`/`UPDATE`/`DELETE`)
operations. As this is a client-side feature, you can use pipeline mode with any
modern PostgreSQL database so long as you use the version 14 client.
### Enhancements for Distributed Workloads
Distributed PostgreSQL databases stand to benefit from PostgreSQL 14. When using
[logical replication](https://www.postgresql.org/docs/current/logical-replication.html),
PostgreSQL can now stream in-progress transactions to subscribers, with
significant performance benefits for applying large transactions on subscribers.
PostgreSQL 14 also adds several other performance enhancements to the logical
decoding system that powers logical replication.
[Foreign data wrappers](https://www.postgresql.org/docs/14/sql-createforeigndatawrapper.html),
which are used for working with federated workloads across PostgreSQL and other
databases, can now leverage query parallelism in PostgreSQL 14. This release
implements this ability in the [`postgres_fdw`](https://www.postgresql.org/docs/14/postgres-fdw.html),
the foreign data wrapper that interfaces with other PostgreSQL databases.
In addition to supporting query parallelism, `postgres_fdw` can now also bulk
insert data on foreign tables and import table partitions with the
[`IMPORT FOREIGN SCHEMA`](https://www.postgresql.org/docs/14/sql-importforeignschema.html)
directive.
### Administration and Observability
PostgreSQL 14 extends its performance gains to the [vacuuming](https://www.postgresql.org/docs/14/routine-vacuuming.html)
system, including optimizations for reducing overhead from B-Trees.
[`ANALYZE`](https://www.postgresql.org/docs/14/sql-analyze.html), used to
collect database statistics, now runs significantly faster in PostgreSQL 14 due
to its own performance improvements.
Compression for PostgreSQL's [TOAST](https://www.postgresql.org/docs/14/storage-toast.html)
system, which is used to store larger data like blocks of text or geometries,
can [now be configured](https://www.postgresql.org/docs/14/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION).
PostgreSQL 14 adds LZ4 compression for TOAST columns while retaining support for
`pglz` compression.
PostgreSQL 14 adds several new features to help with monitoring and
observability, including the ability to [track the progress of `COPY` commands](https://www.postgresql.org/docs/14/progress-reporting.html#COPY-PROGRESS-REPORTING),
[write-ahead-log (WAL) activity](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW),
and [statistics on replication slots](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-SLOTS-VIEW).
Enabling [`compute_query_id`](https://www.postgresql.org/docs/14/runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID)
lets you uniquely track a query through several PostgreSQL systems, including
[`pg_stat_activity`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW),
[`EXPLAIN VERBOSE`](https://www.postgresql.org/docs/14/sql-explain.html), and
through several logging functions.
### SQL Performance, Conformance, and Convenience
Query planning and execution benefit from enhancements in PostgreSQL 14. This
release includes several improvements to PostgreSQL's query parallelism support,
including better performance of parallel sequential scans, the ability for
[`PL/pgSQL`](https://www.postgresql.org/docs/14/plpgsql.html) to execute
parallel queries when using the `RETURN QUERY` directive, and enabling
[`REFRESH MATERIALIZED VIEW`](https://www.postgresql.org/docs/14/sql-refreshmaterializedview.html)
to execute parallel queries. Additionally, queries that use nested loop joins
may see performance benefits through additional caching that is added in
PostgreSQL 14.
[Extended statistics](https://www.postgresql.org/docs/14/planner-stats.html#PLANNER-STATS-EXTENDED)
can now be used in PostgreSQL 14 for expressions. Additionally,
[window functions](https://www.postgresql.org/docs/14/functions-window.html) can
now benefit from incremental sorts, a feature that was introduced in
[PostgreSQL 13](https://www.postgresql.org/about/news/postgresql-13-released-2077/).
[Stored procedures](https://www.postgresql.org/docs/14/sql-createprocedure.html),
which allow for transaction control in a block of code, can now return data by
using `OUT` parameters.
PostgreSQL 14 introduces the ability to "bin", or align, timestamps to a
particular interval using the [`date_bin`](https://www.postgresql.org/docs/14/functions-datetime.html#FUNCTIONS-DATETIME-BIN)
function. This release also adds the SQL conforming
[`SEARCH`](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-SEARCH)
and [`CYCLE`](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-CYCLE)
directives to help with ordering and cycle detection for recursive
[common table expressions](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-RECURSIVE).
### Security Enhancements
PostgreSQL 14 makes it convenient to assign read-only and write-only privileges
to users on tables, views, and schemas using the `pg_read_all_data` and
`pg_write_all_data` [predefined roles](https://www.postgresql.org/docs/14/predefined-roles.html).
Additionally, this release now makes the standard compliant
[`SCRAM-SHA-256`](https://www.postgresql.org/docs/14/auth-password.html) password
management and authentication system the default on new PostgreSQL instances.
### About PostgreSQL
[PostgreSQL](https://www.postgresql.org) is the world's most advanced open
source database, with a global community of thousands of users, contributors,
companies and organizations. Built on over 30 years of engineering, starting at
the University of California, Berkeley, PostgreSQL has continued with an
unmatched pace of development. PostgreSQL's mature feature set not only matches
top proprietary database systems, but exceeds them in advanced database
features, extensibility, security, and stability.
### Links
* [Download](https://www.postgresql.org/download/)
* [Release Notes](https://www.postgresql.org/docs/14/release-14.html)
* [Press Kit](https://www.postgresql.org/about/press/)
* [Security Page](https://www.postgresql.org/support/security/)
* [Versioning Policy](https://www.postgresql.org/support/versioning/)
* [Follow @postgresql on Twitter](https://twitter.com/postgresql)
[application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-09-22 14:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-21 02:19 PostgreSQL 14 press release draft Jonathan S. Katz <[email protected]>
2021-09-21 07:16 ` Laurenz Albe <[email protected]>
2021-09-22 14:18 ` Jonathan S. Katz <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox