PostgreSQL 15 release announcement draft

Hi,

On Tue, Aug 30, 2022 at 03:58:48PM -0400, Jonathan S. Katz wrote:

### Other Notable Changes

PostgreSQL server-level statistics are now collected in shared memory,
eliminating the statistics collector process and writing these stats to disk.
PostgreSQL 15 also revokes the `CREATE` permission from all users except a
database owner from the `public` (or default) schema.

It's a bit weird to lump those two in the same paragraph, but ok.

However, I think the "and writing these stats to disk." might not be
very clear to people not familiar with the feature, they might think
writing stats to disk is part of the new feature. So I propose "as well
as writing theses stats to disk" instead or something.

Michael

On Tue, Aug 30, 2022 at 03:58:48PM -0400, Jonathan S. Katz wrote:

In this latest release, PostgreSQL improves on its in-memory and on-disk sorting
algorithms, with benchmarks showing speedups of 25% - 400% based on sort types.

rather than "based on": "depending on the data types being sorted"

Building on work from the previous PostgreSQL release for allowing async remote
queries, the PostgreSQL foreign data wrapper, `postgres_fdw`, can now commit
transactions in parallel.

asynchronous

benefits for certain workloads. On certain operating systems, PostgreSQL 15

s/certain/some ?

supports the ability to prefetch WAL file contents and speed up recovery times.

PostgreSQL's built-in backup command, `pg_basebackup`, now supports server-side
compression of backup files with a choice of gzip, LZ4, and zstd.

remove "server-side", since they're also supported on the client-side.

PostgreSQL 15 lets user create views that query data using the permissions of

users

the caller, not the view creator. This option, called `security_invoker`, adds
an additional layer of protection to ensure view callers have the correct
permissions for working with the underlying data.

ensure *that ?

alter server-level configuration parameters. Additionally, users can now search
for information about configuration using the `\dconfig` command from the `psql`
command-line tool.

rather than "search for information about configuration", say "list
configuration information" ?

PostgreSQL server-level statistics are now collected in shared memory,
eliminating the statistics collector process and writing these stats to disk.

and *the need to periodically* write these stats to disk

--
Justin

On 8/31/22 1:51 PM, Michael Banck wrote:

Hi,

On Tue, Aug 30, 2022 at 03:58:48PM -0400, Jonathan S. Katz wrote:

### Other Notable Changes

PostgreSQL server-level statistics are now collected in shared memory,
eliminating the statistics collector process and writing these stats to disk.
PostgreSQL 15 also revokes the `CREATE` permission from all users except a
database owner from the `public` (or default) schema.

It's a bit weird to lump those two in the same paragraph, but ok.

I split this up for now, but I may change this section a bit.

However, I think the "and writing these stats to disk." might not be
very clear to people not familiar with the feature, they might think
writing stats to disk is part of the new feature. So I propose "as well
as writing theses stats to disk" instead or something.

Agree that this wasn't clear. I changed the language to "both". I'll
attach the updated draft in the next reply.

Thanks,

Jonathan

On 8/31/22 8:15 PM, Justin Pryzby wrote:

On Tue, Aug 30, 2022 at 03:58:48PM -0400, Jonathan S. Katz wrote:

In this latest release, PostgreSQL improves on its in-memory and on-disk sorting
algorithms, with benchmarks showing speedups of 25% - 400% based on sort types.

rather than "based on": "depending on the data types being sorted"

I followed this suggestion but modified it in a different way. Still,
I'm not sure if this conveys the full breadth of the features. I wonder
if we should expand on the sorting changes more?

Building on work from the previous PostgreSQL release for allowing async remote
queries, the PostgreSQL foreign data wrapper, `postgres_fdw`, can now commit
transactions in parallel.

asynchronous

Modified.

benefits for certain workloads. On certain operating systems, PostgreSQL 15

s/certain/some ?

I think "some" sounds like "it may or may not work" vs. "certain" says
that it "can work with tuning". We still want to ensure that people are
excited about the feature and try it out.

supports the ability to prefetch WAL file contents and speed up recovery times.

PostgreSQL's built-in backup command, `pg_basebackup`, now supports server-side
compression of backup files with a choice of gzip, LZ4, and zstd.

remove "server-side", since they're also supported on the client-side.

The server-side feature is the new piece for PG15. Gzip was already
supported on the client-side; lz4 + zstd are new for PG15.

I think the server-side compression is the part to call out, as you can
benefit from that existing on the server prior to transferring the
backup elsewhere, and have that network savings. However, happy to be
told that we should discuss both server/client compression in the
announcement.

the caller, not the view creator. This option, called `security_invoker`, adds
an additional layer of protection to ensure view callers have the correct
permissions for working with the underlying data.

ensure *that ?

Fixed.

alter server-level configuration parameters. Additionally, users can now search
for information about configuration using the `\dconfig` command from the `psql`
command-line tool.

rather than "search for information about configuration", say "list
configuration information" ?

You can search though -- it supports wildcards. I understand the point
-- really you are still listing it out, but I think one of the neat
things is I can fairly easily search for the name of a config parameter
from the CLI, even if I can't remember the correct or full name of it.

PostgreSQL server-level statistics are now collected in shared memory,
eliminating the statistics collector process and writing these stats to disk.

and *the need to periodically* write these stats to disk

Modified this. However, does this appropriately capture the performance
benefit of having the server-level stats collection modified in this
way? Does it capture all the benefits?

New version attached.

Thanks,

Jonathan

On 9/1/22 9:10 PM, Jonathan S. Katz wrote:

New version attached.

Here is a (penultimate?) draft that includes URLs. Please provide any
additional feedback no later than 2022-09-14 0:00 AoE. After that, we
will begin the translation process.

Thanks,

Jonathan

On 12.09.22 18:52, Jonathan S. Katz wrote:

On 9/1/22 9:10 PM, Jonathan S. Katz wrote:

New version attached.

Here is a (penultimate?) draft that includes URLs. Please provide any
additional feedback no later than 2022-09-14 0:00 AoE. After that, we
will begin the translation process.

<ownhorn>
Is the ability to use ICU for the default collation worth mentioning?
</ownhorn>

On 9/12/22 2:01 PM, Peter Eisentraut wrote:

On 12.09.22 18:52, Jonathan S. Katz wrote:

On 9/1/22 9:10 PM, Jonathan S. Katz wrote:

New version attached.

Here is a (penultimate?) draft that includes URLs. Please provide any
additional feedback no later than 2022-09-14 0:00 AoE. After that, we
will begin the translation process.

<ownhorn>
Is the ability to use ICU for the default collation worth mentioning?
</ownhorn>

<facepalm />

Yes -- it is. I had noted to myself to add that in for a variety of
reasons, not the least of which some of the reported issues around glibc
collations. And then I forgot to add it.

I'll include it in the next draft.

Thanks,

Jonathan

On Mon, Sep 12, 2022 at 12:52:49PM -0400, Jonathan S. Katz wrote:

sorted. Using `row_number()`, `rank()`, and `count()` as
[window functions](https://www.postgresql.org/docs/15/functions-window.html)
also have performance benefits in PostgreSQL 15, and queries using

Remove "using" ?

certain operating systems, PostgreSQL 15 supports the ability to
[prefetch WAL file contents](https://www.postgresql.org/docs/15/runtime-config-wal.html#GUC-RECOVERY-PREFETCH)
and speed up recovery times. PostgreSQL's built-in backup command,

s/and/to/ ?

[`pg_basebackup`](https://www.postgresql.org/docs/15/app-pgbasebackup.html), now
supports server-side compression of backup files with a choice of gzip, LZ4, and

s/with/and/ ?

PostgreSQL 15 includes the SQL standard
[`MERGE`](https://www.postgresql.org/docs/15/sql-merge.html) command.
`MERGE` lets you write conditional SQL statements that include `INSERT`,
`UPDATE`, and `DELETE` actions within a single statement.

maybe "include combinations of INSERT, UPDATE and DELETE ..."

PostgreSQL
[server-level statistics](https://www.postgresql.org/docs/15/monitoring-stats.html)
are now collected in shared memory, eliminating both the statistics collector
process and periodically writing this data to disk.

and *the need to* periodically write this data to disk ?

--
Justin

On 9/12/22 3:34 PM, Justin Pryzby wrote:

On Mon, Sep 12, 2022 at 12:52:49PM -0400, Jonathan S. Katz wrote:

sorted. Using `row_number()`, `rank()`, and `count()` as
[window functions](https://www.postgresql.org/docs/15/functions-window.html)
also have performance benefits in PostgreSQL 15, and queries using

Remove "using" ?

I don't think that's the correct change, but I broke up the sentences.
I'll post the changes shortly.

certain operating systems, PostgreSQL 15 supports the ability to
[prefetch WAL file contents](https://www.postgresql.org/docs/15/runtime-config-wal.html#GUC-RECOVERY-PREFETCH)
and speed up recovery times. PostgreSQL's built-in backup command,

s/and/to/ ?

I suppose that is the end goal of the feature, in which case "to" would
be correct. I made that adjustment.

(I did see tests where recovery time did *not* speed up when prefetching
was used, though it may have been due to the knob settings on the tests).

[`pg_basebackup`](https://www.postgresql.org/docs/15/app-pgbasebackup.html), now
supports server-side compression of backup files with a choice of gzip, LZ4, and

s/with/and/ ?

I don't think that is correct.

PostgreSQL 15 includes the SQL standard
[`MERGE`](https://www.postgresql.org/docs/15/sql-merge.html) command.
`MERGE` lets you write conditional SQL statements that include `INSERT`,
`UPDATE`, and `DELETE` actions within a single statement.

maybe "include combinations of INSERT, UPDATE and DELETE ..."

Added "can".

PostgreSQL
[server-level statistics](https://www.postgresql.org/docs/15/monitoring-stats.html)
are now collected in shared memory, eliminating both the statistics collector
process and periodically writing this data to disk.

and *the need to* periodically write this data to disk ?

I don't see what that adds other than extra words, but I can be
convinced otherwise.

Thanks,

Jonathan

On Tue, 13 Sept 2022 at 04:53, Jonathan S. Katz <jkatz@postgresql.org> wrote:

Here is a (penultimate?) draft that includes URLs. Please provide any
additional feedback no later than 2022-09-14 0:00 AoE. After that, we
will begin the translation process.

Thanks for drafting these up.

I noticed a couple of things, one pretty minor and one that might need
a bit of a reword.

sorted. Using `row_number()`, `rank()`, and `count()` as

dense_rank() is affected by that change too. Maybe it was just omitted
for brevity. I'm ok if it was, but just wanted to make sure it wasn't
an accidental omission.

certain operating systems, PostgreSQL 15 supports the ability to
[prefetch WAL file contents](https://www.postgresql.org/docs/15/runtime-config-wal.html#GUC-RECOVERY-PREFETCH)

I think "ability to prefetch WAL file contents" is not really an
accurate way to describe this feature. What the prefetcher does is
prefetch the pages of tables, indexes and materialized views which are
referenced by WAL, so that when the recovery process comes along
later, the pages of these relations which are being changed by the WAL
record are more likely to be in memory so that the recovery process is
less likely to have to load the referenced pages from disk.

Perhaps the text should read:

"PostgreSQL 15 adds support for prefetching pages referenced in [WAL]."

David

On 9/12/22 4:17 PM, David Rowley wrote:

On Tue, 13 Sept 2022 at 04:53, Jonathan S. Katz <jkatz@postgresql.org> wrote:

Here is a (penultimate?) draft that includes URLs. Please provide any
additional feedback no later than 2022-09-14 0:00 AoE. After that, we
will begin the translation process.

Thanks for drafting these up.

I noticed a couple of things, one pretty minor and one that might need
a bit of a reword.

sorted. Using `row_number()`, `rank()`, and `count()` as

dense_rank() is affected by that change too. Maybe it was just omitted
for brevity. I'm ok if it was, but just wanted to make sure it wasn't
an accidental omission.

It would be an accidental omission. It's also omitted from the release
notes:

"Improve the performance of window functions that use row_number(),
rank(), and count() (David Rowley)"[1]https://www.postgresql.org/docs/15/release-15.html

so we should add it there too :)

certain operating systems, PostgreSQL 15 supports the ability to
[prefetch WAL file contents](https://www.postgresql.org/docs/15/runtime-config-wal.html#GUC-RECOVERY-PREFETCH)

I think "ability to prefetch WAL file contents" is not really an
accurate way to describe this feature. What the prefetcher does is
prefetch the pages of tables, indexes and materialized views which are
referenced by WAL, so that when the recovery process comes along
later, the pages of these relations which are being changed by the WAL
record are more likely to be in memory so that the recovery process is
less likely to have to load the referenced pages from disk.

Thanks. That's a really crisp explanation :)

Perhaps the text should read:

"PostgreSQL 15 adds support for prefetching pages referenced in [WAL]."

What do you think of this (copied from the attached file)

On certain operating systems, PostgreSQL 15 adds support to [prefetch
pages referenced in
WAL](https://www.postgresql.org/docs/15/runtime-config-wal.html#GUC-RECOVERY-PREFETCH)
to help speed up recovery times.

Thanks,

Jonathan

[1]: https://www.postgresql.org/docs/15/release-15.html

On Tue, 13 Sept 2022 at 08:56, Jonathan S. Katz <jkatz@postgresql.org> wrote:

What do you think of this (copied from the attached file)

On certain operating systems, PostgreSQL 15 adds support to [prefetch
pages referenced in
WAL](https://www.postgresql.org/docs/15/runtime-config-wal.html#GUC-RECOVERY-PREFETCH)
to help speed up recovery times.

Looks good. Thanks for adjusting that.

David

On Tue, Sep 13, 2022 at 08:17:27AM +1200, David Rowley wrote:

certain operating systems, PostgreSQL 15 supports the ability to
[prefetch WAL file contents](https://www.postgresql.org/docs/15/runtime-config-wal.html#GUC-RECOVERY-PREFETCH)

I think "ability to prefetch WAL file contents" is not really an
accurate way to describe this feature. What the prefetcher does is
prefetch the pages of tables, indexes and materialized views which are
referenced by WAL, so that when the recovery process comes along
later, the pages of these relations which are being changed by the WAL
record are more likely to be in memory so that the recovery process is
less likely to have to load the referenced pages from disk.

Perhaps the text should read:

"PostgreSQL 15 adds support for prefetching pages referenced in [WAL]."

Related:
/messages/by-id/20220904075450.6g4nm4hralyw3tab@alvherre.pgsql