Skip to main content

PG.L5.COLLATION_VERSION_MISMATCH — indexes sorted by rules the server no longer uses

  • Category: safety
  • Level: 5
  • Confidence: deterministic
  • Downtime class: blocking
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18

A B-tree index on a text column stores its entries in the collation's sort order.

Change the collation — a glibc upgrade, a new ICU, an OS image bump — and that order changes underneath the index. The index is now sorted by rules the server no longer applies, so:

  • an equality lookup can miss a row that is present,
  • a unique constraint can stop rejecting a duplicate.

Nothing raises. The data is not corrupt in any way a checksum would notice; it is merely indexed to a different set of rules.

PostgreSQL records the collation version at creation precisely so this is detectable, and warns once at startup. A warning in a log nobody reads is what this rule turns into a finding.

The order of the fix is the whole difficulty​

-- refreshing FIRST, while the indexes are still sorted the old way:
ALTER DATABASE shop REFRESH COLLATION VERSION;

That records the new version while the indexes are unchanged. It silences PostgreSQL's own warning and leaves the problem — which is worse than doing nothing, because the evidence is now gone.

-- rebuild first, then record the new version:
REINDEX DATABASE shop;
ALTER DATABASE shop REFRESH COLLATION VERSION;

Three ways to be unable to answer, and none of them is a pass​

Neither side reports a version. Some providers report none on some platforms — measured on PostgreSQL 18 on macOS, the libc provider reports none at all. Drift is undetectable there in principle, which is not the same as absent. Reported as undetermined, named as a limit of the provider so nobody goes hunting for a missing privilege.

Only one side reports. The two halves mean opposite things. A recorded version with no current one means the provider stopped reporting — an OS change already happened and the evidence of what it was is gone. A current version with nothing recorded means the object predates version tracking. Each gets its own sentence.

Both present and different. The finding.

Why this finding carries a downtime class when the settings rules do not​

The field prices an operation. A server-baseline rule describes a configuration and prices nothing, which is why those carry none.

Here there genuinely is one: the correction is REINDEX, which takes a lock and rebuilds the index. A deploy pipeline reading the field needs that answer before it schedules the work.

The value is about the fix, not the finding — producing the finding costs nothing at all.

Why the comparison is a plain string equality​

PostgreSQL's own drift check is string equality, and the providers report incomparable formats: ICU says 153.128.47, the builtin provider says 1, glibc says 2.36.

Parsing those into an ordering would invent one across three incompatible schemes — and would make 2.9 newer than 2.36, which is how a real drift gets reported as fine.

What is read​

The database's own collation, which is what almost every index in an ordinary schema is actually built on, and any explicitly created collations outside the system schemas. Both are the same kind of fact and are judged by one rule.

Sources​