If a unique index suddenly tolerates duplicates, or WHERE email = ... misses a row that a sequential scan finds, the index is sorted by collation rules that no longer match what your OS provides. That happens when the C library's sort order changes — most famously in glibc 2.28 — and it is not a Postgres bug, a replication bug, or a bad disk. The fix is to rebuild every index on a text column, then tell Postgres which collation version it is now running against.
I hit this after the most boring change imaginable: bumping a Docker base image from Debian 10 to Debian 12 for a staging database. The data directory was on a mounted volume, so it survived. The sort order underneath it did not.
What the failure actually looks like
The symptoms are inconsistent enough that you distrust your own code first, which is why this costs people days:
| Symptom | What's really happening |
|---|---|
INSERT succeeds on a value that already exists in a UNIQUE column |
The index probe walked the b-tree using new sort rules and never reached the existing entry |
SELECT ... WHERE name = 'x' returns nothing, but SELECT ... WHERE name LIKE 'x' returns a row |
The first uses the index, the second forces a scan |
ORDER BY name returns a different order than it did last week |
The comparison function itself changed |
count(*) differs depending on whether a filtered index is used |
Index-only scans see a subset |
| On Postgres 15+, every connection logs a collation version mismatch warning | Postgres is telling you directly |
That last row is the gift. Since Postgres 15, connecting to a database whose recorded collation version doesn't match the OS produces:
WARNING: database "app" has a collation version mismatch
DETAIL: The database was created using collation version 2.28,
but the operating system provides version 2.36.
HINT: Rebuild all objects in this database that use the default
collation and run ALTER DATABASE app REFRESH COLLATION VERSION.
Collation version tracking for libc collations landed in Postgres 13 at the collation level, and Postgres 15 extended it to the database default. On older majors you get no warning at all — just wrong answers.
Takeaway: a unique constraint that stops being unique without any schema change is a sort-order problem, not a concurrency problem.
Why does changing glibc break a Postgres index?
A b-tree index is a sorted structure, and "sorted" is defined by the collation attached to the column. For the default en_US.UTF-8-style locales on Linux, Postgres does not implement that ordering itself — it calls out to glibc's strcoll/strxfrm.
glibc 2.28 (RHEL 8, Debian 10, Ubuntu 18.10) rewrote its locale data to follow ISO 14651, changing the relative order of many strings containing punctuation, spaces, and non-ASCII characters. A row inserted under the old rules sits at a b-tree position a lookup under the new rules never visits. The index isn't torn or physically damaged; it is perfectly consistent with rules that no longer exist on the machine.
Nothing in the normal upgrade path rebuilds those indexes for you. pg_upgrade relinks the data files, a physical replica streams byte-level changes, and a restored snapshot copies pages verbatim — all three happily carry a b-tree sorted by glibc 2.27 onto a host running glibc 2.36. The one safe path is logical: pg_dump plus restore rebuilds every index under the new rules, which is why some teams never meet this problem until they adopt physical replication or volume mounts.
Takeaway: anything that copies index pages instead of rebuilding them can carry a stale sort order across an OS boundary.
How do I confirm it's a collation problem and not a bug in my app?
Three checks, fastest first. Start by asking Postgres whether it already knows:
SELECT datname, datcollate, datctype, datcollversion
FROM pg_database
WHERE datname = current_database();
SELECT collname,
collversion,
pg_collation_actual_version(oid) AS actual_version
FROM pg_collation
WHERE collversion IS NOT NULL
AND collversion IS DISTINCT FROM pg_collation_actual_version(oid);
Any row in that second result is a collation whose recorded version disagrees with the OS. On Postgres 13 and 14 that query is the only signal you get.
Next, catch the index lying to you — force a sequential scan and compare:
-- indexed path
SELECT count(*) FROM users WHERE email = 'ana.lópez@example.com';
SET enable_indexscan = off;
SET enable_bitmapscan = off;
-- same predicate, no index
SELECT count(*) FROM users WHERE email = 'ana.lópez@example.com';
RESET enable_indexscan;
RESET enable_bitmapscan;
Two different counts for the same predicate in the same session is conclusive. No application bug produces that.
Finally, verify the structure with amcheck, which ships in contrib:
CREATE EXTENSION IF NOT EXISTS amcheck;
SELECT bt_index_check(index => c.oid, heapallindexed => true)
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relam = (SELECT oid FROM pg_am WHERE amname = 'btree')
AND c.relkind = 'i'
AND n.nspname = 'public';
With heapallindexed => true this checks that every heap tuple is actually findable in the index — exactly the property that breaks here. It reads the whole index and takes locks, so run it on a replica or in a quiet window. bt_index_parent_check is stricter but takes a ShareLock that blocks writes.
Takeaway: if a sequential scan and an index scan disagree on count(*), stop debugging your ORM.
How do I fix it without taking the database down?
The repair is a rebuild, and the order matters — refreshing the version first just silences the warning.
-- 1. Rebuild. CONCURRENTLY avoids blocking writes (Postgres 12+).
REINDEX INDEX CONCURRENTLY users_email_key;
-- or, for everything:
REINDEX DATABASE CONCURRENTLY app;
-- 2. Only after the rebuild, record the new version.
ALTER DATABASE app REFRESH COLLATION VERSION;
ALTER COLLATION "en_US.utf8" REFRESH VERSION;
Two things tripped me up. REINDEX ... CONCURRENTLY cannot rebuild a unique index that already contains duplicates — it fails at validation, which is correct behavior and also how you find out how much bad data got in. Find them first with GROUP BY ... HAVING count(*) > 1 (an aggregate over a scan, so it sees everything) and resolve them before rebuilding. A failed concurrent reindex also leaves an invalid index behind with a _ccnew suffix; check pg_index.indisvalid afterward and drop the leftovers.
If you run a physical replica on the new OS, its indexes are the same bad pages — but reindexing on the primary fixes both, because the rebuild is WAL-logged.
Takeaway: reindex first, refresh the version second — the reverse order hides the problem instead of fixing it.
Which collation should you run so this never happens again?
You have three real options, and the tradeoff is between linguistic correctness and version stability.
| Provider | Sort quality | Version stability | Cost |
|---|---|---|---|
libc (en_US.UTF-8) |
Locale-correct | Tied to the host's glibc; changes without warning on OS upgrade | Default everywhere, so it's what you already have |
| ICU | Locale-correct, more consistent across platforms | Versioned explicitly; Postgres records the ICU version and warns on mismatch | Requires an ICU-enabled build; ICU major upgrades still require a reindex |
C / C.UTF-8
|
Byte order only — no case folding, accents sort after z
|
Stable by definition | Wrong user-facing sort for anything non-English |
For most application databases the honest answer is: use C for columns where sort order is an implementation detail — slugs, emails, API tokens, enum-ish text — and ICU on the handful of columns a human reads in sorted order. This is settable per column:
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text COLLATE "C" NOT NULL UNIQUE,
display_name text COLLATE "en-US-x-icu" NOT NULL
);
C has an underrated second benefit: it makes LIKE 'prefix%' usable by a plain b-tree index without text_pattern_ops. It also has a real drawback — under C, ORDER BY puts Zoë before apple, and no amount of application-side sorting fixes that at scale.
For a whole-database default, Postgres 15 and later let you choose the collation provider at database creation time, and Postgres 17 added a built-in provider offering C and C.UTF-8 implemented inside Postgres itself — as of late 2026 the cleanest option if you're starting fresh and don't need linguistic sorting, since it drops the external library dependency entirely.
If you'd rather not own this class of problem, a managed service is a fair trade: Amazon RDS and Google Cloud SQL both do the underlying OS maintenance on announced windows rather than whenever someone edits a Dockerfile. It does not make reindexing unnecessary — the collation version can still move during a major version upgrade — it just means you hear about it from a maintenance notice instead of from a duplicate row.
Takeaway: pick C for machine-readable text columns and reserve locale-aware collation for text humans sort by eye.
Where this bites you even if you never upgrade the OS
Four paths that change glibc without feeling like an upgrade:
- Rebuilding a container image when the base tag moves (
postgres:15today is not built on the Debian release it was two years ago). - Promoting a physical replica that was provisioned on a newer host — the sort order changes at failover.
- Restoring a filesystem or EBS snapshot onto a fresh AMI.
- Moving between Alpine (musl) and Debian (glibc), which swaps the collation implementation, not just its version.
The cheap guard is a startup check: compare datcollversion against pg_collation_actual_version in your migration runner or deep health check, and fail loudly instead of serving wrong rows.
Takeaway: the trigger is rarely "we upgraded the OS" — it's usually "we rebuilt the image."
FAQ
Does REINDEX fix a collation version mismatch?
Yes — rebuilding re-sorts the index using the current OS collation rules. After reindexing, run ALTER DATABASE <name> REFRESH COLLATION VERSION to clear the warning. Refreshing the version without reindexing clears the warning but leaves the broken indexes in place.
Can a glibc upgrade cause duplicate rows in a Postgres unique index?
Yes. The uniqueness check is an index lookup, so if the new sort order sends the probe down a different branch of the b-tree, Postgres never sees the existing row and accepts the insert. Those duplicates must be resolved before a concurrent reindex will succeed.
Is C collation safe for a Postgres primary key or email column?
Yes, and it is usually the better choice there. C compares bytes, so its ordering never changes with the OS, and it lets LIKE 'prefix%' use a standard b-tree index. The tradeoff is that it sorts uppercase before lowercase and places accented characters after ASCII, so avoid it on columns users see sorted.
Bottom line
If a unique index stops catching duplicates or lookups start missing rows right after an OS, AMI, or base-image change, assume collation before you assume corruption. Confirm by comparing an index scan against a forced sequential scan, then REINDEX ... CONCURRENTLY every index on a text column and refresh the collation version — in that order. For new schemas, put C on machine-readable text and ICU on human-sorted text and this failure mode largely goes away. Postgres 15 and later warn you for free; on older versions, add a collation version check to your deploy pipeline, because nothing else will.
Top comments (0)