PostgreSQL 存储与索引系列（一）：数据在磁盘上如何安放——表、页、TOAST 与 VACUUM 这是系列文章的第一期，专注 PostgreSQL 的存储核心：表与行的组织、磁盘页结构、大字段的 TOAST 技术，以及不可或缺的 VACUUM 机制。后文还会介绍表分区，为下一期索引专题打下坚实基础。 1. 从逻辑表到物理文件 在 PostgreSQL 中，每个用户表都对应着磁盘上的一个或多个文件。默认情况下，一张表的数据存储在以表 OID 命名的文件中（位于 $PGDATA/base/<数据库 OID>）。当表或索引超过 1GB 时，PostgreSQL 会自动分割为 文件.1、文件.2 等，绕开某些文件系统的限制。 每个表中的数据是无序存储的“堆”（Heap）：新插入的行通常追加到文件末尾，更新行时可能放置在同一页（如果空间够），也可能移动到新页并留下“转发指针”。这种设计让写入很快，但带来了后续的清理负担——这就是 VACUUM 存在的理由。 2. 页结构：PostgreSQL 的磁盘基本单位 PostgreSQL 磁盘 I/O 的最小单元是页（Page），默认大小为 8 KB（编译时可选）。页既是读写单位，也是内存缓存的单位（shared_buffers 中缓存的就是页）。一张表的所有页组成一个大小可变的数组，每个页有自己的页号（block number）。 一个表页的内部布局如下（从头部到尾部分配）： +-----------------------------+ ← 页起点 | PageHeaderData (24 bytes) | 页元数据：页校验和、空闲空间起始、特殊区域起点等 +-----------------------------+ | ItemIdData (数组) | 4 字节/条，指向具体数据的偏移和长度 | (指向行指针，从页尾逆向增长) | +-----------------------------+ | 空闲空间（未使用区域） | +-----------------------------+ | 实际行数据（从页尾正向填充） | +-----------------------------+ | Special Space (可选) | 索引访问方法专用，如 GiST 需要保存信息 +-----------------------------+ 关键点： ItemId 数组从页头之后开始，采用“从两头向中间”方式：行指针从页头向后增长，实际元组从页尾向前增长。这样空闲空间始终是连续的一段。 每个元组头部包含 t_xmin、t_xmax、t_cid、t_ctid 等 MVCC 字段，用于判断可见性。 页内的元组如果被更新或删除，不会立刻物理移除，而是标记为“死元组”，等待 VACUUM 回收。 3. TOAST：大字段的出路 PostgreSQL 页面只有 8 KB，当一行数据超过约 2 KB（通常是页面大小的四分之一）时，TOAST（The Oversized-Attribute Storage Technique）就会介入。TOAST 将大字段值压缩并切分成小块，存储在单独的系统表 pg_toast 中。原表的行内只留下一个指针（约 18 字节）。 触发条件与策略 任何列类型都可能触发 TOAST，但主要面向变长类型（text、varchar、bytea、jsonb 等）。 四种存储策略（可通过 ALTER TABLE ALTER COLUMN SET STORAGE 调整）： PLAIN：禁用 TOAST，强制行内存储（要求列不超过页大小）。 EXTENDED：允许压缩和行外存储（默认策略）。 EXTERNAL：允许行外存储，但不压缩。适合部分无法接受压缩代价的场景（如某些加密数据）。 MAIN：优先压缩，尽量留在行内，最后才移至行外。 TOAST 表的内部结构 每个拥有大字段的表会关联一个 pg_toast.pg_toast_<OID> 辅助表，该表包含三个列： chunk_id：标识属于哪个大字段值。 chunk_seq：切块序号。 chunk_data：实际的数据块（长度 ~2 KB）。 查询时如果只需 SELECT 非 TOAST 列，PostgreSQL 会避免读取 TOAST 表，提升性能。 4. VACUUM：对抗膨胀的清洁工 MVCC 架构下，更新/删除操作会留下旧版本（死元组），如果不清理，表会无限膨胀。VACUUM 负责： 回收死元组占用的空间，使其能被新元组复用。 更新可见性映射（Visibility Map），告诉 PostgreSQL 哪些页面全部可见，从而让仅索引扫描（Index-Only Scan）可以跳过堆表访问。 冻结事务 ID，防止事务 ID 回卷导致数据丢失。 两种 VACUUM 模式 标准 VACUUM：并发执行，仅清除死元组、整理空闲空间映射（FSM），不压缩表。空闲空间被记录到每个表的 _fsm 文件中供后续插入使用。 VACUUM FULL：排他锁重写整个表，彻底整理碎片并压缩到最小，代价高，适合膨胀严重且可以接受长时间锁表的场景。 关键优化：Visibility Map (VM) 与 Index-Only Scan 每个表还有一个 _vm 文件，每个页在 VM 中用 2 个比特表示： all_visible：该页所有元组对所有事务都可见。 all_frozen：该页所有元组的事务 ID 都已冻结（早期版本只有一个标志）。 当 VM 标记某页 all_visible 后，VACUUM 可以跳过该页；更重要的是，仅索引扫描可以结合 VM 省去回表——先通过索引拿到 ctid，再检查对应页是否在 VM 中标记，若是则直接使用索引数据，否则仍需回表判断可见性。 Autovacuum 调优要点（简要） 避免表膨胀：设置合适的 autovacuum_vacuum_scale_factor（大表用比例）和 autovacuum_vacuum_threshold（小表用绝对值）。 监控 pg_stat_all_tables 的 n_dead_tup […]

PostgreSQL 事务与并发系列 · 第五期 实战调优：事务的常见陷阱与最佳实践 前四期我们深入了 MVCC、锁、隔离级别与序列化异常。本期将把这些知识落地到生产环境的日常运维与开发中，剖析长事务、空闲事务、表膨胀、死锁超时等真实痛点，并提供一套可立即上手的监控、报警与优化方案。 一、长事务：性能与运维的头号杀手 1.1 为什么长事务如此危险？ 阻止 VACUUM 清理：长事务持有的快照会让所有在它之后产生的死亡元组都无法被清理，导致表与索引持续膨胀。 事务 ID 回卷风险：事务 ID 是 32 位环状空间，长事务可能导致 xid 无法冻结，最终数据库会强制关闭。 锁持有时间过长：长事务可能持有表锁或行锁，阻塞其他正常业务。 1.2 如何定位长事务？ SELECT pid, now() - xact_start AS xact_duration, state, query, backend_xid, backend_xmin FROM pg_stat_activity WHERE xact_start IS NOT NULL AND state NOT LIKE 'idle%' ORDER BY xact_duration DESC; 重点关注： xact_duration 超过 1 分钟的事务（视业务而定） backend_xmin 非空且 age 很大的会话 1.3 治理策略 应用层超时 + 重试：设置合理的语句超时（statement_timeout）和事务超时（idle_in_transaction_session_timeout）。 拆分长事务：将长时间运行的事务拆分为多个短事务，或使用异步处理。 定期监控与告警：当长事务超过阈值（如 5 分钟）时，通过脚本发送告警甚至自动终止。 自动终止示例（谨慎使用，建议先通知后终止）： -- 终止 xact_duration > 10 分钟的事务 SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE xact_start < now() - interval '10 minutes' AND state != 'idle'; 二、idle in transaction：被遗忘的会话 2.1 危害 会话处于 idle in transaction 状态，表示它已经 BEGIN 但既不提交也不回滚，也不执行任何语句。这种会话： 持有可能的行锁或表锁，阻塞其他事务。 持有快照，同样阻止 VACUUM 清理。 占用连接资源，导致连接池枯竭。 2.2 发现与清理 SELECT pid, state, xact_start, now() - xact_start AS idle_duration, query FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY idle_duration DESC; 根治方案：在 PostgreSQL 配置文件中设置： idle_in_transaction_session_timeout = '60s' 一旦事务空闲超过 60 秒，自动被终止并回滚。生产环境建议设为 30~120 秒。 同时检查应用代码：确保每一次 BEGIN 后都有对应的 COMMIT 或 ROLLBACK（尤其是异常处理路径）。 三、表膨胀：监控与回收 3.1 为什么会膨胀？ MVCC 的副作用：更新/删除会产生死元组（dead tuples）。如果 VACUUM 跟不上产生速度，表就会膨胀，占用更多磁盘，降低索引效率。 长事务和 idle in transaction 是 VACUUM 最大的敌人——它们强制保留旧版本。 3.2 如何检测膨胀？ 使用 pg_stat_user_tables 观察死元组比例： SELECT schemaname, relname, n_live_tup, n_dead_tup, round(100 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio, last_vacuum, last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY dead_ratio DESC; 如果 […]

PostgreSQL 事务与并发系列 · 第四期 事务隔离级别深度测试与序列化异常 前三期我们构建了从 MVCC 到锁机制的完整知识体系。本期将聚焦三种隔离级别的实际行为边界，通过可复现的案例演示“不可重复读”“幻读”以及最隐蔽的“写偏斜（Write Skew）”，同时揭开 PostgreSQL 可串行化快照隔离（SSI）的神秘面纱。 一、隔离级别速览与默认行为回顾 PostgreSQL 支持三种隔离级别（READ UNCOMMITTED 实际等同于 READ COMMITTED）： 隔离级别 脏读 不可重复读 幻读 序列化异常（写偏斜） READ COMMITTED ❌ ✅ 可能 ✅ 可能 ✅ 可能 REPEATABLE READ ❌ ❌ ❌（PostgreSQL 实现彻底阻止） ✅ 可能 SERIALIZABLE ❌ ❌ ❌ ❌（检测冲突并终止事务） 关键事实：PostgreSQL 的 REPEATABLE READ 级别不仅阻止了不可重复读，还彻底阻止了幻读（通过快照隔离实现了比 SQL 标准更严格的语义）。但即便如此，仍无法防止写偏斜这种序列化异常。 二、实战环境准备 创建一个简单的医生值班表，用于演示所有异常场景： DROP TABLE IF EXISTS doctors; CREATE TABLE doctors ( id INT PRIMARY KEY, name TEXT, on_call BOOLEAN -- 是否值班 ); INSERT INTO doctors VALUES (1, 'Alice', true), (2, 'Bob', true); 规则：至少有一名医生值班（业务约束）。两个事务同时试图交接班，可能会导致违反约束。 三、READ COMMITTED：不可重复读与幻读 3.1 不可重复读 时间 事务 A (READ COMMITTED) 事务 B (READ COMMITTED) T1 BEGIN; T2 SELECT on_call FROM doctors WHERE id=1; → true T3 UPDATE doctors SET on_call=false WHERE id=1; COMMIT; T4 SELECT on_call FROM doctors WHERE id=1; → false T5 COMMIT; 同一个事务内读取到不同的值 → 不可重复读。 3.2 幻读 在 PostgreSQL 中，幻读通常指“范围内新增/删除的行导致两次查询结果集不同”。虽然 REPEATABLE READ 能阻止，但 READ COMMITTED 仍会出现： 时间 事务 A 事务 B T1 BEGIN; T2 SELECT COUNT(*) FROM doctors WHERE on_call=true; → 2 T3 INSERT INTO doctors VALUES (3,'Carol',true); COMMIT; T4 SELECT COUNT(*) FROM doctors WHERE on_call=true; → 3 出现了之前未见的行 → 幻读。 四、REPEATABLE READ：快照隔离如何阻止幻读 在 REPEATABLE READ 隔离级别下，事务在第一条语句执行时获取一个固定的快照，整个事务期间复用。因此 T4 查询仍然看到的是 T2 时刻的快照结果，不会受 T3 提交的影响。 验证脚本： -- 会话 1 BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT COUNT(*) […]

PostgreSQL 事务与并发系列 · 第三期 锁机制与死锁处理实战 前两期我们深入了 MVCC 与快照隔离，知道了“读不阻塞写”。但有些场景必须靠锁来强制串行化。本期将带你掌握 PostgreSQL 中各类锁的用法、冲突关系、死锁成因与排查，并提供生产环境可用的监控脚本。 一、为什么有了 MVCC 还需要锁？ MVCC 主要解决了 读写冲突（读不阻塞写，写不阻塞读），但它无法解决 写写冲突 以及某些需要“绝对最新数据”的场景。 例如： 两个事务同时对同一行执行 UPDATE，必须有一个先等另一个完成，否则更新会相互覆盖。 业务上要求“先查询余额，余额足够才扣款”，如果不用锁就可能出现超卖。 因此，PostgreSQL 在 MVCC 之上仍然保留了完善的锁机制，包括表级锁、行级锁，以及用于应用层协调的咨询锁。 二、表级锁 表级锁由 PostgreSQL 内核自动管理，但你可以通过 LOCK 命令显式指定。表级锁按照冲突程度从低到高排列如下： 锁模式 关键字 冲突对象 典型场景 ACCESS SHARE ACCESS SHARE 仅与 ACCESS EXCLUSIVE 冲突 SELECT 查询自动加此锁 ROW SHARE ROW SHARE 与 EXCLUSIVE、ACCESS EXCLUSIVE 冲突 SELECT FOR UPDATE/SHARE ROW EXCLUSIVE ROW EXCLUSIVE 与 SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突 UPDATE、DELETE、INSERT SHARE UPDATE EXCLUSIVE SHARE UPDATE EXCLUSIVE 与 SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突 VACUUM、CREATE INDEX CONCURRENTLY SHARE SHARE 与 ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突 保护表结构不被修改，但允许读 SHARE ROW EXCLUSIVE SHARE ROW EXCLUSIVE 与 ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突 较少直接使用，类似保护整个表 EXCLUSIVE EXCLUSIVE 与 ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE、ACCESS EXCLUSIVE 冲突 允许读但阻止所有写和并发锁 ACCESS EXCLUSIVE ACCESS EXCLUSIVE 与所有锁模式冲突 DROP TABLE、TRUNCATE、REINDEX、VACUUM FULL、ALTER TABLE 等 查看当前表级锁 SELECT relation::regclass AS table_name, mode, granted, pid FROM pg_locks WHERE locktype = 'relation' AND relation IS NOT NULL; 显式加锁示例 -- 防止其他事务对表进行 DDL 操作，但允许普通查询 LOCK TABLE my_table IN SHARE MODE; -- 禁止任何并发写入，但允许读取 LOCK TABLE my_table IN EXCLUSIVE MODE; -- 完全排他（用于维护） LOCK TABLE my_table IN ACCESS EXCLUSIVE MODE; 三、行级锁 行级锁不会锁整个表，而是锁住特定的行版本。它们也是通过 pg_locks 查看，locktype 为 tuple（旧版本）或 transactionid（行锁本质关联事务）。更直观的是通过 SELECT ... FOR UPDATE/SHARE 来加锁。 行锁模式 关键字 冲突（与另一个行锁） 说明 FOR […]

PostgreSQL 事务与并发系列 · 第二期 深入 MVCC：可见性判断与事务快照揭秘 第一期我们建立了 ACID 和 MVCC 的基本概念。本期将掀开 MVCC 的引擎盖，剖析元组头部的 xmin/xmax、事务快照的内存结构，并通过实战案例看清“读已提交”与“可重复读”的本质差异。 一、回顾：MVCC 的核心思想 PostgreSQL 中，每一行数据（称为元组 tuple）在被修改时不会原地更新，而是插入一个新的行版本，旧的版本仍然留在数据页中。这些版本通过元组头部的两个事务 ID 字段来标记生命周期： xmin – 插入该行版本的事务 ID xmax – 删除或更新该行版本的事务 ID（0 表示未删除） 当事务开始时，系统会为其生成一个快照（Snapshot），里面记录了当前“哪些事务正在运行、哪些事务已经提交”等信息。随后，对于每一个可见性判断，PostgreSQL 会根据快照中的规则决定是否显示该行版本。 二、元组头中的秘密：xmin 与 xmax 查看一个普通表的元组头部信息，可以使用 pageinspect 扩展（需要超级用户权限）： CREATE EXTENSION pageinspect; -- 创建一个测试表 CREATE TABLE test_mvcc (id int PRIMARY KEY, name text); INSERT INTO test_mvcc VALUES (1, 'Alice'), (2, 'Bob'); -- 查看数据页的元组信息 SELECT lp, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('test_mvcc', 0)); 输出示例： lp t_xmin t_xmax t_ctid 1 730 0 (0,1) 2 730 0 (0,2) 这里 t_xmin=730 表示插入这两行的事务 ID 是 730，t_xmax=0 表示当前版本未被删除。 更新时的版本变化 -- 开启一个新事务（假设事务 ID 为 735） BEGIN; UPDATE test_mvcc SET name = 'Alice2' WHERE id = 1; 此时数据页内部会变成： lp t_xmin t_xmax t_ctid 1 730 735 (0,3) 2 730 0 (0,2) 3 735 0 (0,3) 旧版本（lp=1）的 t_xmax 被设置为 735，表示它已被事务 735 删除。 新版本（lp=3）的 t_xmin = 735，表示它由事务 735 创建。 t_ctid 指向新版本的位置 (0,3)，形成一条版本链。 当事务 735 提交后，旧版本对其他事务可能不可见，具体取决于隔离级别和快照。 三、事务快照（Snapshot）的内存布局 PostgreSQL 中，快照是一个结构体（SnapshotData），其核心成员包括： xmin – 当前所有未提交事务中最小的 ID xmax – 下一个将要被分配的事务 ID（即大于等于此值的所有事务，肯定未开始） xip – 一个数组，存储所有活跃（未提交）的事务 ID snapshot_type – 区分 SNAPSHOT_MVCC（普通快照）、SNAPSHOT_SELF（包含当前事务）等 快照是调用 GetSnapshotData() 函数生成的。简单流程： 记录当前系统活跃事务列表（从 ProcArray 中读取） 计算出最小的活跃事务 ID 作为 xmin 用 nextXid（下一个未分配的事务 ID）作为 xmax 将活跃事务 ID 列表复制到 xip 数组 一个典型的快照内容（人为示例）： xmin = 100 xmax = 105 xip = [101, 103] (事务 102 已经提交，104 尚未开始) 四、可见性判断的核心规则 给定一个行版本，其 xmin 和 xmax，以及当前事务的快照，判断是否可见的简化逻辑如下： […]

PostgreSQL 事务与并发系列 · 第一期 从 ACID 到 MVCC：PostgreSQL 并发控制的核心思想 本系列将带你系统掌握 PostgreSQL 事务与并发的每一步。第一期从 ACID 原则谈起，揭开 MVCC 如何实现”读不阻塞写、写不阻塞读”。 一、引言 在关系型数据库的世界里，事务（Transaction）是数据一致性与并发控制的基石。PostgreSQL 之所以被公认为”业界最先进的开源关系型数据库”，其强大的事务处理能力和高并发支持功不可没。 理解 PostgreSQ L的事务与并发机制，不仅仅是掌握几条 SQL 语句。它直接决定了： 你写的应用在高并发下能否稳定运行 你的数据库能否扛住千万级 QPS 的冲击 你遇到死锁、性能瓶颈时能否快速定位并解决 本系列将从 ACID 原则讲起，逐步深入到 MVCC 实现原理、隔离级别的行为差异、锁机制的分类与死锁排查，最后涵盖快照隔离、长事务与膨胀治理，帮助你对 PostgreSQL 的事务与并发模块建立系统性的认知。 二、事务的 ACID 原则 什么是事务？ 用数据库的术语来说，事务是由一个或多个 SQL 语句组成的工作单元。最经典的例子是银行转账——从账户 A 扣款和向账户 B 入账，这两个操作要么一起成功，要么一起失败，绝不允许出现”钱扣了，但对方没收到”这种中间状态。 事务所解决的四大并发现象 PGSQL 的事务机制正是为了解决以下四大潜在问题而产生的：脏读、不可重复读、幻读、序列化异常（后文将详细说明）。 ACID 四大特性 ACID 是事务的四大核心属性，也是 PostgreSQL 设计并发控制机制的根本原则： 特性 含义 PostgreSQL 的实现技术 原子性（Atomicity） 事务中的所有操作要么全部成功提交，要么全部失败回滚 事务管理 + 撤销日志 一致性（Consistency） 事务执行前后，数据库始终保持一致的状态（约束、规则） 主键、外键、检查约束等 隔离性（Isolation） 并发事务之间互不干扰 多版本并发控制（MVCC） 持久性（Durability） 事务一旦提交，其修改就永久保存，即使系统故障也不丢失 预写式日志（WAL）+ 恢复子系统 三、原始的并发控制：锁 在 MVCC 诞生之前，大多数数据库系统采用基于锁的并发控制机制，其中最具代表性的是 S2PL（严格两阶段锁）： 读操作需要获取共享锁 写操作需要获取排他锁 这也正是 PostgreSQL 经典的“读不阻塞写，写也不会阻塞读”设计初衷得以实现的基础。 四、MVCC：PostgreSQL 并发的灵魂 4.1 什么是 MVCC？ 多版本并发控制（Multi-Version Concurrency Control, MVCC）是 PostgreSQL 实现高并发隔离性的核心技术。 核心理念是：当数据被修改时，数据库不直接覆盖原有数据，而是创建一个新的版本，保留历史版本。 这种设计带来最直观的效果就是：读者永远不会被写者阻塞，写者也永远不会被读者阻塞。 4.2 MVCC 的核心组件 PostgreSQL 中的每一条元组（tuple）都带有两个系统字段： xmin：创建这行版本的事务 ID xmax：删除/过期这行版本的事务 ID 当事务读取数据时，PostgreSQL 会根据当前事务的隔离级别和快照信息，来决定应该看到哪个版本的数据，实现在无需加锁的情况下保证事务隔离性和一致性。 五、事务隔离级别 SQL 标准定义了四种隔离级别，但 PostgreSQL 在内部只实现了三种不同的隔离级别——因为其”读未提交”的行为实际等同于”读已提交”。 隔离级别 脏读 不可重复读 幻读 PostgreSQL 默认？ 读未提交 可能 可能 可能 ❌ 读已提交 不可能 可能 可能 ✅ 默认 可重复读 不可能 不可能 可能 🟡 可设置 可串行化 不可能 不可能 不可能 🟡 最高级别 三种的不可重复出现性及其影响：PostgreSQL 都提供了完整的保障。 六、快照隔离 6.1 什么是快照？ 快照是 PostgreSQL 实现 MVCC 的核心数据结构。当一个事务开始时，PostgreSQL 会为该事务生成一个快照，记录： xmin：当前未完成的最小事务 ID xmax：下一个被分配的事务 ID xip_list：当前活跃事务列表 通过这个快照，PostgreSQL 能准确地判断每个数据版本对当前事务是否可见。 6.2 不同隔离级别下的快照行为 读已提交：事务中的每条语句执行前都会重新获取一次快照，这意味着同一个事务中的不同语句可能看到不同的最新数据。 可重复读：事务只在其第一条语句执行时获取一次快照，后续所有语句都复用这个快照，保证整个事务内看到的数据是一致的。 可串行化：基于可重复读的快照机制，但额外增加了对事务间依赖的检测，在冲突时主动终止事务以保持”真正的串行化”语义。 七、PostgreSQL 中的锁 MVCC 减少了大多数情况下的锁竞争，但在某些场景下，锁依然是不可或缺的。 7.1 标准锁的分类 PostgreSQL 的锁分为多个类型，常见的有： 锁模式 使用场景 冲突模式 表级锁 控制对整个表的并发访问 ACCESS SHARE / ROW EXCLUSIVE / ACCESS EXCLUSIVE 等 行级锁 控制对特定行的并发修改 FOR UPDATE / FOR SHARE 7.2 建议锁 PostgreSQL 提供了一种应用层面的锁机制——建议锁。应用可以选择任意一个 64 […]

SQL 与查询优化（PostgreSQL 篇）· 第七期 查询缓存、连接池与中间件优化 从第一期到第六期，我们的焦点一直围绕单实例 PostgreSQL：执行计划、索引设计、连接算法、统计信息、物化视图与分区表，以及锁与并发控制。但是，当到达了几百万乃至上亿行数据之后，应用程序不仅查询复杂，连接数也可能成千上万。 本期我们跳出数据库内核，进入中间件层，探讨查询缓存、连接池和分布式中间件如何进一步提升系统的极限。 一、连接池 – 高并发的前提 1.1 为什么需要连接池？ 在 PostgreSQL 中，每一个新的客户端连接都会在服务器端 fork() 一个新的进程（Backend Process）。一个空闲的数据库连接，即使没有执行任何查询，也会占用大约 5-10 MB 的内存。 然而，对于现代的 Web 应用和无服务器架构，通常不需要上百个全时的数据库连接。一个典型的请求可能只持续几毫秒来执行一个查询，然后释放连接。如果每次请求都重新打开连接，断开后立刻关闭，下一轮请求再次创建……这会引入显著的连接建立/销毁开销。 高并发下会产生两种典型的“连接风暴”现象： 现象 机制 检测手段 连接风暴 连接数超 max_connections 阈值时，新连接请求直接被拒绝 ERROR: too many clients already 向连接风暴”升级” 空闲连接数过多（超过 tcp_keepalives_idle 阈值），系统不断尝试维持活着，且触发了 TCP keepalive 探针风暴 tcpdump 观察到大量极小的探针包，同时 pg_stat_activity 里 idle 连接数极高 查看当前连接状态，以评估是否需要连接池： SELECT state, count(*) FROM pg_stat_activity GROUP BY state; SELECT name, setting FROM pg_settings WHERE name = 'max_connections'; 如果输出中的 idle 状态连接占据了绝大多数，那么，这些空闲连接正在浪费大量的服务器内存资源。 1.2 PgBouncer – 轻量级专用连接池核心 为了解决“连接数爆炸”的问题，标准的解决方案是在客户端和数据库之间引入一个连接池中间件。PgBouncer 就是最著名的专用连接池。 其工作原理类似这样：应用程序连接到 PgBouncer；PgBouncer 维护一个到后端 PostgreSQL 的真实连接的池（池中包含一定数量已建立的连接）；当一个请求到达时，PgBouncer 从池中找出一个空闲的真实连接分配给该请求；请求完成后，真实连接被归还到池中。 PgBouncer 提供三种连接池模式： 模式 工作原理 适用场景 限制 会话池（Session） 连接生命周期 = 客户端会话全程（默认模式） 使用临时表、SET 会话变量、LISTEN/NOTIFY、PREPARE 语句、WITH HOLD 游标 性能收益最小 事务池（Transaction） 连接生命周期 = 单个事务。事务结束（COMMIT/ROLLBACK）后归还连接 短事务为主的 OLTP 系统（推荐模式） 不支持会话级特性（临时表、SET） 语句池（Statement） 连接生命周期 = 单条 SQL 语句执行结束后归还连接 大量短查询场景 不支持多语句事务 配置示例 (pgbouncer.ini)： [databases] mydb = host=localhost port=5432 dbname=mydb [pgbouncer] pool_mode = transaction # 事务池模式 default_pool_size = 20 # 每个数据库/用户的默认连接池大小 max_client_conn = 1000 # 允许的最大客户端连接数 listen_addr = * listen_port = 6432 auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt PgBouncer 非常轻量级：无论有多少客户端连接，一个 PgBouncer 实例仅占用约 2-5 MB 的 RAM；每个连接的内存开销只有 2 KB 左右。纯连接池场景下，建议使用 PgBouncer，轻量简单，能够减少 97.5% 的连接数，并提升 53% 的吞吐量。 1.3 连接池最佳实战 设计容量估算公式： pool_size = (max_connections - admin_connections) × buffer_factor × connection_multiplier / replication_factor 其中 connection_multiplier 取决于中间件架构：直连时代值为 1，引入 PgBouncer 后视压测情况可调整为 0.2~0.5。 Pgbouncer 的事务池模式能突破内存限制，把极大量的客户端连接收敛到少量的后端连接上。但使用事务池模式时，绝不能依赖会话级资源（即，不能在事务结束后还依赖临时数据存活）。这往往要求业务代码中所有 BEGIN … COMMIT ROI 级别的事务，都不应该依赖临时表和会话变量。若违反此限制，可能会看到一些后台报错（关于临时表或预备语句不存在）。 此外，也需要注意一些网络安全问题，尤其是在公网使用连接池时，务必配置 auth_file 中的用户密码，并建议使用 scram-sha-256 […]

SQL 与查询优化（PostgreSQL 篇）· 第六期 锁与并发控制 – 查询优化的另一维度 前五期我们专注 SQL 本身：执行计划、统计信息、连接算法、高级 SQL、分区表，以及优化器调参。 但在高并发系统中，锁与事务往往成为性能瓶颈。本期深入 PostgreSQL 的锁机制、MVCC、Vacuum 原理，学会诊断锁冲突、优化高并发写入，让数据库在多用户环境下依然流畅运行。 一、PostgreSQL 的并发控制基石：MVCC PostgreSQL 没有使用传统的两阶段锁（2PL）来避免读写冲突，而是采用了 多版本并发控制（MVCC）。 读操作不会阻塞写操作，写操作也不会阻塞读操作。 每个 SQL 语句（或事务）看到的是某个时间点的数据库快照。 更新操作会产生新的元组版本，旧版本仍然保留，直到不再需要（被所有活跃事务可见）。 关键系统列： 列 含义 xmin 插入该元组的事务 ID xmax 删除或更新该元组的事务 ID（0 表示未删除） cmin / cmax 事务内命令序列号 查询时，PostgreSQL 结合当前事务的快照（xmin、xmax 与当前事务 ID、pg_snapshot）判断可见性。 MVCC 带来的好处 读不阻塞写，写不阻塞读。 SELECT 可以拿到一致性快照，无需加锁。 MVCC 的代价 旧版本元组会一直留在表中，导致表膨胀。 需要后台进程 autovacuum 清理死元组。 长事务会阻止旧版本被清理，加速膨胀。 二、PostgreSQL 锁体系概览 PostgreSQL 的锁分为表级锁和行级锁，还有一个轻量级的 自旋锁（SpinLock） 用于保护共享内存。 2.1 表级锁模式（从低到高冲突程度） 锁模式 关键词 冲突对象 典型场景 ACCESS SHARE SELECT ACCESS EXCLUSIVE 读取表 ROW SHARE SELECT FOR UPDATE/SHARE EXCLUSIVE, ACCESS EXCLUSIVE 准备修改行 ROW EXCLUSIVE INSERT, UPDATE, DELETE SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 修改数据 SHARE UPDATE EXCLUSIVE VACUUM, CREATE INDEX CONCURRENTLY SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 保护模式变更 SHARE CREATE INDEX (非并发) ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 读但阻止写入 SHARE ROW EXCLUSIVE 较少使用 ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 并发读但禁止并发 DML EXCLUSIVE REFRESH MATERIALIZED VIEW (非并发) ROW SHARE 及以上 防止并发读写 ACCESS EXCLUSIVE DROP, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL 所有其他锁 完全独占 查看当前表级锁： SELECT locktype, relation::regclass, mode, granted, pid FROM pg_locks WHERE locktype = 'relation' AND relation = 'orders'::regclass; 2.2 行级锁 行级锁记录在内存的锁表中（不是存储元组中），通过 xmax 标记哪个事务持有锁。 FOR UPDATE：排他行锁。 FOR SHARE：共享行锁。 FOR NO KEY UPDATE、FOR KEY SHARE：用于外键约束的细化锁。 注意：行级锁与表级锁 ROW […]

SQL 与查询优化（PostgreSQL 篇）· 第五期 查询优化器内部机制与高级调参 前四期我们构建了完整的优化知识体系：执行计划、统计信息、连接算法、高级 SQL、物化视图与分区表。 本期我们钻进优化器的“黑盒”，剖析代价模型、参数开关、并行查询，甚至学会强制执行计划——当你比优化器更懂数据时，这些能力会让你如虎添翼。 一、优化器的工作流程回顾 PostgreSQL 优化器基于代价选择执行计划。流程如下： 解析与重写：SQL → 解析树 → 基于规则的重写（视图展开、常量折叠）。 生成路径：为每个表考虑不同的扫描路径（Seq Scan、Index Scan、Bitmap Scan），为每个 JOIN 考虑不同的连接方法（Nested Loop、Hash Join、Merge Join）和连接顺序。 代价估算：利用统计信息和代价参数计算每个路径的启动成本（获取第一行的成本）和总成本。 选择最优路径：选出成本最低的执行计划（默认是最低总成本，可以通过 cursor_tuple_fraction 调整偏好）。 优化器并不完美，因为它依赖于： 统计信息的准确性（第一期、第三期）。 代价参数的合理性（本期重点）。 查询复杂度限制（from_collapse_limit 等，避免指数爆炸）。 二、代价模型参数详解 代价公式（简化）： 总成本 = seq_page_cost * 顺序页数 + random_page_cost * 随机页数 + cpu_tuple_cost * 处理的元组数 + cpu_index_tuple_cost * 索引元组数 + cpu_operator_cost * 操作次数 这些参数定义在 postgresql.conf 中，可以在会话级动态修改。 2.1 核心 I/O 参数 参数 默认值 含义 调优建议 seq_page_cost 1.0 顺序读取一个数据页的代价 SSD 保持不变或略低（0.9），HDD 可以提高到 1.5~2.0 random_page_cost 4.0 随机读取一个数据页的代价 关键参数。SSD 应设为 1.1～1.5，NVMe 甚至可以 1.0；HDD 保持 4.0 effective_cache_size 4GB（根据系统） 操作系统和 PG 共享缓存的总大小（用于评估索引扫描是否受益于缓存） 设置为系统内存的 50%~75%。设太小会低估索引扫描，设太大会高估 原理：当 random_page_cost 远大于 seq_page_cost 时，优化器会偏向 Seq Scan；当两者接近时，Index Scan 更容易被选中。 实例：某系统用 SSD，但未调整 random_page_cost，许多本应走索引的查询走了全表扫描，导致响应时间从 50ms 飙升到 3s。调整到 1.1 后，计划恢复正常。 -- 会话级调整（测试后可以写入配置文件） SET random_page_cost = 1.1; 2.2 CPU 相关参数 参数 默认值 影响 cpu_tuple_cost 0.01 处理一个元组的 CPU 代价。调高会使 Seq Scan 变贵，倾向于减少扫描行数 cpu_index_tuple_cost 0.005 索引扫描中处理一个索引元组的代价 cpu_operator_cost 0.0025 执行一个操作符或函数的代价 通常保持默认值。在极端 OLAP 场景（大量计算），可以适当调高 cpu_tuple_cost 和 cpu_operator_cost 来反映真实负载。 2.3 连接顺序与搜索限制 参数 默认值 说明 from_collapse_limit 8 将子查询提升到主查询的 FROM 列表时，最多处理多少个表 join_collapse_limit 8 显式 JOIN 时，优化器尝试重排连接顺序的最大表数量 当查询涉及超过 8 张表时，优化器会放弃穷举所有连接顺序，采用贪心或启发式算法。对于极端复杂的查询，提高这两个值可以找到更好的计划，但会增加规划时间。 三、enable_* 开关 – 手动干预优化器 当优化器做出错误选择（例如应该用 Hash Join 却用了 Nested Loop，并且无法通过统计信息修正），你可以临时禁用某种扫描或连接方法，强迫优化器选择正确路径。 所有 enable_* 参数默认为 on，可以在会话级或事务级修改。 开关 作用 enable_seqscan 是否允许顺序扫描。关闭后强制走索引（慎用！通常说明统计信息或 cost 参数有问题） enable_indexscan 是否允许索引扫描 enable_indexonlyscan 是否允许仅索引扫描 enable_bitmapscan 是否允许位图扫描 enable_nestloop 是否允许 Nested Loop 连接 enable_hashjoin 是否允许 Hash Join enable_mergejoin 是否允许 Merge Join enable_partition_pruning […]

SQL 与查询优化（PostgreSQL 篇）· 第四期 物化视图、分区表与批量数据优化 前三期我们深入了执行计划、统计信息、连接算法与高级 SQL 能力。本期聚焦数据架构层面的优化：物化视图、分区表以及批量数据处理技巧。 当单表数据量达到亿级，日常查询和分析变得缓慢时，这些技术能让你在不改业务代码的前提下，获得数量级的性能提升。 一、物化视图 – 预计算的艺术 1.1 什么是物化视图？ 普通视图只是保存一条查询规则，每次访问都会重新执行查询。而物化视图（Materialized View） 会实际存储查询结果，就像一张真实的表。你可以对其创建索引、分析统计信息，甚至进行 ANALYZE。 适用场景： 报表统计（复杂聚合、多表连接），数据不需要绝对实时（容忍秒级或分钟级延迟）。 对外提供预计算结果，避免重复执行昂贵查询。 作为数据仓库的中间层，加速 ETL 后续处理。 基本语法： CREATE MATERIALIZED VIEW daily_sales_summary AS SELECT product_id, date(order_date) AS sale_day, sum(amount) AS total, count(*) AS cnt FROM orders GROUP BY product_id, date(order_date); -- 刷新（全量重建，会锁表） REFRESH MATERIALIZED VIEW daily_sales_summary; -- 并发刷新（不阻塞读取，但需要唯一索引） REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary; 1.2 刷新策略与性能 REFRESH MATERIALIZED VIEW：会锁定视图，禁止读取，直到刷新完成。适合低频（如夜间）全量刷新。 REFRESH MATERIALIZED VIEW CONCURRENTLY：使用临时表增量更新，不阻塞并发的 SELECT。但前提是物化视图上必须存在唯一索引（通常是主键或唯一约束）。刷新时间会比非并发模式长，因为需要比较差异。 注意：如果物化视图非常大（几亿行），并发刷新可能会产生大量临时 I/O 和表膨胀。建议根据业务需求选择刷新频率（每小时/每天），并在低峰期执行。 1.3 增量更新 – 物化视图 + 触发器 PostgreSQL 本身不提供物化视图的增量刷新（类似 Oracle 的快速刷新）。但你可以自己实现： 记录源表的变更（通过触发器写入增量日志表）。 定期将增量数据合并到物化视图中（INSERT ... ON CONFLICT DO UPDATE）。 这需要应用业务逻辑，但能换来近乎实时的预计算结果。 简单示例（基于 daily_sales_summary）： -- 增量表 CREATE TABLE sales_delta (like orders); -- 触发器函数将变更插入增量表... -- 然后定时合并： INSERT INTO daily_sales_summary (product_id, sale_day, total, cnt) SELECT product_id, date(order_date), sum(amount), count(*) FROM sales_delta GROUP BY product_id, date(order_date) ON CONFLICT (product_id, sale_day) DO UPDATE SET total = daily_sales_summary.total + EXCLUDED.total, cnt = daily_sales_summary.cnt + EXCLUDED.cnt; TRUNCATE sales_delta; 1.4 物化视图的索引与统计 物化视图就是一张表，因此可以建立索引、执行 ANALYZE，甚至参与分区。 CREATE INDEX ON daily_sales_summary (sale_day); ANALYZE daily_sales_summary; 查询时直接使用物化视图： SELECT * FROM daily_sales_summary WHERE sale_day = '2025-03-01'; 对比普通视图的性能（数据量 1 亿行订单，按日聚合后仅 10 万行）： 普通视图：每次需扫描 1 亿行，聚合耗时 30 秒。 物化视图：预聚合后只扫描 10 万行，加上索引后响应 < 10 毫秒。 1.5 刷新时避免应用中断 对于需要 7×24 服务的系统，刷新策略可以采用 双视图切换： 创建新物化视图 mv_new。 在 mv_new 上重建索引和统计。 通过一个事务：DROP VIEW mv_current; ALTER VIEW mv_new RENAME TO mv_current; 应用代码中始终指向 mv_current（通过视图或动态表名）。 这种方法可以做到刷新过程毫无停机时间（但需要两倍存储空间）。 […]