PostgreSQL中ICU排序规则'de-u-kn-true-ks-level2'确定性及数据库创建疑问

关于排序规则的确定性困惑 #

你提到的“仅字节序排序能是确定性的”是PostgreSQL文档针对libc提供的传统排序规则的描述，不适用于ICU排序规则。

PostgreSQL对ICU排序规则的“确定性”判断基于以下逻辑：

• 默认情况下，ICU排序规则是确定性的（除非你显式指定deterministic = false），因为ICU的排序逻辑是明确可复现的——给定相同的字符串，排序结果和哈希值始终一致。
• 你的de-u-kn-true-ks-level2规则中，kn-true启用自然数字排序，ks-level2将排序强度设为二级（忽略大小写和重音差异），这些属性都不会破坏确定性，因此PostgreSQL会将其标记为确定性排序规则，这完全符合预期。

数据库级默认排序规则的设置 #

你通过CREATE DATABASE tomsdb TEMPLATE template0 LOCALE_PROVIDER icu ICU_LOCALE 'de-u-kn-true-ks-level2'创建数据库的方式是正确的，这已经将该ICU排序规则设为数据库的默认规则：

• 所有新创建的文本列（text、varchar、char）如果不指定COLLATE子句，都会默认使用这个规则。
• 基于这些列创建的索引也会自动使用该排序规则，无需额外配置。

你可能忽略的细节：

1. 数据库默认排序规则不可事后修改：一旦数据库创建完成，无法更改默认排序规则，所有后续对象都将沿用创建时的设置。
2. 与手动创建的排序规则的等价性：你手动创建的tomscollation和数据库默认的排序规则完全等价（因为使用了相同的ICU locale参数），无需重复创建，直接使用数据库默认即可。
3. 依赖ICU支持：你的PostgreSQL实例必须编译时启用了ICU支持才能使用该特性，否则LOCALE_PROVIDER icu会报错。

可能的未知副作用 #

1. 性能开销：ICU排序规则的计算逻辑比libc更复杂（尤其是自然排序需要解析字符串中的数字），因此大量排序操作、索引构建或查询可能会比使用libc排序规则慢，索引体积也可能更大。
2. 行为兼容性：
   • 自然排序会改变字符串的比较逻辑，例如'A2' < 'A12'会成立，但传统字节序排序中'A12' < 'A2'（因为'1'的ASCII码小于'2'），依赖旧行为的应用需要调整。
   • 大小写不敏感会导致'a' = 'A'、'Ä' = 'ä'（取决于ks-level2的具体处理），如果应用原本依赖大小写敏感的比较，会出现逻辑错误。
3. 备份恢复限制：使用ICU排序规则的数据库无法恢复到未启用ICU支持的PostgreSQL实例，备份时需要确保目标环境满足依赖。
4. 排序细节差异：ICU的自然排序对数字的识别规则可能和你预期的有细微差别，例如包含非连续数字的字符串（如'A1B2'）、带前导零的数字（如'A002' vs 'A2'），建议提前测试核心场景。
5. 第三方工具兼容性：部分第三方PostgreSQL工具可能对ICU排序规则的支持有限，例如某些ETL工具或ORM框架，需要验证兼容性。

内容的提问来源于stack exchange，提问作者Tom