Sorting Out Collations in PostgreSQL: Implementation, Upgrades, Survival Strategies

*Josef Machytka: Speaker portfolio*

A collation defines how a database orders and compares strings. Postgres does not implement its own string‑ordering rules; instead it delegates to external libraries — the operating system’s C library (libc) or the International Components for Unicode (ICU). Collations influence ordering in ORDER BY, B‑tree indexes, unique constraints, partition boundaries and even pattern matching. If the underlying library changes its collation rules (e.g., after a glibc or ICU upgrade), the cached ordering stored in indexes may become inconsistent, leading to wrong query results. This talk summarizes all relevant information about this problem from latest articles and conferences talks and shows how to deal with this issue. It also dives into PostgreSQL code explaining collations implementation details.

Key takeaways

• Collations are defined in Unicode Common Locale Data Repository (CLDR)
• Collations determine ordering — they are external to Postgres and implemented by libc or ICU
• Changing the underlying library changes how strings are sorted and compared
• New build-in collation introduced in PostgreSQL 17 do not solve language related issues

Presented at & Slides

• credativ Tech Talk (slides on GitHub)

Recording

[embed]

Image created by DeepDreamGenerator