PostgreSQL

声明式分区让 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 也不一定完整算到末尾。消费方可能只请求部分结果，不过排序、关联等操作又可能要求读取更多。物化同样不等于为后续语句建立一张带索引的可复用表。决定强制边界之前，应查看计划并测量真实负载，不能因为“物化”听起来像缓存就默认收益更好。 ...

EXPLAIN 展示 PostgreSQL 的执行计划，EXPLAIN ANALYZE 还会真正执行语句并测量工作量。最有用的比较，是同一个节点的估计行数与实际行数。两者都不能单独当成结论：估计来自模型，实测则只描述特定数据、缓存、并发和插桩条件下的一次执行。不能把前者称为谎言，也不能把后者当成脱离环境的永恒事实。 ANALYZE 会真正执行。文章示例区分可恢复的表变更与已消耗的序列值；本图没有重新运行实验。 先比较同一种量 计划中的 cost 是相对成本单位，不是毫秒。两个成本值分别描述启动成本与完成成本，带 LIMIT 的查询可能更重视尽早得到前几行，因此选择不同计划。actual time 才使用毫秒。即使成本和耗时恰好都打印成一百，也不能把它们直接相减或求比例；它们本来就不表示同一件事。 一个节点被重复执行时，actual rows 和节点耗时是每次循环的平均值，需要结合 loops 理解总工作量。但不能把所有节点耗时简单相加，因为父节点已经包含子节点的工作，重复相加会重复计算。并行执行时更不能用这种方式猜总延迟。先沿树看输入输出、循环次数、过滤条件和行数流向，再决定哪个位置值得调查。 BUFFERS 区分 shared、local 和临时块活动。shared hit 表示 PostgreSQL 在共享缓冲区里找到了块；shared read 只表示需要读入，操作系统仍可能从页缓存满足它，不能直接断言每次都访问了物理磁盘。临时表使用本地缓冲区，排序或哈希溢出则可能读写临时文件。父节点计数包含子节点，逐行把所有计数加起来同样会夸大 I/O。 ...

pg_stat_statements 把语句执行归组并累计代价。它既能找出调用很频繁、单次很短但总耗时很大的查询，也能帮助定位单次特别慢的查询。这是两个不同问题，任何一种排名都不能单独证明某项修改会改善业务延迟。执行时间也不等于处理器消耗、存储等待或完整请求时长。先明确要优化的现象，再选择统计列，才能避免把排行榜本身当成优化结论。 归组有用，但有明确边界 PostgreSQL 18 通常把代表性查询文本中的常量替换为参数符号，并按分析后的结构归组。条目身份包含用户、数据库、查询标识，以及是否为顶层执行；查询文本并不是完整身份。对象解析不同，可能让相同文本进入不同条目；相关分组范围内，也存在哈希碰撞把无关结构合并的微小可能。因此监控应保留足够身份字段，而不是只截取一段 SQL 当作永远可靠的主键。 代表性文本用于诊断，不是每条原始请求的审计流水。有些工具语句或归一化失效场景仍会保留字面量，应限制访问并避免把秘密嵌进 SQL。查询标识不承诺跨大版本或任意目录变化保持稳定。PostgreSQL 18 还会对某些长度不同的常量列表归一化；换到另一个版本时，需要查对应手册，不能把当前显示形式当成所有历史版本的统一行为。 根据问题选择排名 总执行时间适合寻找累计数据库工作量大的对象。平均值、最大值与离散程度描述单次调用的分布特点，但它们不是分位数，也不能单独指出原因。波动可能来自参数、缓存状态、竞争或计划变化。看到标准差较大时，应继续检查计划和等待，不能直接宣布发生了锁竞争；平均较高也不自动意味着规划器选择错误。 并发会话的执行区间会重叠，所以累计执行时长可以超过统计窗口的墙钟时间。这不证明服务器重启过，也不证明数据无效。启用计划统计时，规划耗时另有字段。共享块读取表示数据库缓冲管理器发起的读取，仍可能由操作系统缓存满足。乘以服务器块大小后得到的是请求读取量，不是物理设备实际流量；开启输入输出计时增加耗时测量，基础块计数并不依赖它。 坏掉的样子 模块需要在服务器启动时通过 shared_preload_libraries 加载，检查所用数据库也需要创建 SQL 扩展。查询标识计算必须可用，通常由 compute_query_id 的 auto 或 on 提供，也可以来自外部模块。缺少配置、权限限制和关闭跟踪是不同失败，应读取实际错误，而不是把任何缺失条目都解释成没有负载发生。监控查询能够运行，也不代表它有权看见所有其他用户的文本。 可靠增量要求条目身份与采集窗口都可比较。PostgreSQL 18 提供每条记录的 stats_since、最小最大值的独立起点，以及全局重置和淘汰信息。定向重置或淘汰可以只破坏一个条目的连续性，而不重置整个视图。差值非负也不足以证明窗口连续：重置之后积累足够工作，可能把异常掩盖。新条目和窗口变化应重新建立基线，不能跨越它们直接做减法。 归一化受条目身份和采集连续性约束。修订夹具绑定四项身份字段；执行仍有保留，未声明新的 SQL 或耗时结果。 快速验证 使用全新且没有其他负载的 PostgreSQL 18 可丢弃集群和数据库，预先加载模块、启用内置查询标识与顶层语句跟踪，并让测试角色能够看见和使用或安装扩展。保持角色、搜索路径与对象不变，实验期间不得有并发负载、重置或条目淘汰。在一个全新会话中执行下面 SQL，任何错误都应作为失败处理。成功时删除测试表和临时视图；错误可能留下这些对象，新安装的扩展也会保留。它不重置任何已有统计，因此不会为了演示排名而破坏监控窗口。 CREATE EXTENSION IF NOT EXISTS pg_stat_statements; CREATE TABLE pgss_demo (id integer); INSERT INTO pgss_demo SELECT generate_series(1,1000); CREATE TEMP TABLE pgss_key ( userid oid NOT NULL, dbid oid NOT NULL, toplevel boolean NOT NULL, queryid bigint NOT NULL CHECK (queryid <> 0), PRIMARY KEY (userid,dbid,toplevel,queryid) ); DO $$ DECLARE first_plan json; second_plan json; first_id bigint; second_id bigint; BEGIN IF current_setting('compute_query_id') NOT IN ('auto','on') OR current_setting('pg_stat_statements.track') <> 'top' THEN RAISE EXCEPTION 'requires built-in query identifiers and top-level tracking'; END IF; EXECUTE 'EXPLAIN (VERBOSE, FORMAT JSON) SELECT sum(id) FROM pgss_demo WHERE id < 100' INTO STRICT first_plan; EXECUTE 'EXPLAIN (VERBOSE, FORMAT JSON) SELECT sum(id) FROM pgss_demo WHERE id < 500' INTO STRICT second_plan; first_id := (first_plan->0->>'Query Identifier')::bigint; second_id := (second_plan->0->>'Query Identifier')::bigint; IF first_id IS NULL OR first_id = 0 OR second_id IS DISTINCT FROM first_id THEN RAISE EXCEPTION 'expected matching nonzero query identifiers'; END IF; INSERT INTO pgss_key SELECT (SELECT oid FROM pg_roles WHERE rolname=current_user), (SELECT oid FROM pg_database WHERE datname=current_database()), true,first_id; END $$; CREATE TEMP VIEW pgss_target AS SELECT s.* FROM pg_stat_statements AS s JOIN pgss_key AS k USING (userid,dbid,toplevel,queryid); CREATE TEMP TABLE pgss_window AS SELECT dealloc,stats_reset FROM pg_stat_statements_info; SELECT sum(id) FROM pgss_demo WHERE id < 100; CREATE TEMP TABLE pgss_first AS SELECT userid,dbid,toplevel,queryid,calls,stats_since FROM pgss_target; SELECT sum(id) FROM pgss_demo WHERE id < 500; SELECT userid,dbid,toplevel,queryid,query,calls,rows, round(total_exec_time::numeric,3) AS total_ms, round(mean_exec_time::numeric,3) AS mean_ms,stats_since FROM pgss_target; DO $$ BEGIN IF (SELECT count(*) FROM pgss_first) <> 1 OR (SELECT count(*) FROM pgss_target) <> 1 OR (SELECT count(*) FROM pgss_window) <> 1 THEN RAISE EXCEPTION 'expected one identity-bound entry and collection baseline'; END IF; IF NOT EXISTS ( SELECT 1 FROM pgss_first AS a JOIN pgss_target AS b USING (userid,dbid,toplevel,queryid) CROSS JOIN pgss_window AS w CROSS JOIN pg_stat_statements_info AS i WHERE a.calls=1 AND b.calls=2 AND b.calls-a.calls=1 AND b.stats_since=a.stats_since AND i.dealloc=w.dealloc AND i.stats_reset=w.stats_reset) THEN RAISE EXCEPTION 'expected calls 1 then 2 in one unchanged collection window'; END IF; END $$; SELECT userid,dbid,toplevel,queryid,calls, round(total_exec_time::numeric,3) AS total_ms, round(mean_exec_time::numeric,3) AS mean_ms FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; SELECT userid,dbid,toplevel,queryid,calls, round(mean_exec_time::numeric,3) AS mean_ms FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10; SELECT dealloc,stats_reset FROM pg_stat_statements_info; SELECT round((sum(shared_blks_read)::numeric * current_setting('block_size')::numeric)/1073741824,6) AS requested_read_gib FROM pg_stat_statements; DROP VIEW pgss_target; DROP TABLE pgss_first,pgss_window,pgss_key; DROP TABLE pgss_demo; SELECT 'pgss fixture completed' AS completion; 两个求和结果应分别是四千九百五十和十二万四千七百五十。不带 ANALYZE 的 EXPLAIN 在不执行求和的情况下取得两个查询标识，要求二者相同且非零。目标视图使用该标识、当前用户、数据库和顶层标志做完整身份连接，不用代表性文本匹配。两份快照要求先一次、后两次调用，并核对 stats_since 与全局重置、淘汰元数据未变。这些检查依赖无其他负载的前提，不保证哈希永不碰撞，也不是并发监控方案。两个通用排名完全可能相同，它们展示的是排序依据的区别，不制造必然不一致的承诺。对双精度耗时先转换为 numeric，才可以使用指定小数位数的两参数 round。 ...

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

建议锁让应用使用一个标识来协调工作，不要求这个标识对应数据库里某一行。比如，多名工作者可以约定，同一时间只能有一个连接刷新某份报表。PostgreSQL 负责管理锁的持有与竞争，应用则负责定义标识的业务含义，以及哪些操作必须先拿锁才能执行。这个分工决定了它能提供的保障，也决定了绕过协议时会发生什么。 坏掉的样子 建议锁的键只能是一个大整数，或者两个普通整数，没有接受文本名字的函数重载。这两种键空间彼此独立。如果应用通过哈希把名称映射为数字，必须明确一致的编码规则，也要考虑碰撞；同一资源被不同算法映射到不同数字，同样会破坏协调。直接维护有文档的整数命名空间，通常比隐藏在各个调用方里的临时哈希规则更容易核对。 建议锁是参与者共同遵守的协议。忽略它的连接，仍然可以更新所谓受保护的表行；它不能代替唯一约束、事务或权限检查。会话锁在回滚后仍然保留，直到对应次数的解锁完成，或服务器结束该会话。事务锁则随事务结束释放。如果处于自动提交模式，一条语句取得的事务锁，在后续语句开始之前就已经释放，无法保护接下来才执行的工作。 会话持有次数在回滚后保留；事务持有随事务结束。数值为人工预期，不是本轮竞争观察。 快速验证 本轮没有重跑原样保留的双会话示例。保留的审阅记录给出源文核对后的预期，没有原始多会话输出；单用户后端不能证明会话间竞争。 准备同一个 PostgreSQL 18 可丢弃数据库，开启甲、乙两个全新的 psql -X -v ON_ERROR_STOP=1 会话。使用同一角色并具备创建模式权限，确认 advisory_demo 尚不存在；显式事务外保持自动提交，且没有会中断调度的超时。为本实验预留下面的整数键，不要与已有业务约定混用。按照块的顺序切换终端。甲先创建计数器： 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，并不能单独证明没有访问表数据，更不能据此宣称任何写入状态下都能省掉堆读取。 覆盖提供查询值；堆页可见性标志决定 index-only 扫描是否仍须访问堆。写入和满足条件的 VACUUM 可以改变状态。堆访问不一定产生物理磁盘读取。 覆盖实际返回的列 按 sku 查价格时，可以建立 sku 的 B-tree，并用 INCLUDE (price) 把价格作为附加值存进去。搜索由 sku 键引导，附加的 price 不会自动成为第二个搜索键，也不提供按价格排序的顺序。如果这是唯一索引，唯一性约束针对键列，而不是键与附加列的整体组合。这些区别决定它究竟能回答哪些查询。 查询后来增加返回列，就可能破坏覆盖前提。例如 description 不在索引中，执行器需要从其他位置取得它，通常就是堆表。随手改成 SELECT 星号，也可能把原本专门优化的查找变成另一种访问模式。应从实际过滤条件和投影列出发，结合表达式与可见性需求设计，而不是把全部字段都塞进一个预想中的万能覆盖索引。 ...