Re: PostgreSQL 16 Beta 1 release announcement draft
On 5/25/23 12:16 AM, Andres Freund wrote: Hi, On 2023-05-24 23:30:58 -0400, Jonathan S. Katz wrote: Ah, OK, that's why I didn't grok it. I read through the first message in[1] and definitely agree it should be in the announcement. How about: "PostgreSQL 16 also shows up to a 300% improvement when concurrently loading data with `COPY`" I currently have it as the below in the release announcement. If it you send any suggested updates, I can try to put them in before release: PostgreSQL 16 can also improve the performance of concurrent bulk loading of data using [`COPY`](https://www.postgresql.org/docs/16/sql-copy.html) up to a 300%. It also speeds up concurrent loading when not using COPY, just to a lesser degree. But I can't come up with a concise phrasing for that right now... I left as is (in part because of a hurried morning), but we can improve upon it for the GA. Thanks, Jonathan OpenPGP_signature Description: OpenPGP digital signature
Re: PostgreSQL 16 Beta 1 release announcement draft
Hi, On 2023-05-24 23:30:58 -0400, Jonathan S. Katz wrote: > > Ah, OK, that's why I didn't grok it. I read through the first message > > in[1] and definitely agree it should be in the announcement. How about: > > > > "PostgreSQL 16 also shows up to a 300% improvement when concurrently > > loading data with `COPY`" > > I currently have it as the below in the release announcement. If it you send > any suggested updates, I can try to put them in before release: > > PostgreSQL 16 can also improve the performance of concurrent bulk loading of > data using [`COPY`](https://www.postgresql.org/docs/16/sql-copy.html) up to > a 300%. It also speeds up concurrent loading when not using COPY, just to a lesser degree. But I can't come up with a concise phrasing for that right now... Greetings, Andres Freund
Re: PostgreSQL 16 Beta 1 release announcement draft
On 5/24/23 11:30 PM, Jonathan S. Katz wrote: On 5/24/23 9:20 PM, Jonathan S. Katz wrote: I currently have it as the below in the release announcement. If it you send any suggested updates, I can try to put them in before release: PostgreSQL 16 can also improve the performance of concurrent bulk loading of data using [`COPY`](https://www.postgresql.org/docs/16/sql-copy.html) up to a 300%. (without the "a 300%" typo). Jonathan OpenPGP_signature Description: OpenPGP digital signature
Re: PostgreSQL 16 Beta 1 release announcement draft
On 5/24/23 9:20 PM, Jonathan S. Katz wrote: On 5/24/23 8:04 PM, Andres Freund wrote: Hi, On 2023-05-24 19:57:39 -0400, Jonathan S. Katz wrote: On 5/24/23 5:28 PM, Andres Freund wrote: I think the relation extension improvements ought to be mentioned here as well? Up to 3x faster concurrent data load with COPY seems practically relevant. I missed that -- not sure I'm finding it in the release notes with a quick grep -- which commit/thread is this? It was split over quite a few commits, the one improving COPY most significantly is commit 00d1e02be24987180115e371abaeb84738257ae2 Author: Andres Freund Date: 2023-04-06 16:35:21 -0700 hio: Use ExtendBufferedRelBy() to extend tables more efficiently Relevant thread: https://postgr.es/m/20221029025420.eplyow6k7tgu6...@awork3.anarazel.de It's in the release notes as: Allow more efficient addition of heap and index pages (Andres Freund) Ah, OK, that's why I didn't grok it. I read through the first message in[1] and definitely agree it should be in the announcement. How about: "PostgreSQL 16 also shows up to a 300% improvement when concurrently loading data with `COPY`" I currently have it as the below in the release announcement. If it you send any suggested updates, I can try to put them in before release: PostgreSQL 16 can also improve the performance of concurrent bulk loading of data using [`COPY`](https://www.postgresql.org/docs/16/sql-copy.html) up to a 300%. Jonathan OpenPGP_signature Description: OpenPGP digital signature
Re: PostgreSQL 16 Beta 1 release announcement draft
On 5/24/23 8:04 PM, Andres Freund wrote: Hi, On 2023-05-24 19:57:39 -0400, Jonathan S. Katz wrote: On 5/24/23 5:28 PM, Andres Freund wrote: I think the relation extension improvements ought to be mentioned here as well? Up to 3x faster concurrent data load with COPY seems practically relevant. I missed that -- not sure I'm finding it in the release notes with a quick grep -- which commit/thread is this? It was split over quite a few commits, the one improving COPY most significantly is commit 00d1e02be24987180115e371abaeb84738257ae2 Author: Andres Freund Date: 2023-04-06 16:35:21 -0700 hio: Use ExtendBufferedRelBy() to extend tables more efficiently Relevant thread: https://postgr.es/m/20221029025420.eplyow6k7tgu6...@awork3.anarazel.de It's in the release notes as: Allow more efficient addition of heap and index pages (Andres Freund) Ah, OK, that's why I didn't grok it. I read through the first message in[1] and definitely agree it should be in the announcement. How about: "PostgreSQL 16 also shows up to a 300% improvement when concurrently loading data with `COPY`" Thanks, Jonathan [1] https://postgr.es/m/20221029025420.eplyow6k7tgu6...@awork3.anarazel.de OpenPGP_signature Description: OpenPGP digital signature
Re: PostgreSQL 16 Beta 1 release announcement draft
Hi, On 2023-05-24 19:57:39 -0400, Jonathan S. Katz wrote: > On 5/24/23 5:28 PM, Andres Freund wrote: > > > > I think the relation extension improvements ought to be mentioned here as > > well? Up to 3x faster concurrent data load with COPY seems practically > > relevant. > > I missed that -- not sure I'm finding it in the release notes with a quick > grep -- which commit/thread is this? It was split over quite a few commits, the one improving COPY most significantly is commit 00d1e02be24987180115e371abaeb84738257ae2 Author: Andres Freund Date: 2023-04-06 16:35:21 -0700 hio: Use ExtendBufferedRelBy() to extend tables more efficiently Relevant thread: https://postgr.es/m/20221029025420.eplyow6k7tgu6...@awork3.anarazel.de It's in the release notes as: Allow more efficient addition of heap and index pages (Andres Freund) Greetings, Andres Freund
Re: PostgreSQL 16 Beta 1 release announcement draft
On 5/24/23 5:28 PM, Andres Freund wrote: I think the relation extension improvements ought to be mentioned here as well? Up to 3x faster concurrent data load with COPY seems practically relevant. I missed that -- not sure I'm finding it in the release notes with a quick grep -- which commit/thread is this? But yes this does sound like something that should be included, I just want to read upon it. Thanks, Jonathan OpenPGP_signature Description: OpenPGP digital signature
Re: PostgreSQL 16 Beta 1 release announcement draft
Hi, On 2023-05-24 13:06:30 -0400, Jonathan S. Katz wrote: > PostgreSQL 16 Feature Highlights > > > ### 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. I think the relation extension improvements ought to be mentioned here as well? Up to 3x faster concurrent data load with COPY seems practically relevant. Greetings, Andres Freund
Re: PostgreSQL 16 Beta 1 release announcement draft
On 5/22/23 3:23 PM, Erik Rijkers wrote: Op 5/21/23 om 19:07 schreef Jonathan S. Katz: On 5/19/23 12:17 AM, Jonathan S. Katz wrote: Hi, Attached is a draft of the release announcement for PostgreSQL 16 Beta Please provide feedback no later than May 24, 0:00 AoE. This will give Thanks everyone for your feedback. Here is the updated text that 'substransaction' should be 'subtransaction' Fixed. 'use thousands separators' perhaps is better: 'use underscore as digit-separator, as in `5_432` and `1_00_000`' I looked at how other languages document this, and they do use the term "thousands separators." I left that in, but explicitly called out the underscore. 'instrcut' should be 'instruct' Fixed. Attached is the (hopefully) final draft. Thanks, Jonathan The PostgreSQL Global Development Group announces that the first beta release of PostgreSQL 16 is now [available for download](https://www.postgresql.org/download/). This release contains previews of all features that will be available when PostgreSQL 16 is made generally available, though some details of the release can change during the beta period. You can find information about all of the features and changes found in PostgreSQL 16 in the [release notes](https://www.postgresql.org/docs/16/release-16.html): [https://www.postgresql.org/docs/16/release-16.html](https://www.postgresql.org/docs/16/release-16.html) In the spirit of the open source PostgreSQL community, we strongly encourage you to test the new features of PostgreSQL 16 on your systems to help us eliminate bugs or other issues that may exist. While we do not advise you to run PostgreSQL 16 Beta 1 in 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 16 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 16 Feature Highlights ### 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 PostgreSQL 16, users could only create logical replication publishers on primary instances. PostgreSQL 16 adds the ability to perform logical decoding on a standby instance, giving users more options to distribute their workload, for example, use a standby that's less busy than a primary to logically replicate changes. PostgreSQL 16 also includes several performance improvements to logical replication. This includes allowing the subscriber to apply large transactions 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. ### Developer Experience 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/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 underscores as thousands separators, such as `5_432`. This release adds support for the extended query protocol to the
Re: PostgreSQL 16 Beta 1 release announcement draft
Op 5/21/23 om 19:07 schreef Jonathan S. Katz: On 5/19/23 12:17 AM, Jonathan S. Katz wrote: Hi, Attached is a draft of the release announcement for PostgreSQL 16 Beta Please provide feedback no later than May 24, 0:00 AoE. This will give Thanks everyone for your feedback. Here is the updated text that 'substransaction' should be 'subtransaction' 'use thousands separators' perhaps is better: 'use underscore as digit-separator, as in `5_432` and `1_00_000`' 'instrcut' should be 'instruct' Erik
Re: PostgreSQL 16 Beta 1 release announcement draft
On Sun, May 21, 2023 at 12:51:05PM -0400, Jonathan S. Katz wrote: > On 5/19/23 10:57 AM, Nathan Bossart wrote: >> [pg_use_]reserved_connections might also deserve a mention here. AFAICT >> it's the only new predefined role that isn't mentioned in the announcement. >> I'm okay with leaving it out if folks don't think it should make the cut. > > I'm not sure how widely used this one would be, so I left it out. However, > open to other opinions. Fair enough. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com
Re: PostgreSQL 16 Beta 1 release announcement draft
On 5/19/23 12:17 AM, Jonathan S. Katz wrote: Hi, Attached is a draft of the release announcement for PostgreSQL 16 Beta 1. The goal of this announcement is to get people excited about testing the beta and highlight many of the new features. Please review for inaccuracies, omissions, and other suggestions / errors. Please provide feedback no later than May 24, 0:00 AoE. This will give me enough time to incorporate the changes prior to the release the next day. Thanks everyone for your feedback. Here is the updated text that combines all of the feedback from both -advocacy and -hackers. Thanks, Jonathan The PostgreSQL Global Development Group announces that the first beta release of PostgreSQL 16 is now [available for download](https://www.postgresql.org/download/). This release contains previews of all features that will be available when PostgreSQL 16 is made generally available, though some details of the release can change during the beta period. You can find information about all of the features and changes found in PostgreSQL 16 in the [release notes](https://www.postgresql.org/docs/16/release-16.html): [https://www.postgresql.org/docs/16/release-16.html](https://www.postgresql.org/docs/16/release-16.html) In the spirit of the open source PostgreSQL community, we strongly encourage you to test the new features of PostgreSQL 16 on your systems to help us eliminate bugs or other issues that may exist. While we do not advise you to run PostgreSQL 16 Beta 1 in 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 16 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 16 Feature Highlights ### 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 PostgreSQL 16, users could only create logical replication publishers on primary instances. PostgreSQL 16 adds the ability to perform logical decoding on a standby instance, giving users more options to distribute their workload, for example, use a standby that's less busy than a primary to logically replicate changes. PostgreSQL 16 also includes several performance improvements to logical replication. This includes allowing the subscriber to apply large transactions 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) 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) 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`. This release adds support for the extended query protocol to the [`psql`](https://www.postgresql.org/docs/devel/app-psql.html) client. Users can execute a query, e.g. `SELECT $1 + $2`, and use the
Re: PostgreSQL 16 Beta 1 release announcement draft
On 5/19/23 10:57 AM, Nathan Bossart wrote: On Fri, May 19, 2023 at 12:17:50AM -0400, Jonathan S. Katz wrote: PostgreSQL 16 continues to give users to the ability grant privileged access to features without requiring superuser with new [predefined roles](https://www.postgresql.org/docs/devel/predefined-roles.html). These include `pg_maintain`, which enables execution of operations such as `VACUUM`, `ANALYZE`, `REINDEX`, and others, and `pg_createsubscription`, which allows users to create a logical replication subscription. Additionally, starting with release, logical replication subscribers execute transactions on a table as the table owner, not the superuser. [pg_use_]reserved_connections might also deserve a mention here. AFAICT it's the only new predefined role that isn't mentioned in the announcement. I'm okay with leaving it out if folks don't think it should make the cut. I'm not sure how widely used this one would be, so I left it out. However, open to other opinions. Thanks, Jonathan OpenPGP_signature Description: OpenPGP digital signature
Re: PostgreSQL 16 Beta 1 release announcement draft
On 5/19/23 1:42 AM, Erik Rijkers wrote: Op 5/19/23 om 06:17 schreef Jonathan S. Katz: Hi, Attached is a draft of the release announcement for PostgreSQL 16 Beta Hi, The usual small fry: 'continues to to' should be 'continues to' 'continues to give users to the ability' should be 'continues to give users the ability to' 'pg_createsubscription' should be 'pg_create_subscription' 'starting with release' should be 'starting with this release' 'credentials to connected to other services' should be 'credentials to connect to other services' Thanks Erik. I made all of these changes and will upload them in the next review. Jonathan OpenPGP_signature Description: OpenPGP digital signature
Re: PostgreSQL 16 Beta 1 release announcement draft
On Fri, May 19, 2023 at 12:17:50AM -0400, Jonathan S. Katz wrote: > Attached is a draft of the release announcement for PostgreSQL 16 Beta 1. > The goal of this announcement is to get people excited about testing the > beta and highlight many of the new features. Thanks! > PostgreSQL 16 continues to give users to the ability grant privileged access > to > features without requiring superuser with new > [predefined > roles](https://www.postgresql.org/docs/devel/predefined-roles.html). > These include `pg_maintain`, which enables execution of operations such as > `VACUUM`, `ANALYZE`, `REINDEX`, and others, and `pg_createsubscription`, which > allows users to create a logical replication subscription. Additionally, > starting with release, logical replication subscribers execute transactions > on a > table as the table owner, not the superuser. [pg_use_]reserved_connections might also deserve a mention here. AFAICT it's the only new predefined role that isn't mentioned in the announcement. I'm okay with leaving it out if folks don't think it should make the cut. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com
Re: PostgreSQL 16 Beta 1 release announcement draft
Op 5/19/23 om 06:17 schreef Jonathan S. Katz: Hi, Attached is a draft of the release announcement for PostgreSQL 16 Beta Hi, The usual small fry: 'continues to to' should be 'continues to' 'continues to give users to the ability' should be 'continues to give users the ability to' 'pg_createsubscription' should be 'pg_create_subscription' 'starting with release' should be 'starting with this release' 'credentials to connected to other services' should be 'credentials to connect to other services' Thanks, Erik
