Ten ways a glibc change can break your PostgreSQL cluster - Percona

Enterprise-ready open source PostgreSQL
Percona Software for PostgreSQL
Open source PostgreSQL software for production workloads
Model infrastructure requirements for PostgreSQL deployments.
Download PostgreSQL software and tools
Enterprise Support and Services
Expert help to run PostgreSQL in production
Open source MySQL for enterprise use
Percona Software for MySQL
Production-ready open source MySQL software
Compare DBaaS vs MySQL on Kubernetes cloud costs
Download MySQL software and tools
Enterprise Support and Services
Expert help to run MySQL in production
Enterprise-grade value for MongoDB®
Percona Software for MongoDB®
Source-available, enterprise-grade compatible software for MongoDB®
Compare MongoDB® deployment options, costs, and trade-offs.
Download MongoDB® software and tools
Enterprise Support and Services
Expert help to run MongoDB® in production
Run and manage Redis or Valkey
Discover the benefits of migrating from Redis to Valkey
Download Valkey software and tools
Enterprise Support and Services
Expert help to run Redis and Valkey in production
Monitoring & Management (PMM)
Coroot Percona Edition
Expert Consulting and Services
Between any two glibc versions, locales can change, and those changes alter the order of character string comparison (text/varchar/char/citext). It may also happen that locales are removed, such as en_US@ampm, present in RHEL8 and removed in RHEL9, and aa_ER@saaho, present in RHEL9 and then renamed to ssy_ER in RHEL10. Since PostgreSQL relies on locales to compare character strings, a glibc change can directly affect the processing of data already stored and data still to be written.
The latest important milestone happened between RHEL7 and RHEL8. Back then, the change was massive; it affected almost every locale, and thousands of PostgreSQL clusters ran into the problem.
Most of the available information focuses on the idea that only indexes are affected, without detailing what happens with other potentially compromised areas. Reindexing is the suggested solution, and although it helps, it can leave unresolved issues behind.
To help myself, I am working on a project (pg-glibc-collation-audit) that aims to deterministically identify locale changes between two glibc versions. To avoid making this blog a 50-page document, I have compiled all the evidence in that repository accessible via the links.
I have put together a list of 10 cases I know of where a glibc change affects the database and causes data corruption, and I have added two more that do not lead to data corruption but do change how the database behaves after an upgrade.
The full breakage map: Breakage map
The evidence comes from a simple method. Two PostgreSQL clusters are built. One on Rocky Linux 8.9 acting as the primary, and another on Rocky Linux 9.3 that starts as a replica until it is promoted to primary (read/write).
The locale used is sv_SE. The reason is simple; the previously mentioned tool already let me know that sv_SE was one of the locales affected between the two versions.
In glibc 2.28, the sv_SE locale treats W as a variant of V and sorts them together. In 2.34, W is a letter of its own and sorts after V. The example below is enough to explain it.
Full session, with the EXPLAIN plans and the raw output from both nodes: The change
wa stops sitting between va and Vasa and moves to the end. That single move is the origin of everything that follows. Byte order (COLLATE “C”) returned Vasa Wasa va vind vz wa wind on both nodes, so the locale is real on both sides, and the only variable is the glibc version.