PostgreSQL 17 gains built-in collation

https://www.postgresql.org/message-id/E1rmhxt-004ezT-OB%40gemulon.postgresql.org

by josepheconway • 2 years ago

39 10 2 years ago

Built-in collation provider committed to PostgreSQL 17 (still in development) complements of Jeff Davis. This is a huge step forward for PostgreSQL users! If you don't understand why, you should research it.

Kognito• 2 years ago

Removing the inconsistency across different operating systems, I guess?

nwellnhof• 2 years ago

There can even be inconsistencies after glibc upgrades.

JohnBooty4• 2 years ago

I've read the linked article and the proposal, and this seems exciting but I'm interested to know more about the real world impact i.e. what kinds of perf gains might be seen in real world use cases. If you're deeply versed in this maybe you could shine a light on it for the rest of us...

josepheconway2• 2 years ago

I have not tried to measure the performance impact, although I have no doubt that it exists and is significant. Basically it boils down to the difference in performance of doing memcmp() versus strcoll() for every comparison needed to sort your strings.

The 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...

pella• 2 years ago

from the linked discussion:

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    15112

"""

JohnBooty4• 2 years ago

This is interesting. I've been working with databases for ages but hadn't really thought much about collation other than times (mostly with MySQL) when collation didn't work as expected. Had never thought about it in terms of being a perf issue.

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.

Here are the benefits mentioned in the original proposal; see it for much more detail: https://www.postgresql.org/message-id/E1rmhxt-004ezT-OB%40ge...

      * 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
        like

PostgreSQL 17 gains built-in collation

Related Stories