public inbox for [email protected]
help / color / mirror / Atom feedPostgreSQL 14 Beta 1 release announcement draft
12+ messages / 7 participants
[nested] [flat]
* PostgreSQL 14 Beta 1 release announcement draft
@ 2021-05-17 14:15 Jonathan S. Katz <[email protected]>
2021-05-17 15:01 ` Re: PostgreSQL 14 Beta 1 release announcement draft Erik Rijkers <[email protected]>
2021-05-17 17:24 ` Re: PostgreSQL 14 Beta 1 release announcement draft Justin Pryzby <[email protected]>
2021-05-17 19:32 ` Re: PostgreSQL 14 Beta 1 release announcement draft Peter Geoghegan <[email protected]>
2021-05-17 22:26 ` Re: PostgreSQL 14 Beta 1 release announcement draft Darren Duncan <[email protected]>
2021-05-18 01:01 ` Re: PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
0 siblings, 5 replies; 12+ messages in thread
From: Jonathan S. Katz @ 2021-05-17 14:15 UTC (permalink / raw)
To: PostgreSQL Advocacy <[email protected]>
Hi,
Please see the attached draft for the PostgreSQL 14 Beta 1 release
announcement this week.
As with the past beta releases, the primary goal of this announcement is
to make the public aware of the beta release and encourage testing. The
announcement serves as a springboard for feature awareness and to
inspire real world (or equivalent) testing.
Note that this announcement is not ranking the features in any
particular way, and while it's exciting to see all the new features
going into PostgreSQL 14, we cannot include all of them in the announcement.
Please review the announcement for technical inaccuracies and notable
feature omissions, and please provide feedback no later than Wed, May 19
AoE[1]
Thanks!
Jonathan
[1] https://en.wikipedia.org/wiki/Anywhere_on_Earth
The PostgreSQL Global Development Group announces that the first beta release of
PostgreSQL 14 is now [available for download](https://www.postgresql.org/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):
[https://www.postgresql.org/docs/14/release-14.html](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](https://www.postgresql.org/developer/beta/) and how
you can contribute:
[https://www.postgresql.org/developer/beta/](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 in
PostgreSQL systems that have large numbers of connections to the database,
regardless if they are active or idle.
PostgreSQL 14 also contains additional gains in reducing B-tree indexing
overhead, including reducing the bloat on tables with frequently updated
indexes. [GiST indexes](https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPE-GIST)
can now use pre-sorted data during their build process, allowing both for faster
index creation and smaller indexes.
[SP-GiST indexes](https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPE-SPGIST)
now support [covering indexes](https://www.postgresql.org/docs/14/indexes-index-only-scans.html)
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](https://www.postgresql.org/docs/14/parallel-plans.html#PARALLEL-SCANS),
the `RETURN QUERY` directive in [PL/pgSQL](https://www.postgresql.org/docs/14/plpgsql.html)
can now execute queries with parallelism.
[`REFRESH MATERIALIZED VIEW`](https://www.postgresql.org/docs/14/sql-refreshmaterializedview.html)
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](https://www.postgresql.org/docs/14/sql-createforeigndatawrapper.html).
The PostgreSQL foreign data wrapper, [`postgres_fdw`](https://www.postgresql.org/docs/14/postgres-fdw.html),
added support for this in PostgreSQL 14 when the `async_capable` flag is it.
`postgres_fdw` also supports bulk inserts and can import table partitions using
[`IMPORT FOREIGN SCHEMA`](https://www.postgresql.org/docs/14/sql-importforeignschema.html)
and can now execute [`TRUNCATE`](https://www.postgresql.org/docs/14/sql-truncate.html)
can 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`](https://www.postgresql.org/docs/14/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION)
command.
Incremental sorting, introduced in the previous release, can now be used by
[window functions](https://www.postgresql.org/docs/14/functions-window.html) in
PostgreSQL 14. This new release adds more capabilities to [extended statistics](https://www.postgresql.org/docs/14/planner-stats.html),
which can now be used on [expressions](https://www.postgresql.org/docs/14/view-pg-stats-ext-exprs.html).
PostgreSQL has supported compression on its "oversized data" columns (i.e. the
[TOAST](https://www.postgresql.org/docs/14/storage-toast.html) system) for
decades, but this release adds the ability to now choose to use
[LZ4 compression](https://www.postgresql.org/docs/14/sql-createtable.html) on
tables.
### Data Types + SQL
Building upon its existing support for [range types](https://www.postgresql.org/docs/14/rangetypes.html),
PostgreSQL 14 adds the new [multirange type](https://www.postgresql.org/docs/14/rangetypes.html#RANGETYPES-BUILTIN)
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, using the `JSONB` data type with,
you now are able to retrieve nested info just using the `.` operator (e.g.
`this.now.works.to.retrieve.this.json.data`).
PostgreSQL 14 also adds support for `OUT` parameters in [stored procedures](https://www.postgresql.org/docs/14/sql-createprocedure.html),
and allows for the `GROUP BY` clause to use the `DISTINCT` keyword to remove duplicate
`GROUPING SET` combinations.
For recursive [common table expressions](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-RECURSIVE)
(`WITH` queries), PostgreSQL 14 adds the syntactic conveniences of [`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)
to help with ordering and cycle detection respectively.
There is also the new [`date_bin`](https://www.postgresql.org/docs/14/functions-datetime.html#FUNCTIONS-DATETIME-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`](https://www.postgresql.org/docs/14/routine-vacuuming.html),
with optimizations geared towards indexes. Autovacuum now analyzes partitioned
tables and can bubble information about row counts to parent tables. There are
also performance gains in [`ANALYZE`](https://www.postgresql.org/docs/14/sql-analyze.html)
that can be controlled with [`maintenance_io_concurrency`](https://www.postgresql.org/docs/14/runtime-config-resource.html#GUC-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`](https://www.postgresql.org/docs/14/progress-reporting.html#COPY-PROGRESS-REPORTING)
view. This release lets WAL activity be tracked from the [`pg_stat_wal`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW)
view, and to inspect replication slot status from the [`pg_stat_replication_slots`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-SLOTS-VIEW)
view.
There are several new parameters in PostgreSQL 14 to help manage connections.
These include [`idle_session_timeout`](https://www.postgresql.org/docs/14/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT),
which can close idle connections after the specified period, and
[`client_connection_check_interval`](https://www.postgresql.org/docs/14/runtime-config-connection.html#GUC-CLIENT-CONNECTION-CHECK-INTERV...)
parameter that lets PostgreSQL cancel long running queries if a client
disconnects.
The [`REINDEX`](https://www.postgresql.org/docs/14/sql-reindex.html) command in
can now process all of the child indexes of a partitioned table, and PostgreSQL
14 adds the [`pg_amcheck`](https://www.postgresql.org/docs/14/app-pgamcheck.html)
utility to help check for data corruption.
### Replication & Recovery
PostgreSQL 14 adds numerous performance benefits for logical replication,
including the ability to stream in-process transactions to subscribers instead
of waiting for them to be completed. The [`ALTER SUBSCRIPTION`](https://www.postgresql.org/docs/14/sql-altersubscription.html)
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`](https://www.postgresql.org/docs/14/app-pgrewind.html) 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](https://www.postgresql.org/docs/14/predefined-roles.html),
respectively.
This release also defaults to using [SCRAM-SHA-256](https://www.postgresql.org/docs/14/auth-password.html)
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](https://www.postgresql.org/docs/14/auth-cert.html)
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](https://www.postgresql.org/docs/14/release-14.html)
for a complete list of new and changed features:
[https://www.postgresql.org/docs/14/release-14.html](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](https://wiki.postgresql.org/wiki/PostgreSQL_14_Open_Items)
is publicly available in the PostgreSQL wiki. You can
[report bugs](https://www.postgresql.org/account/submitbug/) using this form on
the PostgreSQL website:
[https://www.postgresql.org/account/submitbug/](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](https://www.postgresql.org/developer/beta/) page.
Links
-----
* [Download](https://www.postgresql.org/download/)
* [Beta Testing Information](https://www.postgresql.org/developer/beta/)
* [PostgreSQL 14 Beta Release Notes](https://www.postgresql.org/docs/14/release-14.html)
* [PostgreSQL 14 Open Issues](https://wiki.postgresql.org/wiki/PostgreSQL_14_Open_Items)
* [Submit a Bug](https://www.postgresql.org/account/submitbug/)
Attachments:
[text/plain] 14beta1.md (11.8K, ../../[email protected]/2-14beta1.md)
download | inline:
The PostgreSQL Global Development Group announces that the first beta release of
PostgreSQL 14 is now [available for download](https://www.postgresql.org/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):
[https://www.postgresql.org/docs/14/release-14.html](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](https://www.postgresql.org/developer/beta/) and how
you can contribute:
[https://www.postgresql.org/developer/beta/](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 in
PostgreSQL systems that have large numbers of connections to the database,
regardless if they are active or idle.
PostgreSQL 14 also contains additional gains in reducing B-tree indexing
overhead, including reducing the bloat on tables with frequently updated
indexes. [GiST indexes](https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPE-GIST)
can now use pre-sorted data during their build process, allowing both for faster
index creation and smaller indexes.
[SP-GiST indexes](https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPE-SPGIST)
now support [covering indexes](https://www.postgresql.org/docs/14/indexes-index-only-scans.html)
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](https://www.postgresql.org/docs/14/parallel-plans.html#PARALLEL-SCANS),
the `RETURN QUERY` directive in [PL/pgSQL](https://www.postgresql.org/docs/14/plpgsql.html)
can now execute queries with parallelism.
[`REFRESH MATERIALIZED VIEW`](https://www.postgresql.org/docs/14/sql-refreshmaterializedview.html)
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](https://www.postgresql.org/docs/14/sql-createforeigndatawrapper.html).
The PostgreSQL foreign data wrapper, [`postgres_fdw`](https://www.postgresql.org/docs/14/postgres-fdw.html),
added support for this in PostgreSQL 14 when the `async_capable` flag is it.
`postgres_fdw` also supports bulk inserts and can import table partitions using
[`IMPORT FOREIGN SCHEMA`](https://www.postgresql.org/docs/14/sql-importforeignschema.html)
and can now execute [`TRUNCATE`](https://www.postgresql.org/docs/14/sql-truncate.html)
can 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`](https://www.postgresql.org/docs/14/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION)
command.
Incremental sorting, introduced in the previous release, can now be used by
[window functions](https://www.postgresql.org/docs/14/functions-window.html) in
PostgreSQL 14. This new release adds more capabilities to [extended statistics](https://www.postgresql.org/docs/14/planner-stats.html),
which can now be used on [expressions](https://www.postgresql.org/docs/14/view-pg-stats-ext-exprs.html).
PostgreSQL has supported compression on its "oversized data" columns (i.e. the
[TOAST](https://www.postgresql.org/docs/14/storage-toast.html) system) for
decades, but this release adds the ability to now choose to use
[LZ4 compression](https://www.postgresql.org/docs/14/sql-createtable.html) on
tables.
### Data Types + SQL
Building upon its existing support for [range types](https://www.postgresql.org/docs/14/rangetypes.html),
PostgreSQL 14 adds the new [multirange type](https://www.postgresql.org/docs/14/rangetypes.html#RANGETYPES-BUILTIN)
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, using the `JSONB` data type with,
you now are able to retrieve nested info just using the `.` operator (e.g.
`this.now.works.to.retrieve.this.json.data`).
PostgreSQL 14 also adds support for `OUT` parameters in [stored procedures](https://www.postgresql.org/docs/14/sql-createprocedure.html),
and allows for the `GROUP BY` clause to use the `DISTINCT` keyword to remove duplicate
`GROUPING SET` combinations.
For recursive [common table expressions](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-RECURSIVE)
(`WITH` queries), PostgreSQL 14 adds the syntactic conveniences of [`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)
to help with ordering and cycle detection respectively.
There is also the new [`date_bin`](https://www.postgresql.org/docs/14/functions-datetime.html#FUNCTIONS-DATETIME-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`](https://www.postgresql.org/docs/14/routine-vacuuming.html),
with optimizations geared towards indexes. Autovacuum now analyzes partitioned
tables and can bubble information about row counts to parent tables. There are
also performance gains in [`ANALYZE`](https://www.postgresql.org/docs/14/sql-analyze.html)
that can be controlled with [`maintenance_io_concurrency`](https://www.postgresql.org/docs/14/runtime-config-resource.html#GUC-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`](https://www.postgresql.org/docs/14/progress-reporting.html#COPY-PROGRESS-REPORTING)
view. This release lets WAL activity be tracked from the [`pg_stat_wal`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW)
view, and to inspect replication slot status from the [`pg_stat_replication_slots`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-SLOTS-VIEW)
view.
There are several new parameters in PostgreSQL 14 to help manage connections.
These include [`idle_session_timeout`](https://www.postgresql.org/docs/14/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT),
which can close idle connections after the specified period, and
[`client_connection_check_interval`](https://www.postgresql.org/docs/14/runtime-config-connection.html#GUC-CLIENT-CONNECTION-CHECK-INTERVAL)
parameter that lets PostgreSQL cancel long running queries if a client
disconnects.
The [`REINDEX`](https://www.postgresql.org/docs/14/sql-reindex.html) command in
can now process all of the child indexes of a partitioned table, and PostgreSQL
14 adds the [`pg_amcheck`](https://www.postgresql.org/docs/14/app-pgamcheck.html)
utility to help check for data corruption.
### Replication & Recovery
PostgreSQL 14 adds numerous performance benefits for logical replication,
including the ability to stream in-process transactions to subscribers instead
of waiting for them to be completed. The [`ALTER SUBSCRIPTION`](https://www.postgresql.org/docs/14/sql-altersubscription.html)
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`](https://www.postgresql.org/docs/14/app-pgrewind.html) 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](https://www.postgresql.org/docs/14/predefined-roles.html),
respectively.
This release also defaults to using [SCRAM-SHA-256](https://www.postgresql.org/docs/14/auth-password.html)
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](https://www.postgresql.org/docs/14/auth-cert.html)
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](https://www.postgresql.org/docs/14/release-14.html)
for a complete list of new and changed features:
[https://www.postgresql.org/docs/14/release-14.html](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](https://wiki.postgresql.org/wiki/PostgreSQL_14_Open_Items)
is publicly available in the PostgreSQL wiki. You can
[report bugs](https://www.postgresql.org/account/submitbug/) using this form on
the PostgreSQL website:
[https://www.postgresql.org/account/submitbug/](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](https://www.postgresql.org/developer/beta/) page.
Links
-----
* [Download](https://www.postgresql.org/download/)
* [Beta Testing Information](https://www.postgresql.org/developer/beta/)
* [PostgreSQL 14 Beta Release Notes](https://www.postgresql.org/docs/14/release-14.html)
* [PostgreSQL 14 Open Issues](https://wiki.postgresql.org/wiki/PostgreSQL_14_Open_Items)
* [Submit a Bug](https://www.postgresql.org/account/submitbug/)
[application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 14 Beta 1 release announcement draft
2021-05-17 14:15 PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
@ 2021-05-17 15:01 ` Erik Rijkers <[email protected]>
2021-05-18 10:33 ` Re: PostgreSQL 14 Beta 1 release announcement draft Erik Rijkers <[email protected]>
4 siblings, 1 reply; 12+ messages in thread
From: Erik Rijkers @ 2021-05-17 15:01 UTC (permalink / raw)
To: Jonathan S. Katz <[email protected]>; PostgreSQL Advocacy <[email protected]>
On 5/17/21 4:15 PM, Jonathan S. Katz wrote:
> Please see the attached draft for the PostgreSQL 14 Beta 1 release
> announcement this week.
[..]
Here are a few changes:
when the `async_capable` flag is it. should be:
when the `async_capable` flag is set.
can on foreign tables. should be:
on foreign tables.
using the `JSONB` data type with, should be:
using the `JSONB` data type,
Admittedly I am not a native speaker but that I do not understand
'bubble' is perhaps reason to rephrase?
command in can now should be:
command can now
Thank you,
Erik Rijkers
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 14 Beta 1 release announcement draft
2021-05-17 14:15 PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
2021-05-17 15:01 ` Re: PostgreSQL 14 Beta 1 release announcement draft Erik Rijkers <[email protected]>
@ 2021-05-18 10:33 ` Erik Rijkers <[email protected]>
2021-05-18 21:11 ` Re: PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Erik Rijkers @ 2021-05-18 10:33 UTC (permalink / raw)
To: Jonathan S. Katz <[email protected]>; PostgreSQL Advocacy <[email protected]>
14beta1.md mentions subscripting:
"PostgreSQL 14 now adds a general subscripting framework for retrieving
information in nested objects. For example, using the `JSONB` data type
with,
you now are able to retrieve nested info just using the `.` operator (e.g.
`this.now.works.to.retrieve.this.json.data`)."
Is this correct? I thought the provided JSON subscripting functionality is:
select
jsonb_pretty(js)
, js['this']
, js['this']['now']
, js['this']['now']['works']
, js['this']['now']['works'][1]
from
(values ('{"this": { "now": {"works": [1,2] } } }'::jsonb)) as f(js)
;
jsonb_pretty | js | js |
js | js
------------------------+----------------------------+-------------------+--------+----
{ +| {"now": {"works": [1, 2]}} | {"works": [1,
2]} | [1, 2] | 2
"this": { +| | | |
"now": { +| | | |
"works": [+| | | |
1, +| | | |
2 +| | | |
] +| | | |
} +| | | |
} +| | | |
} | | | |
(1 row)
Where can the `.` operator be used in JSON(B)? What did I miss?
Erik Rijkers
On 5/17/21 5:01 PM, Erik Rijkers wrote:
> On 5/17/21 4:15 PM, Jonathan S. Katz wrote:
>> Please see the attached draft for the PostgreSQL 14 Beta 1 release
>> announcement this week.
>
> [..]
>
> Here are a few changes:
>
>
> when the `async_capable` flag is it. should be:
>
> when the `async_capable` flag is set.
>
>
> can on foreign tables. should be:
> on foreign tables.
>
>
> using the `JSONB` data type with, should be: using the `JSONB` data
> type,
>
>
> Admittedly I am not a native speaker but that I do not understand
>
> 'bubble' is perhaps reason to rephrase?
>
>
> command in can now should be:
> command can now
>
>
> Thank you,
>
> Erik Rijkers
>
>
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 14 Beta 1 release announcement draft
2021-05-17 14:15 PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
2021-05-17 15:01 ` Re: PostgreSQL 14 Beta 1 release announcement draft Erik Rijkers <[email protected]>
2021-05-18 10:33 ` Re: PostgreSQL 14 Beta 1 release announcement draft Erik Rijkers <[email protected]>
@ 2021-05-18 21:11 ` Jonathan S. Katz <[email protected]>
2021-05-19 02:08 ` Re: PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Jonathan S. Katz @ 2021-05-18 21:11 UTC (permalink / raw)
To: Erik Rijkers <[email protected]>; PostgreSQL Advocacy <[email protected]>
On 5/18/21 6:33 AM, Erik Rijkers wrote:
>
> 14beta1.md mentions subscripting:
>
> "PostgreSQL 14 now adds a general subscripting framework for retrieving
> information in nested objects. For example, using the `JSONB` data type
> with,
> you now are able to retrieve nested info just using the `.` operator (e.g.
> `this.now.works.to.retrieve.this.json.data`)."
>
> Is this correct? I thought the provided JSON subscripting functionality
> is:
Oops -- great catch, that's 100% on me and my failure to read/test. I'll
get a modified example into the announcement.
Thanks,
Jonathan
Attachments:
[application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/2-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 14 Beta 1 release announcement draft
2021-05-17 14:15 PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
2021-05-17 15:01 ` Re: PostgreSQL 14 Beta 1 release announcement draft Erik Rijkers <[email protected]>
2021-05-18 10:33 ` Re: PostgreSQL 14 Beta 1 release announcement draft Erik Rijkers <[email protected]>
2021-05-18 21:11 ` Re: PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
@ 2021-05-19 02:08 ` Jonathan S. Katz <[email protected]>
2021-05-20 13:49 ` Re: PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
0 siblings, 1 reply; 12+ messages in thread
From: Jonathan S. Katz @ 2021-05-19 02:08 UTC (permalink / raw)
To: Erik Rijkers <[email protected]>; PostgreSQL Advocacy <[email protected]>
On 5/18/21 5:11 PM, Jonathan S. Katz wrote:
> On 5/18/21 6:33 AM, Erik Rijkers wrote:
>>
>> 14beta1.md mentions subscripting:
>>
>> "PostgreSQL 14 now adds a general subscripting framework for retrieving
>> information in nested objects. For example, using the `JSONB` data type
>> with,
>> you now are able to retrieve nested info just using the `.` operator (e.g.
>> `this.now.works.to.retrieve.this.json.data`)."
>>
>> Is this correct? I thought the provided JSON subscripting functionality
>> is:
>
> Oops -- great catch, that's 100% on me and my failure to read/test. I'll
> get a modified example into the announcement.
Updated, along with comments from earlier today. Latest draft attached.
Review credit given in the press repo.
Thanks!
Jonathan
The PostgreSQL Global Development Group announces that the first beta release of
PostgreSQL 14 is now [available for download](https://www.postgresql.org/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):
[https://www.postgresql.org/docs/14/release-14.html](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](https://www.postgresql.org/developer/beta/) and how
you can contribute:
[https://www.postgresql.org/developer/beta/](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](https://www.postgresql.org/docs/14/btree-implementation.html#BTREE-DELETION).
[GiST indexes](https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPE-GIST)
can now presort data during their build process, allowing both for faster
index creation and smaller indexes.
[SP-GiST indexes](https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPE-SPGIST)
now support [covering indexes](https://www.postgresql.org/docs/14/indexes-index-only-scans.html)
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](https://www.postgresql.org/docs/14/parallel-plans.html#PARALLEL-SCANS),
the `RETURN QUERY` directive in [PL/pgSQL](https://www.postgresql.org/docs/14/plpgsql.html)
can now execute queries with parallelism.
[`REFRESH MATERIALIZED VIEW`](https://www.postgresql.org/docs/14/sql-refreshmaterializedview.html)
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](https://www.postgresql.org/docs/14/sql-createforeigndatawrapper.html).
The PostgreSQL foreign data wrapper, [`postgres_fdw`](https://www.postgresql.org/docs/14/postgres-fdw.html),
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`](https://www.postgresql.org/docs/14/sql-importforeignschema.html)
and can now execute [`TRUNCATE`](https://www.postgresql.org/docs/14/sql-truncate.html)
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`](https://www.postgresql.org/docs/14/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION)
command.
Incremental sorting, introduced in the previous release, can now be used by
[window functions](https://www.postgresql.org/docs/14/functions-window.html) in
PostgreSQL 14. This new release adds more capabilities to [extended statistics](https://www.postgresql.org/docs/14/planner-stats.html),
which can now be applied to [expressions](https://www.postgresql.org/docs/14/view-pg-stats-ext-exprs.html).
PostgreSQL has supported compression on its "oversized data" columns (i.e. the
[TOAST](https://www.postgresql.org/docs/14/storage-toast.html) system) for
decades, but this release adds the ability to now choose to use
[LZ4 compression](https://www.postgresql.org/docs/14/sql-createtable.html) for
column compression.
### Data Types + SQL
Building upon its existing support for [range types](https://www.postgresql.org/docs/14/rangetypes.html),
PostgreSQL 14 adds the new [multirange type](https://www.postgresql.org/docs/14/rangetypes.html#RANGETYPES-BUILTIN)
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](https://www.postgresql.org/docs/14/sql-createprocedure.html),
and allows for the `GROUP BY` clause to use the `DISTINCT` keyword to remove duplicate
`GROUPING SET` combinations.
For recursive [common table expressions](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-RECURSIVE)
(`WITH` queries), PostgreSQL 14 adds the syntactic conveniences of [`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)
to help with ordering and cycle detection respectively.
There is also the new [`date_bin`](https://www.postgresql.org/docs/14/functions-datetime.html#FUNCTIONS-DATETIME-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`](https://www.postgresql.org/docs/14/routine-vacuuming.html),
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`](https://www.postgresql.org/docs/14/sql-analyze.html)
that can be controlled with [`maintenance_io_concurrency`](https://www.postgresql.org/docs/14/runtime-config-resource.html#GUC-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`](https://www.postgresql.org/docs/14/progress-reporting.html#COPY-PROGRESS-REPORTING)
view. This release lets you track WAL activity from the [`pg_stat_wal`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW)
view, and inspect replication slot statistics from the [`pg_stat_replication_slots`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-SLOTS-VIEW)
view.
There are several new parameters in PostgreSQL 14 to help manage connections.
These include [`idle_session_timeout`](https://www.postgresql.org/docs/14/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT),
which can close idle connections after the specified period, and
[`client_connection_check_interval`](https://www.postgresql.org/docs/14/runtime-config-connection.html#GUC-CLIENT-CONNECTION-CHECK-INTERV...)
parameter that lets PostgreSQL cancel long running queries if a client
disconnects.
The [`REINDEX`](https://www.postgresql.org/docs/14/sql-reindex.html) command can
now process all of the child indexes of a partitioned table, and PostgreSQL 14
adds the [`pg_amcheck`](https://www.postgresql.org/docs/14/app-pgamcheck.html)
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`](https://www.postgresql.org/docs/14/sql-altersubscription.html)
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`](https://www.postgresql.org/docs/14/app-pgrewind.html) 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](https://www.postgresql.org/docs/14/predefined-roles.html),
respectively.
This release also defaults to using [SCRAM-SHA-256](https://www.postgresql.org/docs/14/auth-password.html)
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](https://www.postgresql.org/docs/14/auth-cert.html)
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](https://www.postgresql.org/docs/14/release-14.html)
for a complete list of new and changed features:
[https://www.postgresql.org/docs/14/release-14.html](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](https://wiki.postgresql.org/wiki/PostgreSQL_14_Open_Items)
is publicly available in the PostgreSQL wiki. You can
[report bugs](https://www.postgresql.org/account/submitbug/) using this form on
the PostgreSQL website:
[https://www.postgresql.org/account/submitbug/](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](https://www.postgresql.org/developer/beta/) page.
Links
-----
* [Download](https://www.postgresql.org/download/)
* [Beta Testing Information](https://www.postgresql.org/developer/beta/)
* [PostgreSQL 14 Beta Release Notes](https://www.postgresql.org/docs/14/release-14.html)
* [PostgreSQL 14 Open Issues](https://wiki.postgresql.org/wiki/PostgreSQL_14_Open_Items)
* [Submit a Bug](https://www.postgresql.org/account/submitbug/)
Attachments:
[text/plain] 14beta1.md (11.9K, ../../[email protected]/2-14beta1.md)
download | inline:
The PostgreSQL Global Development Group announces that the first beta release of
PostgreSQL 14 is now [available for download](https://www.postgresql.org/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):
[https://www.postgresql.org/docs/14/release-14.html](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](https://www.postgresql.org/developer/beta/) and how
you can contribute:
[https://www.postgresql.org/developer/beta/](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](https://www.postgresql.org/docs/14/btree-implementation.html#BTREE-DELETION).
[GiST indexes](https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPE-GIST)
can now presort data during their build process, allowing both for faster
index creation and smaller indexes.
[SP-GiST indexes](https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPE-SPGIST)
now support [covering indexes](https://www.postgresql.org/docs/14/indexes-index-only-scans.html)
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](https://www.postgresql.org/docs/14/parallel-plans.html#PARALLEL-SCANS),
the `RETURN QUERY` directive in [PL/pgSQL](https://www.postgresql.org/docs/14/plpgsql.html)
can now execute queries with parallelism.
[`REFRESH MATERIALIZED VIEW`](https://www.postgresql.org/docs/14/sql-refreshmaterializedview.html)
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](https://www.postgresql.org/docs/14/sql-createforeigndatawrapper.html).
The PostgreSQL foreign data wrapper, [`postgres_fdw`](https://www.postgresql.org/docs/14/postgres-fdw.html),
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`](https://www.postgresql.org/docs/14/sql-importforeignschema.html)
and can now execute [`TRUNCATE`](https://www.postgresql.org/docs/14/sql-truncate.html)
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`](https://www.postgresql.org/docs/14/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION)
command.
Incremental sorting, introduced in the previous release, can now be used by
[window functions](https://www.postgresql.org/docs/14/functions-window.html) in
PostgreSQL 14. This new release adds more capabilities to [extended statistics](https://www.postgresql.org/docs/14/planner-stats.html),
which can now be applied to [expressions](https://www.postgresql.org/docs/14/view-pg-stats-ext-exprs.html).
PostgreSQL has supported compression on its "oversized data" columns (i.e. the
[TOAST](https://www.postgresql.org/docs/14/storage-toast.html) system) for
decades, but this release adds the ability to now choose to use
[LZ4 compression](https://www.postgresql.org/docs/14/sql-createtable.html) for
column compression.
### Data Types + SQL
Building upon its existing support for [range types](https://www.postgresql.org/docs/14/rangetypes.html),
PostgreSQL 14 adds the new [multirange type](https://www.postgresql.org/docs/14/rangetypes.html#RANGETYPES-BUILTIN)
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](https://www.postgresql.org/docs/14/sql-createprocedure.html),
and allows for the `GROUP BY` clause to use the `DISTINCT` keyword to remove duplicate
`GROUPING SET` combinations.
For recursive [common table expressions](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-RECURSIVE)
(`WITH` queries), PostgreSQL 14 adds the syntactic conveniences of [`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)
to help with ordering and cycle detection respectively.
There is also the new [`date_bin`](https://www.postgresql.org/docs/14/functions-datetime.html#FUNCTIONS-DATETIME-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`](https://www.postgresql.org/docs/14/routine-vacuuming.html),
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`](https://www.postgresql.org/docs/14/sql-analyze.html)
that can be controlled with [`maintenance_io_concurrency`](https://www.postgresql.org/docs/14/runtime-config-resource.html#GUC-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`](https://www.postgresql.org/docs/14/progress-reporting.html#COPY-PROGRESS-REPORTING)
view. This release lets you track WAL activity from the [`pg_stat_wal`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW)
view, and inspect replication slot statistics from the [`pg_stat_replication_slots`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-SLOTS-VIEW)
view.
There are several new parameters in PostgreSQL 14 to help manage connections.
These include [`idle_session_timeout`](https://www.postgresql.org/docs/14/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT),
which can close idle connections after the specified period, and
[`client_connection_check_interval`](https://www.postgresql.org/docs/14/runtime-config-connection.html#GUC-CLIENT-CONNECTION-CHECK-INTERVAL)
parameter that lets PostgreSQL cancel long running queries if a client
disconnects.
The [`REINDEX`](https://www.postgresql.org/docs/14/sql-reindex.html) command can
now process all of the child indexes of a partitioned table, and PostgreSQL 14
adds the [`pg_amcheck`](https://www.postgresql.org/docs/14/app-pgamcheck.html)
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`](https://www.postgresql.org/docs/14/sql-altersubscription.html)
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`](https://www.postgresql.org/docs/14/app-pgrewind.html) 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](https://www.postgresql.org/docs/14/predefined-roles.html),
respectively.
This release also defaults to using [SCRAM-SHA-256](https://www.postgresql.org/docs/14/auth-password.html)
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](https://www.postgresql.org/docs/14/auth-cert.html)
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](https://www.postgresql.org/docs/14/release-14.html)
for a complete list of new and changed features:
[https://www.postgresql.org/docs/14/release-14.html](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](https://wiki.postgresql.org/wiki/PostgreSQL_14_Open_Items)
is publicly available in the PostgreSQL wiki. You can
[report bugs](https://www.postgresql.org/account/submitbug/) using this form on
the PostgreSQL website:
[https://www.postgresql.org/account/submitbug/](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](https://www.postgresql.org/developer/beta/) page.
Links
-----
* [Download](https://www.postgresql.org/download/)
* [Beta Testing Information](https://www.postgresql.org/developer/beta/)
* [PostgreSQL 14 Beta Release Notes](https://www.postgresql.org/docs/14/release-14.html)
* [PostgreSQL 14 Open Issues](https://wiki.postgresql.org/wiki/PostgreSQL_14_Open_Items)
* [Submit a Bug](https://www.postgresql.org/account/submitbug/)
[application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 14 Beta 1 release announcement draft
2021-05-17 14:15 PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
2021-05-17 15:01 ` Re: PostgreSQL 14 Beta 1 release announcement draft Erik Rijkers <[email protected]>
2021-05-18 10:33 ` Re: PostgreSQL 14 Beta 1 release announcement draft Erik Rijkers <[email protected]>
2021-05-18 21:11 ` Re: PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
2021-05-19 02:08 ` Re: PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
@ 2021-05-20 13:49 ` Jonathan S. Katz <[email protected]>
0 siblings, 0 replies; 12+ messages in thread
From: Jonathan S. Katz @ 2021-05-20 13:49 UTC (permalink / raw)
To: Erik Rijkers <[email protected]>; PostgreSQL Advocacy <[email protected]>
On 5/18/21 10:08 PM, Jonathan S. Katz wrote:
> On 5/18/21 5:11 PM, Jonathan S. Katz wrote:
>> On 5/18/21 6:33 AM, Erik Rijkers wrote:
>>>
>>> 14beta1.md mentions subscripting:
>>>
>>> "PostgreSQL 14 now adds a general subscripting framework for retrieving
>>> information in nested objects. For example, using the `JSONB` data type
>>> with,
>>> you now are able to retrieve nested info just using the `.` operator (e.g.
>>> `this.now.works.to.retrieve.this.json.data`)."
>>>
>>> Is this correct? I thought the provided JSON subscripting functionality
>>> is:
>>
>> Oops -- great catch, that's 100% on me and my failure to read/test. I'll
>> get a modified example into the announcement.
>
> Updated, along with comments from earlier today. Latest draft attached.
> Review credit given in the press repo.
The PostgreSQL 14 beta release announcement is live:
https://www.postgresql.org/about/news/postgresql-14-beta-1-released-2213/
Thank you everyone for your feedback!
Jonathan
Attachments:
[application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/2-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 14 Beta 1 release announcement draft
2021-05-17 14:15 PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
@ 2021-05-17 17:24 ` Justin Pryzby <[email protected]>
4 siblings, 0 replies; 12+ messages in thread
From: Justin Pryzby @ 2021-05-17 17:24 UTC (permalink / raw)
To: Jonathan S. Katz <[email protected]>; +Cc: [email protected]
On Mon, May 17, 2021 at 10:15:56AM -0400, Jonathan S. Katz wrote:
> This release has significant improvements in transaction throughput in
> PostgreSQL systems that have large numbers of connections to the database,
> regardless if they are active or idle.
regardless of whether
(I think)
> indexes. [GiST indexes](https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPE-GIST)
> can now use pre-sorted data during their build process, allowing both for faster
> index creation and smaller indexes.
Instead of "use pre-sorted" I think it should say "can now pre-sort.."
> [SP-GiST indexes](https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPE-SPGIST)
> now support [covering indexes](https://www.postgresql.org/docs/14/indexes-index-only-scans.html)
> that allow users to add additional, nonsearchable columns to the index through
> the `INCLUDE` clause.
*in the index
> PostgreSQL 14. This new release adds more capabilities to [extended statistics](https://www.postgresql.org/docs/14/planner-stats.html),
> which can now be used on [expressions](https://www.postgresql.org/docs/14/view-pg-stats-ext-exprs.html).
can be *created on expressions
> PostgreSQL has supported compression on its "oversized data" columns (i.e. the
> [TOAST](https://www.postgresql.org/docs/14/storage-toast.html) system) for
> decades, but this release adds the ability to now choose to use
> [LZ4 compression](https://www.postgresql.org/docs/14/sql-createtable.html) on
> tables.
say "on TOAST tables", otherwise it sounds like what's new is "tables"
rather than "LZ4".
> 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`](https://www.postgresql.org/docs/14/progress-reporting.html#COPY-PROGRESS-REPORTING)
> view. This release lets WAL activity be tracked from the [`pg_stat_wal`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW)
"allows WAL activity to be tracked"
> The [`REINDEX`](https://www.postgresql.org/docs/14/sql-reindex.html) command in
in *PostgreSQL 14
> of waiting for them to be completed. The [`ALTER SUBSCRIPTION`](https://www.postgresql.org/docs/14/sql-altersubscription.html)
> makes it easier to add/remove publications using the new `ADD/DROP PUBLICATION`]
> syntax.
missing "command"
> PostgreSQL 14 now adds a general subscripting framework for retrieving
remove "now"
As usual, Erik already reported half of my typos ;)
--
Justin
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 14 Beta 1 release announcement draft
2021-05-17 14:15 PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
@ 2021-05-17 19:32 ` Peter Geoghegan <[email protected]>
4 siblings, 0 replies; 12+ messages in thread
From: Peter Geoghegan @ 2021-05-17 19:32 UTC (permalink / raw)
To: Jonathan S. Katz <[email protected]>; +Cc: PostgreSQL Advocacy <[email protected]>
On Mon, May 17, 2021 at 7:16 AM Jonathan S. Katz <[email protected]> wrote:
> Please review the announcement for technical inaccuracies and notable
> feature omissions, and please provide feedback no later than Wed, May 19
> AoE[1]
I think that the B-Tree item (bottom-up index deletion) would benefit
from a link to the documentation that describes the feature, for those
that want to drill down:
https://www.postgresql.org/docs/14/btree-implementation.html#BTREE-DELETION
(Though see https://www.postgresql.org/docs/devel/btree-implementation.html#BTREE-DELETION
for the devel version, which is the only version available from the
website at this time.)
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 14 Beta 1 release announcement draft
2021-05-17 14:15 PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
@ 2021-05-17 22:26 ` Darren Duncan <[email protected]>
4 siblings, 0 replies; 12+ messages in thread
From: Darren Duncan @ 2021-05-17 22:26 UTC (permalink / raw)
To: PostgreSQL Advocacy <[email protected]>
On 2021-05-17 7:15 a.m., Jonathan S. Katz wrote:
> Note that this announcement is not ranking the features in any
> particular way, and while it's exciting to see all the new features
> going into PostgreSQL 14, we cannot include all of them in the announcement.
>
> Please review the announcement for technical inaccuracies and notable
> feature omissions, and please provide feedback no later than Wed, May 19
> AoE
For my part the new "datemultirange" data type stood out to me as a very useful
functional improvement, and I support the release announcement and major
features documentation continuing to mention it rather than dropping it for
something else. -- Darren Duncan
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 14 Beta 1 release announcement draft
2021-05-17 14:15 PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
@ 2021-05-18 01:01 ` Jonathan S. Katz <[email protected]>
2021-05-18 02:40 ` Re: PostgreSQL 14 Beta 1 release announcement draft Amit Kapila <[email protected]>
2021-05-18 06:43 ` Re: PostgreSQL 14 Beta 1 release announcement draft SAS <[email protected]>
4 siblings, 2 replies; 12+ messages in thread
From: Jonathan S. Katz @ 2021-05-18 01:01 UTC (permalink / raw)
To: PostgreSQL Advocacy <[email protected]>
On 5/17/21 10:15 AM, Jonathan S. Katz wrote:
> Hi,
>
> Please see the attached draft for the PostgreSQL 14 Beta 1 release
> announcement this week.
Thanks for the initial round of feedback! I have incorporated some of
the suggestions directly, adapted some accordingly, and rejected ones
that were either incorrect or personal preference (such as the editing
process goes). Credit is given in the commit on the press repo.
I have attached the current state of the draft.
Again, please provide feedback no later than Wed, May 19 AoE[1].
Thanks,
Jonathan
[1] https://en.wikipedia.org/wiki/Anywhere_on_Earth
The PostgreSQL Global Development Group announces that the first beta release of
PostgreSQL 14 is now [available for download](https://www.postgresql.org/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):
[https://www.postgresql.org/docs/14/release-14.html](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](https://www.postgresql.org/developer/beta/) and how
you can contribute:
[https://www.postgresql.org/developer/beta/](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 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](https://www.postgresql.org/docs/14/btree-implementation.html#BTREE-DELETION).
[GiST indexes](https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPE-GIST)
can now presort data during their build process, allowing both for faster
index creation and smaller indexes.
[SP-GiST indexes](https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPE-SPGIST)
now support [covering indexes](https://www.postgresql.org/docs/14/indexes-index-only-scans.html)
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](https://www.postgresql.org/docs/14/parallel-plans.html#PARALLEL-SCANS),
the `RETURN QUERY` directive in [PL/pgSQL](https://www.postgresql.org/docs/14/plpgsql.html)
can now execute queries with parallelism.
[`REFRESH MATERIALIZED VIEW`](https://www.postgresql.org/docs/14/sql-refreshmaterializedview.html)
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](https://www.postgresql.org/docs/14/sql-createforeigndatawrapper.html).
The PostgreSQL foreign data wrapper, [`postgres_fdw`](https://www.postgresql.org/docs/14/postgres-fdw.html),
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`](https://www.postgresql.org/docs/14/sql-importforeignschema.html)
and can now execute [`TRUNCATE`](https://www.postgresql.org/docs/14/sql-truncate.html)
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`](https://www.postgresql.org/docs/14/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION)
command.
Incremental sorting, introduced in the previous release, can now be used by
[window functions](https://www.postgresql.org/docs/14/functions-window.html) in
PostgreSQL 14. This new release adds more capabilities to [extended statistics](https://www.postgresql.org/docs/14/planner-stats.html),
which can now be applied to [expressions](https://www.postgresql.org/docs/14/view-pg-stats-ext-exprs.html).
PostgreSQL has supported compression on its "oversized data" columns (i.e. the
[TOAST](https://www.postgresql.org/docs/14/storage-toast.html) system) for
decades, but this release adds the ability to now choose to use
[LZ4 compression](https://www.postgresql.org/docs/14/sql-createtable.html) for
column compression.
### Data Types + SQL
Building upon its existing support for [range types](https://www.postgresql.org/docs/14/rangetypes.html),
PostgreSQL 14 adds the new [multirange type](https://www.postgresql.org/docs/14/rangetypes.html#RANGETYPES-BUILTIN)
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 only the `.` operator (e.g. `this.now.works.to.retrieve.this.json.data`).
PostgreSQL 14 also adds support for `OUT` parameters in [stored procedures](https://www.postgresql.org/docs/14/sql-createprocedure.html),
and allows for the `GROUP BY` clause to use the `DISTINCT` keyword to remove duplicate
`GROUPING SET` combinations.
For recursive [common table expressions](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-RECURSIVE)
(`WITH` queries), PostgreSQL 14 adds the syntactic conveniences of [`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)
to help with ordering and cycle detection respectively.
There is also the new [`date_bin`](https://www.postgresql.org/docs/14/functions-datetime.html#FUNCTIONS-DATETIME-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`](https://www.postgresql.org/docs/14/routine-vacuuming.html),
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`](https://www.postgresql.org/docs/14/sql-analyze.html)
that can be controlled with [`maintenance_io_concurrency`](https://www.postgresql.org/docs/14/runtime-config-resource.html#GUC-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`](https://www.postgresql.org/docs/14/progress-reporting.html#COPY-PROGRESS-REPORTING)
view. This release lets you track WAL activity from the [`pg_stat_wal`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW)
view, and inspect replication slot statuses from the [`pg_stat_replication_slots`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-SLOTS-VIEW)
view.
There are several new parameters in PostgreSQL 14 to help manage connections.
These include [`idle_session_timeout`](https://www.postgresql.org/docs/14/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT),
which can close idle connections after the specified period, and
[`client_connection_check_interval`](https://www.postgresql.org/docs/14/runtime-config-connection.html#GUC-CLIENT-CONNECTION-CHECK-INTERV...)
parameter that lets PostgreSQL cancel long running queries if a client
disconnects.
The [`REINDEX`](https://www.postgresql.org/docs/14/sql-reindex.html) command can
now process all of the child indexes of a partitioned table, and PostgreSQL 14
adds the [`pg_amcheck`](https://www.postgresql.org/docs/14/app-pgamcheck.html)
utility to help check for data corruption.
### Replication & Recovery
PostgreSQL 14 adds numerous performance benefits for logical replication,
including the ability to stream in-process transactions to subscribers instead
of waiting for them to be completed. The [`ALTER SUBSCRIPTION`](https://www.postgresql.org/docs/14/sql-altersubscription.html)
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`](https://www.postgresql.org/docs/14/app-pgrewind.html) 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](https://www.postgresql.org/docs/14/predefined-roles.html),
respectively.
This release also defaults to using [SCRAM-SHA-256](https://www.postgresql.org/docs/14/auth-password.html)
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](https://www.postgresql.org/docs/14/auth-cert.html)
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](https://www.postgresql.org/docs/14/release-14.html)
for a complete list of new and changed features:
[https://www.postgresql.org/docs/14/release-14.html](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](https://wiki.postgresql.org/wiki/PostgreSQL_14_Open_Items)
is publicly available in the PostgreSQL wiki. You can
[report bugs](https://www.postgresql.org/account/submitbug/) using this form on
the PostgreSQL website:
[https://www.postgresql.org/account/submitbug/](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](https://www.postgresql.org/developer/beta/) page.
Links
-----
* [Download](https://www.postgresql.org/download/)
* [Beta Testing Information](https://www.postgresql.org/developer/beta/)
* [PostgreSQL 14 Beta Release Notes](https://www.postgresql.org/docs/14/release-14.html)
* [PostgreSQL 14 Open Issues](https://wiki.postgresql.org/wiki/PostgreSQL_14_Open_Items)
* [Submit a Bug](https://www.postgresql.org/account/submitbug/)
Attachments:
[text/plain] 14beta1.md (11.8K, ../../[email protected]/2-14beta1.md)
download | inline:
The PostgreSQL Global Development Group announces that the first beta release of
PostgreSQL 14 is now [available for download](https://www.postgresql.org/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):
[https://www.postgresql.org/docs/14/release-14.html](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](https://www.postgresql.org/developer/beta/) and how
you can contribute:
[https://www.postgresql.org/developer/beta/](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 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](https://www.postgresql.org/docs/14/btree-implementation.html#BTREE-DELETION).
[GiST indexes](https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPE-GIST)
can now presort data during their build process, allowing both for faster
index creation and smaller indexes.
[SP-GiST indexes](https://www.postgresql.org/docs/14/indexes-types.html#INDEXES-TYPE-SPGIST)
now support [covering indexes](https://www.postgresql.org/docs/14/indexes-index-only-scans.html)
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](https://www.postgresql.org/docs/14/parallel-plans.html#PARALLEL-SCANS),
the `RETURN QUERY` directive in [PL/pgSQL](https://www.postgresql.org/docs/14/plpgsql.html)
can now execute queries with parallelism.
[`REFRESH MATERIALIZED VIEW`](https://www.postgresql.org/docs/14/sql-refreshmaterializedview.html)
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](https://www.postgresql.org/docs/14/sql-createforeigndatawrapper.html).
The PostgreSQL foreign data wrapper, [`postgres_fdw`](https://www.postgresql.org/docs/14/postgres-fdw.html),
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`](https://www.postgresql.org/docs/14/sql-importforeignschema.html)
and can now execute [`TRUNCATE`](https://www.postgresql.org/docs/14/sql-truncate.html)
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`](https://www.postgresql.org/docs/14/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION)
command.
Incremental sorting, introduced in the previous release, can now be used by
[window functions](https://www.postgresql.org/docs/14/functions-window.html) in
PostgreSQL 14. This new release adds more capabilities to [extended statistics](https://www.postgresql.org/docs/14/planner-stats.html),
which can now be applied to [expressions](https://www.postgresql.org/docs/14/view-pg-stats-ext-exprs.html).
PostgreSQL has supported compression on its "oversized data" columns (i.e. the
[TOAST](https://www.postgresql.org/docs/14/storage-toast.html) system) for
decades, but this release adds the ability to now choose to use
[LZ4 compression](https://www.postgresql.org/docs/14/sql-createtable.html) for
column compression.
### Data Types + SQL
Building upon its existing support for [range types](https://www.postgresql.org/docs/14/rangetypes.html),
PostgreSQL 14 adds the new [multirange type](https://www.postgresql.org/docs/14/rangetypes.html#RANGETYPES-BUILTIN)
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 only the `.` operator (e.g. `this.now.works.to.retrieve.this.json.data`).
PostgreSQL 14 also adds support for `OUT` parameters in [stored procedures](https://www.postgresql.org/docs/14/sql-createprocedure.html),
and allows for the `GROUP BY` clause to use the `DISTINCT` keyword to remove duplicate
`GROUPING SET` combinations.
For recursive [common table expressions](https://www.postgresql.org/docs/14/queries-with.html#QUERIES-WITH-RECURSIVE)
(`WITH` queries), PostgreSQL 14 adds the syntactic conveniences of [`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)
to help with ordering and cycle detection respectively.
There is also the new [`date_bin`](https://www.postgresql.org/docs/14/functions-datetime.html#FUNCTIONS-DATETIME-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`](https://www.postgresql.org/docs/14/routine-vacuuming.html),
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`](https://www.postgresql.org/docs/14/sql-analyze.html)
that can be controlled with [`maintenance_io_concurrency`](https://www.postgresql.org/docs/14/runtime-config-resource.html#GUC-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`](https://www.postgresql.org/docs/14/progress-reporting.html#COPY-PROGRESS-REPORTING)
view. This release lets you track WAL activity from the [`pg_stat_wal`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW)
view, and inspect replication slot statuses from the [`pg_stat_replication_slots`](https://www.postgresql.org/docs/14/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-SLOTS-VIEW)
view.
There are several new parameters in PostgreSQL 14 to help manage connections.
These include [`idle_session_timeout`](https://www.postgresql.org/docs/14/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT),
which can close idle connections after the specified period, and
[`client_connection_check_interval`](https://www.postgresql.org/docs/14/runtime-config-connection.html#GUC-CLIENT-CONNECTION-CHECK-INTERVAL)
parameter that lets PostgreSQL cancel long running queries if a client
disconnects.
The [`REINDEX`](https://www.postgresql.org/docs/14/sql-reindex.html) command can
now process all of the child indexes of a partitioned table, and PostgreSQL 14
adds the [`pg_amcheck`](https://www.postgresql.org/docs/14/app-pgamcheck.html)
utility to help check for data corruption.
### Replication & Recovery
PostgreSQL 14 adds numerous performance benefits for logical replication,
including the ability to stream in-process transactions to subscribers instead
of waiting for them to be completed. The [`ALTER SUBSCRIPTION`](https://www.postgresql.org/docs/14/sql-altersubscription.html)
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`](https://www.postgresql.org/docs/14/app-pgrewind.html) 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](https://www.postgresql.org/docs/14/predefined-roles.html),
respectively.
This release also defaults to using [SCRAM-SHA-256](https://www.postgresql.org/docs/14/auth-password.html)
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](https://www.postgresql.org/docs/14/auth-cert.html)
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](https://www.postgresql.org/docs/14/release-14.html)
for a complete list of new and changed features:
[https://www.postgresql.org/docs/14/release-14.html](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](https://wiki.postgresql.org/wiki/PostgreSQL_14_Open_Items)
is publicly available in the PostgreSQL wiki. You can
[report bugs](https://www.postgresql.org/account/submitbug/) using this form on
the PostgreSQL website:
[https://www.postgresql.org/account/submitbug/](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](https://www.postgresql.org/developer/beta/) page.
Links
-----
* [Download](https://www.postgresql.org/download/)
* [Beta Testing Information](https://www.postgresql.org/developer/beta/)
* [PostgreSQL 14 Beta Release Notes](https://www.postgresql.org/docs/14/release-14.html)
* [PostgreSQL 14 Open Issues](https://wiki.postgresql.org/wiki/PostgreSQL_14_Open_Items)
* [Submit a Bug](https://www.postgresql.org/account/submitbug/)
[application/pgp-signature] OpenPGP_signature (840B, ../../[email protected]/3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 14 Beta 1 release announcement draft
2021-05-17 14:15 PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
2021-05-18 01:01 ` Re: PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
@ 2021-05-18 02:40 ` Amit Kapila <[email protected]>
1 sibling, 0 replies; 12+ messages in thread
From: Amit Kapila @ 2021-05-18 02:40 UTC (permalink / raw)
To: Jonathan S. Katz <[email protected]>; +Cc: PostgreSQL Advocacy <[email protected]>
On Tue, May 18, 2021 at 6:31 AM Jonathan S. Katz <[email protected]> wrote:
>
> On 5/17/21 10:15 AM, Jonathan S. Katz wrote:
> > Hi,
> >
> > Please see the attached draft for the PostgreSQL 14 Beta 1 release
> > announcement this week.
>
> Thanks for the initial round of feedback! I have incorporated some of
> the suggestions directly, adapted some accordingly, and rejected ones
> that were either incorrect or personal preference (such as the editing
> process goes). Credit is given in the commit on the press repo.
>
> I have attached the current state of the draft.
>
Few minor comments:
* and inspect replication slot statuses from the [`pg_stat_replication_slots`]
In the above sentence, do you want to say statistics instead of statuses?
* PostgreSQL 14 adds numerous performance benefits for logical
replication, including the ability to stream in-process transactions
I think in the above sentence, instead of in-process it should be in-progress
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL 14 Beta 1 release announcement draft
2021-05-17 14:15 PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
2021-05-18 01:01 ` Re: PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
@ 2021-05-18 06:43 ` SAS <[email protected]>
1 sibling, 0 replies; 12+ messages in thread
From: SAS @ 2021-05-18 06:43 UTC (permalink / raw)
To: [email protected]
Le 18/05/2021 à 03:01, Jonathan S. Katz a écrit :
> On 5/17/21 10:15 AM, Jonathan S. Katz wrote:
>> Hi,
>>
>> Please see the attached draft for the PostgreSQL 14 Beta 1 release
>> announcement this week.
> Thanks for the initial round of feedback! I have incorporated some of
> the suggestions directly, adapted some accordingly, and rejected ones
> that were either incorrect or personal preference (such as the editing
> process goes). Credit is given in the commit on the press repo.
>
> I have attached the current state of the draft.
>
> Again, please provide feedback no later than Wed, May 19 AoE[1].
>
> Thanks,
>
> Jonathan
>
> [1] https://en.wikipedia.org/wiki/Anywhere_on_Earth
Hello,
I think the verb has been lost between first and second release of the draft:
This release has significant improvements in transaction throughput for
PostgreSQL systems that have large numbers of connections to the database,
/regardless if they in an active or idle state./
S.
//
--
Dr Stéphane Schildknecht
Contact régional PostgreSQL pour l'Europe francophone
+33 617 11 37 42
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2021-05-20 13:49 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-17 14:15 PostgreSQL 14 Beta 1 release announcement draft Jonathan S. Katz <[email protected]>
2021-05-17 15:01 ` Erik Rijkers <[email protected]>
2021-05-18 10:33 ` Erik Rijkers <[email protected]>
2021-05-18 21:11 ` Jonathan S. Katz <[email protected]>
2021-05-19 02:08 ` Jonathan S. Katz <[email protected]>
2021-05-20 13:49 ` Jonathan S. Katz <[email protected]>
2021-05-17 17:24 ` Justin Pryzby <[email protected]>
2021-05-17 19:32 ` Peter Geoghegan <[email protected]>
2021-05-17 22:26 ` Darren Duncan <[email protected]>
2021-05-18 01:01 ` Jonathan S. Katz <[email protected]>
2021-05-18 02:40 ` Amit Kapila <[email protected]>
2021-05-18 06:43 ` SAS <[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