News for hackers. Stuff that matters.
Re:PostgreSQL 17 gains built-in collation by JohnBooty on Wednesday March 20, 2024 @11:28AM (#39767974)
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...
Re:PostgreSQL 17 gains built-in collation by josepheconway on Wednesday March 20, 2024 @12:30PM (#39768845)
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...

Re:PostgreSQL 17 gains built-in collation by JohnBooty on Wednesday March 20, 2024 @10:47PM (#39774464)
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!

Re:PostgreSQL 17 gains built-in collation by pella on Wednesday March 20, 2024 @03:16PM (#39770820)
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
"""
Re:PostgreSQL 17 gains built-in collation by JohnBooty on Wednesday March 20, 2024 @10:48PM (#39774476)
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.