• PostgreSQL的学习心得和知识总结（一百四十八）|查看 PostgreSQL 17 中的新内置排序规则提供程序

    目录结构

    注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下：

    1、参考书籍：《PostgreSQL数据库内核分析》
    2、参考书籍：《数据库事务处理的艺术：事务管理与并发控制》
    3、PostgreSQL数据库仓库链接，点击前往
    4、日本著名PostgreSQL数据库专家 铃木启修 网站主页，点击前往
    5、参考书籍：《PostgreSQL中文手册》
    6、参考书籍：《PostgreSQL指南：内幕探索》，点击前往
    7、Looking at the new built-in collation provider in PostgreSQL 17，点击前往

    1、本文内容全部来源于开源社区 GitHub和以上博主的贡献，本文也免费开源（可能会存在问题，评论区等待大佬们的指正）
    2、本文目的：开源共享 抛砖引玉 一起学习
    3、本文不提供任何资源 不存在任何交易 与任何组织和机构无关
    4、大家可以根据需要自行 复制粘贴以及作为其他个人用途，但是不允许转载 不允许商用 （写作不易，还请见谅 💖）
    5、本文内容基于PostgreSQL master源码开发而成

    查看 PostgreSQL 17 中的新内置排序规则提供程序

    文章快速说明索引

    学习目标：

    做数据库内核开发久了就会有一种 少年得志，年少轻狂 的错觉，然鹅细细一品觉得自己其实不算特别优秀 远远没有达到自己想要的。也许光鲜的表面掩盖了空洞的内在，每每想到于此，皆有夜半临渊如履薄冰之感。为了睡上几个踏实觉，即日起 暂缓其他基于PostgreSQL数据库的兼容功能开发，近段时间 将着重于学习分享Postgres的基础知识和实践内幕。

    学习内容：（详见目录）

    1、查看 PostgreSQL 17 中的新内置排序规则提供程序

    学习时间：

    2024年07月14日 15:19:37

    学习产出：

    1、PostgreSQL数据库基础知识回顾 1个
    2、CSDN 技术博客 1篇
    3、PostgreSQL数据库内核深入学习

    注：下面我们所有的学习环境是Centos8+PostgreSQL master+Oracle19C+MySQL8.0

    postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-21), 64-bit
(1 row)

postgres=#

#-----------------------------------------------------------------------------#

SQL> select * from v$version;          

BANNER        Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production	
BANNER_FULL	  Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production Version 19.17.0.0.0	
BANNER_LEGACY Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production	
CON_ID 0


#-----------------------------------------------------------------------------#

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.06 sec)

mysql>

    在最近发布的测试版，PostgreSQL 17 的众多新功能中，有一个内置的 UTF-8 语言环境和带有二进制字符串比较的排序规则。在这篇文章中，让我们看看它为什么有趣、如何使用它以及它的性能如何。

    第三个排序规则提供程序

    在版本 17 之前，我们可以在两个提供程序之间进行选择：操作系统附带的 libc 库和可选的 ICU 库。v17 为 Postgres 本身实现的排序规则添加了第三个提供程序。

    声明将其描述为：

    PostgreSQL 17 包含一个内置排序规则提供程序，它提供与 C 排序规则类似的排序语义，但使用的是 UTF-8 编码而不是 SQL_ASCII。此新排序规则保证是不可变的，从而确保无论 PostgreSQL 安装在什么系统上运行，排序的返回值都不会改变。

    有两种内置排序规则，均适用于 UTF-8，如下：

    SELECT
  collnamespace::regnamespace,
  collname,
  colllocale,
  pg_encoding_to_char(collencoding) as encoding
FROM pg_collation WHERE collprovider='b';  -- 'b' = 'built-in'

 collnamespace | collname  | colllocale | encoding 
---------------+-----------+------------+----------
 pg_catalog    | ucs_basic | C          | UTF8
 pg_catalog    | pg_c_utf8 | C.UTF-8    | UTF8

    ucs_basic 在以前的版本中已经与libc提供程序一起存在，并且与 UTF8 数据库中的C排序规则相同。它在版本 17 中也一样，即：

    • 对于字符串比较，它使用字节顺序，没有任何语言规则。
    • 对于字符分类（即知道某个字符是字母、数字、小写字母还是大写字母……），它根据内部表来回答127 以下的任何代码点（ASCII 字符）；对于其余的，则它对这些问题中的任何一个回答No

    因此，尽管从技术上讲，在 UTF-8 数据库中使用 ucs_basic 或C是可行的，但对于非英语内容来说，这并不适用。

    新的 C.UTF-8 内置语言环境及其 pg_c_utf8 排序规则解决了这个问题，在发布时为 Unicode 库的所有代码点提供了正确的结果（截至 Unicode 15.1，几乎有 150000 个），从而允许对所有字母表进行正确的大小写转换和正则表达式。

    现在，libc 和 ICU 提供的 Unicode 排序规则也是如此，但重点是新的语言环境/排序规则在相同语言环境中提供了以下功能的组合：

    • 快速、不可变的二进制排序（没有 ICU 语言环境提供此功能）
    • 操作系统独立性（除"C"外，没有 libc 语言环境提供此功能）
    • 完整的 Unicode 字符分类（"C"缺少此功能）

    让我们用维恩图来说明这一点：

    在这里插入图片描述

    请注意，ICU 独立于操作系统并不意味着操作系统升级对 ICU 是透明的。操作系统发行版往往在发行时附带最新版本的 ICU 库，因此升级操作系统通常意味着同时升级 ICU，除非您自己编译它。与 ICU 相关的操作系统独立性意味着，当在不同操作系统上使用相同版本的 ICU 时，排序规则在这些系统上的行为相同。

    如何使用它

    要在创建实例或数据库时默认使用新排序规则，我们选择语言环境名称为 C.UTF8 且提供程序为builtin（请注意不要将其与 libc 的 C.UTF-8 或 C.utf8 语言环境混淆）。

    在实例级别：

    initdb --locale-provider=builtin --locale=C.UTF8 -D /usr/local/pgsql/data

    在这种情况下，该实例中创建的所有数据库将默认使用该新语言环境。

    否则，在使用 libc 或 ICU 提供程序初始化的现有实例上，要使用新的内置语言环境创建特定数据库，我们应该这样做：

    CREATE DATABASE test locale_provider='builtin' builtin_locale='C.UTF8' template='template0';

    如果您想知道为什么我们需要强制使用 template0（而不是默认的 template1），那是因为服务器假设我们可能已将本地化数据添加到 template1，而这些数据可能与新数据库的区域设置不兼容。template0 包含与区域设置无关的不可修改的基本目录，因此它是一个安全的选择，而 template1 是 template0 加上 DBA 所做的任何添加。

    最后，在默认情况下不使用该新区域设置的数据库内部，我们可以在 SQL 级别的列或表达式上添加 COLLATE pg_c_utf8 子句，例如：

    CREATE INDEX idx ON tablename using btree(columnname COLLATE pg_c_utf8);

    然后，查询规划器将考虑这样的索引，用于如下查询：

    SELECT * FROM tablename WHERE columnname = 'some-value' COLLATE pg_c_utf8;

    索引和搜索性能

    按字节比较比按语言比较占用更少的 CPU。让我们用互联网电影数据库的一些数据来说明这一点：

    name_basics 是一个包含约 1300 万行的表。primaryName 列包含参与电影制作的人员的姓名，包含约 1000 万个不同的值。这是一个使用不同排序规则进行排序和搜索的好例子。

    \d name_basics
                        Table "public.name_basics"
      Column       |         Type         | Collation | Nullable | Default 
-------------------+----------------------+-----------+----------+---------
 sn_soundex        | character varying(5) |           |          | 
 deathYear         | integer              |           |          | 
 primaryProfession | text                 |           |          | 
 nconst            | integer              |           |          | 
 primaryName       | text                 |           |          | 
 birthYear         | integer              |           |          | 
 s_soundex         | character varying(5) |           |          | 
 ns_soundex        | character varying(5) |           |          | 
 knownForTitles    | text                 |           |          | 
Indexes:
    "idx" btree ("primaryName")

    让我们看看在 Linux Debian 12 系统（GNU libc 2.36，ICU 72）上，在使用不同区域设置创建的数据库上建立primaryName的 btree 索引需要多长时间。

    在这里插入图片描述

    如何解释这些差异：

    • libc 的 en_US.utf8 是最慢的：它进行语言比较时不使用缩写键(note1)
    • ICU 的 en-x-icu 进行语言比较，但使用缩写键优化
    • libc 的 C.utf8 进行更快的二进制比较，但 Postgres 并不 知道，因此它没有使用优化的快速路径，而是调用较慢的通用函数
    • C 和新的 C.UTF-8 语言环境针对最快执行进行了优化，并且性能同样出色

    在索引该列后，让我们看看查询在数据库之间的执行时间比较。对于该测试，我们使用 pg_bench 和查询，当时搜索 1000 个值：

    SELECT count(*) FROM name_basics WHERE "primaryName" IN
(
...<list of 1000 names previously randomly choosen from the table >...
)

    此查询的计划是仅索引扫描Index Only Scan，匹配 6572 行。平均执行时间如下：

    在这里插入图片描述

    差异大致类似于索引创建，只是在索引时 en_US.utf8 和 en-x-icu 之间的差距更大。我的理论是，这是因为 en_US.utf8 禁用了缩写键优化，而 ICU 排序规则启用了缩写键优化。缩写键用于排序，但不用于查找。

    depesz 博客上的一篇最新文章（如果使用默认语言环境，您会在表中留下多少速度？）也显示了比较这些排序规则的基准，但使用 COLLATE 子句，而不是像我为上面报告的数字那样在数据库级别定义语言环境。

    Depesz 指出，二进制比较的表现更好，正如预期的那样，但内置的 pg_c_utf8 排序规则的表现明显差于"C"排序规则，他想知道为什么。答案来自 Jeff Davis 在 pgsql-hackers 列表中的后续内容：加快排序规则缓存。简而言之，与非默认排序规则的比较目前意味着可以更好地优化缓存查找。希望这种情况能很快得到改善，但仍然要记住，为了更快地获得结果，数据库区域设置应优先于 COLLATE 子句。

    结果排序

    二进制排序规则的主要缺点是它们不按“人性化方式”进行排序。例如，假设我们想在排序列表中输出所有以Adèle或Adele开头的 primaryName。我们将进行如下查询：

    SELECT DISTINCT "primaryName" FROM name_basics
 WHERE "primaryName" ~ '^Adèle' OR "primaryName" ~ '^Adele'
 ORDER BY "primaryName" ;

    通过二进制排序，我们得到以下输出：

            primaryName          
------------------------------
 Adele
 Adele & The French StarKids
 Adele Aalto
 Adele Abbott
 Adele Abinante
[...]
 Adèle de Fontbrune
 Adèle de Mesnard
 Adèle de la Fuente
 Adèle van Biljon
 Adèle-Elise Prévost
(1342 rows)

    Adele”被归类在列表的第一部分，而Adèle 则位于第二部分。这是因为 è 字母的代码点 (U+00E8) 大于 e 字母的代码点 (U+00065)。

    使用语言排序规则（如 fr_FR.utf8）时，输出效果更好：

             primaryName          
------------------------------
 Adele
 Adèle
 Adele Aalto
 Adele Abbott
 Adele Abinante
 Adele Abou Ali
 Adele Aburrow
 Adele Adams
 Adele Adderley
 Adele Addison
 Adele Addo
 Adele Adeshayo
 Adele Adkins
 Adèle Ado
[...]
 Adele Zeiner
 Adele Zin
 Adele Zoppis
 Adèle Zouane
 Adele Zupicic
(1342 rows)

    在这个列表中，重音字母和非重音字母 e 在一起，姓氏决定了排序。这显然是我们（人类）喜欢看到的。

    要在二进制排序数据库中获得这种排序，应将带有语言学家排序规则的 COLLATE 子句添加到 ORDER BY 中。它可以是：

    • ORDER BY "primaryName" COLLATE "en_US.utf8"（或其他 libc 排序规则）。它仅在数据库中存在该排序规则时才有效，这取决于操作系统和安装，因此不可移植。它还会强制使用特定的语言/国家/地区，这可能不是应用程序想要的
    • ORDER BY "primaryName" COLLATE "en-x-icu"，使用 ICU 排序规则。优点是每个启用 ICU 的 Postgres 实例都会有此排序规则，因此它几乎是可移植的。但是，这再次强制使用特定的语言
    • ORDER BY "primaryName" COLLATE "unicode"，自 Postgres 16 以来就存在。它调用旨在适用于所有语言的root排序规则。对于 Postgres 15 及更早版本，我们可以使用不太容易记住的und-x-icu

    实际上，现代客户端应用程序可能配备了排序功能，使它们不依赖于数据库排序。例如，Javascript 现代应用程序可能使用 Navigator.language 和 Intl.Collat​​or 根据浏览器的语言进行排序。对于大多数开发人员来说，使用 Javascript 对列表进行排序比找到与浏览器语言匹配的数据库排序规则并将其注入 SQL 查询更为自然。如果结果列表可能太大而无法由浏览器处理，那么当然，数据库端排序又回到了游戏中。

    二进制排序文本索引的另一个好处

    除了速度更快之外，二进制排序索引还直接支持左锚left-anchored搜索。在上一个查询中，如果我们在primaryName上只有一个语言排序索引，那么我们就会得到一个缓慢的顺序扫描：

    EXPLAIN ANALYZE  select distinct "primaryName" from name_basics
  where "primaryName" ~ '^Adèle' or "primaryName" ~ '^Adele'
  order by "primaryName";

    Unique  (cost=243279.95..243541.75 rows=2174 width=14) (actual time=4354.978..4356.406 rows=1342 loops=1)
   ->  Gather Merge  (cost=243279.95..243536.32 rows=2174 width=14) (actual time=4354.978..4356.212 rows=1398 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Unique  (cost=242279.92..242285.36 rows=1087 width=14) (actual time=4346.459..4346.578 rows=466 loops=3)
               ->  Sort  (cost=242279.92..242282.64 rows=1087 width=14) (actual time=4346.453..4346.482 rows=487 loops=3)
                     Sort Key: "primaryName"
                     Sort Method: quicksort  Memory: 25kB
                     Worker 0:  Sort Method: quicksort  Memory: 25kB
                     Worker 1:  Sort Method: quicksort  Memory: 25kB
                     ->  Parallel Seq Scan on name_basics  (cost=0.00..242225.11 rows=1087 width=14) (actual time=9.747..4345.114 rows=487 loops=3)
                           Filter: (("primaryName" ~ '^Adèle'::text) OR ("primaryName" ~ '^Adele'::text))
                           Rows Removed by Filter: 4345599
 Planning Time: 0.186 ms
 Execution Time: 4356.494 ms

    文档中为加速这一过程给出的建议是使用 varchar_pattern_ops 创建第二个索引：

    运算符类 text_pattern_ops、varchar_pattern_ops 和 bpchar_pattern_ops 分别支持类型 text、varchar 和 char 上的 B 树索引。与默认运算符类的区别在于，值是严格逐个字符进行比较，而不是根据特定于语言环境的排序规则进行比较。这使得这些运算符类适合在数据库不使用标准“C”语言环境时由涉及模式匹配表达式（LIKE 或 POSIX 正则表达式）的查询使用。例如，您可以像这样索引 varchar 列：CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

    好消息是，新的 C.UTF-8 内置语言环境和 pg_c_utf8 排序规则具有与标准"C"语言环境相同的优势：它们可以直接用于左锚搜索，如具有此类索引的执行计划所示：

    Unique  (cost=162.45..175.49 rows=2607 width=14) (actual time=30.616..31.048 rows=1342 loops=1)
   ->  Sort  (cost=162.45..168.97 rows=2608 width=14) (actual time=30.612..30.699 rows=1461 loops=1)
         Sort Key: "primaryName"
         Sort Method: quicksort  Memory: 49kB
         ->  Bitmap Heap Scan on name_basics  (cost=10.44..14.46 rows=2608 width=14) (actual time=0.571..26.297 rows=1461 loops=1)
               Recheck Cond: (("primaryName" ~ '^Adèle'::text) OR ("primaryName" ~ '^Adele'::text))
               Filter: (("primaryName" ~ '^Adèle'::text) OR ("primaryName" ~ '^Adele'::text))
               Heap Blocks: exact=1451
               ->  BitmapOr  (cost=10.44..10.44 rows=1 width=0) (actual time=0.356..0.357 rows=0 loops=1)
                     ->  Bitmap Index Scan on idx  (cost=0.00..4.57 rows=1 width=0) (actual time=0.126..0.127 rows=268 loops=1)
                           Index Cond: (("primaryName" >= 'Adèle'::text) AND ("primaryName" < 'Adèlf'::text))
                     ->  Bitmap Index Scan on idx  (cost=0.00..4.57 rows=1 width=0) (actual time=0.227..0.227 rows=1193 loops=1)
                           Index Cond: (("primaryName" >= 'Adele'::text) AND ("primaryName" < 'Adelf'::text))
 Planning Time: 0.473 ms
 Execution Time: 31.132 ms

    结论

    让我们总结一下此表中的主要排序特征，如下：

    "C"
    or ucs_basic    		libc collations
    other than "C"    		ICU collationspg_c_utf8
    Portability between OSes?✅ Yes❌ No✅ Yes✅ Yes
    Bytewise comparisons?✅ Yes❓ Some❌ No✅ Yes
    Abbreviated keys?(note1) -❌ No✅ Yes-
    Transparent OS updates?(note2) ✅ Yes❌ No❌ No✅ Yes
    Unicode classification?❌ No✅ Yes✅ Yes✅ Yes
    Linguistic sort?❌ No✅ Yes✅ Yes❌ No
    Advanced comparisons?(note3) ❌ No❌ No✅ Yes❌ No

    Postgres 17 中的新内置提供程序提供了快速、可移植、更易于升级的 C.UTF-8 语言环境/pg_c_utf8 排序规则。虽然它没有 ICU 提供程序提供的更高级功能，但它很可能是 Unicode 数据库的良好默认语言环境。

    有关该主题的更多信息，我建议在 YouTube 上观看这个精彩的演示：Jeremy Schneider 和 Jeff Davis 的从 A 到 Z 的排序规则 (PGConf.dev 2024)。Jeremy 首先列出了一系列与排序规则相关的非直观事实和要点（我注意到其中有一些对这个博客的引用 🤓 ），然后 Jeff（Postgres 17 新功能的作者）详细介绍了可以使用现有排序规则做什么以及新语言环境如何适应该上下文。

    备注

    1. 缩写键是字符串的二进制表示，用于加速使用语言排序规则的排序。请参阅 Peter Geoghegan 的帖子缩写键：利用局部性来提高 PostgreSQL 的文本排序性能（自 2015 年起），其中解释了该功能。理论上，libc 排序规则也可以使用它们，但发现实现存在错误，因此不适用于索引。

    2. 严格来说，当操作系统更新意味着 Unicode 升级时，它们并不透明。即使二进制排序解决了主要问题，Unicode 中的代码点的添加在理论上也可以改变涉及正则表达式、大小写转换或规范化规则的检查约束的结果。

    3. 高级比较是指 ICU 非确定性排序规则允许的所有功能，其中包括不区分大小写和不区分重音的比较。

  • 相关阅读:
    数据仓库(7)数仓规范设计
    每日一题 1458两个子序列的最大点积
    Mysql事务详解
    奔走相告，一波福利来袭！ “Python量化场景编程技术手册”出炉！
    数据库DDL练习，包含建表语句
    [架构之路-51]：架构师 - 用系统化、结构化思维解决复杂难搞的软件故障问题 - 马克思主义哲学在软件系统中的应用
    写代码不写注释 ＜ 写代码不说环境 ＜ 写代码不给数据 ＜ 写论文不给代码
    驱动开发--创建设备文件--控制LED灯
    Toxel 与 PCPC II
    华为云云耀云服务器L实例评测｜带你从0认识云服务器
  • 原文地址：https://blog.csdn.net/weixin_43949535/article/details/140431850