数据库

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

连接池泄漏首先是所有权问题：业务工作已经结束，但它借走的连接仍然处于借出状态。此时数据库可能仍然正常响应其他客户端，只有这个应用拿不到连接。应把归还资源视为操作契约的一部分，覆盖异常和提前返回，而不是只在成功路径末尾顺手清理一下。 一份借用应当有明确的主人和终点 SQLAlchemy Connection 表示从 Engine 所属池中取得的一次借用。关闭这个 Connection，通常是把底层 DBAPI 连接还回池里，而不一定真正断开数据库连接。归还借用与提交事务是两个独立决定：池可以运转正常，但业务修改已经回滚；池也可能耗尽，而底层数据库连接全部仍然存活。只检查连接是否活着，无法回答谁还占着它。 资源作用域应尽量短，但不能破坏事务要求的原子性。with engine.begin() 在代码块成功时提交，失败时回滚，并释放连接；with engine.connect() 管理的是连接作用域，不承诺自动提交写入，关闭时尚未结束的事务会被回滚。两者看起来都用了 with，但持久化语义不同，不能为了“统一写法”随意互换，否则资源不再泄漏了，数据却可能没有保存。 与当前事务无关的远程等待、用户交互，不应长时间占着事务和连接。这并不意味着可以把必须一起提交的变更拆开。若连接由调用方拥有，辅助函数不应私自关闭；若辅助函数自己借用，就应负责归还，或者在接口中明确把所有权交给谁。只有打开次数的指标，却说不清每份借用由谁结束，仍然没有定位真正的生命周期边界。 坏掉的样子 漏掉一次归还，就减少一份可用容量，后来者开始排队或超时。对于配置了有限容量和获取超时的 QueuePool，新调用并非一定永远挂住，而是可能在执行 SQL 之前就收到池超时。把上限调大可以暂时推迟复发，却也增加数据库压力；缺失的归还路径依然存在，不能算修复。 不过，“池满了”不是泄漏的充分证据。真实并发、长事务、数据库锁等待或应用自身死锁，都可能占住全部借用。数据库 CPU 很低也不决定原因，因为应用可能握着连接等待别的事情。需要把借出数量、获取等待、持有年龄、事务状态和仍在进行的业务工作关联起来，判断这些连接是否还处于合理的使用范围。 一个有说服力的泄漏复现，应证明已经结束的工作仍然保留借用，并且归还这些具体借用后容量恢复。不要依赖垃圾回收及时释放；对象何时被回收不是可靠的资源归还契约。Engine.dispose() 也不能强制收回仍然借出的连接，它处理的是池内已经空闲的连接。重启进程可能暂时清空症状，但不会改变下一次请求重新走进同一遗漏分支的事实。 连接池容量与持久化记录各有生命周期。保留的原样输出支持此本地示例，当前依赖工作流仍待验证。 快速验证 在 macOS 或 Linux 的空目录运行 python3 -m venv .venv，然后执行 .venv/bin/python -m pip install SQLAlchemy==2.0.52。把下面保存为 pool_probe.py，执行 .venv/bin/python pool_probe.py。原文报告使用 Python 3.14.4 和 SQLite 3.53.0；保留的原样代码输出记录 Python 3.12.14、SQLAlchemy 2.0.52 与 SQLite 3.53.1，本轮没有重跑安装或连接池练习。执行时应启用 Python 断言。数据库文件只存在于脚本自己的临时目录，结束后自动清理，不会访问生产数据库。 ...

LSM 树把一部分分散更新的成本转移到内存缓冲与后续归并。这是一种设计取舍，不是“所有磁盘操作从此完全顺序”的承诺，也不保证随机写一定胜过 B 树。真正需要回答的问题，是前台写入、后台压实、读取和保留版本，能否同时放进同一份资源预算。不能用一个没有提供数据生成器的精确耗时，替代对实际工作负载的解释。 缓冲改变工作的发生时机 RocksDB 通常把新写入放入内存表，并可同时写入预写日志。是否启用 WAL、是否同步日志，是另外的持久性选择；仅仅采用 LSM 布局，不保证每个已应答写入都能抵御断电。内存表填满后刷成不可变有序表，产生零层文件，应用则通过其他可用缓冲继续写入。日志追加、刷盘和压实也不是同一个动作。 采用分层压实时，零层文件的键范围可以重叠。更深的层通常各自组成一个有序段，但一个段可以按键范围拆为多个不重叠 SST 文件，不能理解为每层只有一个文件。查找先利用文件范围选择候选，压实则把重叠区间归并成替代文件。动态层大小配置还可能让某些中间层为空，因此不能把所有层永远填满的示意图当作运行事实。 读取仍然需要处理多个版本 同一个键的旧值可能存在于多个段中。读取要选择可见版本，而删除标记必须阻止更老的值重新出现。布隆过滤器可以排除肯定不含目标键的候选文件；返回可能存在后，仍需进一步检查。一次最终成功的查找，也能通过过滤器跳过不相关文件，再到真正包含答案的文件中读取。因此，“过滤器只帮助未命中，对命中毫无作用”并不准确。 压实通过重写数据，降低这种搜索和空间负担。分层策略与通用策略通常在写放大、读放大、空间放大之间作不同选择，但真实结果仍取决于负载、压缩、缓存和配置。统计时必须统一口径：SST 写入字节、WAL 字节、设备写入字节与应用逻辑字节不是同一种量。不写清分子分母的倍率，不能直接用来规划存储吞吐。 坏掉的样子 刷盘或压实跟不上时，不可变缓冲和零层文件可能积累，读取成本上升，引擎还可能减速或暂停前台写入。历史阻塞计数非零，不足以诊断今天的瓶颈。应同时查看最近的阻塞时间、待处理工作、文件数量、存储延迟与应用吞吐。提高阈值可能暂时推迟压力，却把更多工作留到后面，并扩大恢复时需要处理的积压。 删除不会立刻回收所有旧值。只要更低的段仍可能暴露被遮住的值，或保留快照需要相关历史，墓碑就不能随意消失。回收空间取决于压实覆盖了哪些历史，以及版本保留规则是否允许丢弃。若数据已经全部删除，活跃字节数为零，就不能再报告一个有限的“物理字节除以活跃字节”空间放大率；此时应直接报告残留字节与回收条件。 归并版本，保留删除语义。依据原文绘制机制；模型值与原文报告的观察不代表本次运行测试。 快速验证 把程序保存为 lsm_model.py，用 Python 3 执行。它明确只是标准库内存模型，展示三个刷出段和覆盖全部历史的压实。程序不会执行 RocksDB，不会创建真正 SST，不做磁盘输入输出，不模拟引擎调度，也不测延迟。打印的计数不能被重新包装成生产性能结果；示例的作用是把版本选择与墓碑删除条件具体化。 ...

检查点记录一部分已经完成的工作，使数据库恢复不必把整个日志历史都视为待处理内容。具体操作取决于引擎：PostgreSQL 同步检查点要求的数据变更，建立重做起点；SQLite 的 WAL 模式则把符合条件的已提交页面帧从日志转移到主数据库文件。两者都不能简单当作事务提交的另一个名字。 坏掉的样子 已经请求检查点与检查点完整完成，是两个不同观察。SQLite 读者可能持有旧快照，阻止较新的日志帧写入主文件，或者阻止日志重置。被动检查点可以只做当前能够完成的部分，然后正常返回。若只看成功标志，不看日志总帧数与已完成帧数，就会漏掉尚未处理的工作，以及那个仍然阻止完成的读事务。 日志大小也要放在具体上下文中理解。文件可以继续复用原有空间，因此已分配大小不变时，检查点进度仍可能推进。PostgreSQL 完成检查点之后，也可能继续为归档或复制槽保留旧日志。为了让磁盘曲线变小而手动删除日志，会破坏恢复材料。应先核对引擎进度和保留义务，再判断文件大小究竟代表什么。 检查点间隔不是恢复时间的硬上限。重放速度、存储吞吐、写入形态以及实际崩溃位置，都会影响恢复耗时。更频繁的检查点可能减少未来重做，也可能增加平时写流量。把日志放到另一块设备，不会把所有数据页写入也搬过去，更不会使检查点的写盘成本自动消失。参数选择需要对应具体工作负载。 SQLite 的完成依赖旧读者释放；更新早于最终检查点提交。这里只保留历史本地证据。 快速验证 把下面内容保存为 checkpoint_lab.py，使用普通的 python3 checkpoint_lab.py 运行，保持断言开启。脚本只使用标准库 SQLite 模块与私有临时文件。本次记录来自 Python 3.14.4 搭配 SQLite 3.53.0。比较结果时应同时查看运行时的引擎版本，因为 Python 版本与实际链接的 SQLite 版本并不是一回事。 import sqlite3 import tempfile from pathlib import Path with tempfile.TemporaryDirectory(prefix="xbcatm-checkpoint-") as directory: path = Path(directory) / "test.db" writer = sqlite3.connect(path, isolation_level=None, timeout=0.1) reader = sqlite3.connect(path, isolation_level=None, timeout=0.1) try: assert writer.execute("PRAGMA journal_mode=WAL").fetchone() == ("wal",) writer.execute("PRAGMA synchronous=FULL") writer.execute("PRAGMA wal_autocheckpoint=0") writer.execute("CREATE TABLE marker (id INTEGER PRIMARY KEY, value INTEGER)") writer.execute("INSERT INTO marker VALUES (1,10)") assert writer.execute("PRAGMA wal_checkpoint(TRUNCATE)").fetchone() == (0, 0, 0) reader.execute("BEGIN") assert reader.execute("SELECT value FROM marker").fetchone() == (10,) writer.execute("UPDATE marker SET value=20 WHERE id=1") assert writer.execute("SELECT value FROM marker").fetchone() == (20,) passive = writer.execute("PRAGMA wal_checkpoint(PASSIVE)").fetchone() blocked = writer.execute("PRAGMA wal_checkpoint(TRUNCATE)").fetchone() print("passive", passive) print("truncate_with_reader", blocked) assert passive[0] == 0 and passive[1] > passive[2] >= 0 assert blocked[0] == 1 and blocked[1] > blocked[2] assert Path(str(path) + "-wal").stat().st_size > 0 assert reader.execute("SELECT value FROM marker").fetchone() == (10,) reader.execute("COMMIT") completed = writer.execute("PRAGMA wal_checkpoint(TRUNCATE)").fetchone() print("truncate_after_reader", completed) assert completed == (0, 0, 0) assert Path(str(path) + "-wal").stat().st_size == 0 assert reader.execute("SELECT value FROM marker").fetchone() == (20,) print("fresh_reader", 20) finally: reader.close() writer.close() 初始截断检查点把值十写入主文件，并重置日志。随后一个读者显式开启事务，读取十；另一个连接在关闭自动检查点之后，把值二十提交。写者立刻就能读到二十，而旧读者仍然需要自己的原始快照。这个顺序用真实连接建立约束，不依赖线程恰好在某个微小时间窗口发生竞争。 ...

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

部分索引只为满足谓词的行保存索引项。需要的子集很小时，它可以缩小索引，但也可以表达只对这个子集生效的完整性规则。例如，同一个座位允许保留多条历史预订，却只能有一条当前有效预订。部分唯一索引可以把这条规则放在数据库边界上执行，而不只是提高某条查询的速度。 成员资格随谓词变化 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 星号，也可能把原本专门优化的查找变成另一种访问模式。应从实际过滤条件和投影列出发，结合表达式与可见性需求设计，而不是把全部字段都塞进一个预想中的万能覆盖索引。 ...

查询规划器根据预计工作量选择执行路径，并不会先把所有候选方案运行一遍、计时后再保留冠军。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 估计一行，实际返回一百行。两个条件单独都选中百分之一的数据；把选择率相乘，就得到一万行中的万分之一，也就是一行。这套算术在条件独立时有意义，而夹具故意让两列完全相关，所以假设与数据并不相符。 ...