Thread: 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
Attachment
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
Attachment
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
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
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 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
Attachment
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.