Collation Version Mismatch: Indexes Silently Corrupt After glibc/ICU Upgrade

Scenario

After the OS team upgrades the system glibc from 2.31 to 2.35 on a running PostgreSQL 14 server, the PostgreSQL log begins emitting warnings:

WARNING:  collation "en_US.UTF-8" has version mismatch
DETAIL:  The collation in the database was created using version 2.31,
         but the operating system provides version 2.35.
HINT:  Rebuild all objects affected by this collation and run
       ALTER COLLATION "en_US.UTF-8" REFRESH VERSION, or build
       PostgreSQL with "--disable-spinlocks" to suppress this warning.

The database continues to operate without error. This is the danger: queries return results, but any btree index built on a text or varchar column using the en_US.UTF-8 collation may return incorrect or missing rows because the <a class="sev1-termlink" href="https://thesev1database.com/glossary/pathkeys/">sort order used at index creation time no longer matches the sort order the current glibc version produces. Range scans and unique constraint checks are particularly affected.

How to Identify

-- Quick check: are any collations out of sync on this server?
SELECT
    collname,
    collprovider,
    collversion                              AS stored_version,
    pg_collation_actual_version(oid)         AS actual_version,
    collversion <> pg_collation_actual_version(oid) AS version_mismatch
FROM pg_collation
WHERE collversion IS NOT NULL
ORDER BY version_mismatch DESC, collname;

Analysis Steps

-- Step 1: Identify all collations with version mismatch
SELECT
    oid,
    collname,
    collprovider,                        -- 'c' = libc, 'i' = ICU, 'd' = default
    collencoding,
    collversion                          AS stored_version,
    pg_collation_actual_version(oid)     AS actual_version
FROM pg_collation
WHERE collversion IS NOT NULL
  AND collversion <> pg_collation_actual_version(oid);

-- Step 2: Find all indexes that use any affected collation
-- These indexes must be rebuilt before being trusted.
SELECT DISTINCT
    n.nspname                            AS schema,
    c.relname                            AS table_name,
    i.relname                            AS index_name,
    pg_get_indexdef(ix.indexrelid)       AS index_definition,
    co.collname                          AS collation_name
FROM pg_index          ix
JOIN pg_class          c   ON c.oid   = ix.indrelid
JOIN pg_class          i   ON i.oid   = ix.indexrelid
JOIN pg_namespace      n   ON n.oid   = c.relnamespace
JOIN pg_attribute      a   ON a.attrelid = ix.indrelid
                           AND a.attnum   = ANY(ix.indkey)
JOIN pg_collation      co  ON co.oid  = a.attcollation
WHERE co.collversion IS NOT NULL
  AND co.collversion <> pg_collation_actual_version(co.oid)
ORDER BY n.nspname, c.relname, i.relname;

-- Step 3: Check the database-level default collation
SELECT
    datname,
    datcollate,
    datctype,
    daticulocale       -- PG15+: ICU locale if database uses ICU
FROM pg_database
WHERE datname = current_database();

-- Step 4: After rebuilding indexes, refresh the stored collation version
-- This suppresses future warnings for this collation.
-- Run AFTER all affected indexes have been rebuilt — NOT before.
ALTER COLLATION "en_US.UTF-8" REFRESH VERSION;

-- Step 5: Verify no mismatch remains after refresh
SELECT
    collname,
    collversion                          AS stored_version,
    pg_collation_actual_version(oid)     AS actual_version
FROM pg_collation
WHERE collversion IS NOT NULL
ORDER BY collname;

Pitfalls

1. The WARNING does not prevent queries — but results from affected indexes are wrong. PostgreSQL

logs the collation mismatch warning at startup and on first use, then continues serving queries. There is no automatic query error. A btree index stores keys in the sort order of the collation at build time; if glibc now sorts differently, the index contains entries in an order that no longer matches the comparator, causing index scans to stop too early or skip valid rows silently. Do not dismiss this warning as cosmetic.