How a glibc Collation Update Let Postgres Silently Insert 212 Duplicate Orders Past a Unique Index

02:47 UTC. Finance's nightly reconciliation job pages on-call with one line: duplicate external_order_ref count: 212, expected: 0. The table has a unique constraint on that exact column. It has had one since the schema shipped three years ago. Nobody on the call can explain how a unique index let in a single duplicate, let alone 212.

the scramble

First theory: a race condition in the checkout service's idempotency check, something read-then-insert without a transaction boundary. The on-call engineer pulls request logs for the first duplicate pair. The two inserts for the same external_order_ref are 41 minutes apart, from different customer sessions, with different source IPs. Not a race. Ruled out in ten minutes.

Second theory: the Kafka consumer that writes orders from the payment webhook topic is double-processing messages on retry, classic at-least-once delivery without idempotent consumption. The consumer has a dedupe table keyed on the webhook's delivery ID, and that table shows exactly one row per delivery for both halves of every duplicate pair. The dedupe logic worked. The duplicates got through anyway, downstream of it, at the database's own unique constraint.

That's the moment the room goes quiet. If the dedupe table shows one write attempt per delivery but the database ends up with two rows sharing a value that's supposed to be unique, the bug isn't in application code at all.

the hunt

Confirm the constraint actually exists and is marked valid, not disabled or left invalid from a failed CREATE INDEX CONCURRENTLY:

SELECT indexrelid::regclass, indisvalid, indisready
FROM pg_index
WHERE indrelid = 'orders'::regclass AND indisunique;

   indexrelid        | indisvalid | indisready
----------------------+------------+------------
 orders_ext_ref_uidx  | t          | t

SELECT external_order_ref, count(*)
FROM orders
GROUP BY external_order_ref
HAVING count(*) > 1;

(212 rows)

A valid, ready unique index, and 212 groups of duplicates sitting directly underneath it. The index isn't corrupted in any way Postgres itself can detect from its own catalog. Whatever is wrong is in how the index is being read, not whether it exists.

Someone remembers a line from the Postgres logs that's been scrolling by unnoticed for two weeks, dismissed as noise because it's a WARNING, not an error, and it fires on every new connection rather than once:

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

Cross-referencing against the patch log: unattended-upgrades installed libc6 2.35-0ubuntu3.4 over the previous 2.31-13 build thirteen days earlier, during a routine 03:00 security-patch window. glibc updates don't take effect for a running process until it restarts, so Postgres kept running against the old, in-memory collation tables for eleven more days. A scheduled kernel-patch reboot two days before the incident finally restarted the instance, and every text comparison since has been running under the new glibc's sort order instead of the one the indexes were actually built with.

SELECT collname, collversion, pg_collation_actual_version(oid)
FROM pg_collation
WHERE collname = 'en_US.utf8';

 collname   | collversion | pg_collation_actual_version
-------------+-------------+------------------------------
 en_US.utf8 | 2.31        | 2.35

the find

A B-tree index stores its entries sorted according to whatever comparison function was in effect when it was built. Postgres doesn't re-sort an existing index when the underlying collation library changes underneath it; it just keeps using the index, trusting that the ordering is still valid. glibc's Unicode collation tables aren't frozen across versions. Version 2.35 reordered the relative weight of several punctuation and digit sequences versus 2.31, a change narrow enough that most string pairs sort identically under both versions. external_order_ref values are 20-character alphanumeric tokens with embedded hyphens, and a small fraction of them happened to contain exactly the character sequences whose relative order flipped.

For those specific values, a binary search down the B-tree built under 2.31 and navigated with 2.35's comparator can walk to the wrong child page and simply never visit the leaf holding the existing row. The uniqueness check that runs before every insert does exactly that walk: it didn't find a match, because it was looking in a branch that no longer corresponded to where the existing value actually lived. No error, no violation, no log line beyond the connection-time warning that had been firing harmlessly for two weeks. The insert went through clean, twice.

the fix

Rebuild the affected index under the collation Postgres is actually running now, without taking the table offline:

REINDEX INDEX CONCURRENTLY orders_ext_ref_uidx;
-- 44 minutes, no write lock held for more than the final atomic swap

ALTER DATABASE appdb REFRESH COLLATION VERSION;
-- stops the WARNING from firing on every new connection

REINDEX CONCURRENTLY rebuilds the B-tree from the live data using the current comparator, which both fixes future lookups and, as a side effect, runs every existing row back through the uniqueness check. It failed outright on the first attempt, as expected, because the 212 duplicate pairs were still sitting in the table. Those got reconciled manually first: keep the earliest row per external_order_ref, cancel and refund the duplicate fulfillment on the later one, then rerun the reindex clean.

Longer term, the fix is to stop depending on the OS's libc collation for anything Postgres enforces uniqueness or ordering on, since that's a dependency that can change out from under the database on a routine security patch with no warning anyone treats as actionable:

CREATE COLLATION und_icu (provider = icu, locale = 'und-x-icu', deterministic = true);

ALTER TABLE orders ALTER COLUMN external_order_ref
  TYPE varchar(20) COLLATE und_icu;

REINDEX INDEX CONCURRENTLY orders_ext_ref_uidx;

ICU collations ship bundled with Postgres itself rather than deferring to whatever glibc the host OS happens to have installed, so an OS package patch can no longer silently move the goalposts under a live index. A startup health check was also added that runs the pg_collation_actual_version query against every collation in use and pages before the instance accepts application traffic if any of them disagree with the catalog's recorded version. That catches this class of bug at the next restart instead of thirteen days and one more reboot later.

the aftermath

The unique constraint never stopped existing. It just stopped being able to see what it was comparing against, and nothing in Postgres's own bookkeeping was positioned to notice before the 212th insert made it impossible to ignore.