Collations in PostgreSQL: libc, ICU and the builtin provider, and why a library upgrade can corrupt an index

What it is

The documentation defines a collation as an SQL object that maps a name to locale data from a provider: libc (the operating system's locales, tied to the database encoding), icu (the ICU library, with names as BCP 47 tags such as de-x-icu, independent of the encoding) or builtin (only C, C.UTF-8 and PG_UNICODE_FAST, which sort by code point). The database's LC_COLLATE and LC_CTYPE are fixed at creation and cannot be changed except by creating a new database. A column, index or expression can carry its own collation (COLLATE "de-x-icu"), and CREATE COLLATION defines custom ones, including nondeterministic collations (deterministic = false) for case- or accent-insensitive comparison.

Why it matters

First, language order is not code-point order: ORDER BY name and range conditions on text depend on the collation, and an index serves a sort only if its collation matches. Second, a B-tree stores keys in collation order; when the operating system's C library or ICU changes its rules, existing indexes no longer agree with the new comparison, and the ALTER COLLATION documentation warns that this can lead to corrupt indexes. PostgreSQL records a collation version and warns on mismatch; the remedy is REINDEX of the affected objects followed by ALTER COLLATION ... REFRESH VERSION.

How to apply

• Choose the default collation when creating the cluster or database. C or the builtin C.UTF-8 suits identifiers, keys and machine data: the documentation describes C as stable across all versions for a given encoding and C.UTF-8 as stable within a major version. Use a language collation only where users read sorted text.
• Prefer ICU to libc for language collations where both are available: the locale chapter describes ICU behaviour as independent of the operating system and database encoding.
• Put user-facing order on the column or the query (ORDER BY title COLLATE "de-x-icu") and create an index with the same collation if that sort must be fast.
• Use a nondeterministic collation for case-insensitive uniqueness where its documented limits (slower comparison, no B-tree deduplication, some pattern matching unavailable) are acceptable.
• After an OS upgrade, an ICU upgrade or pg_upgrade onto newer libraries, list mismatches with SELECT collname FROM pg_collation WHERE collversion <> pg_collation_actual_version(oid), reindex, then refresh the versions; the database default collation is refreshed separately with ALTER DATABASE ... REFRESH COLLATION VERSION.

Pitfalls

A replica built from a different OS image can carry a different C library and therefore a different order for the same index. Under the C collation only the ASCII letters A to Z count as letters, so upper() and lower() leave other letters unchanged. REFRESH VERSION records the new version without checking that the objects were rebuilt.