丹尼尔·维里特：使用按字节排序的索引
内容提要
本文讨论了Postgres数据库中文本索引的排序方式，介绍了按字节排序的优缺点。按字节排序是指比较字符串中的字节，而不考虑字符的表示。与之相比，语言排序会考虑大小写和其他规则。文章还介绍了如何在索引中使用按字节排序。最后，文章提到了为什么大多数安装不使用按字节排序以及使用C/POSIX的弱点。文章建议在创建数据库时考虑是否需要语言排序，并介绍了C.utf8和内置的C.utf8排序的问题。文章还提到了使用'unicode'或'und-x-icu'排序来实现人类可读的排序。总之，文章认为虽然Postgres具有语言排序的好支持，但并不意味着需要在数据库中的每个文本上使用它们，因此需要考虑使用按字节排序或混合排序的成本。
延伸解读
按字节排序的适用场景与限制
按字节排序在性能和可移植性上占优，且支持LIKE前缀搜索和真正的不可变性，但牺牲了人类可读性和范围搜索的便利性。它适合那些不需要语言排序的列，例如代码、标识符或仅用于精确匹配的文本。然而，如果列需要按字母顺序展示或进行范围查询，按字节排序可能导致不符合预期的结果，如大写字母排在小写字母之前。因此，在决定是否使用按字节排序时，需权衡具体查询需求。
C/POSIX排序的字符处理缺陷
C或POSIX排序虽然能正确按字节排序所有字符集，但在字符分类和大小写转换上仅支持US-ASCII。例如，initcap('élysée' collate "C")会错误地输出'éLyséE'，无法正确处理重音字符。这意味着如果数据库需要处理多语言文本，使用C/POSIX可能导致函数结果错误。因此，对于非英语文本，应考虑使用C.utf8或混合排序，以确保字符分类和大小写转换的正确性。
C.utf8的兼容性与优化问题
C.utf8旨在提供二进制排序的同时支持完整的Unicode字符分类，但它并非POSIX标准，且在不同系统上支持程度不一。例如，GNU libc 2.35之前版本虽提供C.utf8，但排序行为可能并非完全二进制。此外，Postgres不会自动识别C.utf8为二进制排序，因此可能无法利用某些优化，如左锚定LIKE模式的索引使用。在缺乏C.utf8的系统上，可以手动创建lc_collate=C和lc_ctype=合适区域设置的排序规则来替代。
未来内置C.utf8与混合排序策略
为解决C.utf8的跨平台问题，Postgres正在开发内置的二进制排序规则，未来版本可能提供更简单的选择。目前，若数据库默认使用二进制排序，但偶尔需要语言排序，可以使用Postgres 16引入的'unicode'排序或之前的'und-x-icu'，通过显式COLLATE子句实现。这种混合策略允许在大多数情况下享受二进制排序的性能优势，同时在需要时获得人类可读的排序，但需注意显式指定排序规则带来的复杂性。
Q&A
什么是按字节排序的索引？
按字节排序的索引是比较字符串中的字节，而不考虑字符的表示，可能导致排序结果与语言排序不同。
按字节排序与语言排序有什么区别？
按字节排序在性能和可移植性上优于语言排序，但在可读性和范围搜索方面则相反。
如何在Postgres中实现按字节排序？
可以通过设置COLLATE属性为C、POSIX或C.utf8来实现按字节排序。
为什么大多数Postgres安装不使用按字节排序？
大多数Postgres安装未使用按字节排序是因为默认使用操作系统的语言环境设置。
C.utf8排序有什么优势和劣势？
C.utf8排序在处理Unicode字符时表现良好，但并非所有系统都支持，且Postgres未优化其使用。
Postgres 16引入了什么新的排序方式？
Postgres 16引入了'unicode'排序，提供了人类可读的排序方式，简化了使用过程。
