17新版本增加了两种内置的collation provider，对索引的二进制排序和运算符转换性能更优，且跨平台，不再担心操作系统升级导致索引损坏的问题了。
问题背景
在10版本之前，locale区域设置的collation provider用户还不能选择，只能依赖于操作系统提供的libc库。
10版本引入了第三方的ICU(International Component for Unicode)库，ICU库提供比libc库更坚固的保障。不过也存在操作系统升级版本的问题，在相同的版本不同平台下是比较可靠的。
从10版本开始，用户可以局部使用ICU库，但并不能作为db的默认设置或者初始化时进行全局设置。
15版本用户初始化时允许使用icu provider作为全局设置，或者新建库进行设置。
使用libc作为collation provider，数据库对文本字段的索引缺省是按照Linguistic order进行排序。
由于操作系统设置的语言环境变量通常是en_US.UTF-8，索引默认继承了locale的设置。
$LANG -> initdb -> template dbs -> user-created dbs -> columns -> indexes
使用icu作为collation provider，数据库对索引也是按照Linguistic order进行排序，它并未提供Bytewise Comparison方式。
Linguistic order排序考虑了语言习惯，例如大小写、元音等，用户通常不会选择Byte order二进制排序。
这两类排序方式的简单利弊如下：
| Linguistic order | Binary order | |
|---|---|---|
| 易用性 | ✅更好 | ❌更糟 |
| 可读性 | ✅更好 | ❌更糟 |
| 范围搜索 | ✅更好 | ❌更糟 |
| 性能 | ❌更糟 | ✅更好 |
| 便携性 | ❌更糟 | ✅100% |
| 稳定性 | ❌ 不 | ✅是的 |
| LIKE前缀搜索 | ❌ 不 | ✅是的 |
而随着操作系统升级，libc库或ICU库版本可能升级，索引也需要重建，否则会出现损坏。
当然15版本对libc和ICU版本升级引起索引损坏的问题也提供了解决方案：
数据库运行过程中记录并检测数据库的collation版本
postgres=# SELECT pg_database_collation_actual_version(5);
pg_database_collation_actual_version
--------------------------------------
2.17
(1 row)
数据库collation版本能刷新对齐升级后的操作系统
postgres=# ALTER DATABASE postgres REFRESH COLLATION VERSION;
NOTICE: version has not changed
ALTER DATABASE
但为了彻底解决这个问题，社区在17版本提供了内置的collation provider。
两种内置的collation provider
17版本提供了两种内置的locale provider，它们对二进制排序的性能更优，同时"C.UTF-8"对unicode编码下字符的大小写及元音处理比"C"更好。
使用下面的语句可查看两种内置的collations：
postgres=# SELECT
collnamespace::regnamespace,
collname,
colllocale,
pg_encoding_to_char(collencoding) as encoding
FROM pg_collation WHERE collprovider='b';
collnamespace | collname | colllocale | encoding
---------------+-----------+------------+----------
pg_catalog | ucs_basic | C | UTF8
pg_catalog | pg_c_utf8 | C.UTF-8 | UTF8
(2 rows)
ucs_basic兼容以前版本的libc provider，也是缺省的C locale。
pg_c_utf8对upper(), lower(), initcap()函数的处理比ucs_basic更人性化。
如何使用C.UTF-8
beta2版本当前使用语法如下：
$ initdb --builtin-locale=C.UTF-8 --locale-provider=builtin -D mypgdata
除了初始化时进行全局设置，也可单独创建数据库时使用：
CREATE DATABASE builtdb
LOCALE_PROVIDER builtin
BUILTIN_LOCALE 'C.UTF-8'
TEMPLATE template0;
简单测试
本地虚拟机下，运行简单的select语句，对一百万数据做数字到文本转换和排序：
SELECT i::text FROM generate_series(1, 1_000_000) x(i) ORDER BY 1 LIMIT 1;
使用16.3版本测试libc库，时间大约是360ms，使用17 beta2版本内置的C.UTF-8，时间大约是170ms。
推荐阅读
与我联系
微信公众号：象楚之行
墨天轮：https://www.modb.pro/u/15675
微信：skypkmoon
勤耕细作，用心积微；静待花开，量变质成。
原文作者：象楚之行；如有侵权，请联系管理员删除。
