Your Postgres Indexes Can Break Without a Single Line of Code Changing

The ticket says a customer can't log in. Support swears the account exists. You run the query yourself:

SELECT * FROM users WHERE username = 'jo-ann';

Zero rows.

You open the admin panel, which happens to page through the table with a sequential scan, and there she is. Same username, same spelling, no trailing spaces.

A week later a second problem shows up: two rows with the same username, in a column with a UNIQUE index on it. The constraint that was supposed to make that impossible simply let it happen.

Nobody deployed anything. No migration ran. The only change in the last month was a routine one: the Postgres container moved to a newer base image, or the replica was rebuilt on a newer OS, or the database was restored onto a fresh host with different locale data.

That routine change is the whole story. The operating system changed the collation rules used to sort text, and B-tree indexes using that collation were built under the old rules.

A Dictionary Sorted by One Alphabet, Searched by Another

A B-tree index is a dictionary. The entries are sorted, and finding a word means opening to the middle, deciding "my word comes before this page" or "after this page," and repeating until you land on the right page.

That decision, "before or after," is the only thing the search depends on. Postgres doesn't scan the dictionary. It trusts the order. (If you want the full layout of root, internal and leaf pages, Module A-5: Index Internals in the PostgreSQL In-Depth course walks through it. It's the same leaf-page structure that makes random UUID primary keys tax every insert.)

Now imagine the dictionary was printed under one set of alphabet rules, where hyphens are ignored so jo-ann sits next to joann. Years later, someone hands the reader a new rulebook where a hyphen sorts before every letter. The pages haven't moved. Every word is still printed. But the reader, following the new rules, decides jo-ann must come before joan, flips to that section, doesn't find it, and confidently reports that the word isn't in the dictionary.

That is the failure mode: the index pages were ordered under the old rules, while the comparison rules used to navigate them have changed.

Why this matters in production: Postgres does not detect this during normal operation. There is no error and no failed query. Lookups quietly miss rows, and unique indexes quietly accept duplicates, because both operations rely on the B-tree walking to the right leaf page.

Where the Sort Order Actually Lives

For a database using a libc collation such as en_US.UTF-8, which is still what most installs and container images default to, Postgres doesn't carry its own idea of alphabetical order. It delegates locale-aware comparison to the operating system's C library. On a glibc-based Linux system, that means glibc's collation implementation.

So "is jo-ann less than joan?" is answered by whatever version of glibc is installed on the machine running Postgres at that moment. Not the version that was installed when the index was built.

Most glibc upgrades change little or nothing in collation. The famous exception is glibc 2.28, released in 2018, which brought its locale data in line with a newer ISO 14651 standard and changed the relative order of a large number of strings, especially ones containing punctuation and spaces. The PostgreSQL wiki's test case is a one-liner you can run on any Linux box:

( echo "1-1"; echo "11" ) | LC_COLLATE=en_US.UTF-8 sort

Those two strings come out in a different order depending on which side of 2.28 you're on. That change shipped in Debian 10 (buster), Ubuntu 18.10, and RHEL 8, which means a lot of databases crossed that line during routine OS upgrades.

Postgres has no way to know which glibc changes matter and which don't. So the safe assumption is that any change to the C library under an existing data directory might have changed the sort order.

Reproducing It

You can't easily swap glibc under a running server on a laptop, so I simulated it on PostgreSQL 18.6. I created two ICU collations that differ in one rule, whether punctuation is ignored, which is the same kind of rule glibc 2.28 changed:

CREATE COLLATION old_rules (provider = icu, locale = 'en-u-ka-shifted');
CREATE COLLATION new_rules (provider = icu, locale = 'en');

Same four strings, sorted under each:

old_rules:  ab  a c  a-c  ad
new_rules:  a c  a-c  ab  ad

Under the old rules, a-c is treated roughly like ac, so it lands after ab. Under the new rules, the hyphen and the space sort before letters, so a-c jumps ahead of ab.

Then I built a real table: 200,000 usernames (a third of them hyphenated) plus jo-ann, joan and johnny, with a unique index, all under old_rules:

CREATE TABLE users (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username text COLLATE old_rules NOT NULL
);
-- ... load 200,003 rows ...
CREATE UNIQUE INDEX users_username_key ON users (username);

At this point everything is healthy. amcheck passes and WHERE username = 'jo-ann' returns one row.

Then the "OS upgrade": I pointed the column and the index at new_rules directly in the system catalog. The index pages on disk are byte for byte what the old rules built. Only the comparison function changed. That's precisely the situation after a glibc upgrade.

Don't try this on a real database. Editing pg_index and pg_attribute by hand is how you create corruption, not how you fix it. I did it only on a disposable local test cluster, to fake the broken state an OS upgrade leaves behind. It is not part of any remediation. The real fix is further down.

Symptom 1: the row vanishes from indexed lookups

EXPLAIN (COSTS OFF) SELECT * FROM users WHERE username = 'jo-ann';

                  QUERY PLAN
----------------------------------------------
 Index Scan using users_username_key on users
   Index Cond: (username = 'jo-ann'::text)

SELECT * FROM users WHERE username = 'jo-ann';

 id | username
----+----------
(0 rows)

Turn off index scans and read the table directly, and it's right there. (Those enable_* switches are planner debugging tools, covered in Module A-6: Query Planning. Never set them globally in production.)

SET enable_indexscan = off; SET enable_bitmapscan = off;
SELECT * FROM users WHERE username = 'jo-ann';

   id   | username
--------+----------
 200001 | jo-ann

This is the "customer can't log in" ticket. The data is fine. The path to the data is wrong.

Symptom 2: the unique index accepts a duplicate

INSERT INTO users (username) VALUES ('jo-ann') RETURNING id, username;

   id   | username
--------+----------
 200004 | jo-ann
 
INSERT 0 1

A unique index enforces uniqueness by walking to the leaf page where the new key belongs and checking whether it's already there. Under the new rules it walks to a different leaf, finds nothing, and inserts. Every guarantee in Constraints and Data Integrity that's backed by an index is only as good as the index's sort order.

And the duplicate hides itself. Ask through the index and you get one:

 index_says
------------
          1

Ask the table and you get two:

 table_says
------------
          2

Why this matters in production: this is the dangerous one. Missing lookups are visible and get reported. Duplicates in a column your application assumes is unique are silent, and they spread: two accounts, two payment records, a join that now returns double. By the time anyone notices, the bad rows have been written for weeks.

Symptom 3: the fix fails too

The obvious move is to rebuild the index:

REINDEX INDEX CONCURRENTLY users_username_key;

ERROR:  could not create unique index "users_username_key_ccnew"
DETAIL:  Key (username)=(jo-ann) is duplicated.

The rebuild sorts everything under the new rules, notices the duplicate the broken index let in, and refuses. Worse, a failed REINDEX CONCURRENTLY leaves its half-built index behind. Run the detection query below and you'll see users_username_key_ccnew sitting there, marked invalid, still costing write overhead on every insert until you drop it. It's the same invalid-index trap that catches failed CREATE INDEX CONCURRENTLY runs, covered in Zero-Downtime Schema Migrations.

Where This Bites in Real Systems

The common thread is any path that keeps the existing index files while changing the C library underneath them:

• Changing the Docker base image. Bumping postgres:15-bullseye to postgres:15-bookworm keeps the Postgres major version and your data volume, and swaps glibc from 2.31 to 2.36. Moving a data directory between an Alpine image (musl, which doesn't do locale-aware sorting) and a Debian image is a much bigger jump. If your Postgres lives on a Docker volume, the volume outlives the image, which is exactly what makes this easy to miss.
• Physical replicas on a different OS. Streaming replication ships the primary's index pages as-is. A replica running a different glibc answers queries against those pages with its own sort rules. It returns wrong results while it's a replica, and if it's promoted, it starts writing new entries into indexes it misreads.
• pg_upgrade combined with an OS upgrade. pg_upgrade is fast precisely because it reuses the index files. If the new server runs on a newer OS, those files are now walked with new rules.
• Restoring a file-level backup onto a newer host. Same mechanism as above.

Safer migration paths: pg_dump/restore and logical replication rebuild indexes on the target, under the target's rules, rather than carrying the existing index files across. That's one strong argument for logical replication in OS migrations: you pay for it in setup and time, and in exchange the collation problem disappears. I covered the full playbook for that kind of move in Cloud-to-Cloud Database Migration.

Range partitioning on a text key has the same exposure, by the way. Partition bounds are compared with the same collation, so rows can be routed to a different partition than the one that "should" hold them under the old rules.

Detection

The warning you probably never saw

PostgreSQL 18 records a database-level collation version and checks it against the version currently provided by the system on every new connection. Here's what it looks like after a move from Debian bullseye to bookworm (the message format is exactly what Postgres prints; the version numbers are illustrative, since my laptop reproduction reports different ones):

WARNING:  database "app" has a collation version mismatch
DETAIL:  The database was created using collation version 2.31, but the operating system provides version 2.36.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE app REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

That's a good warning. The problem is where it goes. It's sent to the connecting client, and your application's database driver almost certainly discards it. Look for it in a psql session after any OS change. It may be the only early signal you'll get.

Find the indexes at risk

Indexes whose indexed expressions use collatable data can depend on collation, including indexes on text, varchar, char, citext, and expressions such as lower(email). Your bigint and uuid keys are unaffected. This lists the ones that are exposed (the CROSS JOIN LATERAL turns each index's collation list into rows, the pattern from The Power of LATERAL Joins):

SELECT DISTINCT i.indrelid::regclass   AS table_name,
                i.indexrelid::regclass AS index_name,
                c.collname,
                c.collprovider
FROM pg_index i
CROSS JOIN LATERAL unnest(i.indcollation::oid[]) AS ic(coll)
JOIN pg_collation c ON c.oid = ic.coll
WHERE c.collprovider IN ('d', 'c', 'i')
  AND c.collname NOT IN ('C', 'POSIX')
ORDER BY 1, 2;

d is the database default, c is libc and i is ICU. If your database's default provider is builtin (more on that below), the d rows are safe and you can ignore them.

Prove it with amcheck

amcheck validates the B-tree's structural and ordering invariants under the current comparison rules. It's the B-tree equivalent of asking the reader to check every page of the dictionary against the new rulebook:

CREATE EXTENSION IF NOT EXISTS amcheck;
SELECT bt_index_check('users_username_key', heapallindexed => true);

On the broken index:

ERROR:  item order invariant violated for index "users_username_key"
DETAIL:  Lower index tid=(116,4) (points to index tid=(338,1)) higher index tid=(116,5) (points to index tid=(449,1)) page lsn=0/473D668.

bt_index_check takes AccessShareLock, the same lock mode a plain SELECT takes, so reads and writes carry on while it runs and it's safe on a live system. (Its stricter sibling, bt_index_parent_check, takes a ShareLock that blocks writes, so keep that one for maintenance windows. Lock modes and what they block are covered in Module A-13: Locking Internals.) It isn't free though. It reads the entire index, and heapallindexed => true also reads the entire table, so on a large database it's real I/O you'll want to schedule. For a whole database at once, the pg_amcheck command-line tool that ships with Postgres runs the same checks in parallel:

pg_amcheck --heapallindexed --install-missing --jobs=4 -d app

The Fix, in the Right Order

The order matters more than any single step.

1. Find duplicates through the table, not the index. Any query that uses the broken index will hide them, so force sequential scans for the session:

SET enable_indexscan = off;
SET enable_bitmapscan = off;
SET enable_indexonlyscan = off;
 
SELECT username, count(*)
FROM users
GROUP BY username
HAVING count(*) > 1;

2. Resolve them. This is a business decision, not a SQL one. Two "identical" accounts might each have orders attached. In the demo I kept the oldest row, but in production you'd merge or reassign first.

3. Clean up any failed rebuilds, then rebuild:

DROP INDEX CONCURRENTLY IF EXISTS users_username_key_ccnew;
REINDEX INDEX CONCURRENTLY users_username_key;

4. Verify. bt_index_check returns cleanly and WHERE username = 'jo-ann' returns exactly one row again.

5. Only then, acknowledge the new version:

ALTER DATABASE app REFRESH COLLATION VERSION;

Here is the trap. That command does not rebuild or check anything. It updates the recorded version number so the warning stops. Run it first and you've silenced the only alarm while every index is still broken. Treat it as the last line of the runbook, a signature that says "I have rebuilt everything," never as the fix itself.

Making Collation Drift Boring

Pin the C library with the Postgres version

The cheapest protection is process. Treat the base OS image as part of the database version. postgres:16.4-bookworm, not postgres:16. Changing the OS image then becomes a database migration that goes through the same review as a major version upgrade, with "reindex text indexes, then amcheck" written into the checklist.

Use a collation that can't drift with the OS

PostgreSQL 18 ships a builtin collation provider. Its C.UTF-8 locale uses Unicode code point ordering implemented inside PostgreSQL itself, so ordinary OS upgrades don't change its ordering rules:

CREATE DATABASE app
  LOCALE_PROVIDER builtin
  BUILTIN_LOCALE 'C.UTF-8'
  TEMPLATE template0;

The honest cost: code point order isn't what humans expect. Every uppercase letter sorts before every lowercase one, so Zebra comes before apple, and accented characters like é sort after z. For identifiers, emails, usernames, SKUs and other machine-oriented keys, code point ordering is often a good trade-off, because the application usually cares about deterministic equality and lookup semantics rather than human alphabetical order. For display sorting, apply a linguistic collation at query time:

SELECT name FROM products ORDER BY name COLLATE "en-x-icu";

That sort won't be served from an index unless you create one with that collation, and an index like that brings back the versioning problem, just isolated to the one index you chose.

One caveat, and it matters for anyone planning a major-version upgrade. Code point ordering is fixed, but builtin takes its case mapping (what lower() and upper() return) from Unicode tables compiled into Postgres, and the docs only promise those are stable within a major version. PostgreSQL 18 moved to Unicode 16.0, and PostgreSQL 19 (in beta as I write this) moves to Unicode 17.0. The rest of what changes in 19 is in PostgreSQL 19 vs 18: What Actually Changed. An expression index such as lower(email) is therefore worth an amcheck after a pg_upgrade across major versions, even with no OS change at all. For the same reason, the PostgreSQL 18 release notes recommend reindexing full-text search and pg_trgm indexes after pg_upgrade on clusters whose default provider isn't libc. If you need case-insensitive matching, PostgreSQL 18 also adds PG_UNICODE_FAST, a builtin locale with full Unicode case mapping that still sorts by code point.

You also can't switch an existing database's default locale in place. Moving to builtin means a new database, filled by dump and restore or by logical replication, which, conveniently, is also the safer way to do an OS migration.

ICU is better, not immune

ICU collations (provider = icu) decouple sorting from glibc and give you consistent behavior across Linux and macOS. Postgres tracks an ICU collation's version and warns when it changes. But ICU's rules do change between ICU major versions, and in most Linux images ICU comes from an OS package that upgrades along with the base image. ICU makes the drift visible and less frequent. It doesn't make it go away.

The Takeaway

An index is a promise that the data is sorted. With text, the meaning of "sorted" belongs to a library you didn't write, versioned by an OS image you might upgrade without thinking about the database at all.

So the rule is short. Any time the C library under an existing data directory changes, through a new base image, a replica on a newer OS, or pg_upgrade on a new host, assume every index on a libc or ICU collation is suspect until amcheck says otherwise. Rebuild, verify, and only then tell Postgres the new version is fine.

The OS doesn't know it's touching your database. You have to.

Go Deeper

• PostgreSQL In-Depth: the full course, from storage engine to production operations
• Module A-5: Index Internals: B-tree, Hash, GIN and BRIN, and how the planner sees them
• Storage Engines: B-Trees vs LSM Trees, from System Design In-Depth
• Why Postgres and Cassandra Made Opposite Bets on Storage Engines
• Building Production-Grade Images, for pinning the base image your database runs on

References

• PostgreSQL wiki: Locale data changes (glibc 2.28, affected distributions, the sort test)
• PostgreSQL 18 docs: Collation support and Locale providers
• PostgreSQL 18 docs: ALTER COLLATION, notes on collation versions and ALTER DATABASE
• PostgreSQL 18 docs: amcheck and pg_amcheck
• PostgreSQL 18 docs: REINDEX
• PostgreSQL 18 release notes (Unicode 16.0, PG_UNICODE_FAST, full-text search reindex advice)
• PostgreSQL 19 release notes (beta; Unicode 17.0)
• Debian packages: libc6 in bullseye (2.31) and libc6 in bookworm (2.36)