SQL

范围分区把边界定义变成写入路由规则。PostgreSQL 按键值把行放到能够接受它的分区，无论这条数据准时到达，还是晚了几个月。任何查询获得裁剪收益之前，都需要先定义正确边界、使用匹配的时间类型，并安排缺失范围的处理流程。分区建表成功，只是这些条件中的一部分。 使用相邻的左闭右开范围 范围分区包含下界，不包含上界。九月可以从九月一日开始，一直到十月一日之前；十月正好从同一边界开始，双方不会重叠。不要靠猜测一个月最后可表示的时间戳来定义范围。相邻的左闭右开区间，不依赖时间字段究竟保留多少位小数，也能避免边界遗漏。 查询同样需要准确表达区间。大于等于下界、小于上界的条件，适合描述一个月的数据。BETWEEN 包含两个端点，因此，把下月第一瞬间写成其上界，可能把下个月的数据也查进来。分区路由只能执行数据布局规则，不能替一个请求了错误区间的条件修正业务含义。 分区键类型与时区也是定义的一部分。timestamptz 代表瞬间，不带时区的 timestamp 则代表没有对应时区解释的本地日期时间。示例为每个边界显式写出 UTC 偏移。如果业务月份按另一个时区划分，应根据那个时区的规则推导边界，而不是假定所有本地日期都对应相同的实际时长。 缺失范围是一种运维状态 没有分区接受新键值时，插入会报错，除非 DEFAULT 分区能够接住它。日历进入新月份，并不会让 PostgreSQL 自动创建下一张月表。应在写入需要之前创建范围，同时考虑迟到数据和旧日期修正。输入键不必单调增长，路由照样能够工作；关键是相应目标范围仍然存在。 DEFAULT 可以接收显式范围之外的值，减少立即发生的插入失败，但如果不检查默认表，也会掩盖分区管理遗漏。持续增长的默认分区可能混入多个互不相关的时间范围，让后续整理更困难。应让“有意保留的例外数据”与“忘记创建分区的日常数据”保持可辨认，不能把插入暂时不报错当成管理正常。 新增显式分区，会改变默认分区能够接受的值。如果新范围对应的行还留在 DEFAULT 中，PostgreSQL 会拒绝变更，而不会允许两处同时拥有这批数据。应先把这些行迁移到合适的暂存表或按规则处理，再挂载新分区。边界是被执行的数据规则，不是可以随意标错的说明标签。 坏掉的样子 下一月分区未创建时，部署可能恰好在跨月瞬间出现写入失败。补建分区又可能因为 DEFAULT 已经积累对应行而失败。这两种问题都属于路由和数据位置，不是规划器统计陈旧。应先检查被拒绝的键和当前边界，再决定操作，不能直接运行 ANALYZE 或改索引，希望它们修复缺失范围。 ATTACH 可能扫描候选表以验证边界，也可能检查默认分区中是否存在冲突数据。合适且已验证的约束能省去部分扫描，但操作仍需要锁，并要求表结构兼容。PostgreSQL 十八的 ATTACH 在父表上取得 SHARE UPDATE EXCLUSIVE，在候选表和存在的 DEFAULT 表上取得 ACCESS EXCLUSIVE。省掉验证扫描，并不等于省掉这些取锁要求。活跃表上迁移数据时，需要协调写入者和锁获取；一个隔离环境中的事务示例，不是完整的在线迁移操作方案。 ...

排名函数为分区内的每一条输入行附加位置，不会把整个团队、部门或客户的数据压缩成一行聚合结果。真正需要选择的是并列的含义：每行占一个独立位置、允许并列且保留空位的竞赛名次，还是按不同分数组连续计数的名次。三种规则解决的问题不同，应先确定业务规则再写窗口。 三个问题对应三种排名 row_number() 从一开始给每行分配连续整数。如果两行的排序值完全相同，而窗口没有其他区分条件，它们的相对序号没有保证。需要恰好选出两位获胜者时，可以按分数降序，再按稳定且唯一的标识排序。这意味着同分时由标识决定谁占据剩余名额，不能把这个结果当成不影响业务的技术细节。 rank() 给同组并列行分配该组第一行的位置。分数为一百、九十、九十、八十时，名次依次为一、二、二、四。两位第二名占用了两个位置，因此下一档分数前面会留下空位。dense_rank() 则计算同分组的数量，对同一批数据给出一、二、二、三，不会因并列人数增加而跳号。 并列组由窗口 ORDER BY 中的全部表达式决定。如果把唯一标识也加入 rank 的窗口，即使本意只是稳定展示顺序，也会让原来的同分行不再并列。row_number 需要标识裁决、rank 需要保留同分时，应使用不同窗口定义。最终结果的展示顺序可以由外层 ORDER BY 控制，不必因此改掉排名所依据的并列关系。 明确分区与过滤时机 PARTITION BY 让每个组从头编号。不写分区条件时，窗口看到的所有输入行属于同一个分区。这和窗口帧是不同概念：排名依据分区顺序和并列组，不是对某段移动聚合帧计数。添加 ROWS 边界，不会把 rank 变成“这个边界内有多少行”的函数。 窗口看到的是 FROM、WHERE、分组和 HAVING 处理后剩余的行。因此，在同层查询中过滤，可能先删掉部分竞争者，再计算名次。如果需要先给所有人排名，再挑前几个位置，应在子查询或 CTE 中生成窗口值，然后到外层过滤。PostgreSQL 不允许直接把窗口函数放进 WHERE，这也提醒读者区分排名输入与排名输出。 “前两名”必须有明确含义。位置不大于二，每个分区最多返回两行；竞赛名次不大于二，会保留全部并列第二名，结果可能超过两行；密集名次不大于二，则选择前两个不同分数组。当第一档分数本身就有多人并列时，密集排名与竞赛排名的筛选结果也可能不同。这些是不同的结果集合，不是可以随意互换的性能写法。 排名保留的是当前查询提供的行，不保证原始实体恰好一行。一对多连接可能在编号之前复制参赛者，导致同一实体占据多个位置。应先说明输入粒度并解决非预期的连接膨胀，再筛选前若干名。事后添加 DISTINCT 即使删掉重复输出，也不一定修复已经计算的名次；连接产生的行数本身就是排名语义的一部分。 坏掉的样子 row_number 缺少确定的并列裁决条件时，同分选手可能在重复查询中交换位置。反过来，在所有窗口都添加标识，又可能悄悄消除 rank 本来要保留的并列。测试时必须放入恰好卡在截止名次上的同分数据，不能只测每个分数都不同的情况。如果分数允许 NULL，还应明确空值顺序；PostgreSQL 降序默认把空值放前面，需要时应显式指定 NULLS LAST。 ...

LATERAL 允许 FROM 中的一个项目引用它前面项目提供的列。每个父记录需要一个带自身参数的小结果集时，它很实用，例如每位客户最近的两笔订单。整个查询仍是一条 SQL、一次数据库往返，横向子查询明确表达哪些外层值决定当前结果。但这种逻辑关系不承诺唯一的物理执行策略，规划器仍可进行保持语义的优化。 把关联条件写清楚 普通 FROM 子查询不能随意引用旁边表的列，LATERAL 允许在合适的连接位置使用这些引用。o.customer_id = c.id 才是当前客户与订单之间的关联；只写关键词不会自动产生这个条件。如果删除关联，每位客户可能都拿到同一份全局结果。语法通过只能证明语句合法，不能证明取到的是这个客户自己的订单。 实现每组前几名时，把父记录过滤条件、ORDER BY 和 LIMIT 都放进横向子查询。放在整个连接外面的 LIMIT 限制的是最终组合结果，不是每位客户最多几笔订单。排序还需要唯一的决胜字段：两笔订单完全可能具有相同时间戳，只按时间排序时，边界位置选择哪一行没有保证，换一个计划就可能换一个答案。 子查询里的 ORDER BY 决定入选哪些行，并不保证最终结果的展示顺序。客户端要求稳定的客户与订单顺序时，还需要外层 ORDER BY。导出报表和自动化检查尤其依赖这一点；不能因为当前执行计划碰巧按某个顺序返回，就把这种偶然表现当成接口契约，否则升级或统计信息刷新后会出现难以解释的行顺序变化。 决定没有匹配时保留什么 CROSS JOIN LATERAL 在子查询没有返回行时会丢掉对应父记录。LEFT JOIN LATERAL 配合 ON true 则保留父记录，并把右侧列补成 null。对尚未下单的客户，后者往往才符合页面或报表需求。如果把右侧条件移到外层 WHERE，补出来的 null 行可能被过滤掉，原本想保留空客户的行为就被破坏了。 聚合还有一层区别。COUNT(r.id) 只统计非空订单标识，COUNT(*) 连左连接补出的那一行也会计算。没有 GROUP BY 的相关聚合，即使输入为空也可能返回一行，COUNT 是零，SUM 则是 null。因此不能把所有 LATERAL 表达式都理解成同一种列表查询，必须看它实际返回零行、一行还是多行，以及每一列的空值含义。 ...

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

参数绑定为数据库中的取值提供一个明确位置。姓名里即使带有引号或分号，也仍然只是值，不会因此变成另一段查询条件。复用命名预备语句则是另一项优化：服务端能够在同一个会话内，跨多次执行保留语句信息。安全地绑定数据，并不要求应用为每一条查询都显式建立一个命名语句，也不要求先证明查询会频繁重复。 绑定保护 SQL 语法边界，却不会禁用取值内部仍需解释的语言。例如，绑定后的 LIKE 模式仍把百分号与下划线当成通配符。入口究竟接收模式，还是字面子串，需要另行约定，必要时按该运算符的规则转义。正则、全文检索和业务授权同样具有绑定之后的语义，不能因为成功参数化就认为输入已经符合全部产品约束。 坏掉的样子 在 Psycopg 中，占位符 %s 属于驱动接口，不是让程序使用 Python 字符串格式化。值应作为 execute 的第二个参数传入，占位符不要加引号，只有一个位置参数时也需要单元素元组。如果先把值插入字符串，再调用一个支持参数化的接口，原本希望保护的边界早就已经被越过；接口名称本身不会替程序补救这次拼接。 占位符代表值，不代表表名、列名或关键字。动态标识符需要明确的结构选择：先用白名单限制支持的列，再用驱动的标识符组合功能正确引用选中的列名。升降序关键字也应由应用控制。分页的数量与偏移量可以直接绑定，同时仍须按产品要求验证范围。能在语法上接收一个整数，不代表允许请求任意大的页面或无限制消耗资源。 预备语句不会把第一次的执行计划永久钉死。PostgreSQL 区分根据本次参数生成的定制计划，以及跨不同参数复用的通用计划；自动选择时会考虑规划开销与估算执行成本。模式或统计信息变化也可能触发重新规划。因此，语句保持参数化时，访问路径仍可以变化。是否复用某种计划，与输入能否变成 SQL 语法，是不同的性质，不能混为一谈。 命名语句为零时仍可绑定。命名复用和定制／通用计划是独立性质。原文实验未重跑；强制模式不说明延迟。 快速验证 准备安装了 Psycopg 3 的 Python 环境，把 PG_DSN 设置为可丢弃的 PostgreSQL 数据库连接。连接配置沿用已有安全提供方式。实测版本为 Psycopg 3.3.5 与 PostgreSQL 18.6。将下面代码保存为 Python 文件，在已设置该环境变量的终端执行。使用的是临时表，连接关闭后会自动消失： ...

查询规划器根据预计工作量选择执行路径，并不会先把所有候选方案运行一遍、计时后再保留冠军。PostgreSQL 用统计信息和成本模型比较扫描、连接、排序等操作，因此选定的计划始终是建立在当前假设上的预测。诊断预测为何偏离实际，需要找出失效的具体假设，而不是把顺序扫描或嵌套循环当成天生有问题的节点。计划好不好，要放在完整查询的输入和输出要求中判断。 ANALYZE 收集函数依赖后可纠正一种相关性估计，但仍可能误判不兼容的值组合。应比较预计与实际工作；成本不是毫秒，行数估计改善本身不代表执行变快。 成本描述的是一个模型 PostgreSQL 的成本综合页面访问、元组处理、运算符计算等估计工作量，单位并不是毫秒。只需要拿到少量记录时，启动成本可能特别重要；必须产生完整结果时，总成本的意义更大。一个扫描如果已经提供了后面需要的顺序，就可能省掉排序，因此单独看来最便宜的扫描，不一定是整条查询最合适的输入。只比较一个节点会漏掉它给后续处理带来的影响。 规划搜索也有边界。数据库会考虑不同路径和连接顺序，而不是实际执行全部候选。连接关系很多时，PostgreSQL 可以启用遗传查询优化器来控制规划开销。可用索引、合法变换、配置与搜索选择共同限制了候选集合，所以不能把最后的选择描述为“对所有想象得到的实现都保证数学最优”。这种边界并不表示规划器不可信，只是说明它需要接受真实测量的检验。 坏掉的样子 统计刚刷新也可能估错。普通统计分别描述每一列，在组合两个过滤条件时可能假设它们相互独立，而实际两列总是一起变化。中间结果被低估后，反复查找看起来就比实际便宜，后续连接顺序也可能受到影响。如果模型本身没有表达这两列的关系，重复运行相同的分析不会凭空补上这部分知识。统计陈旧与统计种类不足，不能混成一种诊断。 扩展统计也只解决特定问题。函数依赖可以帮助估计与常量比较且彼此兼容的等值条件，但它不保存具体值之间的完整对应关系，也不是范围条件或任意跨列比较的一般证明。即使已经收集到很强的依赖，一组实际上互不兼容的常量仍可能被严重高估。因此验证中既需要兼容的正向情况，也需要一个不成立的组合来展示限制。 快速验证 在 PostgreSQL 18 的 psql 中执行下面的临时表实验。两列始终保存相同的值，一共有一百种不同值，每种重复一百次。所有修改都放在事务中，最后回滚会一并移除表和相应统计对象。 \set ON_ERROR_STOP on BEGIN; CREATE TEMP TABLE dimensions (a integer,b integer); INSERT INTO dimensions SELECT g%100,g%100 FROM generate_series(1,10000) AS g; ANALYZE dimensions; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM dimensions WHERE a=7 AND b=7; CREATE STATISTICS pg_temp.dimensions_dependency (dependencies) ON a,b FROM dimensions; ANALYZE dimensions; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM dimensions WHERE a=7 AND b=7; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM dimensions WHERE a=7 AND b=8; DO $$ BEGIN IF (SELECT count(*) FROM dimensions WHERE a=7 AND b=7) <> 100 OR (SELECT count(*) FROM dimensions WHERE a=7 AND b=8) <> 0 THEN RAISE EXCEPTION 'wrong correlated population'; END IF; END $$; ROLLBACK; 保留的 PostgreSQL 18.6 执行报告中，在建立扩展统计之前，对 a=7 AND b=7 估计一行，实际返回一百行。两个条件单独都选中百分之一的数据；把选择率相乘，就得到一万行中的万分之一，也就是一行。这套算术在条件独立时有意义，而夹具故意让两列完全相关，所以假设与数据并不相符。 ...

复合 B-tree 索引先按第一个键排序，再在第一个键相同的组内按下一个键排序。这种元组顺序可以让“某个租户的一段时间”很容易定位。应理解的是条件能缩小多少索引扫描范围，而不是禁止查询后面的列，也不是要求 WHERE 中的条件必须按建索引的顺序书写。这几个概念混在一起，会导致错误的索引设计。 租户优先键顺序的局部片段，以及一种可能的后列搜索策略。重复搜索是否划算取决于成本；本图不是计划跟踪，最终输出顺序仍取决于实际路径。 排序的是键，不是 SQL 条件 对于 (tenant, happened) 索引，tenant 等值条件加 happened 范围条件描述了树中的一小段。PostgreSQL 可以定位这一段，而不用检查所有租户。即使把时间条件写在 WHERE 的前面、租户条件写在后面，逻辑上的合取关系也没有改变。数据库定义中的键顺序与查询文本的书写顺序，是两件不同的事情，不能拿文本位置判断索引是否可用。 通常，前导列的等值条件和第一个非等值键上的范围条件，决定必须扫描的区间。更后面的键条件仍可在索引中检查，即使不能继续缩小一个简单连续区间，也可能减少访问堆表的次数。调换索引键会改变这些机会，但不意味着某种排列在所有查询中都完全无用。需要结合查询实际要求的范围和返回列判断。 后面的键仍可能有用 PostgreSQL 18 在合适时可以使用 B-tree skip scan：如果遍历缺少等值条件的前导键取值，并在每组内重新搜索，比读取大部分索引更便宜，就可能采用这种路径。只有少数几个租户时，仅过滤 happened 也可能通过各租户分组完成查找；前导键不同值很多时，这种重复搜索可能太贵，规划器便可能选择顺序扫描或其他索引。 即使没有合算的 skip scan，某些只涉及后面键的条件仍可能扫描复合索引。问题是成本、覆盖范围与选择性，不是 SQL 是否合法，也不是索引必然被忽略。不能把“最左前缀”这种便于记忆的说法扩展成绝对禁令，尤其在目标数据库明确支持其他访问路径时。版本和真实计划必须优先于口号。 把排序和工作负载一起考虑 固定 tenant 后，后面的 happened 顺序可以支持该租户的时间排序。但跨多个租户时，索引先按租户分组，不会直接提供全局时间顺序。Bitmap 扫描也不保留索引顺序，仍可能需要单独排序。因此应看完整计划，而不是看到索引包含排序列就断言不会发生 Sort。LIMIT 对尽快返回前几行的需求也会改变最合适的路径。 ...