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（通过视图或动态表名）。 这种方法可以做到刷新过程毫无停机时间（但需要两倍存储空间）。 […]

SQL 与查询优化（PostgreSQL 篇）· 第三期 连接优化与统计信息深度调优 经过前两期，你已经能读懂执行计划、用好窗口函数与 CTE。本期进入多表连接优化的核心地带，并深挖优化器的“大脑”——统计信息。 我们将剖析三种连接算法背后的代价模型，学会用扩展统计信息解决多列关联的基数误判，最终通过真实案例把执行时间从分钟级降至毫秒级。 一、连接算法深度解析 – 优化器的三种武器 当查询涉及多个表时，PostgreSQL 优化器需要在 Nested Loop、Hash Join、Merge Join 中做出选择，并决定连接顺序。这个决策的基础是 基数估计（每步返回的行数）与 代价计算。 1.1 Nested Loop – 索引依赖之王 原理：对外层结果集的每一行，遍历内层表查找匹配行。 代价：cost = outer_rows * inner_scan_cost 如果内层走索引（Index Scan），inner_scan_cost ≈ O(log N)；如果内层全表扫描，代价爆炸。 适用场景： 外层表很小（例如只返回几十行）。 内层表有高效索引（通常是连接键）。 支持非等值连接（如 a.id > b.id），这是 Hash Join 做不到的。 执行计划识别： Nested Loop (cost=0.29..2150.30 rows=10) -> Index Scan using idx_users_id on users (rows=1) -> Index Scan using idx_orders_user_id on orders (rows=10) 1.2 Hash Join – 中等数据集的王者 原理：先构建一个表的哈希表（通常在内存中），然后扫描另一个表探测。 代价：cost = build_cost + probe_cost ≈ O(outer + inner)（无索引也可）。 适用场景： 两表较大，但连接键无索引或索引选择性不佳。 等值连接（= 或 IN）。 内存足够容纳哈希表（work_mem 参数控制，不足则会 spill 到磁盘，严重降低性能）。 执行计划识别： Hash Join (cost=5000..12000 rows=50000) Hash Cond: (o.user_id = u.id) -> Seq Scan on orders o (rows=1000000) -> Hash (cost=3000..3000 rows=100000) -> Seq Scan on users u 调优点： 增大 work_mem 避免哈希表落盘（监控 temp_files）。 调整连接顺序：让行数较少的表作为 build 表（哈希表源）。 1.3 Merge Join – 排序的交换 原理：两表按连接键预先排序，然后像合并有序数组一样归并。 代价：cost = sort_cost(outer) + sort_cost(inner) + merge_cost。 适用场景： 连接键本身有序（例如已有索引，或子查询带 ORDER BY）。 连接条件为不等式（<, > 等）。 数据量非常大且 Hash Join 因内存不足会频繁落盘时。 执行计划识别： Merge Join (cost=15000..18000 rows=50000) Merge Cond: (o.user_id = u.id) -> Index Scan using idx_orders_user_id on orders (rows=1000000) -> Sort (cost=10000..10200 rows=80000) Sort Key: u.id -> Seq Scan on users u 注意：如果两表都需要显式排序，额外开销可能超过 Hash Join。 1.4 连接顺序 – 基数的艺术 多表连接时（如 A JOIN B JOIN C），优化器会尝试不同的连接顺序。基数估计的准确性直接影响顺序选择。例如： SELECT * FROM A JOIN B […]

SQL 与查询优化（PostgreSQL 篇）· 第二期 窗口函数与 CTE 的深度优化 继第一期掌握执行计划与索引基础后，本期聚焦 SQL 的高级能力：窗口函数（Window Functions） 与 公共表表达式（CTE）。 你将学会如何用它们写出更简洁高效的查询，同时避开常见的性能陷阱——包括 CTE 物化屏障、递归 CTE 的优化技巧，以及窗口函数与索引的协作之道。 一、窗口函数 – 不改变行数的聚合利器 1.1 核心概念 窗口函数在保留每一行原始数据的同时，基于一组行（窗口）进行计算。 相比于聚合 GROUP BY（行数减少），窗口函数不压缩结果集。 语法模板： 函数() OVER ( [PARTITION BY 分组列] [ORDER BY 排序列] [ROWS/RANGE 窗口帧子句] ) 常见窗口函数： 类别 函数 用途 排名 ROW_NUMBER(), RANK(), DENSE_RANK() 为行分配序号 偏移 LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE() 访问同一分区内前后行 分布 NTILE(n), PERCENT_RANK() 分桶与百分位 常规聚合 SUM(), AVG(), COUNT(), MAX(), MIN() 移动或累积聚合 1.2 典型高效场景 (1) 每组取 Top N（如每个客户最近 3 笔订单） WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders ) SELECT * FROM ranked WHERE rn <= 3; 优化要点： PARTITION BY customer_id 配合索引 (customer_id, order_date DESC) 可实现 仅索引扫描，避免显式排序。 若表巨大，可加 INCLUDE 覆盖其他输出列。 (2) 计算环比/同比（LAG 替代自连接） ❌ 低效的自连接写法： SELECT o1.date, o1.amount, o2.amount AS prev_amount FROM sales o1 LEFT JOIN sales o2 ON o1.date = o2.date + interval '1 day'; ✅ 窗口函数高效写法： SELECT date, amount, LAG(amount, 1) OVER (ORDER BY date) AS prev_amount FROM sales; 自连接会产生嵌套循环或合并连接，而窗口函数只需一次扫描，按序计算。 若 date 字段有唯一索引，可进一步走 Index Only Scan。 (3) 移动窗口聚合（7 日移动平均） SELECT date, amount, AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d FROM sales; ROWS 基于物理行号，适合日期连续场景；若日期有缺失，建议用 RANGE 基于实际时间间隔（需配合 ORDER BY date 且 date 类型可加减）。 1.3 窗口函数的执行计划与优化 执行计划中窗口函数体现为 WindowAgg 节点。 […]

SQL 与查询优化（PostgreSQL 篇）· 第一期 读懂执行计划 & 索引优化入门 本系列专注于 PostgreSQL 中 SQL 能力的深度挖掘与查询性能调优。 第一期从最核心的工具——执行计划入手，结合统计信息与索引选择，带你建立系统性的优化思维。 一、为什么查询优化如此重要？ 很多开发人员写出功能正确的 SQL，却忽略了执行效率。当数据量从万级增长到千万级，一个缺少索引或写法不佳的查询可能导致响应时间从毫秒变成分钟。PostgreSQL 提供了强大的优化器、丰富的索引类型和精准的代价模型，但前提是你要学会如何“引导”它。 优化不是盲目加索引，而是： 看懂执行计划 – 知道 PG 实际怎么执行你的 SQL。 理解统计信息 – 让优化器获得准确的基数估算。 合理选择索引与改写 SQL – 用最小的代价让查询走最优路径。 二、执行计划 – 透视 PG 的内部动作 2.1 基础命令：EXPLAIN EXPLAIN SELECT * FROM orders WHERE customer_id = 12345; 输出示例： Seq Scan on orders (cost=0.00..2043.00 rows=10 width=88) Filter: (customer_id = 12345) Seq Scan：顺序扫描整张表。 cost：第一个数字是启动成本（获取第一行的代价），第二个数字是总成本。 rows：优化器估计返回的行数。 width：结果行平均字节数。 真正执行并返回实际耗时： EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345; ANALYZE 会真正运行查询，输出实际执行时间、实际行数、内存使用等，是分析的黄金工具。 ⚠️ 注意：EXPLAIN ANALYZE 会修改数据（如果 SQL 包含 INSERT/UPDATE/DELETE），建议在事务中回滚或使用只读查询。 2.2 常见扫描方式 扫描类型 说明 适用场景 Seq Scan 全表顺序扫描 表很小、或需要读取大部分数据（>5%~10%）、或没有可用索引 Index Scan 索引扫描 + 回表 索引过滤条件强，返回行数较少 Index Only Scan 仅索引扫描 所有需要的字段都在索引中，不需要回表 Bitmap Index Scan + Bitmap Heap Scan 位图扫描 多个索引条件组合，或返回行数中等（减少随机 I/O） 举例： -- 创建索引后 CREATE INDEX idx_orders_customer ON orders(customer_id); EXPLAIN SELECT * FROM orders WHERE customer_id = 12345; 输出变为： Index Scan using idx_orders_customer on orders (cost=0.29..8.30 rows=10 width=88) Index Cond: (customer_id = 12345) 2.3 连接方法（Join 策略） PostgreSQL 有三种常见连接算法： 连接方式 原理 复杂度 适用场景 Nested Loop 外层表每行去内层表匹配 O(outer * inner) 驱动表很小，内层表有索引 Hash Join 构建一个表的哈希表，另一表探测 O(outer + inner) 两表较大且等值连接，无索引可用 Merge Join 两表按连接键排序后归并 O(outer + inner) 两表已有序或连接键有序，常用于不等值连接 阅读执行计划时，注意最里层（缩进最多的节点）最先执行。例如： Hash Join (cost=...) Hash Cond: (o.customer_id = c.id) -> Seq Scan on orders o -> Hash -> Seq Scan on customers c 先扫描 […]

PostgreSQL 架构原理第六期：性能调优实战 —— 从参数到 SQL 的全链路优化 引言 前五期我们分别剖析了 PostgreSQL 的进程模型、存储引擎、事务与并发控制、查询优化器以及备份恢复体系。理解这些内部机制的根本目的，是能够诊断和解决实际生产环境中的性能问题。本期将把这些知识串联起来，聚焦于性能调优实战，提供一套系统性的方法论。 本文涵盖： 性能调优的顶层思路与测量手段 操作系统与硬件层面的优化要点 PostgreSQL 核心参数详解与配置建议 SQL 改写与索引设计的实战技巧 监控工具与慢查询定位 真实案例：从 30 秒到 30 毫秒的调优过程 一、性能调优的方法论 调优不是盲目修改参数，而应遵循以下步骤： 明确目标：吞吐量（TPS/QPS）还是延迟（P99 响应时间）？面向 OLTP 还是 OLAP？ 建立基准：使用 pgbench 或业务压测脚本，收集正常负载下的性能指标。 识别瓶颈：通过系统层（CPU、内存、磁盘 I/O、网络）和数据库层（等待事件、锁、慢查询）综合分析。 提出假设：例如“索引缺失导致顺序扫描”、“shared_buffers 太小导致大量缓存 miss”。 单变量修改：一次只改一个参数或一条 SQL，验证效果。 迭代回归：在测试环境重现并确认无副作用后，应用于生产。 黄金法则：先定位瓶颈，再动手优化。盲目调整参数往往适得其反。 二、操作系统与硬件层面的优化 数据库性能最终受限于硬件。以下为常见最佳实践： 2.1 CPU PostgreSQL 的进程模型对 CPU 核心数敏感。OLTP 场景需要高单核频率；OLAP 场景更依赖多核并行。 配置 max_parallel_workers_per_gather 让分析查询利用多核。 注意 CPU 节能模式（cpufreq）应设为 performance。 2.2 内存 物理内存充足：数据库内存消耗包括 shared_buffers、操作系统缓存、每个后端进程的 work_mem 等。 禁用 swap 或降低 vm.swappiness（建议 10 以下），避免内存交换导致抖动。 2.3 磁盘与文件系统 使用 SSD/NVMe 时，务必调整 random_page_cost = 1.0 或 1.1（默认 4.0），否则优化器会严重低估随机读代价，导致本该走索引的查询选择顺序扫描。 文件系统推荐 XFS（或 ext4 + noatime 挂载选项）。 将 pg_wal 目录单独放在低延迟设备上（如单独的 SSD 分区），减少与数据文件的 I/O 争用。 2.4 网络 流复制和远程访问需要低延迟网络。备库与主库之间的延迟直接影响同步复制的事务提交时间。 考虑使用专用网卡或绑定多队列。 三、PostgreSQL 核心参数调优 以下参数按影响面从大到小排列，并提供推荐的初始值和调优原则。 3.1 内存相关 参数 默认值 推荐范围 说明 shared_buffers 128MB 系统内存的 15% ~ 25% 缓存数据页。设置过大会导致 checkpoint 压力大，并且与操作系统页缓存竞争。通常 OLTP 设为 4GB~8GB，OLAP 可更高。 effective_cache_size 4GB 系统内存的 50% ~ 75% 优化器估算顺序扫描代价的依据，应设置为操作系统预计可用于文件缓存的大小。 work_mem 4MB 根据并发和内存调整 排序、哈希表等内部操作的内存上限。注意是每个操作、每个 backend 独立消耗，高并发时容易 OOM。可先设为 8MB~64MB，观察 explain 中是否出现 Disk 操作。 maintenance_work_mem 64MB 512MB ~ 2GB VACUUM、CREATE INDEX 等维护操作可用内存。可设较大（如 1GB）加速维护。 wal_buffers 16MB -1 自动（通常 16MB） WAL 缓冲区大小。写入负载高时可适当增加至 32MB~64MB。 3.2 写入与检查点 参数 推荐调整 说明 synchronous_commit off 或 remote_write（备库） 异步提交可大幅提升写入吞吐，但故障时可能丢失最近事务。主库根据 RPO 要求权衡。 wal_writer_delay 200ms（默认） WAL 刷写间隔。写入压力大时可降至 50ms，但增加 CPU。 checkpoint_timeout 15min（默认） 检查点间隔。可增加到 30min 减少刷写频率，但恢复时间变长。 max_wal_size 1GB（旧版） 建议设为总 WAL 空间上限，如 64GB。min_wal_size 设为 1~2GB。 checkpoint_completion_target 0.9 让检查点平滑写入，避免 I/O 尖峰。 3.3 查询优化器相关 针对 SSD 环境： random_page_cost = […]

PostgreSQL 架构原理第五期：备份与恢复 —— 物理备份、PITR 与复制技术 引言 在前四期中，我们从进程模型、存储引擎、事务与并发控制，到查询优化器，逐步深入了解了 PostgreSQL 的内核原理。数据是企业的核心资产，如何保证数据不丢失、如何从灾难中快速恢复、如何实现 7×24 小时高可用，是数据库运维的必修课。 本期将聚焦 PostgreSQL 的备份与恢复体系，涵盖以下内容： 逻辑备份与物理备份的对比与适用场景 基于 WAL 的持续归档与时间点恢复（PITR）原理 pg_basebackup 与低级别基础备份的实现机制 物理复制（流复制）与高可用架构（主备、同步/异步、故障切换） 逻辑复制的原理、用途与与物理复制的差异 备份恢复的最佳实践与常见陷阱 一、备份方法概览：逻辑备份 vs 物理备份 特性 逻辑备份 物理备份 工具 pg_dump / pg_dumpall pg_basebackup、文件系统快照 备份内容 SQL 语句或自定义格式的归档 数据目录的完整二进制文件（包括 WAL 文件） 恢复粒度 可恢复单个表或数据库 只能整个集群恢复（不能跨版本或选择性恢复） 恢复速度 慢（需要重放 SQL 并重建索引） 快（直接复制文件，应用 WAL） 并行能力 可并行（-j 参数） 支持并行传输 适用场景 小数据量、跨版本升级、部分数据导出 大数据量、灾难恢复、高可用搭建 一致性保证 基于快照（SERIALIZABLE） 基于 WAL 的全局一致性（需要归档或使用复制协议） 核心结论： 日常开发、小规模迁移用逻辑备份。 生产环境中，基于物理备份 + WAL 归档的 PITR 方案是最可靠的容灾手段。 二、物理备份基础：数据目录与 WAL 文件 PostgreSQL 的物理备份本质上是文件系统级别的复制，但必须保证复制出的文件处于一致性状态。单纯用 cp 或 rsync 复制正在运行的数据库的数据目录，会得到不一致的备份（部分已刷盘的页面 vs 部分未刷盘）。为了解决这个问题，PostgreSQL 提供了两种方法： 低级别 API：使用 pg_start_backup() 与 pg_stop_backup() 配合文件系统工具。 高层工具：pg_basebackup 内部使用复制协议，自动完成上述步骤。 2.1 低级别基础备份流程 -- 1. 开始备份，创建一个标签文件（backup_label） SELECT pg_start_backup('label_name'); -- 2. 使用外部工具拷贝整个数据目录（可以跳过 pg_wal 目录，但需要另外归档） cp -R $PGDATA /backup/base -- 3. 结束备份，自动生成备份历史文件 SELECT pg_stop_backup(); pg_start_backup 执行检查点，强制将当前 WAL 位置写到 backup_label 文件中，并记录起始 LSN。 拷贝期间数据库可以继续读写，但所有修改都会写入 WAL。 pg_stop_backup 生成一个备份历史文件（.backup），其中包含备份的起始/结束 LSN 以及所需 WAL 文件列表。恢复时需要这些 LSN 信息。 这种方法的优点是不依赖 pg_basebackup，适合结合 ZFS、LVM 快照或 rsync 脚本实现定制化备份。 2.2 pg_basebackup 工具 pg_basebackup 使用复制协议连接到 PostgreSQL 主库，自动完成 pg_start_backup、数据传输和 pg_stop_backup。常用选项： pg_basebackup -D /path/to/backup -F t -z -X fetch -P -U repluser -h host -F t：输出为 tar 包；-F p 为普通目录。 -X fetch：备份过程中同时获取所需的 WAL 段文件（stream 模式更实时）。 -P 显示进度。 注意：pg_basebackup 要求备份用户具备 REPLICATION 权限，并且需要启用 max_wal_senders > 0。 三、持续 WAL 归档与时间点恢复（PITR） 物理备份本身是某一时刻的静默拷贝，要恢复到最新状态或任意时间点，必须配合增量 WAL 日志。 3.1 WAL 归档的原理 在 postgresql.conf 中设置： archive_mode = on archive_command = 'cp %p /archive/%f' # 将完成的 WAL 段文件复制到归档目录 每当 […]

PostgreSQL 架构原理第四期：查询优化器 —— 统计信息与代价模型 引言 在前三期的基础上，我们已经了解了 PostgreSQL 的进程模型、存储引擎以及事务并发控制。从用户提交一条 SQL 到真正执行，中间有一个至关重要的环节——查询优化器。它负责在众多可能的执行路径中，找出预计代价最小的那个执行计划。优化器之所以能做出相对准确的决策，核心依赖于两样东西：表的统计信息 和 代价模型。 本期我们将深入剖析： 统计信息的收集与存储：ANALYZE 命令、pg_statistic 系统表、pg_class 中的基本统计 最常见的数据分布：NULL 分数、唯一值数量、高频值（MCV）、直方图 代价模型中的启动代价、运行代价、总代价以及各种操作的代价估算公式 单表扫描（顺序扫描、索引扫描、位图扫描）的代价计算 连接操作的代价估算（嵌套循环、哈希连接、归并连接） 统计信息过时或不准时的应对策略 一、统计信息的收集：ANALYZE 命令 统计信息是优化器的“眼睛”。PostgreSQL 通过 ANALYZE 命令（包含在 VACUUM ANALYZE 或自动 autovacuum 中）收集表和索引的统计信息。 1.1 基本统计存储位置 pg_class：存储表级和索引级的全局统计，如： reltuples：表的总行数估计值 relpages：表的磁盘页数（1 页 = 8KB） relallvisible：可见性映射中标记为全部可见的页数 relfrozenxid：表的冻结阈值 pg_statistic：存储每个列的具体数据分布统计。为了安全，普通用户只能通过视图 pg_stats 查看，其内容经过脱敏（例如去除隐私数据）。 1.2 ANALYZE 的工作原理 ANALYZE 并不扫描全表（除非表很小），而是采用采样方法： 从每个表或索引中随机抽取约 30000 行（由 default_statistics_target 控制，默认 100，实际采样行数为 300 × 目标值，因为内部乘了 300 的因子）。 对每个目标列进行统计： null_frac：NULL 值比例 n_distinct：不同值的数量（如果为负数，表示比例；例如 -0.1 表示大约有 0.1 * 总行数） most_common_vals 和 most_common_freqs：最常见的值及其出现频率 histogram_bounds：等频直方图边界值（当值种类超过一定数量时，不存储 MCV 而使用直方图） correlation：物理行序与逻辑值的相关性（-1 到 1），影响索引扫描代价 采样完成后，将统计信息存入 pg_statistic 并更新 pg_class.reltuples 和 relpages。 1.3 统计信息收集参数 default_statistics_target：默认 100。增大该值会提高采样行数，得到更精确的统计，但 ANALYZE 时间和内存消耗会增加。 可以为特定列设置更高的目标：ALTER TABLE t ALTER COLUMN c SET STATISTICS 500; 自动 ANALYZE 由 autovacuum 触发，当表修改行数达到一定阈值时自动运行。 二、代价模型的基本概念 PostgreSQL 采用基于代价的优化（cost-based optimization），为每个可能的执行计划节点计算一个浮点数值代价。代价的单位并不是真实的时间，而是磁盘顺序页读取的耗时作为基准单位。其他操作（如 CPU 处理、随机页读取）的代价都相对于此进行量化。 2.1 代价的组成部分 每个计划节点都包含三个主要代价字段： startup_cost：获取第一条返回行之前需要付出的代价。例如对索引扫描，需要先读取索引找到第一匹配项。 total_cost：获取所有行（假设所有行都被检索）的总代价。 run_cost = total_cost – startup_cost，通常不直接显示，但内部计算会用到。 优化器最终选择 total_cost 最小的完整计划树，但当使用游标或限制子句时，startup_cost 可能会更重要（例如希望尽快返回第一行）。 2.2 代价参数 代价公式中可配置的成本因子参数（均位于 postgresql.conf）： 参数 默认值 含义 seq_page_cost 1.0 顺序页读取代价（基准） random_page_cost 4.0 随机页读取代价（通常设为 1.5~2.0 对于 SSD） cpu_tuple_cost 0.01 处理一个元组的 CPU 开销 cpu_index_tuple_cost 0.005 索引扫描时处理一个索引元组的开销 cpu_operator_cost 0.0025 执行一个操作符或函数的开销（如 =、+） parallel_setup_cost 1000 并行查询启动子进程的代价 parallel_tuple_cost 0.1 并行查询中传输一个元组的代价 这些参数可以根据硬件特性调整，例如在全闪存存储上降低 random_page_cost 可以提高索引扫描的竞争力。 2.3 代价计算公式举例 处理一个元组的 CPU 代价通常为 cpu_tuple_cost。处理一个 WHERE 子句中的操作符代价为 cpu_operator_cost 乘以操作符个数。读取一个数据页的代价根据顺序或随机读取而不同。 三、单表扫描的代价估算 3.1 顺序扫描（Seq Scan） 顺序扫描整个表，读取所有页面。代价公式： startup_cost = 0 run_cost = (pages × seq_page_cost) + (tuples × cpu_tuple_cost) total_cost = run_cost 其中 pages = pg_class.relpages，tuples = pg_class.reltuples。 […]

PostgreSQL 架构原理第三期：事务与并发控制 —— MVCC、快照与锁机制 引言 前两期我们分别从进程模型、内存结构、查询流程以及存储引擎的角度剖析了 PostgreSQL 的内部机制。本期将聚焦于数据库并发控制的核心——事务与隔离。PostgreSQL 凭借其实现精巧的多版本并发控制（MVCC），能够在不使用传统读锁的情况下提供高并发读写的隔离性，同时避免了“读-写”阻塞问题。 本文将系统讲解： 事务 ID 与元组头中的版本信息 事务状态与 Commit Log（clog） 快照（Snapshot）的构成与可见性判断规则 四种隔离级别及 PostgreSQL 的具体实现 锁机制：表级锁、行锁、页锁与死锁检测 可串行化快照隔离（SSI）的原理浅析 一、事务与事务 ID 每个事务在启动时都会获得一个唯一的事务标识符（XID），它是一个 32 位无符号整数，取值范围约为 42 亿。PostgreSQL 采用循环使用机制，通过 VACUUM 处理事务回卷（wraparound）问题。 XID 的分配发生在事务执行任意写操作或使用 SELECT FOR UPDATE 等显式加锁语句时。只读事务默认不分配 XID，除非指定了 SERIALIZABLE 隔离级别。 1.1 特殊 XID 0：InvalidXid，表示无效事务。 1：BootstrapXid，表示系统表初始化事务。 2：FrozenXid，用于冻结元组，表示该元组对所有事务均可见且早于所有正常 XID。 1.2 元组头上的版本信息 回顾第二期中堆元组头的关键字段： t_xmin：插入元组的事务 XID。 t_xmax：删除或更新元组的事务 XID；若为 0，表示元组尚未被删除。 t_cid：事务内命令序号，用于同一事务中前后命令间的可见性。 t_ctid：指向当前元组或新版本元组的物理位置（页号+行指针）。 当执行 UPDATE 时，PostgreSQL 实际上执行的是： 将旧元组的 t_xmax 设置为当前事务 XID，t_ctid 指向新元组。 插入新元组，其 t_xmin 为当前事务 XID，t_xmax 为 0，t_ctid 指向自身。 执行 DELETE 时，仅将旧元组的 t_xmax 设为当前事务 XID。 二、事务状态与 Commit Log（clog） 光有 t_xmin 和 t_xmax 还不够，查询时需要知道一个事务到底是已提交还是已中止。PostgreSQL 将每个事务的状态存储在 Commit Log（clog） 中，位于数据目录下的 pg_xact 子目录（早期版本为 pg_clog）。 2.1 clog 存储格式 每个事务状态占用 2 个比特位，可能的取值： TRANSACTION_STATUS_IN_PROGRESS (0x00) —— 进行中 TRANSACTION_STATUS_COMMITTED (0x01) —— 已提交 TRANSACTION_STATUS_ABORTED (0x02) —— 已中止 TRANSACTION_STATUS_SUB_COMMITTED(0x03) —— 子事务已提交（内部用） clog 被划分为多个 8KB 页面，每个页面可存储 32K 个事务的状态（8KB × 1024 字节/页 × 4 个事务/字节 = 32768）。PostgreSQL 会将 clog 页面缓存到共享内存中，以减少 I/O。 2.2 事务状态查询流程 当可见性判断需要知道某个 XID 的状态时： 根据 XID 计算出所在 clog 页面及页内偏移。 若页面不在共享缓存中，从 pg_xact 读取并缓存。 读取 2 比特状态，返回 COMMITTED 或 ABORTED。 为了加速频繁访问的事务状态，PostgreSQL 还在元组头的 t_infomask 中缓存了两个标志位：HEAP_XMIN_COMMITTED 和 HEAP_XMIN_ABORTED（类似地对 t_xmax 也有缓存）。一旦确认过事务提交状态，就设置相应标志位，避免重复查询 clog。 三、快照（Snapshot）与可见性判断 MVCC 的核心思想是为每个查询（或事务）提供一个数据的“快照”，根据快照中的事务状态信息判断每个元组是否对当前查询可见。 3.1 快照的结构 快照主要由以下几个数组组成： xmin：快照中最早仍活跃的事务 ID（即所有小于 xmin 的 XID 要么已提交，要么已中止）。 xmax：快照中第一个未分配的事务 ID（即所有 XID >= xmax 都视为未开始）。 xip：当前活跃（进行中）的事务 ID 列表。 例如：当前活跃事务为 [100, 105, 110]，则 xmin = 100，xmax = 111（假设下一个未使用的 XID 是 111），xip 包含 100、105、110。 注意： 对于 READ […]

PostgreSQL 架构原理第二期：存储引擎深度解析 —— 堆表、元组结构与 TOAST 机制 引言 在上一期中，我们从整体架构入手，探讨了 PostgreSQL 的进程模型、共享内存、查询处理全流程、WAL 以及缓冲区管理器。理解这些上层机制后，一个自然的问题是：数据最终是如何在磁盘上组织存储的？PostgreSQL 的存储引擎以“堆表 + 索引 + TOAST”的组合方式，实现了高效的按行存取、可变长度字段支持以及 MVCC 所需的多版本管理。 本文将深入数据文件的内部世界，详细讲解以下内容： 逻辑结构与物理文件的映射关系 堆表文件的组成：主文件、FSM、VM 页面（Page）的内部布局 元组（Tuple）的存储格式与关键字段 TOAST 机制的触发条件与工作原理 索引的基本存储结构（以 B-tree 为例） 一、逻辑结构到物理文件的映射 PostgreSQL 中，一个数据库实例包含多个数据库，一个数据库包含多个模式，模式中包含表、索引等对象。每个表（或索引）在物理上对应着一个或多个磁盘文件。 1.1 OID 与 relfilenode 每个数据库对象都有一个唯一的对象标识符（Object Identifier, OID），存储在其系统表 pg_class 中。对于表和索引，pg_class.relfilenode 字段指定了磁盘上使用的文件名。绝大多数情况下 relfilenode = OID，但执行 TRUNCATE、REINDEX 或某些 VACUUM FULL 操作时，relfilenode 会发生变化，从而创建一个新文件。 1.2 表空间与路径 表空间（tablespace）允许将不同的数据库对象存储在不同的文件系统位置上。每个表空间目录下包含以数据库 OID 命名的子目录，数据库子目录下存放着以 relfilenode 命名的文件。默认表空间 pg_default 使用数据目录下的 base 目录。 1.3 超过 1GB 的文件分段 为了避免某些操作系统对单个文件大小的限制（如 2GB），PostgreSQL 将大于 1GB 的文件自动分割成多个段（segment）。主文件名为 relfilenode，后续段命名为 relfilenode.1，relfilenode.2，依此类推。段大小可在编译时通过 --with-segsize 修改，默认 1GB。 二、堆表的相关文件 对于一个普通的堆表（heap table），通常会伴随三个文件： 文件后缀 含义 无 主数据文件，存储实际的行数据 _fsm 空闲空间映射（Free Space Map） _vm 可见性映射（Visibility Map） 2.1 空闲空间映射（FSM） FSM 记录了每个数据页内部的空闲空间大小。当需要插入新行时，PostgreSQL 不需要扫描所有页面来寻找空闲空间，而是快速查询 FSM 找到足够空间的页面。FSM 本身使用一棵三层的树结构组织，存储在专用的 _fsm 文件中。 2.2 可见性映射（VM） VM 标记哪些页面中全部元组对所有当前事务都是可见的（即没有任何需要清理的死元组）。VACUUM 操作可以跳过这些页面，显著提高清理效率。同时，仅索引扫描（Index-Only Scan） 依赖 VM 来判断是否可以直接使用索引元组而不回表访问堆元组。VM 以位图形式存储，每个位代表一个页面。 三、页面内部结构 PostgreSQL 磁盘数据的基本单位是 页面（Page），默认大小为 8KB。一个页面可以分为几个逻辑区域： +---------------------------+ <-- 页头 | PageHeaderData (24 bytes) | +---------------------------+ <-- 行指针起始 | ItemIdData (4 bytes each) | 指向实际元组的偏移量和长度 | ... | +---------------------------+ <-- 空闲空间 | 未使用空间 | | ... | +---------------------------+ <-- 元组数据起始 | 元组数据 (HeapTuple) | 从页面尾部向前填充 | ... | +---------------------------+ <-- 特殊空间 (可选) 3.1 页头 PageHeaderData 页头固定 24 字节（不含可选的特殊空间），包含以下重要字段： pd_lsn：本页面最后一次修改对应的 WAL 日志的 LSN。 pd_checksum：页面的校验和（如果启用）。 pd_flags：标志位，如是否包含空行指针、是否全部可见等。 pd_lower：行指针区域末尾的偏移量（即空闲空间开始处）。 pd_upper：元组数据区域起始的偏移量（即空闲空间结束处）。 pd_special：特殊空间起始偏移量（例如 B-tree 索引页面用于存放右兄弟指针）。 pd_pagesize_version：页面大小及版本信息。 pd_prune_xid：本页面中需要清理的最早旧事务 ID，用于提示 VACUUM。 行指针（ItemIdData，4 字节）包含元组在页面内的偏移量、长度以及一些标志位。行指针数组从 pd_lower 向上增长，元组数据从 pd_upper 向下增长。当两者相遇时，页面空间耗尽。 3.2 特殊空间 只有某些访问方法需要特殊空间。对于堆表，特殊空间大小为 0；对于 B-tree 索引页面，特殊空间存储了页面级别信息（如右兄弟页号）。 四、元组（Tuple）的存储格式 堆表中的每一行对应一个元组。元组包含两部分：元组头 和 用户数据。 4.1 元组头结构 HeapTupleHeaderData 元组头长度固定为 […]

PostgreSQL 架构原理第一期：从进程模型到核心内存机制 引言 PostgreSQL 被誉为“世界上最先进的开源关系型数据库”，其强大的功能和可靠性背后，是一套经过数十年打磨的精致架构。理解 PostgreSQL 的内部工作原理，不仅能帮助我们写出更高效的查询，更能为性能调优和问题诊断打下坚实基础。 本文将带大家走进 PostgreSQL 的内核世界，从宏观的进程模型到微观的内部组件，循序渐进地揭示它是如何工作的。本文将重点涵盖以下内容： “进程每用户”模型及连接建立流程 进程间通信与共享内存的核心角色 查询处理的完整生命周期 WAL 机制及其在数据安全中的作用 缓冲区管理器的三层结构 一、PostgreSQL 的进程架构：每个用户一个进程 PostgreSQL 的实现采用了一种经典的 “每用户一个进程” （process per user）的客户端/服务器模型。在这个模型中，每个客户端进程都恰好连接到一个后端（backend）进程。 由于我们事先无法预知会有多少个连接建立，PostgreSQL 使用一个特殊的“监督进程”来管理所有连接，这个进程被称为 postmaster。postmaster 在指定的 TCP/IP 端口上持续监听传入的连接请求，每次检测到一个连接请求时，它就派生（fork）出一个新的后端进程来专门处理这个连接。 一旦建立连接，客户端进程就可以向后端进程发送 SQL 查询。查询以纯文本形式传输，客户端无需进行解析。后端进程负责解析查询、创建执行计划、执行计划，并将结果行返回给客户端。 这种进程模型的设计有如下几个优点： 隔离性：每个后端进程拥有独立的内存空间，一个后端进程的崩溃不会影响其他连接。 可扩展性：可以根据连接数灵活地增加或减少进程数量。 安全性：进程级别的隔离为多用户环境提供了天然的安全边界。 二、共享内存：进程协作的桥梁 2.1 共享内存的角色 多个后端进程需要协同工作，共享数据和状态信息。这是通过 共享内存 实现的。PostgreSQL 使用信号量和共享内存来确保在并发数据访问期间的数据完整性。 当数据库服务器启动时，它会向操作系统请求分配一块足够大的共享内存区域，用来存储数据库的全局状态信息、锁信息、缓存数据（如缓冲区缓存、WAL 缓冲区等）以及其他需要快速访问的数据结构。 2.2 共享内存的主要组件 PostgreSQL 的共享内存包含几个关键部分： 共享缓冲区（shared_buffers） ：这是共享内存最主要、容量最大的区域，用于缓存表和索引的数据页，以减少磁盘 I/O。 WAL 缓冲区（wal_buffers） ：用于暂存即将写入预写日志（WAL）的事务日志记录，然后再刷新到磁盘。 锁信息区域：存储各种锁结构，用于协调进程间的并发控制。 全局状态信息：如后台进程的状态、系统统计信息等。 2.3 内存分区概览 除了共享内存外，每个后端进程还拥有独立的本地内存区域，主要包含： work_mem：用于排序操作和哈希表的内存（如 ORDER BY、DISTINCT、哈希连接等）。 maintenance_work_mem：用于 VACUUM、REINDEX 等维护操作的内存。 temp_buffers：用于存储临时表的内存。 这种本地内存和共享内存分工明确的设计，使得 PostgreSQL 能够在保证数据一致性的同时获得较高的并发性能。 三、SQL 查询的完整生命周期 一条 SQL 查询从提交到返回结果，需要经过多个处理阶段。下面我们按流程逐一展开。 3.1 解析器 —— 语法检查与查询树生成 当查询以纯文本形式从客户端发送到后端进程后，后端进程的 解析器 首先对查询进行语法检查。如果查询语法正确，解析器会创建一个 查询树 数据结构，表示查询的结构和语义。 查询树包含了查询中涉及的表、列、常量表达式以及操作符等信息，是后续处理步骤的基础。 3.2 重写系统 —— 视图展开与规则应用 解析生成的查询树随后进入 重写系统。重写系统的核心任务是根据系统表（system catalogs）中存储的规则，对查询树进行转换。 视图（View）是重写系统的一个典型应用场景。当用户查询一个视图时，重写系统会将针对视图的查询重写为直接访问基表的查询。这个转换对用户而言是完全透明的。 3.3 优化器 —— 寻找最优执行计划 重写后的查询树被传递给 规划器/优化器。优化器的任务是创建一个预计执行速度最快的执行计划。 优化器的工作流程大致如下： 生成扫描计划：首先为查询中使用的每个关系（表）生成可能的扫描方式，例如顺序扫描和索引扫描。 选择连接策略：如果查询涉及连接操作，优化器会从三种策略中选择——嵌套循环连接、合并连接 和 哈希连接，并搜索不同的连接顺序。 代价估算：优化器使用一种基于 路径（Path） 的数据结构来简化计划的表示，并为每个候选路径估算执行代价。 选择最佳路径：将代价最低的路径展开为完整的 计划树，传递给执行器。 当查询中涉及的关系数量超过 geqo_threshold 阈值时，优化器会使用 遗传查询优化器（GEQO） 进行启发式搜索。 3.4 执行器 —— 执行计划并返回结果 最终阶段，执行器 接收计划树，递归地遍历树的各个节点，按照计划所描述的方式从存储系统中扫描关系、执行排序和连接操作、计算条件表达式，并将最终的行结果返回给客户端。 执行器在处理过程中会调用缓冲区管理器来高效地读写磁盘页面。 四、预写日志（WAL）：数据完整性的基石 预写日志（Write-Ahead Logging，WAL）是 PostgreSQL 确保数据完整性和持久性的核心机制。 4.1 WAL 的核心概念 WAL 的核心概念非常简单：对数据文件的更改必须在这些更改被记录到日志文件之后才能写入磁盘。也就是说，描述更改的 WAL 记录必须先刷新到持久存储。 通过这个过程，即使在数据库崩溃后，我们也能使用 WAL 日志来恢复数据：任何尚未应用于数据页的更改都可以从 WAL 记录中重做（REDO），从而使数据库恢复到一致的状态。 4.2 WAL 的性能优势 WAL 带来了显著的性能提升： 减少磁盘写入：只需要将 WAL 文件刷新到磁盘即可保证事务已提交，不再需要将每个数据文件都刷新。 顺序写入友好：WAL 文件是顺序写入的，而顺序写入的成本远低于随机写入。 批量提交：处理多个并发小事务时，一次 fsync 即可提交多个事务。 4.3 WAL 的存储结构 WAL 文件存储在数据目录下的 pg_wal 子目录中，作为一系列段文件，每个段文件默认大小为 16MB。每个段又划分为页面，每页默认 8KB。 每个 WAL 记录的位置由 日志序列号（LSN） 唯一标识，LSN 是 WAL 中的一个字节偏移量，随着每个新记录的写入而单调递增。 4.4 检查点与恢复 PostgreSQL 定期执行 检查点 操作，将所有脏页（已被修改的数据页）写入磁盘，这使得崩溃后的恢复只需从最新的检查点开始重放后续的 WAL 记录即可，极大地加速了恢复过程。 WAL 技术还使得 在线备份 和 时间点恢复（PITR） 成为可能，只需保存数据库的物理备份和归档的 WAL 日志，即可恢复到任意时间点。 五、缓冲区管理器：内存与磁盘的中转站 5.1 总体结构 缓冲区管理器管理着共享内存缓冲池和持久存储之间的所有数据传输，对数据库的性能有着至关重要的影响。PostgreSQL 的缓冲区管理器由三个核心组件构成： 缓冲池（Buffer Pool） ：一个数组结构，每个槽存储一个数据文件页面（如表页、索引页），每个页面大小为 8KB。数组的索引称为 buffer_id。 缓冲区描述符（Buffer Descriptor） ：与缓冲池槽一一对应的结构体数组。每个描述符保存着对应槽的元数据，包括页面的状态（如是否脏页）、引用计数、使用次数等信息。 缓冲表（Buffer Table） […]