BTW, the new amcheck tool, available in Postgres 10, lets you validate that an index is consistent with its designated sort order (B-Tree operator class). At least you now have some way of detecting the kind of inconsistency you describe.

I wrote amcheck, and maintain a version targeting earlier releases of PostgreSQL on Github: https://github.com/petergeoghegan/amcheck

This somewhat mixes two issues, really.

Firstly, there are issues with glibc versioning, when the collation rules change even without proper version bump. I don't have a link to details at hand, but I've heard a number of complaints about it over the years from various PostgreSQL hackers, including Peter Geoghegan who did a lot of sort optimizations in 9.5 & 9.6. I'm sure there's something deep in the PostgreSQL mailing list archives. You'll have to dig deep, because we learned the lesson years ago.

Regarding strxfrm(), PostgreSQL certainly does not store the results anywhere - it's only used during the actual sort, e.g. during CREATE INDEX or to help with ORDER BY in a query, and then thrown away. So either I'm forgetting something, or Robert mixed this up in the blog post.

There's however an issue with strxfrm() disagreeing with strcoll(), which is a major problem, of course, because we build and index using strxfrm() and then fail to find the values in it using strcoll(). For more details, including links to pgsql-bugs and RH bugtracker, see https://wiki.postgresql.org/wiki/Abbreviated_keys_glibc_issu...

ICU seems not to have these issues, because it has better and more carefully maintained versioning scheme. Perhaps we'll still get broken indexes after an ICU upgrade, but at least we'll know about it.

PostgreSQL never stored strxfrm() output in indexes, but did at one time use it to build indexes (that is, strxfrm() was used to build values when sorting). However, it would be nice to be able to store strxfrm() output on disk, in indexes, which as you say leaves the implementation sensitive to changes in the transformation algorithm, and not just the underlying behavior defined by a collation.

Fortunately, it is possible to do this safely with ICU, which explicitly decouples implementation issues from behavioral issues. Maybe this will happen at some point in the future. For now, abbreviated keys are only used for sorting to build an index, and not in any subsequent index scan.

If you want much faster text sorting back, using "abbreviated keys", you can use ICU collations in Postgres 10. With the C standard library, strxfrm() was disabled when it was found to be buggy within glibc [1].

[1] https://wiki.postgresql.org/wiki/Abbreviated_keys_glibc_issu...

> I mean, not only are PostgreSQL locale-sorted-indexes not portable across glibc releases. They aren't portable across any other system change either. No moving between distros or doing distro upgrades, etc... Those are all misfeatures probably worth fixing.

Hence ICU support, which allows to address these (because collations are properly versioned).