Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →If PostgreSQL appears to have accepted duplicate text keys after a Linux or glibc upgrade, a stale B-tree index is one possible cause. The operating system’s locale rules may now sort strings differently from the rules used when the index was built. A lookup can then miss a stored key, allowing an insert or update that a healthy unique index should reject. This is a specific risk—not an automatic effect of every OS upgrade—and it depends on the locale, collation provider and data path.
How a collation change can undermine a unique index
A collation is a set of rules for comparing and sorting text. PostgreSQL collation objects connect SQL collation names to locale categories supplied by the operating system or to ICU collators. With the libc provider, those rules come from the system C library, commonly glibc on Linux.
As an Amazon Associate I earn from qualifying purchases.
A B-tree index stores text keys according to the ordering in effect when the index was built. If the active provider later compares some strings differently, searches using the new rules may not follow the same ordering as the stored index. PostgreSQL’s documentation warns that changed collation definitions can cause corrupt indexes and other problems because stored objects rely on a particular sort order (PostgreSQL 18: ALTER COLLATION).
Unique constraints and primary keys are enforced with unique B-tree indexes. In the stale-index scenario described by the PostgreSQL Wiki’s locale-data guidance, an index scan can fail to find an existing key; an update can consequently insert a value that should have been rejected as a duplicate. This does not mean a valid unique constraint normally permits duplicates. It points to a mismatch between the index’s stored ordering and the current comparison rules.
#1 Best Overall
Which deployments may be exposed
The risk is tied to actual locale behavior, not simply to the fact that an operating system changed. The Wiki identifies B-tree indexes on text, varchar, char and citext as potentially affected, including multicolumn indexes when at least one key is textual. Check indexes using the database default collation as well as explicitly named collations.
- Data directories carried across an OS or locale-data change: an existing index may retain its former ordering while the new system supplies changed rules.
pg_upgradecombined with changed locale data: preserving database files while moving to a system with different locale behavior can leave relevant indexes stale.- Streaming replication or binary backup: a standby or restored binary copy using different locale data can encounter the same mismatch.
- Logical dump/restore and logical replication: the Wiki identifies these paths as unaffected by this particular stale binary-index mechanism, because they do not carry the old binary index structure forward.
For the described glibc-change scenario, the Wiki says C and POSIX locales are unaffected by glibc locale changes, and ICU-provider collations are not affected by this particular glibc change. Those limits do not guarantee immunity from every possible future change to a provider’s rules.
Rank #2
Range-partitioned tables also need attention when text-like values and affected collations are used as partition keys. Changed comparisons can leave rows in partitions that no longer match the intended range placement.
Check for version mismatches and dependent objects
PostgreSQL records a provider-specific collation version when a collation is created and can warn when the current provider version differs. The documented dependency query below finds collations whose recorded and actual versions disagree, along with dependent objects. Run it in each database that may be affected:
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;For an inventory of indexes that use libc or database-default collations other than C and POSIX, the Wiki provides this query:
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 the second query as an inventory aid, not as proof that every listed index is stale or that every affected index has been found. Review the index definitions, actual locale/provider changes and PostgreSQL major version. Include unique indexes and multicolumn indexes with any text key; separately review range-partitioned tables that use affected text collations.
A missing warning is not proof that the locale rules stayed the same. For GNU libc, PostgreSQL uses the C library version as a proxy; distributions can backport locale-data changes without changing that version. The official ALTER COLLATION documentation describes this limitation.
Free tools Windows power users keep installed
One-click scans. No signup required.
Where old and new systems are available, compare the operating-system sort output for representative values under the relevant locale. The Wiki illustrates comparing 1-1 and 11 under en_US.UTF-8, but matching results for those two strings do not rule out differences elsewhere. A glibc 2.28 locale-data update in 2018 is a historical example of a change that could affect many users, not a measure of current prevalence or a prediction for a particular distribution.
Rebuild first, then refresh collation metadata
- Assess and contain the risk. Record the PostgreSQL major version, OS release, glibc version, database locale and provider for relevant collations. Avoid putting a migrated instance into production until the affected indexes have been assessed and rebuilt. For a standby whose locale data differs from the primary, correct the mismatch or rebuild before bringing it into service.
- Inventory every database. Run the mismatch/dependency query and review the index inventory. Include unique and multicolumn indexes with text keys, plus relevant range-partitioned tables.
- Rebuild affected indexes. Use an appropriate
REINDEX INDEX,REINDEX TABLEor broader operation for the deployed PostgreSQL version and object. Choose regular or concurrent reindexing based on the maintenance window and lock requirements. - Resolve any uniqueness failures. A concurrent rebuild can fail when it finds duplicate values. Inspect the table data and decide which values to retain or change according to application rules before restoring the unique index. Do not casually remove the uniqueness guarantee. After rebuilding, verify that the index is valid and unique.
- Refresh version metadata only after rebuilding. Run
ALTER COLLATION ... REFRESH VERSIONfor affected named collations after their dependent objects have been rebuilt. If the database default collation changed, useALTER DATABASE ... REFRESH COLLATION VERSION. These commands update recorded version metadata and clear the mismatch warning; they do not verify that dependent indexes were rebuilt. - Validate before reopening traffic. Confirm intended indexes are valid, re-run the mismatch/dependency inventory, check relevant partition placement and retain a known-good backup under the deployment’s rollback plan.
Choose the reindex method around write availability
| Method | Write impact | Operational trade-off |
|---|---|---|
Regular REINDEX |
Blocks writes to the table during the build. | Use when the maintenance window permits write downtime; check command limitations for the PostgreSQL major version and object. |
REINDEX CONCURRENTLY |
Allows ordinary inserts, updates and deletes to continue, with reduced write blocking. | Requires additional scans and work, can take longer, and may fail on uniqueness violations. Check version-specific command limitations and verify index validity afterward. |
The exact supported forms and restrictions vary by PostgreSQL major version and object type. Consult the REINDEX documentation for PostgreSQL 18 and the documentation matching the server you actually run.
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.
