From: Jeff Davis <pgsql@j-davis.com>
To: Nico Williams <nico@cryptonector.com>
Cc: Alexander Borisov <lex.borisov@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Improve the performance of Unicode Normalization Forms.
Date: Fri, 20 Jun 2025 10:15:47 -0700
Message-ID: <792ee592556431aaa2ad1570a978574f53e5be7a.camel@j-davis.com> (raw)
In-Reply-To: <aFWM9coWT9JUJ8m3@ubby>
References: <844d3dd7-2955-4794-95d1-7f4c13cb89fc@gmail.com>
<4211ffd7fe154c4af693b98d78f4a3689ce8cc30.camel@j-davis.com>
<aFWM9coWT9JUJ8m3@ubby>
On Fri, 2025-06-20 at 11:31 -0500, Nico Williams wrote:
> In the slow path you only
> normalize the _current character_, so you only need enough buffer
> space
> for that.
That's a clear win for UTF8 data. Also, if there are no changes, then
you can just return the input buffer and not bother allocating an
output buffer.
> The really nice thing about form-insensitive/form-preserving
> functionality is that it is form-preserving rather than normalizing
> on
> create and lookup, and that makes the fast-path described above
> feasible. Whereas if you normalize on create and lookup you have to
> heap allocate enough space for each string normalized.
Non-deterministic ICU collations are already insensitive to most
normalization differences. Some differences are not handled when it
involves too many combining marks, but you can use the "full
normalization" option to address that. See:
https://www.postgresql.org/docs/current/collation.html#ICU-COLLATION-SETTINGS-TABLE> The other nice
> thing is that f-i/f-p behavior is a lot less surprising to users --
> the
> input methods they use don't matter.
Postgres is already form-preserving; it does not auto-normalize. (I
have suggested that we might want to offer something like that, but
that would be a user choice.)
Currently, the non-deterministic collations (which offer form-
insensitivity) are not available at the database level, so you have to
explicitly specify the COLLATE clause on a column or query. In other
words, Postgres is not form-insensitive by default, though there is
work to make that possible.
> What motivated this f-i/f-p behavior was that HFS+ used NFD (well,
> something very close to NFD) but input methods (even on OS X)
> invariably
> produce NFC (well, something close to it), at least for Latin
> scripts.
> This means that on OS X if you use filenames from directory listings
> those will be NFD while user inputs will be NFC, and so you can't
> just
> memcmp()/strcmp() those -- you have to normalize _or_ use form-
> insensitive string comparison, but nothing did that 20 years ago.
> Thus
> doing the form-insensitivity in the filesystem seemed best, and if
> you
> do that you can be form-preserving to enable the optimization
> described
> above.
Databases have similar concerns as a filesystem in this respect.
Regards,
Jeff Davis
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pgsql@j-davis.com, nico@cryptonector.com, lex.borisov@gmail.com
Subject: Re: Improve the performance of Unicode Normalization Forms.
In-Reply-To: <792ee592556431aaa2ad1570a978574f53e5be7a.camel@j-davis.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox