| 09:55 | <hsivonen> |
(FWIW, it appears that even SQLite's ICU4C mode sorts without stored collation keys even though the use case for stored collation keys is "databases")
My team just spent a quarter making a special version of ICU to help a database team not corrupt their data when upgrading our library.
By "stored collation key" do you mean that when a column is stored with a particular collation, then (as an implementation detail) a collation key is also stored along with that column's data?
sffc was this an existing 3rd-party DBMS like Oracle or MySQL? Or was it a custom internal Google thing?
If the former, then I'd be curious to learn if any RDBMS does that. AFAIK, I/O is much more expensive than CPU in most modern platforms, so it seems fine to recalculate a collation key whenever sorting or comparing while running a query or when building a persisted index. Yes, it's slower, but if perf is a problem then building an index is typically the right solution rather than optimizing table scans.
For an index to be the right solution, the database needs to be willing and performance-wise capable of rebuilding the index upon collation library and/or CLDR update. Otherwise, you get value-added services like AWS maintaining a glibc whose collation behavior is frozen to accommodate Postgres creating a collation-dependent persistent index. |
| 16:54 | <sffc> | Yes what Henri mentioned is the type of problem I was referencing |
| 23:25 | <justingrant> | Got it. So Postgres relies on the OS's glibc for collation instead of statically linking a particular snapshot of a collation library with each release? Or is the OS's glibc fixed to match Postgres's behavior so that client apps will sort exactly like the DBMS does, even when not running any DB-related code?
Regardless, how can this problem actually be solved? If I want to have a case-insensitive, accent-insensitive DB index, and the rules for accent-insensitive comparison change between ICU releases, then the index has to be rebuilt, right? |