Collation Challenges: Sorting it Out
summary
Postgres collations change when underlying glibc changes and that can silently corrupt btree indexes and constraints: upgrading RHEL7 (glibc 2.17) databases to RHEL9 (glibc 2.34) can flip sort order, create duplicate-primary-key insertions, and make reindex fail. A 26 million-string torture test proves signatures differ and timings vary wildly (example orders: ~3 min unsorted, 3 s with index, versus 53+ min on later glibc builds). Amazon packages a compatibility RPM reproducing glibc 2.17 to pin ordering, fix corrupt indexes by reindexing, and restore prior performance in RDS/Aurora.
AI-written summary · about
chapters (9)
- 0:00Introduction and speaker background
- 1:10Collation basics and GBC versions
- 4:48Real-world collation differences
- 8:40Collation torture test and performance impact
- 14:27Distributed system collation issues
- 20:57Forward-looking collation solution
- 27:52Backward-compatible collation fix
- 39:13Deployment and risks of collation fixes
- 41:41Q&A on collation risks and future plans
