From a9fc86d0c0958efe99df8809a49454928968d7c1 Mon Sep 17 00:00:00 2001 From: "Jonathan S. Katz" Date: Wed, 24 May 2023 13:04:52 -0400 Subject: [PATCH] Additional feedback to the PostgreSQL 16 Beta 1 release Reviewed-by: Erik Rijkers Reviewed-by: David Rowley --- releases/16/16beta1.md | 71 +++++++++++++++++++++--------------------- 1 file changed, 35 insertions(+), 36 deletions(-) diff --git a/releases/16/16beta1.md b/releases/16/16beta1.md index 9e7f279..d0986cb 100644 --- a/releases/16/16beta1.md +++ b/releases/16/16beta1.md @@ -26,7 +26,24 @@ you can contribute: PostgreSQL 16 Feature Highlights -------------------------------- -### Logical replication enhancements +### Performance + +PostgreSQL 16 includes performance improvements in query execution. This release +adds more query parallelism, including allowing `FULL` and `RIGHT` joins to +execute in parallel, and parallel execution of the `string_agg` and `array_agg` +aggregate functions. Additionally, PostgreSQL 16 can use incremental sorts in +`SELECT DISTINCT` queries. There are also several optimizations for +[window queries](https://www.postgresql.org/docs/16/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS), +improvements in lookups for `RANGE` and `LIST` partitions, and support for +"anti-joins" in `RIGHT` and `OUTER` queries. + +This release also introduces support for CPU acceleration using SIMD for both +x86 and ARM architectures, including optimizations for processing ASCII and JSON +strings, and array and subtransaction searches. Additionally, PostgreSQL 16 +introduces [load balancing](https://www.postgresql.org/docs/16/libpq-connect.html#LIBPQ-CONNECT-LOAD-BALANCE-HOSTS) +to libpq, the client library for PostgreSQL. + +### Logical Replication Enhancements Logical replication lets PostgreSQL users stream data in real-time to other PostgreSQL or other external systems that implement the logical protocol. Until @@ -42,74 +59,57 @@ in parallel, use indexes other than the `PRIMARY KEY` to perform lookups during `UPDATE` or `DELETE` operations, and allow for tables to be copied using binary format during initialization. -### Performance - -PostgreSQL 16 includes performance improvements in query execution. This release -adds more query parallelism, including allowing `FULL` and `OUTER` joins to -execute in parallel, and parallel execution of the `string_agg` and `array_agg` -aggregate functions. Additionally, PostgreSQL 16 can use incremental sorts in -`SELECT DISTINCT` queries. There are also several optimizations for -[window queries](https://www.postgresql.org/docs/devel/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS), -improvements in lookups for `RANGE` and `LIST` partitions, and support for -"anti-joins" in `RIGHT` and `OUTER` queries. - -This release also introduces support for CPU acceleration using SIMD for both -x86 and ARM architectures, including optimizations for processing ASCII and JSON -strings, and array and substransaction searches. Additionally, PostgreSQL 16 -introduces [load balancing](https://www.postgresql.org/docs/devel/libpq-connect.html#LIBPQ-CONNECT-LOAD-BALANCE-HOSTS) -to libpq, the client library for PostgreSQL. - ### Developer Experience -PostgreSQL 16 continues to implement the [SQL/JSON](https://www.postgresql.org/docs/devel/functions-json.html) -standard for manipulating [JSON](https://www.postgresql.org/docs/devel/datatype-json.html) +PostgreSQL 16 continues to implement the [SQL/JSON](https://www.postgresql.org/docs/16/functions-json.html) +standard for manipulating [JSON](https://www.postgresql.org/docs/16/datatype-json.html) data, including support for SQL/JSON constructors (e.g. `JSON_ARRAY()`, `JSON_ARRAYAGG()` et al), and identity functions (`IS JSON`). This release also -adds the SQL standard [`ANY_VALUE`](https://www.postgresql.org/docs/devel/functions-aggregate.html#id-1.5.8.27.5.2.4.1.1.1.1) +adds the SQL standard [`ANY_VALUE`](https://www.postgresql.org/docs/16/functions-aggregate.html#id-1.5.8.27.5.2.4.1.1.1.1) aggregate function, which returns any arbitrary value from the aggregate set. For convenience, PostgreSQL 16 now lets you specify non-decimal integer -literals, such as `0xff`, `0o777`, and `0b101010`, and use thousands separators, -such as `5_432`. +literals, such as `0xff`, `0o777`, and `0b101010`, and use underscores as +thousands separators, such as `5_432`. -This release adds support for the extended query protocol to the [`psql`](https://www.postgresql.org/docs/devel/app-psql.html) +This release adds support for the extended query protocol to the [`psql`](https://www.postgresql.org/docs/16/app-psql.html) client. Users can execute a query, e.g. `SELECT $1 + $2`, and use the -[`\bind`](https://www.postgresql.org/docs/devel/app-psql.html#APP-PSQL-META-COMMAND-BIND) +[`\bind`](https://www.postgresql.org/docs/16/app-psql.html#APP-PSQL-META-COMMAND-BIND) command to substitute the variables. -### Security features +### Security Features PostgreSQL 16 continues to give users the ability to grant privileged access to features without requiring superuser with new -[predefined roles](https://www.postgresql.org/docs/devel/predefined-roles.html). +[predefined roles](https://www.postgresql.org/docs/16/predefined-roles.html). These include `pg_maintain`, which enables execution of operations such as `VACUUM`, `ANALYZE`, `REINDEX`, and others, and `pg_create_subscription`, which allows users to create a logical replication subscription. Additionally, starting with this release, logical replication subscribers execute transactions on a table as the table owner, not the superuser. -PostgreSQL 16 now lets you use regular expressions in the [`pg_hba.conf`](https://www.postgresql.org/docs/devel/auth-pg-hba-conf.html) -and [`pg_ident.conf`](https://www.postgresql.org/docs/devel/auth-username-maps.html) +PostgreSQL 16 now lets you use regular expressions in the [`pg_hba.conf`](https://www.postgresql.org/docs/16/auth-pg-hba-conf.html) +and [`pg_ident.conf`](https://www.postgresql.org/docs/16/auth-username-maps.html) files for matching user and databases names. Additionally, PostgreSQL 16 adds the ability to include other files in both `pg_hba.conf` and `pg_ident.conf`. -PostgreSQL 16 also adds support for the SQL standard [`SYSTEM_USER`](https://www.postgresql.org/docs/devel/functions-info.html#id-1.5.8.32.3.4.2.2.24.1.1.1) +PostgreSQL 16 also adds support for the SQL standard [`SYSTEM_USER`](https://www.postgresql.org/docs/16/functions-info.html#id-1.5.8.32.3.4.2.2.24.1.1.1) keyword, which returns the username and authentication method used to establish a session. PostgreSQL 16 also adds support for Kerberos credential delegation, which allows extensions such as `postgres_fdw` and `dblink` to use the authenticated credentials to connect to other services. This release also adds several new -security-oriented connection parameters for clients. This includes [`require_auth`](https://www.postgresql.org/docs/devel/libpq-connect.html#LIBPQ-CONNECT-REQUIRE-AUTH), +security-oriented connection parameters for clients. This includes [`require_auth`](https://www.postgresql.org/docs/16/libpq-connect.html#LIBPQ-CONNECT-REQUIRE-AUTH), where a client can specify which authentication methods it is willing to accept -from the server. You can now set `sslrootcert` to `system` to instrcut +from the server. You can now set `sslrootcert` to `system` to instruct PostgreSQL to use the trusted certificate authority (CA) store provided by the client's operating system. -### Operations +### Monitoring and Management PostgreSQL 16 adds several new monitoring features, including the new -[`pg_stat_io`](https://www.postgresql.org/docs/devel/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW) +[`pg_stat_io`](https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW) view that provides information on I/O statistics. This release also provides a -timestamp for the last time that a [table or index was scanned](https://www.postgresql.org/docs/devel/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW). +timestamp for the last time that a [table or index was scanned](https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW). There are also improvements to the normalization algorithm used for `pg_stat_activity`. @@ -122,7 +122,6 @@ also adds support for the predefined `unicode` and `ucs_basic` collations. PostgreSQL 16 adds additional compression options to `pg_dump`, including support for both `lz4` and `zstd` compression. - ### Other Notable Changes PostgreSQL 16 removes the `promote_trigger_file` option to enable the promotion -- 2.39.5