A collation change under an existing B-tree makes lookups miss rows and unique checks admit duplicates. Detect it with collation versions and amcheck, then reindex.
The row that is there and cannot be found
After an operating system upgrade, a PostgreSQL server at Belgavi's Book Store came back up cleanly. Replication caught up, the health checks passed and the application reconnected. Two days later the warehouse team reported that searching for shelf code 1-2 returned nothing, while a full report listed the shelf with forty books on it. Shortly after, the unique constraint on shelf codes accepted a second row for 1-2.
No disk failed and no query was wrong. The B-tree index on the shelf-code column had been built under one set of sorting rules and was now being searched under another. This guide explains why that happens, which upgrades cause it, how to detect it with the tools PostgreSQL ships, and how to plan an upgrade so it does not happen to you. The worked example uses an illustrative pair of collations in a small model, not the actual glibc tables, because the point is the mechanism.
Text order comes from outside the database
For text columns with a libc collation such as en_US.UTF-8, PostgreSQL asks the operating system's C library to compare strings. The PostgreSQL documentation says so directly: the libc provider uses the locales provided by the operating system C library. A B-tree index stores keys in the order those comparisons define, and every later search walks the tree assuming the same order.
If the C library's rules change, the stored order and the comparison disagree. The PostgreSQL wiki page on locale data changes describes the consequences in plain terms: an index scan may fail to find data that is actually there, an update may insert duplicate data that a unique index should have prevented, and a range-partitioned table may route rows to the wrong partition. It singles out glibc 2.28, released in August 2018, as a large update to the locale data, and lists the situations that expose you: upgrading the operating system under an existing data directory, using pg_upgrade across such an upgrade, running a streaming-replication standby on a host with different locale data, or restoring a physical backup onto one. Logical transfers such as pg_dump and logical replication are not affected, because they rebuild indexes on the target.
The wiki also gives a one-line test you can run on old and new hosts: sort the two strings 1-1 and 11 with the locale and compare the output. They sort differently on different locale versions.
How a changed comparator loses rows
The companion builds a sorted list, standing in for the leaf level of a B-tree, from twelve shelf codes using an OLD comparator that ignores hyphens and spaces at the first level and breaks ties on the full string. It then searches the list with a NEW comparator that compares characters as written. Under OLD, the index order is:
1-1, 11, 1-2, 12, 1 3, 13, 2-1, 21, A-10, A10, A-2, A2Under NEW the same keys sort as:
1 3, 1-1, 1-2, 11, 12, 13, 2-1, 21, A-10, A-2, A10, A2A binary search with the NEW comparator over the OLD order finds nine of the twelve keys and misses 1-2, 1 3 and A-2. Each miss happens because the search compares the target with a key in the middle, decides the target must be on one side, and never looks on the other side where the key actually sits. An insert that checks uniqueness the same way, by searching for the key and inserting at the position the search lands on, accepts a second 1-2. The data is all present; the structure that finds it is wrong.
Real B-tree code is more elaborate than a sorted list, but the property that breaks is the same one: the tree is only searchable if the stored order agrees with the comparison used to search it.
What PostgreSQL records and warns about
PostgreSQL has tracked collation versions for some time. The ALTER COLLATION documentation explains that when a collation object is created, the provider-specific version is recorded in the catalog; when the collation is used, the current version is checked against the recorded one and a warning reports a mismatch, with a hint to rebuild affected objects and then run ALTER COLLATION ... REFRESH VERSION. For the database default collation the analogous command is ALTER DATABASE ... REFRESH COLLATION VERSION.
Two caveats in the same documentation decide how much to trust the warning. For libc collations on glibc systems, the C library's version is used as a proxy for the collation version, and the documentation notes this is imperfect because distribution maintainers can back-port newer collation definitions into older C library releases. And refreshing the version only updates the catalog; it does not check that affected objects were rebuilt. A warning is a strong signal. The absence of a warning is not proof of safety.
The documentation also gives a query that lists collations whose recorded version differs from the one the system now provides, together with the objects that depend on them:
SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
pg_describe_object(classid, objid, objsubid) AS "Object"
FROM pg_depend d
JOIN pg_collation c
ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
WHERE c.collversion <> pg_collation_actual_version(c.oid)
ORDER BY 1, 2;Checking the indexes themselves
The amcheck extension verifies B-tree invariants, including that keys are in order. bt_index_check takes an index and optional flags; with heapallindexed it also checks that every heap tuple has an index entry, and with checkunique it checks unique indexes for duplicates. The documentation's example runs it over the indexes of a catalog; for an upgrade check, run it over every B-tree index on text-like columns:
CREATE EXTENSION IF NOT EXISTS amcheck;
SELECT c.relname,
bt_index_check(index => c.oid, heapallindexed => true, checkunique => i.indisunique)
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am am ON am.oid = c.relam AND am.amname = 'btree'
WHERE EXISTS (
SELECT 1 FROM pg_attribute a
WHERE a.attrelid = i.indrelid AND a.attnum = ANY (i.indkey)
AND a.atttypid IN ('text'::regtype, 'varchar'::regtype, 'bpchar'::regtype)
);In the model, the equivalent of this check reports exactly three adjacent pairs out of order, 11 before 1-2, 12 before 1 3 and A10 before A-2, which are the boundaries around the three missed keys. On a real database, a failure from amcheck is a reason to reindex before anything else.
bt_index_check takes only an AccessShareLock, the same lock a plain SELECT takes, but it reads the whole index, and with heapallindexed the whole table as well. On large tables that is a lot of I/O, so run it on a restored copy or a standby promoted for the purpose, or schedule it off-peak. It also does not verify invariants that span parent and child pages; bt_index_parent_check does, at the cost of a stronger lock. A clean result after an upgrade is good evidence; a clean result on the old host tells you nothing about the new one.
Repair and the upgrade plan
The repair is to rebuild every affected index: REINDEX for each B-tree on text, varchar, char or citext columns, using REINDEX CONCURRENTLY where blocking writes is not acceptable, followed by checking range-partitioned tables whose partition key is textual. If a unique index already admitted duplicates, the rebuild fails on them, and you must resolve the duplicate rows first. Only after rebuilding should you refresh the recorded collation version. In the model, rebuilding the list under the NEW comparator finds all twelve keys and reports no violations.
For a planned operating system upgrade, the safer sequence is:
- On the old host, record
SELECT collname, collversion FROM pg_collationand the database collation versions, and run the1-1versus11sort test. - Build the new host and run the same test there before moving any data.
- If the order differs, prefer a logical move (pg_dump and restore, or logical replication) over pg_upgrade or a physical copy, because the target rebuilds every index under its own rules.
- If you must move physically, schedule the reindex of all text-keyed B-trees before the database takes traffic, and run amcheck afterwards.
- Never let a physical standby run on a host whose locale data differs from the primary's, and include standbys in the upgrade plan.
Removing the dependency
Two choices reduce the exposure for new databases. The ICU provider supplies collation behaviour independent of the operating system's C library, and PostgreSQL records ICU versions on all platforms, so changes become visible as version mismatches. ICU itself changes between versions too, so this moves the problem to a library you upgrade deliberately rather than removing it. The builtin provider supports the C, C.UTF-8 and PG_UNICODE_FAST locales; its C.UTF-8 collation uses code point order only, which does not change with library upgrades. Code point order is not what people expect from an alphabetical list, so use it for identifiers such as shelf codes, SKUs and case numbers, and sort human-facing text with an explicit linguistic collation at query time.
The broader lesson applies beyond PostgreSQL: any persistent structure ordered by a comparison function you do not control, whether an index, a sorted file, a partition map or a sorted cache, is valid only while that function stays the same. Treat the comparison function as part of the data's format.
Run the example yourself
The OLD and NEW illustrative comparators, the sorted-index search, the order check and the duplicate insert. It does not use glibc or PostgreSQL. Companion code on GitHub; its README lists the prerequisites, exact commands, expected output and limits.