Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add modern SHA-2 based password hashes to pgcrypto.

  2. Convert 'x IN (VALUES ...)' to 'x = ANY ...' then appropriate

  3. Allow usage of match_orclause_to_indexcol() for joins

  1. PostgreSQL 18 GA press release draft

    Jonathan S. Katz <jkatz@postgresql.org> — 2025-09-10T03:13:43Z

    Hi,

Attached is a draft of the PostgreSQL 18 GA press release. A few 
comments before the ask for reviewing:

* I still need to write up the theme and the quote, which I'll provide 
tomorrow after hearing the first round of feedback
* The release is a bit long - this is due in part to what I think is a 
lot of really good features in PostgreSQL 18. "Something for everyone"

Please review for the press release for the following:

* Correctness and accuracy of what's described - we want to be 
technically correct, but we also need to explain why something matters 
to our users.
* Notable omissions (apologies if I missed something, but also note not 
everything going to make the cut).
* Features to exclude.
* How easy phrases are to translate / confusing phrases
* Typos.

Like previous years, I won't really look at stylistic remarks, unless it 
falls into the "making it simpler to translate / confusing phrase" bucket.

After 2025-09-14 0:00 UTC I won't be able to accept more feedback, as 
we'll need to begin the translation process. Anyone who provides 
accepted feedback will also get a mention in the press release "thank 
you" page[1], which stays up for about a year ;)

Thanks for your help!

Jonathan

[1] https://www.postgresql.org/about/press/

  2. Re: PostgreSQL 18 GA press release draft

    Peter Smith <smithpb2250@gmail.com> — 2025-09-10T03:38:38Z

    FWIW, here are a couple of AI-detected issues.

This typo:
"with contains installers" should be "which contains installers"

This potential mismatch:
"Built on over 35 years of engineering" is inconsistent with the
earlier "nearly 30 years" statement?

======
Kind Regards,
Peter Smith.
Fujitsu Australia.

  3. Re: PostgreSQL 18 GA press release draft

    Florents Tselai <florents.tselai@gmail.com> — 2025-09-10T04:07:47Z

    On Wed, Sep 10, 2025 at 6:13 AM Jonathan S. Katz <jkatz@postgresql.org>
wrote:

> Hi,
>
> Attached is a draft of the PostgreSQL 18 GA press release. A few
> comments before the ask for reviewing:
>
> * I still need to write up the theme and the quote, which I'll provide
> tomorrow after hearing the first round of feedback
> * The release is a bit long - this is due in part to what I think is a
> lot of really good features in PostgreSQL 18. "Something for everyone"
>
> Please review for the press release for the following:
>
> * Correctness and accuracy of what's described - we want to be
> technically correct, but we also need to explain why something matters
> to our users.
> * Notable omissions (apologies if I missed something, but also note not
> everything going to make the cut).
> * Features to exclude.
> * How easy phrases are to translate / confusing phrases
> * Typos.
>
> Like previous years, I won't really look at stylistic remarks, unless it
> falls into the "making it simpler to translate / confusing phrase" bucket.
>
> After 2025-09-14 0:00 UTC I won't be able to accept more feedback, as
> we'll need to begin the translation process. Anyone who provides
> accepted feedback will also get a mention in the press release "thank
> you" page[1], which stays up for about a year ;)
>
> Thanks for your help!
>
> Jonathan
>
> [1] https://www.postgresql.org/about/press/


The virtual gen columns paragraph could be its own subsection, even.
I'd expect it to be a highly anticipated feature.

  4. Re: PostgreSQL 18 GA press release draft

    jian he <jian.universality@gmail.com> — 2025-09-10T04:29:01Z

    On Wed, Sep 10, 2025 at 11:13 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:
>
> Hi,
>
> Please review for the press release for the following:
>
> * Correctness and accuracy of what's described - we want to be
> technically correct, but we also need to explain why something matters
> to our users.

PostgreSQL 18 adds [temporal
constraints](https://www.postgresql.org/docs/18/release-18.html#RELEASE-18-CONSTRAINTS)
-- constraints over ranges -- for both `PRIMARY KEY` and `UNIQUE`
constraints using the `WITHOUT OVERLAPS` clause, and on `FOREIGN KEY`
constraints using the `PERIOD` clause.

this link (https://www.postgresql.org/docs/18/release-18.html#RELEASE-18-CONSTRAINTS)
seems not that appropriate, since it does not explain WITHOUT OVERLAPS,
link (https://www.postgresql.org/docs/devel/sql-createtable.html#SQL-CREATETABLE-PARMS-UNIQUE)
would be more appropriate?

  5. Re: PostgreSQL 18 GA press release draft

    Jakub Wartak <jakub.wartak@enterprisedb.com> — 2025-09-10T06:58:14Z

    On Wed, Sep 10, 2025 at 5:14 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:
>
> Hi,
>
> Attached is a draft of the PostgreSQL 18 GA press release. A few
> comments before the ask for reviewing:
>

Hi Jonathan,

> Finally, PostgreSQL 18 introduces support for [NUMA awareness](https://www.postgresql.org/docs/18/install-make.html#CONFIGURE-OPTION-WITH-LIBNUMA) that adds basic [NUMA observability](https://www.postgresql.org/docs/18/view-pg-shmem-allocations-numa.html) to PostgreSQL.

For some reason I find this sentence not accurate: NUMA awareness
somehow indicates that we get performance benefits out of this, but we
do not (yet*)  - we simply provide some insight into NUMA memory
layout so far and link against libnuma.  So maybe we should remove
"introduces support for [NUMA
awareness](https://www.postgresql.org/docs/18/install-make.html#CONFIGURE-OPTION-WITH-LIBNUMA)
that"
and just leave it as
"Finally, PostgreSQL 18 adds basic [NUMA observability]" ?

Rationale: for every other software that I saw NUMA awareness
reference was always linked to gaining performance , but just linking
against libnuma is not giving us this.

* = with on going work for 19 (future Tomas's we'll get there I hope)

-J.

  6. Re: PostgreSQL 18 GA press release draft

    John Naylor <johncnaylorls@gmail.com> — 2025-09-10T13:52:31Z

    On Wed, Sep 10, 2025 at 10:13 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:

> PostgreSQL 18 also supports using x86 AVX-512 instructions for CRC32 calculations, which are used in page checksums and are also available in the new [`crc32`](https://www.postgresql.org/docs/18/functions-binarystring.html#FUNCTIONS-BINARYSTRING-OTHER) function.

Hi Jon,

The checksum stored in the page header is not actually a CRC but
something else entirely. WAL records are a convenient example of
something that uses CRC.

Also, CRC32 is not hardware accelerated, only CRC32C is. We have two
SQL-callable functions.  To avoid confusion maybe they can be omitted
from this longer-than-usual announcement -- they seem like a niche
feature anyway.

--
John Naylor
Amazon Web Services

  7. Re: PostgreSQL 18 GA press release draft

    Peter Geoghegan <pg@bowt.ie> — 2025-09-10T19:59:24Z

    On Tue, Sep 9, 2025 at 11:13 PM Jonathan S. Katz <jkatz@postgresql.org> wrote:
> Attached is a draft of the PostgreSQL 18 GA press release.

I have some feedback on this sentence:

"It can also automatically optimize queries using `OR` or `IN (VALUES
...)` in `WHERE` clauses for faster execution".

This is factually correct, but I think that it gives too much
importance to the `IN (VALUES ...)` transformation added by commit
c0962a11. IMV we shouldn't mention anything about transformations that
affect queries that use IN(), since it only applies to `IN (VALUES
...)` -- which is a rather limited special case. Especially because
this IN(VALUES()) case is limited to transforming queries that only
have true constants in the VALUES() clause -- it cannot work with
parameters at all.

I say this in part because I've noticed that existing press reports
about this functionality (which were based on the beta1 announcement)
say that it affects IN() queries in general, which isn't true. Again,
I know that you haven't made that same mistake here -- but a lot of
people will read `IN (VALUES ...)` as "any and all IN() lists".
They'll tend to interpret "VALUES" as "some values that appear in an
IN()", and not "a VALUES() clause that appears in an IN()".

The work from commits d4378c00 and ae456916 is truly important, and
definitely merits prominent mention in the press release. That'll
transform a query written as "SELECT * FROM tenk1 WHERE tenthous = 1
OR tenthous = 3 OR tenthous = 42 OR tenthous = 0" into a
representation that was previous only used when the query was written
"SELECT * FROM tenk1 WHERE tenthous IN (1,3,42,0)" (namely, it
transforms the original such that we can use the ScalarArrayOpExpr
representation).

This transformation is particularly useful in cases where it'll allow
us to get an index-only scan plan instead of a BitmapOr plan (with one
bitmap index scan child node for each of the 3 "tenthous" values from
the query), which is the only plan we could ever get on earlier
releases. The transformation process for these OR cases *can* work
with dynamic parameters (unlike the VALUES() stuff), and so can even
be used on the inner side of a join (see also commit 627d6341, which
dealt with making it possible to use OR transformation with joins).

Putting it all together, I suggest the following alternative:

"It can also automatically transform queries with `OR` constructs in
their `WHERE` clause into a logically equivalent IN() representation
that can be pushed down to index scan nodes, leading to significantly
faster execution".

-- 
Peter Geoghegan

  8. Re: PostgreSQL 18 GA press release draft

    Jonathan S. Katz <jkatz@postgresql.org> — 2025-09-12T01:49:09Z

    On 9/9/25 11:38 PM, Peter Smith wrote:
> FWIW, here are a couple of AI-detected issues.
> 
> This typo:
> "with contains installers" should be "which contains installers"

Thanks!

> This potential mismatch:
> "Built on over 35 years of engineering" is inconsistent with the
> earlier "nearly 30 years" statement?

That actually needs updating, since it's nearly 40 years now. The first 
statement is on open source development, the second is on the overall 
lifetime of POSTGRES since Berkeley.

Jonathan

  9. Re: PostgreSQL 18 GA press release draft

    Jonathan S. Katz <jkatz@postgresql.org> — 2025-09-12T01:51:31Z

    On 9/10/25 12:29 AM, jian he wrote:
> On Wed, Sep 10, 2025 at 11:13 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:
>>
>> Hi,
>>
>> Please review for the press release for the following:
>>
>> * Correctness and accuracy of what's described - we want to be
>> technically correct, but we also need to explain why something matters
>> to our users.
> 
> PostgreSQL 18 adds [temporal
> constraints](https://www.postgresql.org/docs/18/release-18.html#RELEASE-18-CONSTRAINTS)
> -- constraints over ranges -- for both `PRIMARY KEY` and `UNIQUE`
> constraints using the `WITHOUT OVERLAPS` clause, and on `FOREIGN KEY`
> constraints using the `PERIOD` clause.
> 
> this link (https://www.postgresql.org/docs/18/release-18.html#RELEASE-18-CONSTRAINTS)
> seems not that appropriate, since it does not explain WITHOUT OVERLAPS,
> link (https://www.postgresql.org/docs/devel/sql-createtable.html#SQL-CREATETABLE-PARMS-UNIQUE)
> would be more appropriate?

I like the suggestion and made the swap. Thanks!

Jonathan

  10. Re: PostgreSQL 18 GA press release draft

    Jonathan S. Katz <jkatz@postgresql.org> — 2025-09-12T01:53:29Z

    On 9/10/25 2:58 AM, Jakub Wartak wrote:

> and just leave it as
> "Finally, PostgreSQL 18 adds basic [NUMA observability]" ?
> 
> Rationale: for every other software that I saw NUMA awareness
> reference was always linked to gaining performance , but just linking
> against libnuma is not giving us this.

Hm, based on this I'm inclined to remove it. It's hard to explain a "so 
what" here, since the so what is that it's making it easier for us to 
perform this work below:

> * = with on going work for 19 (future Tomas's we'll get there I hope)

Thanks,

Jonathan

  11. Re: PostgreSQL 18 GA press release draft

    Jonathan S. Katz <jkatz@postgresql.org> — 2025-09-12T01:55:07Z

    On 9/10/25 9:52 AM, John Naylor wrote:
> On Wed, Sep 10, 2025 at 10:13 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:
> 
>> PostgreSQL 18 also supports using x86 AVX-512 instructions for CRC32 calculations, which are used in page checksums and are also available in the new [`crc32`](https://www.postgresql.org/docs/18/functions-binarystring.html#FUNCTIONS-BINARYSTRING-OTHER) function.
> 
> Hi Jon,
> 
> The checksum stored in the page header is not actually a CRC but
> something else entirely. WAL records are a convenient example of
> something that uses CRC.
> 
> Also, CRC32 is not hardware accelerated, only CRC32C is. We have two
> SQL-callable functions.  To avoid confusion maybe they can be omitted
> from this longer-than-usual announcement -- they seem like a niche
> feature anyway.

Thanks; I've removed the reference.

Jonathan

  12. Re: PostgreSQL 18 GA press release draft

    Jonathan S. Katz <jkatz@postgresql.org> — 2025-09-12T02:21:42Z

    On 9/10/25 3:59 PM, Peter Geoghegan wrote:

> "It can also automatically transform queries with `OR` constructs in
> their `WHERE` clause into a logically equivalent IN() representation
> that can be pushed down to index scan nodes, leading to significantly
> faster execution".

Thanks for the detailed explanation, I'm proposing:

"It can also optimize queries that use `OR` conditions in a `WHERE` to 
use an index, leading to significantly faster execution."

since the mechanism of the transformation is less important, but the 
outcome is that people can benefit from the previous optimziation 
without having to rewrite their queries.

Jonathan

  13. Re: PostgreSQL 18 GA press release draft

    Peter Geoghegan <pg@bowt.ie> — 2025-09-12T02:25:55Z

    On Thu, Sep 11, 2025 at 10:21 PM Jonathan S. Katz <jkatz@postgresql.org> wrote:
> Thanks for the detailed explanation, I'm proposing:
>
> "It can also optimize queries that use `OR` conditions in a `WHERE` to
> use an index, leading to significantly faster execution."
>
> since the mechanism of the transformation is less important, but the
> outcome is that people can benefit from the previous optimziation
> without having to rewrite their queries.

Sounds good.

Thanks
-- 
Peter Geoghegan

  14. Re: PostgreSQL 18 GA press release draft

    Jonathan S. Katz <jkatz@postgresql.org> — 2025-09-12T15:46:25Z

    On 9/9/25 11:13 PM, Jonathan S. Katz wrote:
> Hi,
> 
> Attached is a draft of the PostgreSQL 18 GA press release. A few 
> comments before the ask for reviewing:
> 
> * I still need to write up the theme and the quote, which I'll provide 
> tomorrow after hearing the first round of feedback
> * The release is a bit long - this is due in part to what I think is a 
> lot of really good features in PostgreSQL 18. "Something for everyone"
> 
> Please review for the press release for the following:
> 
> * Correctness and accuracy of what's described - we want to be 
> technically correct, but we also need to explain why something matters 
> to our users.
> * Notable omissions (apologies if I missed something, but also note not 
> everything going to make the cut).
> * Features to exclude.
> * How easy phrases are to translate / confusing phrases
> * Typos.
> 
> Like previous years, I won't really look at stylistic remarks, unless it 
> falls into the "making it simpler to translate / confusing phrase" bucket.
> 
> After 2025-09-14 0:00 UTC I won't be able to accept more feedback, as 
> we'll need to begin the translation process. Anyone who provides 
> accepted feedback will also get a mention in the press release "thank 
> you" page[1], which stays up for about a year ;)

Thanks for all of your feedback thus far! Attached is the next version 
of the draft. This incorporates the feedback, and now includes the 
thematic statement and a draft quote.

As per above, I'll need remaining feedback no later than 2025-09-14 0:00 
UTC - after this, it's frozen for the translation process to begin.

Thanks!

Jonathan

  15. Re: PostgreSQL 18 GA press release draft

    Jeff Davis <pgsql@j-davis.com> — 2025-09-12T17:11:59Z

    On Tue, 2025-09-09 at 23:13 -0400, Jonathan S. Katz wrote:
> Hi,
> 
> Attached is a draft of the PostgreSQL 18 GA press release. A few 
> comments before the ask for reviewing:

"This release adds the [`PG_UNICODE_FAST`], which accelerates lookups
using the `upper` and `lower` string comparison functions, and helps
speed up the new [`casefold`] function for case-insensitive
comparisons."

Use the link:
https://www.postgresql.org/docs/18/collation.html#COLLATION-MANAGING-STANDARD
which is a bit more descriptive. (Though perhaps that document could be
improved with examples.)

What's actually new with PG_UNICODE_FAST are the "full" Unicode
semantics for case transformations, e.g. it uppercases 'ß' to 'SS'. The
rest is the same as PC_C_UTF8, which uses "simple" Unicode semantics.

The name PG_UNICODE_FAST is meant to convey that it provides full
unicode semantics for case mapping and pattern matching, while also
being fast because it uses memcmp for comparisons. While the name
PG_C_UTF8 is meant to convey that it's closer to what users of the libc
"C.UTF-8" locale might expect.

Regards,
	Jeff Davis

  16. Re: PostgreSQL 18 GA press release draft

    Masahiko Sawada <sawada.mshk@gmail.com> — 2025-09-12T18:18:11Z

    On Fri, Sep 12, 2025 at 8:46 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:
>
> On 9/9/25 11:13 PM, Jonathan S. Katz wrote:
> > Hi,
> >
> > Attached is a draft of the PostgreSQL 18 GA press release. A few
> > comments before the ask for reviewing:
> >
> > * I still need to write up the theme and the quote, which I'll provide
> > tomorrow after hearing the first round of feedback
> > * The release is a bit long - this is due in part to what I think is a
> > lot of really good features in PostgreSQL 18. "Something for everyone"
> >
> > Please review for the press release for the following:
> >
> > * Correctness and accuracy of what's described - we want to be
> > technically correct, but we also need to explain why something matters
> > to our users.
> > * Notable omissions (apologies if I missed something, but also note not
> > everything going to make the cut).
> > * Features to exclude.
> > * How easy phrases are to translate / confusing phrases
> > * Typos.
> >
> > Like previous years, I won't really look at stylistic remarks, unless it
> > falls into the "making it simpler to translate / confusing phrase" bucket.
> >
> > After 2025-09-14 0:00 UTC I won't be able to accept more feedback, as
> > we'll need to begin the translation process. Anyone who provides
> > accepted feedback will also get a mention in the press release "thank
> > you" page[1], which stays up for about a year ;)
>
> Thanks for all of your feedback thus far! Attached is the next version
> of the draft. This incorporates the feedback, and now includes the
> thematic statement and a draft quote.
>
> As per above, I'll need remaining feedback no later than 2025-09-14 0:00
> UTC - after this, it's frozen for the translation process to begin.
>

I found a typo;

s/gen_rand_uuid()/gen_random_uuid()/

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

  17. Re: PostgreSQL 18 GA press release draft

    Nico Williams <nico@cryptonector.com> — 2025-09-12T18:21:06Z

    On Fri, Sep 12, 2025 at 10:11:59AM -0700, Jeff Davis wrote:
> The name PG_UNICODE_FAST is meant to convey that it provides full
> unicode semantics for case mapping and pattern matching, while also
> being fast because it uses memcmp for comparisons. While the name
> PG_C_UTF8 is meant to convey that it's closer to what users of the libc
> "C.UTF-8" locale might expect.

How does one do form-insensitive comparisons?

Nico
--

  18. Re: PostgreSQL 18 GA press release draft

    Jonathan S. Katz <jkatz@postgresql.org> — 2025-09-12T18:59:34Z

    On 9/12/25 2:18 PM, Masahiko Sawada wrote:

> 
> I found a typo;
> 
> s/gen_rand_uuid()/gen_random_uuid()/

Good catch - thanks!

Jonathan

  19. Re: PostgreSQL 18 GA press release draft

    Jeff Davis <pgsql@j-davis.com> — 2025-09-12T19:13:23Z

    On Fri, 2025-09-12 at 13:21 -0500, Nico Williams wrote:
> On Fri, Sep 12, 2025 at 10:11:59AM -0700, Jeff Davis wrote:
> > The name PG_UNICODE_FAST is meant to convey that it provides full
> > unicode semantics for case mapping and pattern matching, while also
> > being fast because it uses memcmp for comparisons. While the name
> > PG_C_UTF8 is meant to convey that it's closer to what users of the
> > libc
> > "C.UTF-8" locale might expect.
> 
> How does one do form-insensitive comparisons?

If you mean case insensitive matching, you can do:

   CASEFOLD(a) = CASEFOLD(b)

in any locale or provider, but it's best when using PG_UNICODE_FAST or
ICU, because it handles some nuances better. For instance:

   CASEFOLD('ß') = CASEFOLD('SS') AND
   CASEFOLD('ß') = CASEFOLD('ss') AND
   CASEFOLD('ß') = CASEFOLD('ẞ')

are all true in PG_UNICODE_FAST and "en-US-x-icu", but not in libc
collations.

ICU also has case-insensitive collations, which offer a bit more
flexibility:

https://www.postgresql.org/docs/current/collation.html#COLLATION-NONDETERMINISTIC

Regards,
	Jeff Davis

  20. Re: PostgreSQL 18 GA press release draft

    Álvaro Herrera <alvherre@kurilemu.de> — 2025-09-12T19:24:40Z

    Hello,

Thanks for putting this together!  It's quite the monster.  I read
through and found the following points worth mentioning:

I think almost all of the non-stock section titles Are Lacking Case
Title.

> The new [`io_method`](https://www.postgresql.org/docs/18/runtime-config-resource.html#GUC-IO-METHOD) setting lets you toggle between the AIO methods, including `worker` and `io_uring` (when built with PostgreSQL, available on certain Linux systems), or you can choose to maintain the current PostgreSQL behavior with the `sync` setting. There are now more parameters to consider tuning with AIO, which you can [learn more about in the documentation](https://www.postgresql.org/docs/18/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-IO).

I don't understand the "when built with PostgreSQL".  Did you mean to
reference something else here?

> PostgreSQL 18 further accelerates query performance with features that automatically make your workloads faster. This release introduces "skip scan" lookups on [multicolumn B-tree indexes](https://www.postgresql.org/docs/18/indexes-multicolumn.html), which improves execution time for queries that omit an `=` condition on one or more prefix index columns. It can also optimize queries that use `OR` conditions in a `WHERE` to use an index, leading to significantly faster execution. There are also numerous improvements for how PostgreSQL plans and executes table joins, from boosting the performance of hash joins to allowing merge joins to use incremental sorts.

introduces "skip scan" lookups ..., which improve
(remove ending 's')

> PostgreSQL 18 now supports parallel builds for [GIN indexes](https://www.postgresql.org/docs/18/gin.html), joining B-tree and [BRIN indexes](https://www.postgresql.org/docs/current/brin.html) in supporting this capability. Additionally, [materialized views](https://www.postgresql.org/docs/18/rules-materializedviews.html) can now use unique indexes that aren't B-trees as partition keys, expanding how you can construct materialized views.

Materialized views can use non-btree indexes as partition keys?  Sounds
cool, but the linked-to matview page doesn't talk about partitioning at
all.  I think there's something wrong with the way this has been
written.  [trawls the release notes]  Hmm, the release notes have this
text:

   "Allow non-btree unique indexes to be used as partition keys and in
   materialized views"

I think the confusion stems from having missed the "and" there.

Actually I wonder if these two items (commits f278e1fe3 and 9d6db8bec)
are actually worthy of being in the press release.  They are about using
unique indexes that aren't btrees, but as I understand, in stock
Postgres there isn't any other way to build unique indexes, so this is
about allowing out-of-core index AMs to be used in these contexts.


> PostgreSQL 18 makes text processing easier and faster with several new enhancements. This release adds the [`PG_UNICODE_FAST`](https://www.postgresql.org/docs/18/locale.html#LOCALE-PROVIDERS), which accelerates lookups using the `upper` and `lower` string comparison functions, and helps speed up the new [`casefold`](https://www.postgresql.org/docs/18/functions-string.html#FUNCTIONS-STRING-OTHER) function for case-insensitive comparisons. Additionally, PostgreSQL 18 now supports making `LIKE` comparisons over text that uses a [nondeterministic collation](https://www.postgresql.org/docs/18/collation.html#COLLATION-NONDETERMINISTIC), simplifying how you can perform more complex pattern matching. This release also changes [full text search](https://www.postgresql.org/docs/18/textsearch.html) to use the default collation provider of a cluster instead of always using libc, which may require you to reindex all [full text search](https://www.postgresql.org/docs/18/textsearch-tables.html#TEXTSEARCH-TABLES-INDEX) and [`pg_trgm`](https://www.postgresql.org/docs/18/pgtrgm.html#PGTRGM-INDEX) indexes after running [`pg_upgrade`](https://www.postgresql.org/docs/18/pgupgrade.html).

I think this should say "This release adds the PG_UNICODE_FAST local
provider", or something like that, because ending in just
"PG_UNICODE_FAST" seems to be unintelligible.

> ### Authentication and security features

In this section I would also mention that pgcrypto gained SHA-2 cipher
for passwords (commit 749a9e20c979).

> PostgreSQL 18 now supports reporting logical replication write conflicts in logs and in the [`pg_stat_subscription_stats`](https://www.postgresql.org/docs/18/monitoring-stats.html#MONITORING-PG-STAT-SUBSCRIPTION-STATS) view. Additionally, [`CREATE SUBSCRIPTION`](https://www.postgresql.org/docs/18/sql-createsubscription.html) now defaults to use parallel streaming for applying transactions, which can help improve performance. The [`pg_createsubscriber`](https://www.postgresql.org/docs/18/app-pgcreatesubscriber.html) now has an `--all` flag so you can create logical replicas for all databases in an instance with a single command. PostgreSQL 18 also lets you automatically [drop idle replication slots](https://www.postgresql.org/docs/18/runtime-config-replication.html#GUC-IDLE-REPLICATION-SLOT-TIMEOUT) to help prevent storing too many write-ahead log files on a publisher.

My English grammar fails me here.  I would say "... now defaults to
using", but maybe your "now defaults to use" is correct.

"The pg_createsubscriber **utility**" ?

> Databases initialized with PostgreSQL 18 [`initdb`](https://www.postgresql.org/docs/18/app-initdb.html) now have page checksums enabled by default. This can affect upgrades from non-checksum enabled clusters, which would require you to create a new PostgreSQL 18 cluster with the `--no-data-checksums` option when using [`pg_upgrade`](https://www.postgresql.org/docs/18/pgupgrade.html).

I'm not sure that the relnotes really need to explain how to use
pg_upgrade.  To me it seems enough to say that initdb now creates
checksum-enabled clusters by default.


Thanks!

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"La fuerza no está en los medios físicos
sino que reside en una voluntad indomable" (Gandhi)

  21. Re: PostgreSQL 18 GA press release draft

    Nico Williams <nico@cryptonector.com> — 2025-09-12T20:48:55Z

    On Fri, Sep 12, 2025 at 12:13:23PM -0700, Jeff Davis wrote:
> On Fri, 2025-09-12 at 13:21 -0500, Nico Williams wrote:
> > How does one do form-insensitive comparisons?
> 
> If you mean case insensitive matching, you can do:

I meant form-insensitive, as in comparing equivalent strings where one
might be using decomposed sequences and another pre-conposed sequences
(and either might be normalized or not at all).

Nico
--

  22. Re: PostgreSQL 18 GA press release draft

    Jeff Davis <pgsql@j-davis.com> — 2025-09-12T21:43:31Z

    On Fri, 2025-09-12 at 15:48 -0500, Nico Williams wrote:
> I meant form-insensitive, as in comparing equivalent strings where
> one
> might be using decomposed sequences and another pre-conposed
> sequences
> (and either might be normalized or not at all).

Use NORMALIZE(a) = NORMALIZE(b).

This is getting a little off-topic from what's actually being released,
so please reopen a more relevant thread or start a new one, and CC me.

Regards,
	Jeff Davis

  23. Re: PostgreSQL 18 GA press release draft

    Jonathan S. Katz <jkatz@postgresql.org> — 2025-09-14T13:41:30Z

    On 9/12/25 1:11 PM, Jeff Davis wrote:
> On Tue, 2025-09-09 at 23:13 -0400, Jonathan S. Katz wrote:
>> Hi,
>>
>> Attached is a draft of the PostgreSQL 18 GA press release. A few
>> comments before the ask for reviewing:
> 
> "This release adds the [`PG_UNICODE_FAST`], which accelerates lookups
> using the `upper` and `lower` string comparison functions, and helps
> speed up the new [`casefold`] function for case-insensitive
> comparisons."
> 
> Use the link:
> https://www.postgresql.org/docs/18/collation.html#COLLATION-MANAGING-STANDARD
> which is a bit more descriptive. (Though perhaps that document could be
> improved with examples.)
> 
> What's actually new with PG_UNICODE_FAST are the "full" Unicode
> semantics for case transformations, e.g. it uppercases 'ß' to 'SS'. The
> rest is the same as PC_C_UTF8, which uses "simple" Unicode semantics.
> 
> The name PG_UNICODE_FAST is meant to convey that it provides full
> unicode semantics for case mapping and pattern matching, while also
> being fast because it uses memcmp for comparisons. While the name
> PG_C_UTF8 is meant to convey that it's closer to what users of the libc
> "C.UTF-8" locale might expect.

I went with breaking this up into two sentences:

This release adds the 
[`PG_UNICODE_FAST`](https://www.postgresql.org/docs/18/collation.html#COLLATION-MANAGING-STANDARD), 
which provides full Unicode semantics for case transformations while 
helping to accelerate many comparisons. This includes the `upper` and 
`lower` string comparison functions and the new 
[`casefold`](https://www.postgresql.org/docs/18/functions-string.html#FUNCTIONS-STRING-OTHER) 
function for case-insensitive comparisons.

Jonathan

  24. Re: PostgreSQL 18 GA press release draft

    Jonathan S. Katz <jkatz@postgresql.org> — 2025-09-14T13:57:54Z

    On 9/12/25 3:24 PM, Álvaro Herrera wrote:
> Hello,
> 
> Thanks for putting this together!  It's quite the monster.  I read
> through and found the following points worth mentioning:

Thank you!

> I think almost all of the non-stock section titles Are Lacking Case
> Title.

Putting this is as an example of "stylistic comment" ;)

>> The new [`io_method`](https://www.postgresql.org/docs/18/runtime-config-resource.html#GUC-IO-METHOD) setting lets you toggle between the AIO methods, including `worker` and `io_uring` (when built with PostgreSQL, available on certain Linux systems), or you can choose to maintain the current PostgreSQL behavior with the `sync` setting. There are now more parameters to consider tuning with AIO, which you can [learn more about in the documentation](https://www.postgresql.org/docs/18/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-IO).
> 
> I don't understand the "when built with PostgreSQL".  Did you mean to
> reference something else here?

I re-read it and realized it doesn't add much value, so I just removed it.

>> PostgreSQL 18 further accelerates query performance with features that automatically make your workloads faster. This release introduces "skip scan" lookups on [multicolumn B-tree indexes](https://www.postgresql.org/docs/18/indexes-multicolumn.html), which improves execution time for queries that omit an `=` condition on one or more prefix index columns. It can also optimize queries that use `OR` conditions in a `WHERE` to use an index, leading to significantly faster execution. There are also numerous improvements for how PostgreSQL plans and executes table joins, from boosting the performance of hash joins to allowing merge joins to use incremental sorts.
> 
> introduces "skip scan" lookups ..., which improve
> (remove ending 's')

Good catch - I revised and s/, which/that/

> Actually I wonder if these two items (commits f278e1fe3 and 9d6db8bec)
> are actually worthy of being in the press release.  They are about using
> unique indexes that aren't btrees, but as I understand, in stock
> Postgres there isn't any other way to build unique indexes, so this is
> about allowing out-of-core index AMs to be used in these contexts.

Thanks for the analysis on this one. Upon reflection, I'm going to 
remove this item from the press release. I'll move the parallel GIN 
builds to the first paragraph.

> I think this should say "This release adds the PG_UNICODE_FAST local
> provider", or something like that, because ending in just
> "PG_UNICODE_FAST" seems to be unintelligible.

Heh, should have added "collation"; fixed.

>> ### Authentication and security features
> 
> In this section I would also mention that pgcrypto gained SHA-2 cipher
> for passwords (commit 749a9e20c979).

Cool - added:

Additionally, 
[`pgcrypto`](https://www.postgresql.org/docs/18/pgcrypto.html) now 
supports [SHA-2 encryption for password 
hashing](https://www.postgresql.org/docs/18/pgcrypto.html#PGCRYPTO-CRYPT-ALGORITHMS).

> My English grammar fails me here.  I would say "... now defaults to
> using", but maybe your "now defaults to use" is correct.

I'm OK to use "using" if it's simpler for translating.

> "The pg_createsubscriber **utility**" ?

Thanks; I thought I had that in originally.

>> Databases initialized with PostgreSQL 18 [`initdb`](https://www.postgresql.org/docs/18/app-initdb.html) now have page checksums enabled by default. This can affect upgrades from non-checksum enabled clusters, which would require you to create a new PostgreSQL 18 cluster with the `--no-data-checksums` option when using [`pg_upgrade`](https://www.postgresql.org/docs/18/pgupgrade.html).
> 
> I'm not sure that the relnotes really need to explain how to use
> pg_upgrade.  To me it seems enough to say that initdb now creates
> checksum-enabled clusters by default.

I included it because I think this is a potential tripwire. I don't know 
what the universe of non-checksum enabled clusters is out there, but I 
expect that it's pretty large. I think this is helpful for folks who are 
less familiar with PostgreSQL operational and upgrade mechanics (after 
all, they may only upgrade once a year at best) so it's a little simple 
to discover. Plus, this will help to surface the method as something to 
note through whatever AI summarizing techniques people are using.

Thanks!

Jonathan

  25. Re: PostgreSQL 18 GA press release draft

    Jeff Davis <pgsql@j-davis.com> — 2025-09-14T17:08:49Z

    On Sun, 2025-09-14 at 09:41 -0400, Jonathan S. Katz wrote:
> I went with breaking this up into two sentences:
> 
> This release adds the 
> [`PG_UNICODE_FAST`](
> https://www.postgresql.org/docs/18/collation.html#COLLATION-MANAGING-
> STANDARD), 
> which provides full Unicode semantics for case transformations while 
> helping to accelerate many comparisons. This includes the `upper` and
> `lower` string comparison functions and the new 
> [`casefold`](
> https://www.postgresql.org/docs/18/functions-string.html#FUNCTIONS-
> STRING-OTHER) 
> function for case-insensitive comparisons.

Perfect, thank you.

Regards,
	Jeff Davis

  26. Re: PostgreSQL 18 GA press release draft

    Jonathan S. Katz <jkatz@postgresql.org> — 2025-09-14T20:04:47Z

    On 9/12/25 11:46 AM, Jonathan S. Katz wrote:

> As per above, I'll need remaining feedback no later than 2025-09-14 0:00 
> UTC - after this, it's frozen for the translation process to begin.

Thank everyone for your feedback. I've attached the copy of the release 
announcement that will be translated and distributed. This is now frozen.

Thanks!

Jonathan