PostgreSQL 17 gains built-in collation
postgresql.org
postgresql.org
For those wanting to save a click, here are the benefits listed in the linked article:
The builtin provider's C.UTF-8 offers several important advantages
over libc:
* faster sorting -- benefits from additional optimizations such as
abbreviated keys and varstrfastcmp_c
* faster case conversion, e.g. LOWER(), at least compared with some
libc implementations
* available on all platforms with identical semantics, and the
semantics are stable, testable, and documentable within a given
Postgres major version
Being based on memcmp, the builtin C.UTF-8 locale does not offer
natural language sort order. But it is an improvement for most use
cases that might otherwise use libc's "C.UTF-8" locale, as well as
many use cases that use libc's "C" locale.
* platform-independent ctype semantics based on Unicode, not tied to
any dependency's implementation
* ability to combine fast memcmp() collation with rich ctype
semantics
* user-visible semantics can be documented and tested
* stability within a PG major version
* transparency of changes: tables would be checked in to .h files,
so whoever runs the "update-unicode" build target would see if
there are unexpected or impactful changes that should be addressed
in the release notes
* the built-in tables themselves can be tested exhaustively by
comparing with ICU so we can detect trivial parsing errors and the
likeThe bigger deal in my view is that it provides at least an option for a Postgres project controlled, built-in, immutable collation. Unlike glibc or ICU which are historically the only options (mostly glibc), and both of which are known to change their collation ordering from time to time completely outside control of the Postgres developers. If you want a deeper dive on why this is an issue, please watch the presentation I gave at pgcon last year found linked here: https://www.pgcon.org/events/pgcon_2023/schedule/session/345...
The platform indepenence alone would be worth it, I think, irrespective of any perf gains!
https://www.postgresql.org/message-id/flat/ff4c2f2f9c8fc7ca2...
""" v15:Performance:
Sotring 10M strings:
libc "C" 14s
builtin C_UTF8 14s
libc "C.utf8" 20s
ICU "en-US-x-icu" 31s
Running UPPER() on 10M strings:
libc "C" 03s
builtin C_UTF8 07s
libc "C.utf8" 08s
ICU "en-US-x-icu" 15s""" v20Performance:
ASCII-only data:
lower initcap upper
"C" (libc) 2426 3326 2341
pg_c_utf8 2890 6570 2825
pg_unicode_fast 2929 7140 2893
"C.utf8" (libc) 5410 7810 5397
"en-US-x-icu" 8320 65732 9367
Including non-ASCII data:
lower initcap upper
"C" (libc) 2630 4677 2548
pg_c_utf8 5471 10682 5431
pg_unicode_fast 5582 12023 5587
"C.utf8" (libc) 8126 11834 8106
"en-US-x-icu" 14473 73655 15112Then again, I've been working with databases long enough to know that there's really no such thing as a "typical query" so now that I think about it, my question was a bit pointless or at least unanswerable.