4 ms·

PostgreSQL 17 gains built-in collation

39 points by josepheconway 3y ago

josepheconway 3y 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 3y ago
  Removing the inconsistency across different operating systems, I guess?
  - nwellnhof 3y ago
    There can even be inconsistencies after glibc upgrades.
    - bzzzt 3y ago
      Yes, you can run into that kind of stuff when configuring stuff like accent insensitive search. Most people just wrap the unstable function in a 'stable' wrapper which means you will have to reindex after an incompatible glibc update.
- JohnBooty 3y 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...
  - josepheconway 3y 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-sorting-out-glibc-collation-challenges/ https://www.pgcon.org/events/pgcon_2023/schedule/session/345...
    - JohnBooty 3y ago
      Thanks very much! I will watch that as soon as possible. The platform indepenence alone would be worth it, I think, irrespective of any perf gains!
  - pella 3y ago
    from the linked discussion: https://www.postgresql.org/message-id/flat/ff4c2f2f9c8fc7ca27c1c24ae37ecaeaeaff6b53.camel%40j-davis.com 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 """
    - JohnBooty 3y ago
      Thanks! I guess my question was more about typical query performance moreso than isolated benchmarks focused slowly on sorting. Then 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.
deleted 3y ago
[deleted]
JohnBooty 3y 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%40gemulon.postgresql.org 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

39 points by josepheconway 3y 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 3y ago
Removing the inconsistency across different operating systems, I guess?
- nwellnhof 3y ago
  There can even be inconsistencies after glibc upgrades.
  - bzzzt 3y ago
    Yes, you can run into that kind of stuff when configuring stuff like accent insensitive search. Most people just wrap the unstable function in a 'stable' wrapper which means you will have to reindex after an incompatible glibc update.
JohnBooty 3y 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...
- josepheconway 3y 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-sorting-out-glibc-collation-challenges/ https://www.pgcon.org/events/pgcon_2023/schedule/session/345...
  - JohnBooty 3y ago
    Thanks very much! I will watch that as soon as possible. The platform indepenence alone would be worth it, I think, irrespective of any perf gains!
- pella 3y ago
  from the linked discussion: https://www.postgresql.org/message-id/flat/ff4c2f2f9c8fc7ca27c1c24ae37ecaeaeaff6b53.camel%40j-davis.com 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 """
  - JohnBooty 3y ago
    Thanks! I guess my question was more about typical query performance moreso than isolated benchmarks focused slowly on sorting. Then 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.

deleted 3y ago

[deleted]

JohnBooty 3y 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%40gemulon.postgresql.org 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