iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A glibc or operating-system upgrade can change how a locale compares text, while PostgreSQL’s existing B-tree indexes still reflect the old ordering. In that mismatch, an index lookup may miss a stored key and a unique index may fail to reject a duplicate. The risk is specific to affected collations and data paths—not an automatic result of every OS upgrade. Assess the collations and dependent indexes, rebuild affected objects, and only then refresh PostgreSQL’s recorded collation versions.
Why a unique index can miss a duplicate
A collation is the set of rules used to compare and sort text. PostgreSQL collations can use locale data from the operating system’s C library (libc) or use ICU. A libc collation therefore depends on the locale definitions supplied by the operating system.
A text B-tree index stores entries in an order established when it is built. If a later locale-data change alters comparisons, the active provider may no longer agree with that stored order. An index search can then fail to find a key that is present. Because PostgreSQL enforces unique constraints and primary keys with unique B-tree indexes, this inconsistency can undermine expected uniqueness checks. The PostgreSQL documentation warns that changed collation definitions can lead to corrupt indexes and other problems (PostgreSQL 18: ALTER COLLATION); the PostgreSQL Wiki describes the missed-search and duplicate-insert failure mode (Locale data changes).
This does not mean a healthy, current unique index normally permits duplicates. If duplicates appear after a locale change, treat them as evidence of a possible index/order inconsistency and investigate before relying on the affected constraint.
#1 Best Overall
Which databases and indexes are at risk?
Exposure depends on whether the locale data actually changed, which provider and locale the collation uses, and how the database was moved or upgraded. A glibc change does not affect every locale or every PostgreSQL deployment.
Potentially affected objects
- B-tree indexes on
text,varchar,char, orcitextvalues using an affected collation. - Multicolumn B-tree indexes if at least one indexed text key uses an affected collation.
- Unique indexes on those keys, including indexes that back unique constraints or primary keys.
- Range-partitioned tables whose textual partition keys use an affected collation; check whether rows remain in the correct partitions.
The PostgreSQL Wiki identifies C and POSIX locales as unaffected by glibc locale changes, and says ICU-provider columns are not affected by this particular glibc scenario. These are specific to the described change, not guarantees against all future changes to any provider.
Rank #2
Data paths that deserve review
- Keeping an existing data directory while upgrading the Linux distribution.
- Running
pg_upgradewhile also changing locale data. - Streaming replication to a standby whose locale data differs from the primary.
- Restoring a binary backup on a system with different locale data.
The Wiki distinguishes these stale binary-index risks from logical pg_dump backup/restore or upgrades and logical replication, which it identifies as unaffected by this particular mechanism.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsHow to identify mismatched collations and dependent objects
PostgreSQL records a provider-specific collation version when a collation is created and can warn when that version differs from the provider’s current version. For GNU libc, it uses the C library version as a proxy. That check is imperfect: distributions can backport locale-definition changes without changing the library version. A missing warning therefore does not prove that comparison behavior stayed the same. See the official ALTER COLLATION documentation.
Run the documented dependency query in each database to list collations with version mismatches and 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;The PostgreSQL Wiki also provides this index inventory query for libc/database-default collations other than C and POSIX:
SELECT DISTINCT indrelid::regclass::text,
indexrelid::regclass::text,
collname,
pg_get_indexdef(indexrelid)
FROM (
SELECT indexrelid, indrelid, indcollation[i] coll
FROM pg_index, generate_subscripts(indcollation, 1) g(i)
) s
JOIN pg_collation c ON coll = c.oid
WHERE collprovider IN ('d', 'c')
AND collname NOT IN ('C', 'POSIX');Use this as an inventory aid, not proof that every returned index is stale or that every affected index has been found. Review the actual locale change, PostgreSQL major version, index definitions, and collation assignments. The Wiki’s full guidance is at Locale data changes.
For a suspected libc change, the Wiki suggests comparing operating-system sort output for representative strings under the relevant locale on the old and new systems. Its example compares 1-1 and 11 under en_US.UTF-8; matching results for that pair do not rule out other differences.
Repair affected indexes before refreshing version metadata
- Assess and contain the change. Record the PostgreSQL major version, OS release, glibc version, database locale, and provider for each relevant collation. Do not put a migrated instance into production until affected indexes have been assessed and rebuilt. If a standby has different locale data from its primary, correct the mismatch or rebuild as part of preparing it for service.
- Inventory each database. Run the dependency and index queries above. Include unique indexes and multicolumn indexes with text keys. Check range partitions that use affected textual partition keys.
- Rebuild affected objects. Depending on scope, use
REINDEX INDEX,REINDEX TABLE, or a suitable broader operation. Confirm the syntax and limitations for the deployed PostgreSQL major version before scheduling the work. - Resolve duplicate values if a rebuild finds them. A concurrent reindex can fail on a uniqueness violation. Inspect the table and resolve real duplicate values according to the application’s rules before restoring the unique index. Do not casually remove the uniqueness guarantee.
- Refresh recorded versions after rebuilding. For an affected named collation, run
ALTER COLLATION ... REFRESH VERSION. If the database default collation changed, useALTER DATABASE ... REFRESH COLLATION VERSION. Refreshing updates metadata and clears the mismatch warning; it does not establish that dependent indexes were rebuilt. - Validate before reopening traffic. Confirm intended indexes are valid and unique, rerun the mismatch/dependency inventory, and verify relevant partition placement. Keep a known-good backup and follow the deployment’s maintenance and rollback plan.
Choose a reindex method around write availability
| Method | Write impact | Work and operational considerations |
|---|---|---|
Regular REINDEX |
Blocks writes to the table while the index is built. | Use when the maintenance window can accommodate the write interruption. Check the command’s scope and restrictions for the PostgreSQL major version in use. |
REINDEX CONCURRENTLY |
Allows ordinary inserts, updates, and deletes to continue with reduced write blocking. | Performs extra scans and work, may take longer, and can fail if it detects uniqueness violations. Verify the rebuilt index is valid before considering the task complete. |
PostgreSQL documents the relevant behavior and restrictions in REINDEX. Plan for elapsed time, additional I/O and CPU, and the possibility that duplicate data must be resolved before a unique index can be rebuilt successfully.
What the warning—and a cleared warning—actually tells you
A collation-version mismatch is a useful signal to investigate, not a complete inventory of changed locale behavior. Conversely, a clean version check cannot rule out distribution backports that changed locale definitions without changing the libc version PostgreSQL uses as its proxy. After rebuilding dependent objects, refreshing the version makes PostgreSQL’s recorded metadata match the current provider version. That metadata update alone is not a repair for stale indexes.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
