Collations from A to Z
summary
Runs a COI torture test of about 26 million strings (91 patterns over 10 years of Ubuntu and RHEL) and finds collation changes in every major release except Ubuntu 24.04, with past changes causing index corruption, WAL-replay crashes and even invisible row loss; mitigation often requires rebuild indexes (but not on hot standbys). Shows options: ICU root collations, ICU numeric and case-insensitive flags, and a new C.UTF8 built-in provider (Postgres 17) that’s stable and far faster than libc or linguistic collations, which can be ~5x slower.
AI-written summary · about
chapters (16)
- 0:00Introduction
- 0:12Historical overview
- 1:15Collation myths and truths
- 5:15Testing for changes
- 8:02Risks of changing locales
- 10:38Unicode and libc challenges
- 13:21Workarounds and stability
- 16:00Posix local sorting
- 20:29Database default behaviors
- 23:53Collation options in Postgres
- 29:28Version 17 improvements
- 30:43ICU root collation
- 33:09Implementation examples
- 36:53Advanced ICU features
- 41:41Future work and summary
- 46:11Q&A session
related talks
29mLocalization on PostgreSQL
55mSorting Out glibc Collation Challenges
46mCollation Challenges: Sorting it Out
45mThe wonderful world of collations, encodings and text storage
47mSmooth Sailing...
15mCharacter set and collation basics
30mFix your Strings in PostgreSQL
01h 02mThere's No Such Thing as Plain Text