Collation Version Changes Quietly Corrupt PostgreSQL Text Indexes

Two weeks after a routine fleet-wide OS upgrade, one of our services started throwing duplicate-key errors on a slug column. The insert claimed the value existed; the SELECT the on-call engineer ran said it did not. Both were telling the truth, and the gap between those two truths cost us a day. The OS upgrade had shipped a newer glibc, the newer glibc had changed sort rules for our locale, and every btree index on every text column was now quietly wrong — entries physically ordered by the old rules, probes performed with the new ones. Nothing logs an error. The index just starts lying.

It is the least visible kind of PostgreSQL corruption I know: no checksum failures, no PANIC, no replication lag, just wrong answers in edge cases that grow over time as the index drifts further from the library's opinion. Here is how it happens, how to find it, and the exact order of operations to fix it without taking the system down.

How a collation change corrupts a btree without touching it

A btree index on a text column stores keys sorted by the collation's comparison function. glibc's strcoll and ICU's collator are data-driven: the rules live in locale definition files that change between library releases, and they do change — reorderings, new weight assignments, different punctuation handling. PostgreSQL does not re-sort anything when the library changes; it keeps inserting and probing with whatever the current library says. The instant the comparison function disagrees with the physical order on disk, the index is inconsistent. Descending the tree with the new function can land you in a subtree that does not contain the key you are after. A SELECT misses a row that a sequential scan finds. An insert's uniqueness check descends to the wrong leaf, concludes the value is absent, and either inserts a real duplicate that index scans then hide — or, on a different path, reports a duplicate for a value no query can return. That was our incident: the phantom duplicate key, reported by an index that had simply lost track of its own contents.

The warning in your logs: pg_collation and collversion

PostgreSQL records a collversion for each collation in pg_collation — the library's version stamp captured when the collation was defined or refreshed. When the running library reports a different version, PostgreSQL logs a warning the first time the collation is used: collation has version mismatch. It is a warning, not an error, and it drowns in log volume fast. The coverage arrived in stages: ICU collations have carried version tracking since PostgreSQL 10, libc collations joined in 13 on glibc and Windows, and FreeBSD followed in 14 — which is why this failure was almost invisible for years. On anything older, or on a platform where the library reports no version at all, the corruption is silent end to end, and the queries below are your only tripwire.

-- Which collations does this cluster track versions for?
SELECT collname, collprovider, collversion
FROM pg_collation
WHERE collversion IS NOT NULL;

-- Which user indexes actually depend on non-C collations?
SELECT n.nspname AS schema, t.relname AS table_name, i.relname AS index_name,
       pg_get_indexdef(i.oid) AS def
FROM pg_index x
JOIN pg_class i ON i.oid = x.indexrelid
JOIN pg_class t ON t.oid = x.indrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
JOIN pg_collation c ON c.oid = ANY (x.indcollation)
WHERE c.collname NOT IN ('C', 'POSIX')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(i.oid) DESC;

That second query is the triage list. Every index it returns was built under some library version, and after an upgrade every one of them is a suspect. Note the join goes through the index's own collation list, indcollation, not the column's default — an index declared with an explicit COLLATE clause follows its own rules, and it is easy to miss if you only audit table definitions.

Prove it with amcheck before you rebuild anything

Suspicion is not proof, and reindexing a terabyte of indexes on suspicion is how you turn a quiet week into a bad one. The amcheck extension verifies that a btree is internally consistent: keys in order within and across pages, parent and child pages agreeing. Run bt_index_check on the suspects first — it is the lighter check and safe on a live system. If you want the stronger guarantee, bt_index_parent_check walks parent-child relationships too, but it takes a lock that blocks writers on that index, so schedule it rather than improvising it at noon.

CREATE EXTENSION IF NOT EXISTS amcheck;

-- Light, online:
SELECT bt_index_check('orders_slug_key');

-- Stronger, blocks writers on the index; use a quiet window:
SELECT bt_index_parent_check('orders_slug_key');

A green bt_index_check does not fully clear a collation-suspect index — amcheck checks consistency against the current comparison function, and a subtly wrong index can pass — but a failure settles the question instantly, and a pass plus a resolved collversion lets me sleep. For the highest-stakes unique indexes I also run the poor man's audit: count rows through a plain sequential scan, then again with enable_seqscan switched off so the count is served by the index alone, and compare. Any disagreement is a rebuild, no further debate. Count plain rows, not distinct values: COUNT(DISTINCT) skips NULLs while a nullable unique index stores one entry per NULL, so distinct-count arithmetic disagrees for innocent reasons.

The fix order: REINDEX first, REFRESH VERSION second

Here is the mistake I see in every postmortem someone brings me: they ran ALTER COLLATION ... REFRESH VERSION, the warning disappeared, and they declared victory. REFRESH VERSION does one thing — it re-stamps pg_collation with the library's current version. It touches zero index bytes. All it does is silence the smoke detector. Run it first and you have erased the only durable reminder that your indexes are suspects, which is arguably worse than never having been warned.

The correct order is rebuild, then refresh. REINDEX INDEX CONCURRENTLY builds a fresh index under the current collation rules and swaps it in, blocking reads and writes only briefly at the boundaries. Work through the triage list; if a concurrent reindex fails midway it can leave an invalid index behind — find it in pg_index where indisvalid is false, drop it, and run again. Only after every suspect index is rebuilt do you refresh the collation version, because only then is it a true statement: the library version and the bytes on disk finally agree.

-- 1. Rebuild under the current library rules (not inside a transaction block):
REINDEX INDEX CONCURRENTLY orders_slug_key;

-- 2. Only after all suspects are rebuilt, record the new version:
ALTER COLLATION "en_US.UTF-8" REFRESH VERSION;

The C-collation escape hatch for identifier columns

Most text columns in most schemas are not natural language at all. They are slugs, tokens, status strings, country codes, SKUs — machine identifiers that will never be shown to a human as sorted prose. For those, locale-aware ordering is pure risk with zero payoff. The escape hatch is the C collation: byte-wise comparison, identical on every operating system and every library version, forever. Columns and indexes declared COLLATE "C" are immune to this entire failure class. Two bonuses: byte comparison is measurably faster than locale-aware strcoll, and under C a plain btree supports LIKE 'prefix%' scans, which a non-C collation index refuses without a text_pattern_ops opclass. After the incident we migrated every slug and token column to COLLATE "C"; the natural-language columns stayed on a tracked collation with the runbook below.

Moving to ICU instead of libc does not exempt you, by the way — ICU versions change too, and PostgreSQL tracks them the same way. What changes is the cadence and the predictability of the changes. For brand-new clusters, PostgreSQL 17's builtin provider with C.UTF-8 gives you a stable, UTF-8-aware default that does not move under your feet when the base image bumps a library.

The upgrade runbook I now enforce

• Before any OS or container base-image upgrade, diff glibc and libicu versions between the old and new images. If either moved, text indexes are in scope.
• After the upgrade, grep the logs for "collation version mismatch" and run the pg_collation query on every database.
• Build the suspect index list, bt_index_check the critical ones, and REINDEX CONCURRENTLY the full list.
• Only then run ALTER COLLATION ... REFRESH VERSION.
• Add the collversion query to the post-upgrade checklist permanently. It costs nothing and catches the silent case where nobody read the logs.

Watching index health with MonPG

Corruption like this surfaces as weird deltas: sequential scans on tables that should be index-served, unique violations with no visible twin, sudden bloat from rebuilds. MonPG monitors PostgreSQL today and graphs exactly those operational edges — scan shapes, index usage, invalid indexes — which is how the next collation drift gets caught in hours instead of weeks. The PostgreSQL monitoring surface has the collection details, and the REINDEX field guide covers the rebuild mechanics, including the CONCURRENTLY failure modes, in more depth than I had room for here.