OS upgrades and silent index corruption

A B-tree index on a text column stores keys in the sort order defined by the collation that was active when each key was inserted. For libc collations, that order comes from the operating system's C library. An OS upgrade that ships a new glibc (or a new ICU library) can change the rules — and PostgreSQL does not revalidate existing indexes against the new rules. Queries keep using the affected indexes and can silently return wrong results: rows missing from range scans and prefix searches, incorrect ORDER BY ... LIMIT output, unique checks that fail to spot duplicates. Nothing raises an error.

How an OS upgrade corrupts indexes

String comparison in a libc collation goes through the operating system (strcoll_l and friends). When glibc changes the sort order of a locale, keys inserted after the upgrade are placed according to the new rules while keys inserted before it still sit in old-rule positions. The index remains structurally valid — page links, checksums, and tuple formats are all fine — but it is no longer sorted under one consistent order. Any index scan that relies on ordering can stop early, skip entries, or return them in the wrong sequence.

The best-known trigger is glibc 2.28 (2018), which aligned many locales with a new common sorting template. Upgrades that cross it — for example RHEL/CentOS 7 → 8 or Debian 9 → 10 — are the classic breakage scenario, but any distro upgrade can ship changed locale data. ICU collations have the same exposure to ICU library upgrades, independent of glibc.

Which indexes are at risk

• B-tree indexes on text, varchar, char, and domains over them, when the column collation is provided by libc and is not C/POSIX — including indexes that use the database default collation when the database itself uses the libc provider.
• Unique constraints and primary keys on those columns, since they are backed by such indexes.
• ICU-provider collations are affected by ICU library version changes, not by glibc.

Not affected: C and POSIX collations (byte-order comparison, stable everywhere), the builtin provider locales such as C.UTF-8 (immutable by design, PostgreSQL 17+), hash indexes (no ordering), and indexes on non-collatable types such as integer, bigint, uuid, or timestamptz.

See Collation Support in the PostgreSQL 18 documentation for provider semantics.

Inventory before the upgrade

Before any OS upgrade, list the indexes that depend on libc ordering and save the output — it is your diff baseline afterwards:

SELECT DISTINCT
  i.indexrelid::regclass AS index_name,
  i.indrelid::regclass   AS table_name,
  c.collname             AS collation,
  c.collprovider         AS provider   -- c = libc, d = default, i = icu, b = builtin
FROM pg_index i
JOIN LATERAL unnest(i.indcollation) AS u(coll_oid) ON true
JOIN pg_collation c ON c.oid = u.coll_oid
WHERE c.collprovider IN ('c', 'd')
  AND c.collname NOT IN ('C', 'POSIX')
ORDER BY 2, 1;

The default collation (provider d) inherits the database locale, so check what each database actually uses:

SELECT datname, datcollate, datlocprovider, datcollversion
FROM pg_database;

If datlocprovider is c and datcollate is not C/POSIX, every default-collation text index in that database depends on libc. The query above covers index key columns; expression indexes can embed additional collations in their expressions and need a manual review of their definitions.

Detecting damage after the upgrade

Compare recorded collation versions

Since PostgreSQL 10, the catalog records the provider version of each collation in pg_collation.collversion; PostgreSQL 15 extended this to the database default collation (pg_database.datcollversion, together with ALTER DATABASE ... REFRESH COLLATION VERSION). When an object is used whose recorded version no longer matches what the OS reports, the session emits a warning with a version-mismatch notice once per collation. You can compare directly without waiting for warnings:

SELECT collname, collprovider, collversion,
       pg_collation_actual_version(oid) AS os_version
FROM pg_collation
WHERE collversion IS DISTINCT FROM pg_collation_actual_version(oid);

pg_collation_actual_version asks the operating system for the currently installed version. Rows returned by this query are collations whose behavior may have changed under your feet. On PostgreSQL 9.6 and older the version-tracking infrastructure does not exist — there is no warning and nothing to compare, so detection rests entirely on the structural check below or on planned reindexing.

Verify index structure with amcheck

The amcheck extension re-evaluates B-tree ordering using the current comparison rules. That makes it a direct detector for this failure mode: an index built under old rules can be internally consistent yet fail verification after the upgrade, because verification now expects the new order.

CREATE EXTENSION IF NOT EXISTS amcheck;

SELECT bt_index_parent_check('app.orders_customer_name_idx', heapallindexed => true);

The function returns no rows when the index is clean and raises an error on the first inconsistency. bt_index_parent_check is the stricter variant (it also checks parent/child page relationships); bt_index_check is lighter. bt_index_parent_check takes a ShareLock on the index and its table, blocking concurrent INSERT/UPDATE/DELETE, so run it in a maintenance window; bt_index_check takes only an AccessShareLock — the same lock a plain SELECT takes — and does not block writes.

Two limitations to keep in mind: amcheck only reports when stored key order conflicts with the current rules — a collation change that happens not to reorder any existing key passes silently, so a clean run is not proof of safety. Conversely, after an OS upgrade that touched glibc or ICU, an amcheck error on a text index almost certainly means "rebuild", not "hardware fault".

Rebuilding affected indexes

Rebuild without blocking the application:

REINDEX INDEX CONCURRENTLY app.orders_customer_name_idx;

-- or every index on a table at once:
REINDEX TABLE CONCURRENTLY app.orders;

REINDEX CONCURRENTLY keeps the table readable and writable, takes longer than a plain REINDEX, and cannot run inside a transaction block. A failed or interrupted run can leave an INVALID index behind — find it in pg_index (NOT indisvalid) and drop it before retrying. For an instance-wide incident, rebuild per affected table ordered by index size so the largest windows are scheduled deliberately.

After rebuilding, refresh the recorded versions to clear the mismatch warnings:

ALTER COLLATION "de_DE" REFRESH VERSION;
ALTER DATABASE app REFRESH COLLATION VERSION;

Only refresh versions after the dependent indexes have been rebuilt — refreshing first silences the warning while the corruption is still in place.

Prevention

• Prefer ICU or builtin collations for new databases. CREATE DATABASE ... LOCALE_PROVIDER = icu ICU_LOCALE = 'de-DE' pins ordering to an explicitly versioned ICU rule set instead of whatever glibc the distro ships; where natural-language ordering is not needed, the builtin provider's C.UTF-8 (PostgreSQL 17+) is immutable across OS upgrades. Existing libc-based databases can move individual columns to ICU collations with CREATE COLLATION plus a concurrent index rebuild.
• Separate OS upgrades from PostgreSQL major upgrades. pg_upgrade copies data files without rebuilding indexes, so combining both upgrades in one window doubles the exposure and makes anomalies hard to attribute. Do them in separate windows, with the version comparison and an amcheck pass in between; upgrade paths are covered in Replication, failover, and upgrades.
• Recheck after every glibc/ICU bump. The version-comparison query is cheap; run it after each OS patch cycle and reindex whatever changed.
• Know what your queries rely on. The damage surfaces through index scans and ordered output — Indexes and EXPLAIN covers how to see which plans depend on the affected indexes.

My PostgreSQL (version) cluster runs on (distro and version) and I plan an OS upgrade to (target). The database default collation is (datcollate / datlocprovider).
1. Give me the SQL to inventory every index depending on a libc or default collation, excluding C/POSIX, including expression indexes.
2. Show how to compare pg_collation.collversion against the OS-provided version with pg_collation_actual_version.
3. Generate an amcheck verification script for the affected indexes and a REINDEX INDEX CONCURRENTLY plan ordered by pg_relation_size.
4. From my index list below, mark which indexes are NOT at risk and why.
My indexes: (paste \di+ output)