PostgreSQL's default collation comes from the environment where you run initdb, which usually means a natural language collation supplied by the C library or ICU. When an operating system upgrade brings new versions of those libraries with changed comparison rules, indexes on string expressions can end up ordered incorrectly — index corruption. Rebuilding the affected indexes is the only cure.
Why collation can break across upgrades
Two design decisions explain the exposure. First, most other database systems ship their own collation implementations and can therefore keep the rules frozen; PostgreSQL chose an existing implementation instead when commit 5b1311acfb added locale support in 1997, trading stability for saved effort. Second, many other systems default to the C collation, which is immune to the problem. PostgreSQL inherits the locale from the shell that runs initdb, so clusters commonly end up with a natural language collation whether or not that was intended.
C versus natural language collations
The C collation compares strings byte by byte and orders them by numerical value, so a single memcmp() settles the order for two strings of equal length. Ordering therefore depends only on the character encoding, and encodings never reassign existing code points — although Unicode adds new ones — so the order never changes. Comparison is also considerably cheaper than with natural language rules.
Natural language collations are necessarily more involved, because their rules go far beyond “A” < “B”. Special characters raise questions: is “Ö” equal to or greater than “O”, or equal to “OE”? Is “côte” less than “coté”? In Czech, “c” < “h” < “ch”. No two languages share the same rules.
Complex rules mean implementations have bugs that need fixing — fine for a library, dangerous for a relational database whose indexes assume a fixed order. For background on the mechanics, Peter Eisentraut's articles are useful: How collation works, Overview of ICU collation settings and part 2.
The price of C ordering
C collation sorts nothing like a human expects. PostgreSQL supports only encodings that are supersets of ASCII, and in ASCII every uppercase letter precedes every lowercase one, so “Xavier” < “blue”. Non-ASCII characters sort above all ASCII characters, which frustrates anyone hunting for “Österreich” in a dropdown list.
This does not force you to accept ASCII ordering everywhere. A column can carry its own collation:
PgSQL
|
1 2 3 4 5 |
CREATE TABLE tab ( id bigint PRIMARY KEY, name text COLLATE "de-AT-x-icu" NOT NULL, ... ); |
An ORDER BY name then uses the Austrian ICU collation automatically, and any index built on name picks up the column's collation too. Alternatively, name the collation in the query:
PgSQL
|
1 2 |
SELECT ... FROM tab ORDER BY name COLLATE "it-VA-x-icu", birthday; |
To support such a query with an index, build the index to match the ORDER BY clause exactly:
PgSQL
|
1 |
CREATE INDEX ON tab (name COLLATE "it-VA-x-icu", birthday); |
What you gain
Only some columns ever appear in ORDER BY, and others hold strings for which C ordering is perfectly adequate — all-uppercase ASCII, for instance. Restricting natural language collations to the columns that genuinely need them shrinks the set of indexes that an operating system upgrade can corrupt; in many cases you check a handful and rebuild none. Comparisons on the remaining C columns stay cheaper as well.
The builtin provider
PostgreSQL v17 gave the server its own collation provider, the first step toward depending less on outside libraries. It shipped with the collations C and C.UTF-8; v18 added PG_UNICODE_FAST. None offers natural language support. The two later collations rely on Unicode for matters such as case conversion, and those can shift when PostgreSQL moves to a newer Unicode version. That is unlikely to affect index definitions, but C is the fully safe choice — and it makes little sense to lean on an external library for a collation you chose precisely for its simplicity.
Creating clusters and databases
To get the builtin C collation, invoke initdb with options along these lines:
Shell
|
1 2 3 4 5 6 |
initdb \ --encoding=UTF8 \ --locale-provider=builtin \ --builtin-locale=C \ --locale=C \ /data/directory |
A database can also be created with the builtin C collation inside a cluster that was initialized differently:
PgSQL
|
1 2 3 4 5 |
CREATE DATABASE newdb TEMPLATE template0 LOCALE_PROVIDER builtin BUILTIN_LOCALE "C" LOCALE "C"; |
When the locale setting differs from the cluster default, template0 must be the template.
Recommendation
C collation is stable and fast; its ordering simply does not suit natural languages. Use it to create your clusters and databases anyway, and fall back to another collation per column or in the ORDER BY clause where the ordering matters. Fewer indexes can then be corrupted by an operating system upgrade. On PostgreSQL v17 and later, take the C collation from the builtin provider to cut back on external dependencies.
