PostgreSQL

声明式分区让 PostgreSQL 对外提供一张逻辑表，同时把行存放在不同分区中。父表负责写入路由，并作为统一查询入口；真正持有数据的是物理分区。它的主要收益往往来自生命周期管理和选择性访问，但选择分区时，也会影响键、查询形态和运维锁，不能只把它看作拆文件。 逻辑父表与物理子表 分区父表本身没有行存储。每个已挂载分区保存符合边界的行，可以有自己的存储参数、索引和维护活动。分区索引也类似，是各物理分区索引的集合，并不是在父表上再保存一整份相同数据。理解这一点，有助于区分统一入口与实际占用磁盘的关系。 范围、列表和哈希分区使用不同划分规则。应根据真实访问和保留要求选择键：按月保存事件可能适合范围分区，其他负载可能需要不同分布。声明式分区本身不会把这些分区自动分散到独立数据库服务器。本地分区仍共享所在数据库的资源和事务环境，不能因为表被拆开，就推断它已经实现分布式分片。 保持父表名字稳定，可以简化应用入口，但不能证明应用代码完全不用改。查询需要暴露适合的分区键限制，才可能从裁剪受益；主键与外键引用也可能需要重新设计。原本按全局唯一标识操作的调用，必须保留同样的身份语义，不能在物理拆分过程中悄悄改变其含义。 分区键不只影响路由 分区表上的唯一或主键约束必须包含全部分区键列，而且这些约束要求分区键不含表达式。各分区的独立索引，不能自行证明其他分区中不存在相同键。因此，day 与 id 的复合主键，并不保证 id 在不同日期之间仍然全局唯一。 从单表全局 id 主键迁移时，这个差别尤其重要。只是把日期加入主键，可能弱化原有身份契约。更改之前，应检查调用方、引用关系和重复防护要求。分区键不是可以完全脱离公开数据模型进行调整的纯物理参数，否则原来按 id 更新一行的操作，可能开始匹配多行。 修改分区键可以把行移动到另一个分区。目标分区必须存在并接受该行，索引和约束仍然生效。这是真实数据变更，不是免费的元数据修改。如果保留策略已经移除了目标范围，迟到的修正可能失败，即使逻辑父表仍然存在。生命周期设计应同时考虑正常新增和历史更正。 坏掉的样子 DETACH 把一张表从分区层级移除，但保留该表及其数据。通过父表查询时不再看到它，脱离后的关系仍可单独检查或导出。DROP 则会连同分区数据一起删除。脱离不等于删除，这两种操作也都不会自动生成已经验证过的归档或备份，不能因为维护命令成功就认定数据已妥善保留。 分区维护需要锁。普通 detach 需要父表上的强锁，可能等待长时间运行的操作，也会影响并发访问。PostgreSQL 提供锁行为不同的并发脱离形式，但有额外限制，例如不能放在显式事务块中执行。应按照实际契约选择操作，不能把“元数据操作”理解为“一定不会阻塞”。 分区过多也会增加规划和内存成本，没有一个普遍适用的行数阈值能证明分区一定划算。应测量有代表性的读取和维护操作，在写入需要前创建未来分区，并明确迟到数据与保留策略如何配合。对于较小且索引合理的表，大量细碎分区可能只增加管理工作，而没有带来足够收益。 快速验证 把下面内容保存为 partition-lifecycle.sql，在可丢弃的 PostgreSQL 18 数据库中执行 psql -X -v ON_ERROR_STOP=1 -f partition-lifecycle.sql。实验使用临时分区和普通事务内 detach，最后回滚移除整个测试环境。 ...

公用表表达式在一条 SQL 内为查询结果命名。它可以先说明数据总体和中间计算，再写最终 SELECT，让复杂报表更容易阅读。这个名字不是持久表，也不自动承诺缓存结果、固定扫描次数或按顺序执行的一组命令。理解 CTE 时，应把可读结构与执行方式分别检查。 先确定作用域，再讨论执行 WITH 子句只属于当前语句。后续语句不能继续引用它的名字，执行结束后也没有一个可供建立索引或查询的 schema 对象。如果计算结果或查询定义需要保留到后续语句继续使用，应选择临时表、视图或其他合适的数据库对象。CTE 适合命名结果与语句作用域相匹配的工作，不能靠命名制造跨请求缓存。 现代 PostgreSQL 可以把非递归、没有 volatile 函数的 SELECT CTE 折叠进外层查询。默认情况下，只引用一次时会进行这种合并，使外层限制参与共同优化。因此，外层按主键查找时，条件可以直接作用于底层表的索引，而不必先生成独立中间结果再过滤。 同一个符合条件的 CTE 被引用多次时，PostgreSQL 默认通常会物化它。这能减少重复昂贵计算，但也可能阻止某个引用位置上的选择性条件被推入共享计算。这个区别从 PostgreSQL 十二开始适用；认为所有 WITH 都是固定优化边界，描述的是旧行为，不能覆盖当前默认规则。应根据实际版本和引用次数检查，而不是依赖旧经验。 物化是一项取舍 MATERIALIZED 请求独立计算。NOT MATERIALIZED 允许多次引用时也进行折叠，可能重复一部分工作，但让每次使用只访问自己需要的数据。对于递归或不是无副作用 SELECT 的 CTE，PostgreSQL 会忽略 NOT MATERIALIZED；它不会因此允许任意复制写入或 volatile 操作。 物化的 SELECT CTE 也不一定完整算到末尾。消费方可能只请求部分结果，不过排序、关联等操作又可能要求读取更多。物化同样不等于为后续语句建立一张带索引的可复用表。决定强制边界之前，应查看计划并测量真实负载，不能因为“物化”听起来像缓存就默认收益更好。 ...

EXPLAIN 展示 PostgreSQL 的执行计划，EXPLAIN ANALYZE 还会真正执行语句并测量工作量。最有用的比较，是同一个节点的估计行数与实际行数。两者都不能单独当成结论：估计来自模型，实测则只描述特定数据、缓存、并发和插桩条件下的一次执行。不能把前者称为谎言，也不能把后者当成脱离环境的永恒事实。 先比较同一种量 计划中的 cost 是相对成本单位，不是毫秒。两个成本值分别描述启动成本与完成成本，带 LIMIT 的查询可能更重视尽早得到前几行，因此选择不同计划。actual time 才使用毫秒。即使成本和耗时恰好都打印成一百，也不能把它们直接相减或求比例；它们本来就不表示同一件事。 一个节点被重复执行时，actual rows 和节点耗时是每次循环的平均值，需要结合 loops 理解总工作量。但不能把所有节点耗时简单相加，因为父节点已经包含子节点的工作，重复相加会重复计算。并行执行时更不能用这种方式猜总延迟。先沿树看输入输出、循环次数、过滤条件和行数流向，再决定哪个位置值得调查。 BUFFERS 区分 shared、local 和临时块活动。shared hit 表示 PostgreSQL 在共享缓冲区里找到了块；shared read 只表示需要读入，操作系统仍可能从页缓存满足它，不能直接断言每次都访问了物理磁盘。临时表使用本地缓冲区，排序或哈希溢出则可能读写临时文件。父节点计数包含子节点，逐行把所有计数加起来同样会夸大 I/O。 找到有意义的估计偏差 某个选择性条件估计只返回十行，实际却返回一万行，通常值得先调查，再考虑重写其上方的连接。统计信息陈旧、分布倾斜、列间相关、参数值变化或选择性模型能力不足，都可能造成偏差。ANALYZE 可以刷新统计信息，却不能保证每个估计精确，也不会自动修好所有慢计划。先看真实数据和缺失的信息，再决定是否调整统计目标或增加扩展统计。 行数估计准确也不能证明查询高效。缺少访问路径、昂贵表达式、排序溢出、锁等待和并发争用仍可能存在。反过来，小表或需要返回大量行的查询采用顺序扫描可能正合适，不应把“没用索引”一律判为错误。分析时应保留应用实际使用的参数和查询形状，避免用一个稀有值的漂亮计划替代整个生产分布。 坏掉的样子 最危险的误解，是把 ANALYZE 当成只读预演。UPDATE 会改变行，触发器会执行，锁会被获取，SELECT 也可能调用带副作用的函数。事务末尾执行 ROLLBACK 可以恢复事务性表变化，却不能一般性撤销序列前进或外部系统副作用。对语句影响范围不确定时，应使用可丢弃的数据集，不能靠“最后会回滚”就放心在生产运行。 ...

因为 pg_stat_statements 会把同一条查询形态的每一次执行都聚合起来，「这个页面慢」就变成了一张排好序的表。代价只是一行配置加一次重启；回报是你能知道上万种查询形态里，到底是哪一种在占用时钟。 真正重要的决定只有一个：按哪一列排序。按平均耗时排，只是把慢日志已经告诉过你的事再说一遍；按总耗时排，才能找出那个一直悄悄吃掉数据库四分之一时间的查询。 先归一化，再聚合 这个扩展并不原样保存你的 SQL。常量会被折叠成参数符号，于是 WHERE tenant_id = 42 和 WHERE tenant_id = 8712 合并成同一条记录，展示出来的文本是第一个产生该 queryid 的查询。编号规则是位置式的——符号从当前文本里最大 $n 的下一个数字开始，所以 PL/pgSQL 里的 SELECT i + 1 INTO j 会显示成 SELECT i + $2。列表的行为一样：只差 IN 元素个数的两条查询会被压成 IN ($1 /*, ... */)，调用次数记为 2。 ...

PostgreSQL 提供两种 JSON 存储类型，它们承担的表示成本不同。json 验证并保留输入文本，jsonb 把文本转换成拆解后的二进制值。两者都能提取字段和路径。选择类型时，需要回答的是是否必须保留文本形态、是否反复处理文档，以及真实查询需要哪些算子，而不是简单判断能不能读取内部字段。 保留文本还是保留结构值 json 会保留无意义空白、对象键的顺序和重复对象键。处理函数仍需要解析这些文本，遇到重复键时，以最后一个值作为有效值。因此，存储保留了重复项，并不代表每个消费方都会得出同样解释。不要把重复键当成应用契约中有意义的数据结构，否则跨语言解析时很容易产生分歧。 jsonb 去掉无意义空白和对象键顺序，同一个对象键只保留最后一次出现的值。这里说的是对象：数组仍然保留元素顺序和重复元素。数组的包含判断有自己的匹配规则，不能因为包含查询把两个数组视为匹配，就反推存储时发生了排序或去重。表示、相等比较和包含关系是三个需要分别理解的问题。 这种二进制格式属于 PostgreSQL，并不是 BSON，也不是可跨系统使用的签名格式。jsonb 输出的文本没有承诺遵循某个外部 JSON 规范化算法。如果签名覆盖原始请求字节，应另外保留这些字节，例如使用 bytea，并针对该表示验证签名。json 保留的是接收后的 JSON 文本；客户端编码转换和协议处理可能已改变网络上的原始字节，因此不能把它当成任意请求的逐字节归档。 两种类型都支持路径提取 两者都支持用 -> 提取 JSON 值，用 ->> 提取文本，以及用 #>、#>> 读取嵌套路径。读取 json 内部字段时，不需要先把整篇文档强制转换成 jsonb。性能区别在于重新解析文本还是处理已经拆解的表示，而不是 json 缺少这些操作符。 jsonb 还支持结构包含、键存在判断和 GIN 等索引策略。从两种类型提取出的标量表达式，也可以按实际需要建立适当的表达式索引。能否使用索引仍取决于准确的表达式、算子和负载，选了 jsonb 不代表任意嵌套条件都会变快。经常参与关联、约束或排序的字段，往往更适合独立的有类型列，以明确数据契约并方便优化。 ...

建议锁让应用使用一个标识来协调工作，不要求这个标识对应数据库里某一行。比如，多名工作者可以约定，同一时间只能有一个连接刷新某份报表。PostgreSQL 负责管理锁的持有与竞争，应用则负责定义标识的业务含义，以及哪些操作必须先拿锁才能执行。这个分工决定了它能提供的保障，也决定了绕过协议时会发生什么。 坏掉的样子 建议锁的键只能是一个大整数，或者两个普通整数，没有接受文本名字的函数重载。这两种键空间彼此独立。如果应用通过哈希把名称映射为数字，必须明确一致的编码规则，也要考虑碰撞；同一资源被不同算法映射到不同数字，同样会破坏协调。直接维护有文档的整数命名空间，通常比隐藏在各个调用方里的临时哈希规则更容易核对。 建议锁是参与者共同遵守的协议。忽略它的连接，仍然可以更新所谓受保护的表行；它不能代替唯一约束、事务或权限检查。会话锁在回滚后仍然保留，直到对应次数的解锁完成，或服务器结束该会话。事务锁则随事务结束释放。如果处于自动提交模式，一条语句取得的事务锁，在后续语句开始之前就已经释放，无法保护接下来才执行的工作。 快速验证 准备同一个 PostgreSQL 18 可丢弃数据库，开启甲、乙两个全新的 psql -X -v ON_ERROR_STOP=1 会话。为本实验预留下面的整数键，不要与已有业务约定混用。按照块的顺序切换终端。甲先创建计数器： CREATE SCHEMA advisory_demo; CREATE TABLE advisory_demo.counter (id integer PRIMARY KEY,n integer); INSERT INTO advisory_demo.counter VALUES (1,0); 甲传入显式文本类型的键，预期返回 42883，即不存在匹配的函数。故意报错这一段结束后恢复遇错停止设置： \set ON_ERROR_STOP off SELECT pg_advisory_lock('job-name'::text); \echo text_key_state :SQLSTATE \set ON_ERROR_STOP on 甲在事务里把同一把会话锁取两次，然后回滚。注意函数名使用的是会话级形式： ...

部分索引只为满足谓词的行保存索引项。需要的子集很小时，它可以缩小索引，但也可以表达只对这个子集生效的完整性规则。例如，同一个座位允许保留多条历史预订，却只能有一条当前有效预订。部分唯一索引可以把这条规则放在数据库边界上执行，而不只是提高某条查询的速度。 成员资格随谓词变化 CREATE INDEX ... WHERE active 只包含 active 为 true 的行，false 和 null 都不满足条件。是否允许 null 是独立的模型选择；下面实验把 active 定义为非空，使每条预订明确处于有效或无效状态。索引键也不必就是谓词中出现的列：这里用 seat 作为键，用 active 决定哪些行参与。 普通写入会改变成员资格。无效记录变成有效记录时，必须进入索引并接受检查；有效记录失效后，则离开当前有效子集。这个边界不是一旦建索引就永远固定的历史分界线。每次写入仍要判断谓词，状态切换也有维护成本，所以索引更小不意味着所有更新都免费，更不能无条件承诺写入一定更快。 唯一性可以只针对子集 在 seat 上建立 WHERE active 的唯一索引，会拒绝同一个座位出现两条有效记录，却允许任意多条无效历史记录。即使没有 SELECT 使用这个索引，数据库仍会执行唯一性检查。因此查询加速与数据完整性是保留索引的两种不同理由，不能只看扫描计数为零就把承担约束的索引删除。 应用先查“有没有”再插入，比数据库约束弱，因为另一个事务可能在检查与写入之间插入。唯一索引会在 PostgreSQL 中协调冲突写入，失败方需要处理唯一性错误或明确选择的冲突策略。下面夹具按确定顺序验证拒绝行为；并发协调属于数据库约束契约，不代表已经实现支付、扣款和跨服务预订的完整业务流程。 查询也要表达同一边界 规划器把部分索引用于访问路径时，必须能够证明查询条件蕴含索引谓词。仅按 seat 查询，并不能证明调用方只需要有效记录，因为历史记录也可能匹配。显式查询 active 记录才可能符合条件，但小表仍可能采用更便宜的顺序扫描。逻辑上相似的表达式，也只有在规划器能够识别其蕴含关系时才有用，不能仅凭人眼觉得相近。 ...

覆盖索引包含某一条查询需要的值，PostgreSQL 有时可以通过 index-only scan 直接返回它们，避免访问对应的堆元组。这里的“有时”很关键：列是否齐全属于结构条件，执行时能否真正跳过堆访问，还取决于可见性信息。计划里出现 Index Only Scan，并不能单独证明没有访问表数据，更不能据此宣称任何写入状态下都能省掉堆读取。 覆盖实际返回的列 按 sku 查价格时，可以建立 sku 的 B-tree，并用 INCLUDE (price) 把价格作为附加值存进去。搜索由 sku 键引导，附加的 price 不会自动成为第二个搜索键，也不提供按价格排序的顺序。如果这是唯一索引，唯一性约束针对键列，而不是键与附加列的整体组合。这些区别决定它究竟能回答哪些查询。 查询后来增加返回列，就可能破坏覆盖前提。例如 description 不在索引中，执行器需要从其他位置取得它，通常就是堆表。随手改成 SELECT 星号，也可能把原本专门优化的查找变成另一种访问模式。应从实际过滤条件和投影列出发，结合表达式与可见性需求设计，而不是把全部字段都塞进一个预想中的万能覆盖索引。 并非所有索引方法都能还原 index-only 执行所需的完整值。B-tree 支持这种扫描，GIN 通常保存的是从原值提取的键，而不是完整原值，因此不支持 index-only scan。索引不仅要包含或能够还原所需数据，也要支持对应操作。其他数据库也使用“覆盖索引”这个名称，但不能据此把 PostgreSQL 的可见性机制直接套过去。 可见性映射决定是否访问堆 PostgreSQL 必须判断候选元组是否对当前 MVCC 快照可见，索引项没有保存全部所需可见性信息。Index-only scan 会检查堆页在 visibility map 中的 all-visible 位；该位已设置时，才能直接返回索引中的值，而不读取堆元组。这样省下的是堆访问，仍然需要读取索引和可见性映射，并不等于完全没有 I/O。 ...