Thread: PostgreSQL 18 Beta 1 release announcement draft

PostgreSQL 18 Beta 1 release announcement draft

From
"Jonathan S. Katz"
Date:
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

Attachment

Re: PostgreSQL 18 Beta 1 release announcement draft

From
Peter Geoghegan
Date:
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



Re: PostgreSQL 18 Beta 1 release announcement draft

From
Bertrand Drouvot
Date:
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



Re: PostgreSQL 18 Beta 1 release announcement draft

From
Álvaro Herrera
Date:
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/



Re: PostgreSQL 18 Beta 1 release announcement draft

From
Nathan Bossart
Date:
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



Re: PostgreSQL 18 Beta 1 release announcement draft

From
Andres Freund
Date:
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



Re: PostgreSQL 18 Beta 1 release announcement draft

From
Bruce Momjian
Date:
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.



Re: PostgreSQL 18 Beta 1 release announcement draft

From
Andres Freund
Date:
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



Re: PostgreSQL 18 Beta 1 release announcement draft

From
"Jonathan S. Katz"
Date:
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

Attachment

Re: PostgreSQL 18 Beta 1 release announcement draft

From
"Jonathan S. Katz"
Date:
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

Attachment

Re: PostgreSQL 18 Beta 1 release announcement draft

From
"Jonathan S. Katz"
Date:
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

Attachment

Re: PostgreSQL 18 Beta 1 release announcement draft

From
Álvaro Herrera
Date:
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



Re: PostgreSQL 18 Beta 1 release announcement draft

From
Jelte Fennema-Nio
Date:
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.



Re: PostgreSQL 18 Beta 1 release announcement draft

From
Jacob Champion
Date:
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



Re: PostgreSQL 18 Beta 1 release announcement draft

From
"Jonathan S. Katz"
Date:
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

Attachment

Re: PostgreSQL 18 Beta 1 release announcement draft

From
Bruce Momjian
Date:
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.