DeepSeek总结的PostgreSQL排序规则，以及为什么数据会损坏

原文地址https://postgr.es/p/7Ds什么是排序规则以及为什么我的数据会损坏肖恩·托马斯 | 2026年4月3日GNU C 库 (glibc) 2.28 版本于 2018 年 8 月 1 日发布自此之后Postgres 的世界就再也不同了。在其众多变更中包含了对语言环境排序规则数据的大规模更新使其与 ISO 14651 标准的 2016 年第 4 版以及 Unicode 9.0.0 保持一致。这并非微调。这是过去大约 18 年间积累的语言环境修改的顶峰所有这些修改都合并到了这一个版本中。没有人为此庆祝。随之而来的是 Postgres 历史上最重大、最隐蔽的数据完整性事件之一。索引在无人察觉中损坏查询结果毫无征兆地改变唯一约束也不再可信。最糟糕的是什么你得知道去检查它。Postgres 不会抱怨。操作系统也不会抱怨。一切看似正常直到突然出现问题。这就是一个库升级如何在全世界悄然损坏数据库的故事Postgres 社区对此做了什么以及如何确保你不会再遇到同样的问题。到底什么是排序规则在我们理解出问题的原因之前我们需要了解排序规则究竟是做什么的。其核心是排序规则定义了文本如何进行比较和排序。这听起来很简单但在英语字母表之外排序规则就变得复杂得多。以德语字母ß为例。它是否应该和 “ss” 排序相同通常是。那像é和è这样的带重音字符呢在排序时它们应该被视为等同于 “e”还是应该拥有自己独特的位置瑞典字母表呢其中ä和ö排在z之后而不是被视为a和o的变体每种语言对这些问题都有自己的答案而排序规则将这些答案编码成一组数据库需要遵循的规则。当 Postgres 需要对文本列进行排序、强制唯一约束或构建 B-tree 索引时它会问排序规则“这两个字符串哪个排在前面” 排序规则的答案决定了从查询结果到索引查找是否能找到任何数据的一切。历史上Postgres 将该问题委托给操作系统的 C 库。Postgres 并没有内嵌实现美式英语排序规则因此使用en_US.UTF-8语言环境创建的数据库依赖于外部库。更具体地说Postgres 过去只是简单地调用 glibc 中的strcoll()在 Linux 系统上并相信返回的任何答案。这种信任多年来一直有效。然后有一天它失效了。世界改变的那一天那么glibc 2.28 中究竟发生了什么变化考虑一个使用en_US.UTF-8语言环境的简单例子。在更新之前包含特殊字符的字符串排序如下a, $a, a$, A, b, $b, b$, B在 glibc 2.28 之后这些相同的字符串排序如下$a, $b, a, A, a$, b, B, b$这可不是一个小调整。包含标点符号、大小写混合和特殊字符的字符串的相对位置发生了巨大变化。另一个有据可查的例子a-a和aa的排序顺序在新旧版本之间直接翻转了。包含带连字符、下划线或货币符号的数据现在经历了看似不一致的排序规则。这不是 glibc 中的一个 bug。glibc 开发者正在纠正多年来与 Unicode 标准累积的偏差。新的排序顺序可以说是更正确的。但正确性对于基于错误假设建立的索引来说毫无安慰作用。一场无声灾难的剖析然而为什么这会导致这么多麻烦让我们看看 Postgres B-tree 索引如何处理文本。当 Postgres 在文本列上构建索引时它会根据当前激活的排序规则对值进行排序并按该顺序存储它们。Postgres 基于这些结果构建实际的磁盘树结构。之后Postgres 通过将搜索词与存储的键进行比较来导航 B-tree根据排序规则判断哪个字符串排在前面来决定是向左还是向右遍历树。想象一下底层的库改变了关于哪个字符串排在前面想法。索引的物理布局反映了旧的排序顺序但每个新的比较都使用了新的排序顺序。Postgres 在应该向右走的时候向左走或者应该向左走的时候向右走之前有效的数据变得不可见。该行仍然在表中顺序扫描仍然可以找到它但索引查找会完全错过它。后果从这里开始向外蔓延不可见的行。使用索引扫描的查询可能会悄悄跳过现有的行。SELECT可能返回 999 行而实际上有 1000 行匹配谓词。幻象唯一性冲突。由 B-tree 索引支持的唯一约束可能无法检测到实际的重复项因为索引遍历找不到现有条目。或者它们可能因为遍历到了错误的节点而错误地拒绝有效的条目。错误的查询结果。任何依赖带有文本列的ORDER BY的查询在升级前后会产生不同的结果。Merge Join依赖于两个输入都按完全相同的方式排序可能会静默地产生不正确的结果。复制分歧。如果主库运行一个 glibc 版本而副本运行另一个版本那么针对相同数据执行的相同查询会产生不同的结果。2014 年的 TripAdvisor 事件就展示了使用流复制的这种确切场景尽管当时很少有人理解其全部含义。所有这些都在悄无声息中发生。没人知道也不可能知道出了问题直到为时已晚。多米诺骨牌效应glibc 2.28 版本并没有同时影响到所有的 Linux 发行版。相反它在大约一年的时间里随着每个发行版按照自己的时间表采用它而向外蔓延2018年10月Fedora 29 和 Ubuntu 18.10 随 glibc 2.28 一起发布。2019年3月Debian PostgreSQL 维护者 Christoph Berg 在 debian-glibc 邮件列表中发出警报将情况描述为危急。他提议为使用 PostgreSQL 集群的 Debian 用户提供自动警告。2019年5月RHEL 8 和 CentOS 8 随 glibc 2.28 一同到来从 RHEL 7 中的 glibc 2.17 跃升。这在单次升级周期中跨越了 11 个版本。2019年7月Debian 10 (Buster) 紧随其后。RHEL 的跳跃尤其残酷。许多企业环境运行在 CentOS 或 RHEL 上从版本 7 升级到版本 8 的操作系统升级只是迟早的事。没有人预料到一次常规的发行版升级会悄悄损坏他们的数据库索引。Arch Linux 用户一如既往地走在最前沿是煤矿中的金丝雀。Daniel Verite 在 2018 年 8 月发表了《当心你的下一次 glibc 升级》这是最早的公开警告之一。Postgres Wiki 创建了专门的页面来追踪不断变化的局势。来自 Crunchy Data、Citus Data 和 CYBERTEC 的博客文章紧随其后每篇都强调了同样令人不安的事实如果你升级了 glibc 并且没有重建索引你的数据可能已经损坏。定位损失对于任何受影响的系统第一步是识别有风险的索引。任何使用依赖于语言环境的排序规则C或POSIX以外的任何排序规则的text、varchar、char或citext列上的 B-tree 索引都可能是损坏的。Postgres 社区确定了一个诊断查询如下所示SELECTindrelid::regclassAStable_name,indexrelid::regclassASindex_name,collnameAScolumn_name,pg_get_indexdef(indexrelid)ASindex_definitionFROM(SELECTindexrelid,indrelid,indcollation[i]AScollFROMpg_index,generate_subscripts(indcollation,1)g(i))sJOINpg_collation cONcollc.oidWHEREcollproviderIN(d,c)ANDcollnameNOTIN(C,POSIX);该查询返回的每个索引都需要重建。对于 Postgres 12 及更高版本这意味着REINDEXINDEXCONCURRENTLY index_name;CONCURRENTLY选项被证明是生产系统的救命稻草因为它允许在不锁定整个表的情况下完成重建。对于那些被困在 Postgres 11 或更早版本的用户解决办法更加丑陋并发创建一个替换索引删除旧索引然后重命名新索引。主键索引和唯一约束使得这尤其痛苦。问题的规模是惊人的。一个有数百个表和数千个文本索引的数据库需要重建每一个。而且这不是一次性修复。任何未来更改排序规则的 glibc 升级都需要重复这个过程。似曾相识具有讽刺意味的是glibc 2.28 事件甚至不是 glibc 第一次在 Postgres 中导致索引损坏。2015 年的几个 glibc 版本附带了一个有问题的strcoll()实现它产生的结果与strxfrm()不一致同时违反了 ISO C90 和 POSIX 标准。Postgres 9.5 引入了缩写键来加速文本索引构建而 glibc 的 bug 导致它们产生了损坏的索引。Postgres 9.5.2 中的修复是完全禁用非 C 语言环境的缩写键这是一个持续到今天的基于 libc 的排序规则的性能回归。那时的用户也需要REINDEX。三年内发生两起重大事件都是由同一个根本问题引起的Postgres 信任一个外部库来执行关键操作而该库的行为既不稳定也无法保证。答案已昭然若揭。摆脱 glibc 的漫漫长路Postgres 社区对这些事件的回应是慎重而坚定的在近十年的渐进式进展中展开。Postgres 10 (2017)引入了对 ICU (International Components for Unicode) 作为替代排序规则提供者的初步支持。Peter Eisentraut 在这方面的工作是先见之明正好在 glibc 2.28 发布前一年完成。这是第一次可以创建由 ICU 而非 libc 支持的排序规则CREATECOLLATION german(providericu,localede-DE);ICU 独立于操作系统维护自己的排序规则数据。ICU 通过严格的版本控制系统更新其规则这意味着 Postgres 可以检测到变化并发出警告。Postgres 13 (2020)为 glibc 添加了排序规则版本跟踪。Postgres 开始在索引创建时记录排序规则版本并在底层版本发生变化时发出警告。这是针对该问题的第一个真正的预警系统。它无法防止损坏但至少日志能说明全部问题。Postgres 15 (2022)是一个许多人期待的事件ICU 现在可以用作整个数据库集群的默认排序规则提供者initdb --locale-providericu --icu-localeen-US在此之前ICU 仅可用于单个排序规则对象这既不方便又容易出错。Postgres 17 (2024)提供了许多人认为是真正解决方案的东西builtin排序规则提供者。该提供者将排序规则逻辑直接编译到 Postgres 自身中没有外部的 glibc 或 ICU 依赖。builtin提供者附带两个主要的排序规则pg_c_utf8: 使用 Unicode 码点排序配合与 POSIX 兼容的模式匹配和简单的大小写映射。pg_unicode_fast: 使用 Unicode 码点排序配合完整的 Unicode 大小写映射和标准模式匹配行为。两者都保证在 Postgres 的主版本内是不可变的。操作系统更改了我的排序顺序这整类 bug 在使用这些排序规则时根本不会发生。今日初始化集群的正确方法尽管有这些历史但截至 Postgres 18glibc 仍然是默认的排序规则提供者。新初始化的集群将使用 glibc 库除非另有指定。这意味着每个新数据库都可能面临与 glibc 2.28 导致的同类问题只是在等待下一个主要的库更新来触发它。因此我建议在创建新集群时始终指定builtin提供者initdb --locale-providerbuiltin--localeC.UTF-8两个标志就消除了整个类别的数据损坏风险。C.UTF-8语言环境提供了正确的 UTF-8 字符处理同时按 Unicode 码点顺序排序没有任何潜在的意外。对于在现有集群中创建数据库的用户同样的原则适用CREATEDATABASEmydb LOCALE_PROVIDERbuiltin BUILTIN_LOCALEC.UTF-8TEMPLATEtemplate0;当这样做时必须指定template0作为模板数据库因为通常不可能使用与源数据库不同的排序规则。在 Postgres 项目更改默认值之前并且正在进行关于这一点的讨论每个 DBA 都需要在每个新集群或新数据库中将此作为一个有意识的选择。没有免费的午餐如果新的内部提供的排序规则这么好为什么不是每个人都在使用它们第一个原因是很少有人会停下来考虑这个问题。他们可能相信这个问题正在被处理并将在未来版本中得到解决而且会像魔术一样发生。没有人愿意面对数据迁移的丑陋现实。也许他们只是错过了那次风波的新用户。另一个原因更加微妙。pg_c_utf8和pg_unicode_fast都按 Unicode 码点值排序。这本质上是 UTF-8 编码文本的字节顺序排序。它是确定性的、快速的并且非常适合索引。但它不符合人类在大多数语言中对语言学上正确排序的期望。考虑德语中的名字SELECTnameFROM(VALUES(Müller),(Muller),(Ötzi),(Ozzy),(Über),(Upper))ASt(name)ORDERBYnameCOLLATEpg_c_utf8;name--------Muller Müller Ozzy Upper Ötzi Über然而一个以德语为母语的人会期望这样的排序SELECTnameFROM(VALUES(Müller),(Muller),(Ötzi),(Ozzy),(Über),(Upper))ASt(name)ORDERBYnameCOLLATEde-x-icu;name--------Muller Müller Ötzi Ozzy Über Upper对于大多数应用工作负载来说这实际上并不重要。API 返回 JSON前端在客户端排序数据搜索操作关心的是匹配而不是排序。但是对于直接向用户显示排序列表的应用程序例如目录、产品列表、报表等语言学的排序顺序仍然很重要。应用语言学排序这正是 ICU 发挥价值的地方。集群可以为了安全性和性能而运行在builtin提供者上然后在必要的地方精确地应用 ICU 排序规则。有两种方法。列级别排序规则当某个特定列总是需要语言学排序时是理想的选择CREATETABLEcustomers(idserialPRIMARYKEY,nametextCOLLATEde-x-icu,emailtext);现在name列总是根据德语语言学规则排序而数据库中的所有其他文本列使用安全的、确定性的builtin排序规则。name列上的索引将使用 ICU 排序规则因此该特定索引变得依赖于 ICU 的版本管理。ICU 在版本管理方面通常比 glibc 更严格所以这并不那么冒险。表达式级别排序规则更适合于偶发的语言学排序SELECTnameFROMcustomersORDERBYnameCOLLATEde-x-icu;这仅针对这个特定的排序操作应用德语 ICU 排序规则。底层的列及其索引仍然保持在builtin排序规则上。这实现了语言学排序而不会改变存储或索引行为。因此这是最安全的方法尽管由于需要额外的语法而略显不便。历史集群的幽灵新集群很容易。在init时指定builtin提供者然后继续你的生活。但是那些已经在运行基于 libc 排序规则的数百万现有 Postgres 集群呢它们不会就此消失而且数据库创建后无法更改其默认排序规则。话虽这么说但还是有几个选择迁移到新集群。使用pg_dump和pg_restore或逻辑复制将数据移动到一个使用builtin提供者初始化的新集群中。这是最干净的方法但需要规划和潜在的停机时间。手动迁移到 ICU 或builtin排序规则。在基于 libc 的集群中各个列可以使用不同的排序规则。通过将表或受影响的列迁移到更安全的排序规则来分阶段进行部署并始终使用该排序规则创建新列。为了永久修复未来的集群迁移仍然是必要的但这个过程提供了一条通往安全的路径。监控排序规则版本。Postgres 13 会在排序规则的底层版本发生变化时记录警告。注意这些警告它们是在尖叫着告诉你REINDEX是必要的。留意以下消息WARNING: collation xx-x-icu has version mismatch DETAIL: The collation in the database was created using version 1.2.3.4, but the operating system provides version 2.3.4.5.每次主要操作系统升级后都进行REINDEX。对于基于 glibc 的集群最安全的方法是在每次主要的发行版升级后重建每个文本索引。没有例外。使用前面的诊断查询来识别受影响的索引并对识别出的候选项执行REINDEX INDEX CONCURRENTLY。继续前进glibc 2.28 事件改变了整个 Postgres 社区对外部依赖的思考方式。在 2018 年之前操作系统库更新可能导致数据库损坏的想法只有少数人担心。而它悄无声息地发生——让损坏持续数周、数月甚至数年——更是雪上加霜。Postgres 社区以其典型的英雄主义方式做出了回应。排序规则版本跟踪、ICU 提供者支持以及最终的builtin排序规则展示了 Postgres 开发者愿意为解决问题付出多大的努力。这并不是完全拒绝信任操作系统提供的库但鉴于当时的情况与外部排序规则资源解耦仍然是一个审慎的反应。然而我们不应该固步自封即使是现在glibc 仍然是默认选项这表明教训并未被所有人完全吸收。每次运行initdb而不带上--locale-providerbuiltin都是在创建另一个承载着同样风险的集群这个风险曾几乎给许多人带来灾难。我个人在 2025 年还遇到过带有此类损坏的集群这已经是事情发生整整七年后了。为什么要延续这个错误呢所以下次你启动一个 Postgres 集群时帮自己一个忙使用builtin语言环境提供者。未来的你那个刚刚不假思索地升级到最新 Ubuntu LTS 的你会感谢你的。