#PostgreSQL has relied on the OS to handle text sorting for most of its history. When glibc 2.28 shipped in 2018 with a major Unicode collation overhaul, every existing text index built under the old rules became invalid... but without warnings or errors.
#Postgres 17 added a builtin locale provider that removes the external dependency entirely:
initdb --locale-provider=builtin --locale=C.UTF-8
More details in this week's PG Phriday: https://www.pgedge.com/blog/what-is-a-collation-and-why-is-my-data-corrupt
The GNU C Library (glibc) version 2.28 entered the world on August 1st, 2018 and Postgres hasn't been the same since. Among its many changes was a massive update to locale collation data, bringing it in line with the 2016 Edition 4 release of the ISO 14651 standard and Unicode 9.0.0. This was not a subtle tweak. It was the culmination of roughly 18 years of accumulated locale modifications, all merged in a single release.
Join @vyruss today, 4/3 at 10 AM EST for a live, hands-on session based on Chapter 2 of his book, "#PostgreSQL Mistakes and How to Avoid Them" hosted on LinkedIn Live. 🐘
He'll break down common #SQL anti-patterns that lead to incorrect results and hidden performance issues, pairing explanations with live terminal demos.
You should leave feeling much better equipped to write safer (& faster!) #Postgres queries.
Register here: https://www.linkedin.com/events/7443380201146990595/
Cyber The G.A.M.E.
pgEdge Postgres
Dave Mason
Andrew Armstrong
Anthony Powell