PostgreSQL 18 Beta 1 release announcement draft

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 by 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 instance with a single command. Additionally, PostgreSQL 18 lets you create the schema definition of a foreign table using the 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 by 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?

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 enabled by 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 enabled by 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 instance with a single command. Additionally, PostgreSQL 18 lets you create the schema definition of a foreign table using the 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* allowing merge 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 authentication mechanisms 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 5/7/25 5:38 AM, Matthias van de Meent wrote:

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* allowing merge 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.

Thanks - I don't consider something "nitpicky" if the original sentence
is hard to parse. I've reworded it as recommended.

### 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.

Thanks; I took these suggestions.

Jonathan

On 5/7/25 4:54 PM, Bruce Momjian wrote:

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".

Went with:

`md5` authentication will be fully removed in a future major version
release.

Thanks,

Jonathan