Обсуждение: PostgreSQL 18 Beta 1 release announcement draft

Hi,

Attached is a draft of the PostgreSQL 18 Beta 1 release announcement. 
The goal of this announcement is to introduce the new capabilities 
planned for PostgreSQL 18 and give users an idea of areas we'd like to 
see tested.

Please check for accuracy and if there are glaring omissions (happy to 
have the discussion on what to include in here, though note it's not 
possible to list everything in here). If a description is unclear or 
there are typos, I'm also happy to modify it; that said, for these 
suggestions I'm looking for recommendations that bring better clarity to 
a description vs. nitpicking over phrasing.

While I can make changes up until 2025-05-08 12:00 UTC, I won't be able 
to make anything substantive past 2025-05-08 02:00 UTC (especially 
because there's also the regular release this week!), so please have all 
feedback in by  2025-05-08 02:00 UTC.

Thanks - and thanks to everyone for their hard work on this release - 
I'm very excited for PostgreSQL 18!

Jonathan

On Mon, May 5, 2025 at 3:07 PM Jonathan S. Katz <jkatz@postgresql.org> wrote:
>
> Hi,
>
> Attached is a draft of the PostgreSQL 18 Beta 1 release announcement.
> The goal of this announcement is to introduce the new capabilities
> planned for PostgreSQL 18 and give users an idea of areas we'd like to
> see tested.
>
> Please check for accuracy and if there are glaring omissions (happy to
> have the discussion on what to include in here, though note it's not
> possible to list everything in here). If a description is unclear or
> there are typos, I'm also happy to modify it; that said, for these
> suggestions I'm looking for recommendations that bring better clarity to
> a description vs. nitpicking over phrasing.

One small piece of feedback on this:

"PostgreSQL 18 adds support for using "skip scan" lookups on
multicolumn B-tree indexes, which can result in faster execution times
for queries with multiple conditions."

I don't think that this is quite right; there doesn't have to be
multiple conditions involved in the query (though it *is* true that
skip scan can only be used with a multicolumn B-tree index). The
typical skip scan involves a query whose predicate has only a single
"=" condition, which is on the second column from a multicolumn index.
Typically, the first index column won't appear in the query predicate
at all -- it is wholly omitted, and yet the index can be scanned
reasonably efficiently when there aren't too many distinct values in
the leading/skipped column. (Of course it can be a lot more
complicated than this, but I'm just talking about the typical, simple
case.)

I suggest something closer to:

"PostgreSQL 18 adds support for using "skip scan" lookups on
multicolumn B-tree indexes, which can result in faster execution times
for queries that omit a "=" condition on one or more prefix index
columns."

There's a real danger in users confusing skip scan for loose index
scan, which is actually quite a different feature. Many hackers have
made the same mistake in the past. ISTM that specifically describing
how the feature applies to queries that omit an "=" condition makes
this misunderstanding less likely to occur.

--
Peter Geoghegan

Hi,

On Mon, May 05, 2025 at 03:07:01PM -0400, Jonathan S. Katz wrote:
> Hi,
> 
> Attached is a draft of the PostgreSQL 18 Beta 1 release announcement.

Thanks for working on it!

One small feedback on this:

"
and now shows per-connection statistics on I/O utilization
"

I think we can add WAL to the game, something like:

"
and now shows per-connection's I/O and WAL statistics 
"

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

On 2025-May-05, Jonathan S. Katz wrote:

> ### Other Highlights
> 
> Starting with PostgreSQL 18, data checksums, which are used to validate the integrity of stored data, are now enabled
bydefault on new PostgreSQL clusters. You can choose to disable this behavior using the `initdb --no-data-checksums`
command.
> 
> `pg_createsubscriber` now supports an `--all` flag so you can create logical replicas for all databases in an
instancewith a single command. Additionally, PostgreSQL 18 lets you create the schema definition of a foreign table
usingthe definition of a local table using the `CREATE FOREIGN TABLE ... LIKE` command.
 

In this section I would add the work on not-null and NOT ENFORCED
constraints, not because I think they are very important, but because
they have the potential to break stuff; and if they do, it would be very
useful to know before GA.  Maybe something like

  Some constraint features have been revised: foreign-key and check
  constraints can now be made NOT ENFORCED and altered into
  enforceability; not-null constraints now preserve their names as
  mandated by the SQL-standard, support the NOT VALID and NO INHERIT
  clauses, as well as behave more consistently on inheritance situations.


Now that I look again, I see no mention of temporal UNIQUE, PRIMARY KEY
and FOREIGN KEY constraints (commits fc0438b4e805 and 89f908a6d0ac).  I
think they are a very important feature, perhaps they even warrant a
section of their own.  So maybe I'd suggest to add a section
"### Constraints" and put all these things in there.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

On Mon, May 05, 2025 at 03:07:01PM -0400, Jonathan S. Katz wrote:
> Before PostgreSQL 18, an important step after performing a major version
> upgrade was to run the `ANALYZE` to generate statistics, which is a
> critical component of helping PostgreSQL to select the most efficient
> query plan. Based on the size and overall activity of a PostgreSQL
> cluster, this could be a time consuming process, and potentially impact
> query performance until the process completed. PostgreSQL 18 introduces
> the ability to keep planner statistics through a major version upgrade,
> removing the need to perform the analyze and help an upgraded cluster to
> get to its expected performance state sooner once it's available.

To nitpick, the last sentence isn't totally accurate.  Folks still need to
analyze tables for which stats were not transferred (e.g., extended stats),
and we additionally still recommend a database-wide analyze to update the
cumulative stats that autovacuum uses, which are also not transferred.  We
might just want to remove the "removing the need to perform the analyze"
part and focus on the benefits of transferring most of the stats.

> Additionally, `pg_upgrade`, the utility used to facilitate a major
> version upgrade, added several performance enhancements to help
> accelerate upgrades with many objects, such as tables and sequences. This
> release adds the `--jobs` flag, which allows pg_upgrade to process its
> checks in parallel, and the `--swap` flag, which swaps upgrade
> directories instead of copying, cloning, or linking files.

The --jobs flag has existed for a while.  What v18 does is parallelize
gathering cluster information and performing cluster checks, and it uses
the pre-existing --jobs option to determine how parallel to do these
things.

> This release deprecates `md5` password authentication in favor of using
> SCRAM authentication that was first added in PostgreSQL 10.

IMHO we should emphasize that MD5 password support will be removed in a
future release and that users should migrate to something else soon.  I'm
imagining it'll still be at least a couple of years before I muster up the
courage to actually propose removing MD5 support, but I think it's
important to broadcast our intent here.

-- 
nathan

Hi,

On 2025-05-05 15:07:01 -0400, Jonathan S. Katz wrote:
> ### Performance
>
> PostgreSQL 18 introduces an asynchronous I/O (AIO) subsystem using
> `io_uring` on Linux, which, when combined with direct I/O (DIO), lets
> PostgreSQL directly interface with storage interfaces. This new subsystem
> gives PostgreSQL the opportunity to maximize its usage of hardware transfer
> capacity, and this initial release supporting file system reads such as
> sequential scans, bitmap heap scans, and vacuums, with tests showing up to a
> 2-3x performance improvements.

This isn't quite right:

- AIO is usable on all platforms, it *optionally* can use io_uring on linux -
  the default is to use AIO using worker processes

- One can see substantial perf benefits *without* using direct IO - in fact,
  DIO remains a "developer oriented" option, because our use of AIO is too
  limited for it to be generally usable

  I think for now I would probably just not mention DIO. Hopefully we can make
  it more widely usable in 19.


How about something like:

PostgreSQL 18 introduces an asynchronous I/O (AIO) subsystem. This new
subsystem allows to increase I/O throughput and to hide I/O latency. On Linux
`io_uring` can be used for AIO, a worker based implementation is available on
all platforms. This initial release supporting file system reads such as
sequential scans, bitmap heap scans, and vacuums, with tests showing up to a
2-3x performance improvements.


Where's that 2-3x number from, btw? Depending on how narrow a case one is
looking at, it's possible to see bigger wins... But I'm not sure it's worth
looking too narrowly.


> ### Other Highlights
>
> Starting with PostgreSQL 18, data checksums, which are used to validate the integrity of stored data, are now enabled
bydefault on new PostgreSQL clusters. You can choose to disable this behavior using the `initdb --no-data-checksums`
command.

Should we point out that this might require adjustments for upgrade scripts?
Probably not in the release announcement?

Greetings,

Andres Freund

On Tue, May  6, 2025 at 11:55:53AM -0400, Andres Freund wrote:
> Hi,
> 
> On 2025-05-05 15:07:01 -0400, Jonathan S. Katz wrote:
> > ### Performance
> >
> > PostgreSQL 18 introduces an asynchronous I/O (AIO) subsystem using
> > `io_uring` on Linux, which, when combined with direct I/O (DIO), lets
> > PostgreSQL directly interface with storage interfaces. This new subsystem
> > gives PostgreSQL the opportunity to maximize its usage of hardware transfer
> > capacity, and this initial release supporting file system reads such as
> > sequential scans, bitmap heap scans, and vacuums, with tests showing up to a
> > 2-3x performance improvements.
> 
> This isn't quite right:
> 
> - AIO is usable on all platforms, it *optionally* can use io_uring on linux -
>   the default is to use AIO using worker processes

Actually, is AIO using worker processes new behavior or just a new
implementation of our old behavior?  I might need to point that out in
the release notes.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.

Hi,

On 2025-05-06 16:53:46 -0400, Bruce Momjian wrote:
> On Tue, May  6, 2025 at 11:55:53AM -0400, Andres Freund wrote:
> > On 2025-05-05 15:07:01 -0400, Jonathan S. Katz wrote:
> > > ### Performance
> > >
> > > PostgreSQL 18 introduces an asynchronous I/O (AIO) subsystem using
> > > `io_uring` on Linux, which, when combined with direct I/O (DIO), lets
> > > PostgreSQL directly interface with storage interfaces. This new subsystem
> > > gives PostgreSQL the opportunity to maximize its usage of hardware transfer
> > > capacity, and this initial release supporting file system reads such as
> > > sequential scans, bitmap heap scans, and vacuums, with tests showing up to a
> > > 2-3x performance improvements.
> > 
> > This isn't quite right:
> > 
> > - AIO is usable on all platforms, it *optionally* can use io_uring on linux -
> >   the default is to use AIO using worker processes
> 
> Actually, is AIO using worker processes new behavior or just a new
> implementation of our old behavior?  I might need to point that out in
> the release notes.

New.

- Andres

On 5/6/25 11:17 AM, Nathan Bossart wrote:

> IMHO we should emphasize that MD5 password support will be removed in a
> future release and that users should migrate to something else soon.  I'm
> imagining it'll still be at least a couple of years before I muster up the
> courage to actually propose removing MD5 support, but I think it's
> important to broadcast our intent here.

I took most of these changes. I don't think we should add the MD5 "will 
be removed in a future release" in the beta announcement, as that could 
lead to confusion (e.g. it's removed in beta 2!). I'm fine with adding 
that in the GA.

Jonathan

On 5/6/25 11:55 AM, Andres Freund wrote:
> Hi,
> 
> On 2025-05-05 15:07:01 -0400, Jonathan S. Katz wrote:
>> ### Performance
>>
>> PostgreSQL 18 introduces an asynchronous I/O (AIO) subsystem using
>> `io_uring` on Linux, which, when combined with direct I/O (DIO), lets
>> PostgreSQL directly interface with storage interfaces. This new subsystem
>> gives PostgreSQL the opportunity to maximize its usage of hardware transfer
>> capacity, and this initial release supporting file system reads such as
>> sequential scans, bitmap heap scans, and vacuums, with tests showing up to a
>> 2-3x performance improvements.
> 
> This isn't quite right:
> 
> - AIO is usable on all platforms, it *optionally* can use io_uring on linux -
>    the default is to use AIO using worker processes
> 
> - One can see substantial perf benefits *without* using direct IO - in fact,
>    DIO remains a "developer oriented" option, because our use of AIO is too
>    limited for it to be generally usable
> 
>    I think for now I would probably just not mention DIO. Hopefully we can make
>    it more widely usable in 19.
> 
> 
> How about something like:
> 
> PostgreSQL 18 introduces an asynchronous I/O (AIO) subsystem. This new
> subsystem allows to increase I/O throughput and to hide I/O latency. On Linux
> `io_uring` can be used for AIO, a worker based implementation is available on
> all platforms. This initial release supporting file system reads such as
> sequential scans, bitmap heap scans, and vacuums, with tests showing up to a
> 2-3x performance improvements.

I took this word-for-word.

> Where's that 2-3x number from, btw? Depending on how narrow a case one is
> looking at, it's possible to see bigger wins... But I'm not sure it's worth
> looking too narrowly.

I believe it came from Melanie/Thomas (or both) when I was asking around 
how to provide a general claim. I feel that something directional for 
the beta is "good enough", and we can fine tune the statement for the GA 
announcement.

>> ### Other Highlights
>>
>> Starting with PostgreSQL 18, data checksums, which are used to validate the integrity of stored data, are now
enabledby default on new PostgreSQL clusters. You can choose to disable this behavior using the `initdb
--no-data-checksums`command.
 
> 
> Should we point out that this might require adjustments for upgrade scripts?
> Probably not in the release announcement?

Hm, I actually think it'd be good to mention that in the beta in case 
people need to plan for that. I added a small line to that.

Jonathan

On 5/6/25 9:15 AM, Álvaro Herrera wrote:
> On 2025-May-05, Jonathan S. Katz wrote:
> 
>> ### Other Highlights
>>
>> Starting with PostgreSQL 18, data checksums, which are used to validate the integrity of stored data, are now
enabledby default on new PostgreSQL clusters. You can choose to disable this behavior using the `initdb
--no-data-checksums`command.
 
>>
>> `pg_createsubscriber` now supports an `--all` flag so you can create logical replicas for all databases in an
instancewith a single command. Additionally, PostgreSQL 18 lets you create the schema definition of a foreign table
usingthe definition of a local table using the `CREATE FOREIGN TABLE ... LIKE` command.
 
> 
> In this section I would add the work on not-null and NOT ENFORCED
> constraints, not because I think they are very important, but because
> they have the potential to break stuff; and if they do, it would be very
> useful to know before GA.  Maybe something like
> 
>    Some constraint features have been revised: foreign-key and check
>    constraints can now be made NOT ENFORCED and altered into
>    enforceability; not-null constraints now preserve their names as
>    mandated by the SQL-standard, support the NOT VALID and NO INHERIT
>    clauses, as well as behave more consistently on inheritance situations.
> 
> 
> Now that I look again, I see no mention of temporal UNIQUE, PRIMARY KEY
> and FOREIGN KEY constraints (commits fc0438b4e805 and 89f908a6d0ac).  I
> think they are a very important feature, perhaps they even warrant a
> section of their own.  So maybe I'd suggest to add a section
> "### Constraints" and put all these things in there.

Here's the next update (CCing folks who commented); I added a sentence 
on the new constraints that are available in the developer experience 
section, and the sentence around the behavior towards the bottom. Please 
let me know if you think this sufficiently explains the feature.

Thanks for the review!

Jonathan

On 2025-May-06, Jonathan S. Katz wrote:

> Here's the next update (CCing folks who commented); I added a sentence on
> the new constraints that are available in the developer experience section,
> and the sentence around the behavior towards the bottom. Please let me know
> if you think this sufficiently explains the feature.

I think it does.  Thanks much.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Update: super-fast reaction on the Postgres bugs mailing list. The report
was acknowledged [...], and a fix is under discussion.
The wonders of open-source !"
             https://twitter.com/gunnarmorling/status/1596080409259003906

On Mon, 5 May 2025 at 21:07, Jonathan S. Katz <jkatz@postgresql.org> wrote:
> Attached is a draft of the PostgreSQL 18 Beta 1 release announcement.

It might make sense to add a small sentence like "this release
introduces version 3.2 of the wire protocol, but libpq still uses 3.0
by default. This is the first new protocol version since Postgres
7.4". I think for users it's probably not super interesting
information, but we really want client/pooler/proxy authors to start
supporting the protocol negotiation. That will make it possible to at
some point start using a newer protocol version by default.

On Mon, 5 May 2025 at 21:07, Jonathan S. Katz <jkatz@postgresql.org> wrote:
>
> Hi,
>
> Attached is a draft of the PostgreSQL 18 Beta 1 release announcement.
> The goal of this announcement is to introduce the new capabilities
> planned for PostgreSQL 18 and give users an idea of areas we'd like to
> see tested.
>
> Please check for accuracy and if there are glaring omissions (happy to
> have the discussion on what to include in here, though note it's not
> possible to list everything in here). If a description is unclear or
> there are typos, I'm also happy to modify it; that said, for these
> suggestions I'm looking for recommendations that bring better clarity to
> a description vs. nitpicking over phrasing.

> ### Performance
>
> [...] There are also numerous performance improvements for how PostgreSQL plans and executes table joins, *from*
allowingmerge joins *to* use incremental sorts *and* improving the overall performance of hash joins.
 

(emphasis mine)

This has a "from [A] to [B] and [C]" structure, but the 'to' in the
sentence isn't part of that structure; instead it's part of "allowing
merge joins *to* use incremental sorts" (we had merge joins and
incremental sorts before, they just didn't work together yet). This
caused a double-take on my end.
I think it would be easier to read if it was instead written as "from
[A] to [B]" as follows:

[...], *from* improving the overall performance of hash joins *to*
allowing merge joins to use incremental sorts.

... optionally with one more such feature added to allow the use of
"and" in this too.

> ### Upgrading

I'd rename this section to "Upgrade performance", "Upgrade
experience", or "Upgrade workflow".

Reason: Most new release posts which have a section "Upgrading" which
contains details on how to upgrade, while this section is about
improvements in the upgrade workflow of PostgreSQL. This difference
also initially caused me to skip the section when doing an initial
pass for fact checks.


Hope these aren't too nitpick-y.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)

On Tue, May 06, 2025 at 11:24:44PM -0400, Jonathan S. Katz wrote:
> I took most of these changes. I don't think we should add the MD5 "will be
> removed in a future release" in the beta announcement, as that could lead to
> confusion (e.g. it's removed in beta 2!). I'm fine with adding that in the
> GA.

Makes sense.

-- 
nathan

On Tue, May  6, 2025 at 11:24:44PM -0400, Jonathan Katz wrote:
> On 5/6/25 11:17 AM, Nathan Bossart wrote:
> 
> > IMHO we should emphasize that MD5 password support will be removed in a
> > future release and that users should migrate to something else soon.  I'm
> > imagining it'll still be at least a couple of years before I muster up the
> > courage to actually propose removing MD5 support, but I think it's
> > important to broadcast our intent here.
> 
> I took most of these changes. I don't think we should add the MD5 "will be
> removed in a future release" in the beta announcement, as that could lead to
> confusion (e.g. it's removed in beta 2!). I'm fine with adding that in the

You could say "will be removed in a future major release".

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.

On Tue, May 6, 2025 at 8:46 PM Jonathan S. Katz <jkatz@postgresql.org> wrote:
> Here's the next update

Thanks!

> PostgreSQL 18 introduces `oauth` authentication, which people can create extensions that support OAuth 2.0 based
authenticationmechanisms that PostgreSQL can authenticate with. 

Suggested alternative: "PostgreSQL 18 introduces `oauth`
authentication, which lets the client retrieve OAuth 2.0 tokens and
allows people to create server extensions that authenticate users with
those tokens."

--Jacob

On 5/7/25 5:18 AM, Jelte Fennema-Nio wrote:
> On Mon, 5 May 2025 at 21:07, Jonathan S. Katz <jkatz@postgresql.org> wrote:
>> Attached is a draft of the PostgreSQL 18 Beta 1 release announcement.
> 
> It might make sense to add a small sentence like "this release
> introduces version 3.2 of the wire protocol, but libpq still uses 3.0
> by default. This is the first new protocol version since Postgres
> 7.4". I think for users it's probably not super interesting
> information, but we really want client/pooler/proxy authors to start
> supporting the protocol negotiation. That will make it possible to at
> some point start using a newer protocol version by default.

Good call out. Added something like this:

==
PostgreSQL 18 also introduces a new version (3.2) of the PostgreSQL wire 
protocol, which is the first new protocol version since PostgreSQL 7.4 
(2003). libpq still uses version 3.0 by default while clients (e.g., 
drivers, poolers, proxies) add support for the new protocol version.
==

Jonathan

On Wed, May 7, 2025 at 2:45 PM Jonathan S. Katz <jkatz@postgresql.org> wrote:
> I did a double take on the current sentence, and revised it to:
>
> ==
> PostgreSQL 18 introduces `oauth` authentication, which lets users
> authenticate using OAuth 2.0 mechanisms supported through PostgreSQL
> extensions.
> ==
>
> I don't know how much we want to get into the back-and-forth flow in the
> announcement itself, since it's more about raising awareness that this
> exists, and then people can read the docs to find out more.

LGTM, thanks!

--Jacob

On Wed, May  7, 2025 at 02:52:27PM -0700, Jacob Champion wrote:
> On Wed, May 7, 2025 at 2:45 PM Jonathan S. Katz <jkatz@postgresql.org> wrote:
> > I did a double take on the current sentence, and revised it to:
> >
> > ==
> > PostgreSQL 18 introduces `oauth` authentication, which lets users
> > authenticate using OAuth 2.0 mechanisms supported through PostgreSQL
> > extensions.
> > ==
> >
> > I don't know how much we want to get into the back-and-forth flow in the
> > announcement itself, since it's more about raising awareness that this
> > exists, and then people can read the docs to find out more.
> 
> LGTM, thanks!

I have a few suggested changes to the PG 18 Beta 1 release notes in case
this text will be used for future PG 18 releases.

---------------------------------------------------------------------------

increase I/O throughput and to hide I/O latency.
increase I/O throughput and to reduce I/O latency.
                               ^^^^^^

On Linux io_uring can be used for AIO, a worker based implementation
On Linux io_uring can be used for AIO, a worker-based implementation
                                               ^
This initial release supporting file system reads such as sequential
This initial release supports file system reads such as sequential
                            ^

This release also includes optimizations for WHERE clauses that contain
OR and IN (...) statements to better utilize recent indexing
improvements which can also result in better query performance.
SENTENCE TOO COMPLEX

which are commonly used for search over JSON and full-text data. 
which are commonly used for searching JSON and full-text data. 
                            ^^^^^^^^^

and adds several improvements to queries over partitioned tables,
and adds several improvements for queries over partitioned tables,
                              ^^^

a critical component of helping PostgreSQL to select
a critical component to help PostgreSQL select
                     ^^^^^^^^^^^^^^^^^^^^^^^^^

introduces the CASEFOLD to help 
introduces CASEFOLD to help 
           ^^^^^^^^

and also adds the ssl_tls13_ciphers to let
and also adds ssl_tls13_ciphers to let
              ^^^^^^^^^^^^^^^^^

and surfaces this information
and reports this information
    ^^^^^^^

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.