前言一个让 DBA 惊出冷汗的诡异现象在传统的关系型数据库认知中“读Read”与“写Write”是天生分离的两条路径。在大部分开发者的直觉里执行一条标准的SELECT * FROM tenant_orders WHERE user_id 10086;语句数据库引擎应该只是默默地打开磁盘文件、将数据页加载到内存、过滤数据、最后返回给客户端。整个过程中绝对不应该对存储系统踩下任何一个“写”的脚印。然而在生产环境运维 PostgreSQL以下简称 PG时许多资深 DBA 都遭遇过类似的“诡异”惊魂时刻在业务流量完全处于纯读取、没有任何INSERT、UPDATE或DELETE的低峰对账时段磁盘的 Write IOPS 莫名其妙地飙升甚至在操作系统的监控中能清晰看到数据目录base/或临时文件目录pgsql_tmp/正在发生剧烈的磁盘刷写。这既不是中了勒索病毒也不是后台进程在搞鬼。这就是 PostgreSQL 社区中著名的“读产生写Select causes Write”现象。作为一款将学术严谨性与工业级性能完美结合的数据库PostgreSQL 为什么要设计出这种看起来“违反直觉”的机制这背后隐藏着怎样的架构之美与性能妥协本篇技术博客将带你潜入 PostgreSQL 的内核源码与存储引擎Storage Engine最深处逐一剥离、解构触发SELECT写的三大核心机制并给出企业级的生产调优实战指南。一、 第一大元凶提示位更新Hint Bits—— 读出来的“脏页”这是 PostgreSQL 最具特色、也最容易让初学者产生困惑的底层设计。要理解它我们必须先从 PG 的 MVCC多版本并发控制设计谈起。1. 消失的“事务提交状态”在 Oracle 或 MySQL (InnoDB) 中当一个事务提交Commit时系统通常会去回滚段Undo Log或撤销日志中修改该事务的状态或者通过锁机制来清理行头部的事务标记。然而PostgreSQL 并没有传统的Undo Log。在 PG 的存储架构中数据的所有历史版本Tuple元组都直接混杂地堆放在普通的数据块Page默认 8KB中。每一个元组的头部HeapTupleHeaderData都记录着创建它的事务 IDt_xmin和删除/修改它的事务 IDt_xmax。当一个写事务执行了COMMIT;为了实现极速的提交响应PostgreSQL 只做两件事在预写日志WAL中记录一条提交日志并刷盘。在内存的CLOGCommit Log事务状态提交日志中将该事务 ID 的状态位从IN_PROGRESS修改为COMMITTED。关键点来了此时躺在磁盘数据块里的那些具体数据行Tuple其头部的t_xmin依然只是一个冰冷的数字例如10050它自己根本不知道自己所属的这个事务到底已经提交了还是被回滚Abort了。2. “第一个读者”的连带责任当提交之后另一条业务SELECT语句作为“第一个读者”扫描到了这个数据块。为了判断这一行数据对自己是否可见Visibility CheckSELECT进程不能只看数据行本身它必须顺着元组头部的t_xmin 10050跨模块去内存的CLOG中查询10050的状态。如果 CLOG 显示已提交OK数据可见。如果 CLOG 显示已回滚数据不可见。这个去 CLOG 校验的过程叫做可见性判断Visibility Map Scan / CLOG Lookups。如果每条SELECT每次读到每一行都要去查一次 CLOG在高并发下CLOG 的访问就会变成全系统的性能超级瓶颈。为了解决这个问题PG 的架构师设计了提示位Hint Bits。当第一个SELECT历经千辛万苦从 CLOG 查到10050已经成功提交后它会顺手在当前数据页的该行元组头部把两个二进制位HEAP_XMIN_COMMITTED或HEAP_XMIN_INVALID点亮。这就是提示位更新。[ 写事务 Commit ] ──── 仅修改内存中的 CLOG 状态 (极快) │ ▼ (此时数据块里的行头部一无所知) [ SELECT 作为首个读者 ] ── 发现行头部无标记 ── 去查 CLOG 确认已提交 │ ├─ 1. 返回数据给客户端 └─ 2. 顺手修改行头部的 Hint Bits (打上已提交烙印) │ ▼ [ 导致 Shared Buffers 中的数据页变脏 ] │ ▼ [ Checkpointer / Bgwriter 异步刷盘产生写 IO ]3. 为什么修改提示位会引发磁盘写修改 Hint Bits 的动作是在 PostgreSQL 的内存缓冲区Shared Buffers中进行的。一旦SELECT修改了行头部的二进制标志位该数据页8KB Page的内容就发生了物理改变。在数据库内核中这个页面立刻被标记为脏页Dirty Page。既然变成了脏页根据数据库的持久化规则它就必须被写回磁盘随后系统的后台清理进程Bgwriter或是Checkpointer检查点进程在扫描缓冲区时发现了这个被SELECT污染的脏页。它们会调用操作系统的write()系统调用将这个页面重新刷写到物理磁盘上。结论这就是为什么你明明执行的是纯查询SELECT却在监控中看到了高额的脏页落地与磁盘写 IO。它是在为写事务的“极速提交”偿还性能债务通过读操作顺手把状态固化下来以便造福后续的每一个读者后续的SELECT读到提示位已点亮直接放行不再查 CLOG。二、 第二大元凶临时文件落盘Temporary Files—— 内存装不下的野心如果说 Hint Bits 的写操作是悄无声息、润物细无声的那么第二种引发SELECT写的机制则往往暴烈得多甚至能瞬间将生产环境的磁盘 IO 彻底拉满。这就是磁盘临时文件Temporary Files。1.work_mem的高能防线PostgreSQL 的内存分配机制与 MySQL 等数据库有着显著的不同。在 PG 中参数work_mem工作内存并不是全局共享的而是为每一个查询中的每一个内部操作符Sort、Hash、Merge 等单独分配的。当你的SELECT语句包含以下操作时PG 必须在内存中构建临时的数据结构ORDER BY需要对结果集进行排序Sort 节点。GROUP BY或DISTINCT需要构建哈希表进行去重或聚合HashAggregate 节点。X JOIN Y如 Hash Join需要将其中一张小表完整地加载到内存中建立哈希表。假设你将work_mem设置为了默认的4MB。2. 物理内存溢出与外排机制External Merge Sort当你的 SQL 执行计划Execution Plan开始运转执行器发现需要排序或做 Hash 的中间数据量达到了50MB远远超过了分配给它的4MB限制。为了保护操作系统不至于因为 OOM内存溢出而崩溃PostgreSQL 执行器会立刻启动内核熔断保护它会在数据库的物理存储目录中专门开辟一个名为base/pgsql_tmp/的临时临时目录。将内存中放不下的数据切片以文件的形式临时写入这个目录文件命名通常类似于pgsql_tmp12345.0。接着在磁盘上执行经典的外部归并排序External Merge Sort或是分批次的多阶段哈希Multi-batch Hash。[ SELECT 触发大表 ORDER BY ] │ ▼ 检查中间数据量 (50MB) work_mem 限制 (4MB) │ ├─ 是 ──────────────────────────────┐ │ ▼ ▼ [内存足够] [内存熔断启动磁盘外排] 全部在内存中完成 在 base/pgsql_tmp/ 创建临时文件 │ 频繁读写磁盘切片进行归并 │ │ └───────────────┬───────────────────┘ ▼ 返回结果集给客户端 │ ▼ 自动销毁磁盘临时文件3. 这个写过程对系统的杀伤力有多大这种由SELECT引起的临时文件写入其磁盘 IO 轨迹非常激进写速度极快执行器会以最大能力将中间数据倾倒进物理磁盘瞬间霸占磁盘写入带宽。双重惩罚数据写下去之后随后还要从磁盘中重新读上来进行归并引发大量的读 IO。生命周期极其短暂一旦这条SELECT语句执行完毕、数据成功返回给客户端PG 执行器会默默地将这些临时文件全部unlink删除。这就会导致一种极其困惑的监控现象你看到磁盘写入发生大幅度尖峰但你去查看数据库的物理磁盘空间占用却发现空间并没有减少。这就是因为临时文件在短时间内“快进快出”被自动清理干净了。三、 第三大元凶冷数据页冻结Freeze Tuple Page Clean—— 捎带脚的岁修第三种导致SELECT引发写的机制与 PostgreSQL 的底层生命周期管理 ——事务 ID 回绕Transaction ID Wraparound息息相关。1. 什么是事务回绕与冻结PostgreSQL 的事务 IDTxID是一个无符号的 32 位整数最大容量约为 42 亿。在持续的高并发写入下TxID 总有一天会耗尽并重头开始回绕。为了防止新老事务重叠导致旧数据蒸发PG 要求必须定期对数据库里那些古老的、再也不会改变的行进行“岁修清理” —— 将其行头部的 TxID 替换为一个特殊的、代表无穷久远过去的标志位Frozen XID冰冻事务 ID。这个工作通常由大名鼎鼎的后台大管家AutoVacuum自动清理进程在夜间或低峰期默默完成。2.SELECT的“顺手牵羊”与页面清理然而AutoVacuum 也是人写的代码它的资源是有限的。为了减轻后台 Vacuum 的集中 IO 尖峰PG 引入了一种“人人为我我为人人”的协作设计。当一条SELECT语句在执行全表扫描Sequential Scan时它不得不将整张大表的所有数据页从磁盘逐个读取到共享缓冲区。在这个漫长的扫描过程中SELECT进程的执行器会顺便检查每个页面的元数据状态。如果它发现以下情况该数据页已经连续经历了极长时间没有发生过任何变更属于绝对的冷数据。页面上的元组年龄Age已经超过了配置的系统冻结阈值如vacuum_freeze_min_age。当前页面在Visibility Map可见性映射表中尚未被完全标记。为了替系统分忧这个SELECT进程在把数据读出来的同时会在内存中顺手对这个数据页执行Page Clean页面清理与Tuple Freeze元组冻结操作。它把这些极老的数据行的头部直接改写为已冰冻状态。结果页面再次被这一举动染成了“脏页”随后的命运与 Hint Bits 一样被 Checkpointer 异步拍回磁盘引发了明显的磁盘写入。四、 深度总结三种SELECT写机制的本质区别为了方便架构师与 DBA 在排查问题时快速对号入座我们将上述三种导致查询产生写入的底层机制进行横向对比触发机制 (Mechanism)写入发生的位置 (Write Location)产生写的底层本质 (Root Cause)对系统的影响及表现 (Symptoms)提示位更新 (Hint Bits)共享缓冲区 (Shared Buffers)最终通过数据目录落地固化 MVCC 事务的提交状态避免后续重复查 CLOG表现为平缓、持续的脏页写入数据目录物理空间不增大。临时文件落盘 (Temporary Files)实例根目录下的base/pgsql_tmp/中间排序/哈希数据量超过了单个操作符的work_mem限制表现为突发、极其剧烈的 IOPS 尖峰伴随读写同步空间短暂膨胀后瞬间恢复。行冻结与岁修 (Page Freeze/Clean)共享缓冲区 (Shared Buffers)最终通过数据目录落地全表扫描时顺手分摊 Vacuum 压力提前固化冷数据的冰冻状态常见于对大型历史归档表执行大范围SELECT时引发集中的脏页刷盘。五、 企业级 DBA 实战如何诊断与调优SELECT带来的写 IO读到这里我们已经达成了“知其所以然”。在真正的企业级生产环境中当磁盘 IO 报警因SELECT产生写而响起时我们该如何像名侦探一样切入、诊断并完美解决它步骤一通过执行计划EXPLAIN精准抓取临时文件如果你的系统 IO 暴涨是因为第二种原因临时文件那么最直接的办法就是让执行计划暴露它。在开发或压测环境中切记不要只执行单纯的EXPLAIN一定要带上ANALYZE和BUFFERS两个高能参数-- 强制执行分析并输出缓冲区开销 EXPLAIN (ANALYZE, BUFFERS) SELECT customer_id, SUM(amount) FROM tenant_orders WHERE order_date 2026-01-01 GROUP BY customer_id ORDER BY SUM(amount) DESC;抓鬼关键输出解读如果发现输出的执行树中包含类似下面的字样那么抓个正着Sort Method: external merge Disk: 45120kB Buffers: shared hit1203, temp read5640 written5640external merge Disk: 45120kB铁证如山因为内存装不下系统向磁盘临时写入了约 45MB 的数据进行外部归并排序temp read5640 written5640这代表了在pgsql_tmp目录中发生的临时块读写次数。【针对性药方】不要盲目调大全局内存。你可以仅针对这条产生大排序的特定慢 SQL在当前 Session 会话级别临时调大内存限制或者调整全局参数-- 1. 全局微调根据服务器可用物理内存保守评估 ALTER SYSTEM SET work_mem 64MB; SELECT pg_reload_conf(); -- 2. 会话级别定向精准倾斜推荐仅对当前复杂报表连接生效 SET work_mem 128MB; -- 随后执行你的大 SELECT 语句此时排序完全在内存中进行Sort Method: quicksort Memory磁盘临时文件降为 0步骤二开启数据库内核层面的临时文件监控在线上生产环境你不可能对每一条 SQL 都去手动执行EXPLAIN ANALYZE。我们需要让内核主动汇报到底有哪些业务SELECT正在疯狂写磁盘。在postgresql.conf中配置以下两个核心运维参数# 记录所有超过 0KB即只要产生了就记录的临时文件信息 log_temp_files 0 # 配合 pg_stat_statements 插件监控全局开销 shared_preload_libraries pg_stat_statements当配置生效后一旦有SELECT憋不住开始写临时文件PostgreSQL 的错误日志postgresql.log中就会打印出如下诊断信息LOG: temporary file: path base/pgsql_tmp/pgsql_tmp23841.0, size 52428800 bytes这行日志清晰地告诉你进程 23841 刚刚创建了一个50MB的临时文件顺藤摸瓜即可捕获对应的嫌疑 SQL。步骤三利用pg_prewarm与激进 Vacuum 拆解 Hint Bits / Freeze 债务如果你的系统经过排查发现并没有临时文件产生但每次对很久没读过的冷表执行SELECT时磁盘写入依然会上扬那说明系统正在偿还Hint Bits提示位或Freeze冻结的宿债。为了避免这些开销在业务高峰期集中爆发、引发整体响应延迟Tail Latency变长DBA 可以采取主动出击、提前对冲的策略。1. 使用官方自带插件pg_prewarm提前预热与重写在低峰期如凌晨 3 点利用pg_prewarm将核心的热点表提前加载到数据库的 Shared Buffers 中。在这个扫描加载的过程中第一波提示位更新和行冻结会在低峰期顺手完成-- 创建官方预热扩展 CREATE EXTENSION IF NOT EXISTS pg_prewarm; -- 将特定租户订单表完全读入内存顺手把 Hint Bits 全给它点亮 SELECT pg_prewarm(tenant_orders);2. 优化自动清理参数让 AutoVacuum 成为主力军不要让SELECT承担过多的冻结和清理压力。通过让 AutoVacuum 工作得更激进确保大部分冷数据页在变成“绝对冷寂”前就已经被后台的专用 worker 进程处理完毕# 提高自动清理的吞吐上限默认 200 太保守了现在都是 NVMe SSD 的时代 autovacuum_vacuum_cost_limit 2000 # 降低触发冻结的年龄门槛让后台进程更早介入岁修 vacuum_freeze_min_age 50000000结语理解底层的妥协走向架构的通透PostgreSQL 独特的“读产生写”设计是其坚持无 Undo Log 的纯正 MVCC 架构、追求极致写提交性能以及防范系统 OOM 崩溃等哲学思想下的必然产物。作为一名优秀的开发人员或架构师读懂这一层你便不会在遇到诡异的磁盘写 IO 报警时手忙脚乱。相反你会感叹于 Hint Bits 在“用空间换取长远时间”上的精妙也会赞赏work_mem防线在数据安全上的严密。在日常编写复杂 SQL 或者是架构多租户集群时时刻谨记天下没有免费的午餐写事务偷过的懒极速 Commit总有一个SELECT会在未来替它负重前行。 互动交流你的生产集群也遭遇过类似的“灵异事件”吗如果你正在遭受由于不合理的执行计划、未知的内存溢出或逻辑复制卡死导致的磁盘 IO 飙升欢迎在评论区或通过中启乘数科技官方技术社区与我们展开深度探讨。中启乘数科技杭州有限公司专注于为您提供最专业、全天候的PostgreSQL 技术维保、云原生一体机CData以及高性能数据库连接池与统一平台CLup解决方案。联系我们servicescsudata.com | 400-887-8716渴望进一步优化你的 PostgreSQL 查询架构如果你希望对当前的系统进行免费的“体检”可以在回复中提供以下关键上下文产生大量磁盘写入的SELECT语句的EXPLAIN (ANALYZE, BUFFERS)完整文本。你的生产服务器当前的物理总内存大小以及当前分配给shared_buffers和work_mem的具体数值。当前底层承载数据的存储介质类型如 传统 HDD、普通企业级 SSD还是顶配的 NVMe / 密集型云盘我将根据你提供的第一手物理数据为你量身定制一份无临时文件落盘的、纯内存级的高校 SQL 调优与参数整改方案