以前我最烦听到“PostgreSQL体系结构”这种词总觉得又是一堆进程图、内存图看完就忘跟实际运维半毛钱关系没有。直到有一回某客户的环境半夜连接数从100涨到400整个库像被抽干了力气查询全变慢我翻遍参数文档也找不出原因最后硬着头皮把体系结构重新捋了一遍才发现问题根本不在某个参数而在于我对这套“进程内存文件”的协作方式理解得太表面。那次之后我彻底改了思路体系结构不是考试重点是排查问题时手里那张地图。这篇内容我想按实战的方式来聊不画教科书里的那种框图而是把PG那套进程模型、内存分配、WAL、MVCC与数据文件的关系拆成一个个“出问题之后你该怎么想”的片段。适合正在做PG运维、或者刚从MySQL转过来、总感觉PG的“脾气”对不上的同学。1. 一张结构图如何解释绝大多数PG运维问题先说结论PG的体系结构可以压缩成三句话——客户端连上来postmaster分配一个后端进程去服务后端进程干活时先在共享内存里找数据页找不到就去磁盘读所有修改先写WAL日志再由检查点慢慢刷回数据文件。这三句话能解释我遇到过的八成以上的故障。1.1 进程模型一个连接不止一个进程很多从MySQL过来的人刚开始最不适应的就是PG的进程模型。MySQL的线程模型里一个连接就是一条线程资源开销相对轻PG不一样每来一个客户端连接postmaster主进程都要fork出一个独立的postgres后端进程来为它服务。这个动作本身有代价所以“连接数太多”在PG里不是简单的数值问题而是实打实的内存和进程调度问题。我见过最典型的情况是应用侧用了很粗的连接池默认配了500个连接结果一上线连接数直接飙升。当时查pg_stat_activity看到一堆“idle in transaction”的会话后端进程每个都占着一小块私有内存加起来相当可观。所以排查连接问题时第一反应不应该是“调大max_connections”而应该是打开pg_stat_activity去查这些连接是从哪来的、在干什么、事务有没有正常提交。当然PG也不全是“一个连接一个进程”它还有一批后台进程在默默干活。普通运维至少要知道这几个角色的存在WAL写入进程负责把日志刷到磁盘检查点进程负责把脏页刷回数据文件autovacuum的launcher和worker负责自动清理统计收集进程负责采集系统视图的数据。这些后台进程一旦出问题比如日志刷得太慢、检查点持续时间过长、autovacuum长期不触发系统会表现出各种奇怪的症状而且不一定报错。1.2 共享内存与私有内存两个容易混的参数PG的内存结构分两大块共享内存和会话私有内存这两个概念不搞清楚参数调优就是盲人摸象。共享内存里最核心的是shared_buffers它是PG自己的数据页缓存区。所有后端进程要访问某个数据页时会先到shared_buffers里找找不到才去操作系统读磁盘。注意这里有个关键点shared_buffers不等于操作系统的page cache它是PG进程之间共享的一层和OS缓存是两层东西。很多文档推荐shared_buffers设置为物理内存的25%但对大内存机器设太高也有副作用比如和OS page cache争抢、checkpoint刷脏页的成本变大。实际中我见过因为shared_buffers设到物理内存的70%导致整个系统内存分配紧张、崩溃恢复时间特别长的案例。另一块是每个后端进程私有的内存大家最熟的就是work_mem。排序、哈希连接、聚合操作都会用到它。它是“按需分配、按会话私用”的意思是500个连接如果同时做排序每个都能吃到自己那份work_mem不是500个连接共享一个work_mem。这个账算下来很吓人work_mem默认4MB400个并发排序就是1.6GB的潜在开销如果再有人把work_mem调到64MB那就是25GB起步。所以遇到这类问题我一般会先用pg_stat_activity看实际有多少会话在跑涉及排序或哈希的查询再决定要不要调。1.3 数据目录中那些文件夹结构是有形的进程和内存是动态的结构数据目录则是静态的结构。哪个文件对应什么内容排查问题时价值极大。$PGDATA下面最常见的几个目录base是实际存储数据库数据的目录global存放全局系统表pg_wal是WAL日志目录pg_xact存放事务提交状态pg_tblspc是表空间的软链接目录。这些目录一旦理解了一些“神秘”现象就变得非常直白。比如你发现pg_wal目录暴涨那就不是某个表数据变大了而是WAL的生成和归档出了问题。再往细看一张普通表在磁盘上不止一个文件。一个表的物理文件通常有主文件存储数据本身、fsm文件记录空闲空间映射、vm文件可见性映射。索引和表类似。为什么PG要额外维护fsm和vm这两个“附属文件”fsm的作用是让后端进程能够快速找到页里的空闲空间去插入新数据vm的作用是让顺序扫描可以跳过那些“对任何事务都不可见”的页面。这两者在vacuum和index-only scan里的重要性后面聊到vacuum时会再次出现。2. WAL机制体系结构里的“记账本”思维如果说进程和内存决定了PG的“并发性格”那么WAL机制决定了它的“可靠性底线”。WAL全称是Write-Ahead Logging翻译过来就是“提前写日志”。PG坚信一条原则任何数据页的修改在写回数据文件之前必须先落到WAL日志里。2.1 为什么“先记账、后改账”反而更快第一次接触这个设计的人通常会问多写一次日志不是更慢吗这里的关键在于数据页修改是随机小写入而WAL是追加式顺序写。一次事务提交如果只改了几个数据页落盘时的停顿几乎可以忽略因为要刷的只是一个顺序追加的日志段而如果把数据页直接刷到磁盘那可能要随机写好几个地方。对机械硬盘来说顺序写和随机写的差距能到几十倍即使是NVMe固态随机小写入的延迟也不稳定。所以PG的取舍是提交事务时只保证WAL落了盘数据页可以留在shared_buffers里慢慢刷。这个机制用记账来打比方特别合适数据文件是账本明细WAL是流水日记。每天营业结束后账本明细可以慢慢整理但每一笔交易发生的那一瞬间必须先记到流水日记里。如果账本被烧了还能靠流水日记恢复出完整交易记录。WAL日志本身是一段一段写的。一落盘就代表事务成功即使后面操作系统崩溃、进程被杀数据文件里的旧内容还在PG会在重启时读取WAL把那些还没有刷入数据文件的修改重新应用一遍这个过程就是崩溃恢复。2.2 wal_level、归档与复制的连带决策理解了WAL是可靠性基石再去看wal_level参数就不会觉得它只是个“开关”了。wal_level有三个主要级别minimal、replica、logical。minimal级别写入量最低但很多功能会受限比如不能做流复制、某些操作的WAL会被尽可能省掉。replica是生产环境的常选支持只读备库和归档。logical是在replica基础上额外记录逻辑解析所需的信息用于逻辑复制或者需要把WAL解析成SQL流做数据同步的场景。归档和流复制也建立在WAL之上。archive_mode打开后归档进程会在WAL段切换时把日志文件另存一份交给archive_command指定的命令处理。这是做物理备份、基于时间点恢复的基础。流复制则不同主库通过wal_sender进程直接把WAL发给备库备库接收后应用。这里有个实践中常踩的坑如果备库因为网络或磁盘问题落后很多主库的pg_wal目录会一直保留着备库还没拿走的WAL段时间一长就把磁盘撑爆了。所以排查pg_wal膨胀时我会同时看pg_stat_replication里的备库状态而不只是盯着msg日志目录。2.3 checkpoint为什么高峰期会“卡一下”WAL的设计决定了数据页不会立即刷盘那就必须有一个机制把shared_buffers里的脏页最终写回数据文件否则WAL会无限膨胀崩溃恢复时间也会越来越长。这个机制就是checkpoint。checkpoint行为由几个参数控制checkpoint_timeout控制时间触发间隔默认是5分钟max_wal_size控制WAL量触发阈值默认1GB。只要到了时间或者WAL文件总大小接近max_wal_size检查点进程就会启动把shared_buffers里所有脏页刷回磁盘。很多人以为checkpoint是后台悄悄做的不会影响业务。但实际上checkpoint刷盘往往伴随着大量的顺序写IO如果在业务高峰期赶上WAL量达到max_wal_size触发检查点磁盘带宽会被瞬间占掉一大截SQL就会明显变慢。还有一点容易被忽略checkpoint之后WAL里记录的首次修改的页面需要写整页也就是full_page_writes参数的作用。它为了保证崩溃恢复时不会因为“页面是半个旧半个新”而损坏数据代价是checkpoint后的第一个修改周期WAL写入量会变大。这个参数一般不建议关哪怕是固态盘也不要轻易为了一点性能拿数据完整性去赌。观察checkpoint频率有一个很直接的方式查pg_stat_bgwriter视图看checkpoints_timed和checkpoints_req两个字段。前者是时间触发次数后者是WAL量触发次数。如果checkpoints_req占比很高说明业务写入量大max_wal_size相对偏小可以考虑调大如果磁盘IO压力大还可以通过延长checkpoint_timeout来降低检查点频率但代价是崩溃恢复时间变长。这是一个需要权衡的账。3. Vacuum不是“清理垃圾”是MVCC结构下的生存法则我第一次用PG时犯过一个错误拿MySQL的习惯去理解update以为update就是把数据覆盖掉。PG告诉你事情没这么简单。3.1 Update在结构层面做了什么PG的多版本并发控制MVCC核心是靠行版本的xmin和xmax字段来实现的。一个事务update一行时并不会去物理覆盖原来的行而是在数据页里插入一个新版本的行旧版本仍然留在页面里靠xmax标记“这个版本已经被删除或更新了”。这时就出现了一个结构上的必然结果更新越频繁旧版本堆积越多这些旧版本就是死元组。死元组会占着页面空间还会让索引里存在过期的条目如果一直不清理表和索引就会膨胀。清理动作就是vacuum所做的扫描数据页找到并移除对任何活跃事务都不可见的死元组释放页面空间并更新fsm空闲空间映射和vm可见性映射。这里要特别提一句PostgreSQL的vacuum和MySQL的purge线程有点像但PG把主动权给了DBA和autovacuum。如果你不让autovacuum跑或者让它在高峰期被取消死元组就会越积越多。更麻烦的是膨胀一旦形成单纯vacuum不一定能把空间还给操作系统它只是释放页面内部空间供后续新插入使用。只有表尾部的页面完全空出来vacuum才可能把文件尾部截断否则表文件大小不会降下来。这也是为什么有些表vacuum之后data目录不见小因为它只是“内部瘦身”文件尺寸没变。3.2 autovacuum参数不要拍脑袋autovacuum是PG内置的自动清理机制默认开启。它按什么标准触发两个参数autovacuum_vacuum_threshold默认50autovacuum_vacuum_scale_factor默认0.2。触发条件大致是死元组数大于 threshold 表行数 × scale_factor。看到这里你大概已经意识到一个问题对于小表50条死元组的阈值很快就能触发但对于大表比如1亿行的表需要死元组数超过2000万才触发autovacuum。这么高的阈值下等到autovacuum开始清理时表可能已经膨胀得很厉害了。所以生产环境里对超大的表要么调低scale_factor要么直接手动设置表的autovacuum_vacuum_scale_factor或者用分区表把单表行数控制在合理范围。autovacuum在跑的时候也有开销它会消耗IO和CPU还会短暂持有锁。很多人问要不要在高峰期关掉autovacuum我的建议是不要全局关更不要长时间关。如果实在担心影响业务可以调大autovacuum_vacuum_cost_delay让清理任务慢一点把IO压力摊开。想观察它跑没跑可以查pg_stat_activity看有没有autovacuum worker进程在执行也可以查pg_stat_user_tables里的last_autovacuum和vacuum_count。3.3 事务ID回卷体系结构里真正的“灭亡倒计时”在一堆性能话题里事务ID回卷是一个一旦发生就是灾难的问题。PG的事务ID是一个32位无符号整数大约能容纳21亿个事务。为了不让它真的到达上限PG引入了冻结机制当一个元组的事务年龄超过vacuum_freeze_min_age时vacuum就会把它标记为冻结相当于让它对任何事务都可见。但如果这个清理和冻结动作长期没发生就会出现事务ID回卷风险。数据库设计了一个强制防线当数据库最老的事务ID年龄接近autovacuum_freeze_max_age默认2亿时autovacuum会被强制拉起来做防回卷清理即使你在配置里关掉了autovacuum也会跑。如果连强制清理都失败数据库最终会拒绝执行正常事务日志里会出现类似“database is not accepting commands to avoid wraparound data loss”的告警整个库直接进入只读保护状态。这种故障我见过一次当时是某个业务表长期没人管数据库里又有一个非常老的backup进程一直不结束导致vacuum没法推进最老事务的进度。排查时查pg_database的datfrozenxid字段和age()函数就能看出来危险程度。这个问题的关键是提前监控把它当作和磁盘空间同等重要的日常巡检项。4. 常见故障的结构性解读前面把核心机制过了一遍这部分就是纯粹的“病例分析”。每一条都能对应到具体的结构元素也都有从实际故障里总结出来的排查路径。4.1 连接打满max_connections背后的内存账报错信息是“too many clients already”大多数人第一反应是调大max_connections。但在动手之前应该先算一笔账每个连接至少对应一个后端进程每个进程都会消耗一定的共享内存同时可能按需分配私有内存。max_connections不是一个可以随便拉高的数字它直接影响shared_buffers和进程内存的整体布局。把连接数从300调到1000内存容量不变后端进程数量翻了倍系统内存压力会明显增大。真正合理的排查路径是先查pg_stat_activity看连接到底处于什么状态。是按时间分布的“idle”连接还是大量“idle in transaction”卡着不提交的会话还是有大量的连接短时间内反复建立和销毁对第一种和第二种情况问题多半在应用侧是连接池配置或事务处理逻辑的问题调大max_connections治标不治本只有确实存在并发尖峰才需要考虑提高连接上限而且最好同时配合使用连接池中间件把后端实际连接数压下来。4.2 慢查询突然变慢别只盯着SQL一条执行计划很稳定的SQL某天突然从10ms变成200ms很多人的第一反应是看执行计划、加索引。但有一条从体系结构角度的排查思路可能更快先看它是不是被“抢了资源”。查pg_stat_activity里wait_event字段如果大量会话在等IO最可能的三个原因就是checkpoint正在集中刷脏页、autovacuum在跑大表、备库正在拖WAL导致主库磁盘带宽被占用。我处理过一次慢查询案例SQL本身执行计划完全没变但一到整点就变慢排查半天发现是归档命令到了整点触发了一次全量文件扫描把磁盘IO打满了。这属于外部IO竞争如果不看结构光优化SQL是没用的。另一种情况是锁等待。PG的锁机制里DDL锁、行锁、vacuum相关的锁都可能让查询进入阻塞状态。wait_event里出现了“lock”相关等待就要用pg_locks视图定位具体锁的持有者。有一个常见场景长事务持有旧快照autovacuum干到一半发现不能清理旧版本一直卡着所有相关表的新查询都受影响。4.3 磁盘空间报警表膨胀和pg_wal目录暴涨磁盘报警是运维日常里最常见的事故但PG这里有两类不同的问题。一类是数据表膨胀特征是某个表的死元组太多vacuum一直没跟上。另一类是pg_wal目录暴涨。很多新手看到pg_wal太大直接手动删文件这是很危险的操作PG的WAL文件是有编号的删除正在使用的WAL段会导致实例崩溃甚至无法恢复。pg_wal暴涨的常见原因有几种备库落后导致主库需要保留更多WAL段archive_command失败导致WAL无法归档日志堆积wal_keep_size或max_wal_size设置过大还有长事务一直没提交导致后续的WAL都不能被清理。正确的做法是先定位是哪种原因再针对性地处理。比如备库落后就检查备库状态和网络归档失败就看归档日志。除非确认WAL文件已经归档且不再需要否则绝对不建议手动删除。5. 体系结构知识的落地一套自检的命令组合讲了一堆原理最后还是得落到怎么用。这里分享一套我每到一个新环境都会做的“架构体检”不需要额外装插件只用系统自带的视图和命令十几分钟就能对实例的结构健康状况有一个基本判断。5.1 用pg_stat系列视图反推健康状况pg_stat_database里可以看到每个数据库的连接数、事务提交数、回滚数、缓存命中率等。特别推荐关注回滚数如果回滚率异常高说明应用层的事务逻辑可能有问题这会反映到WAL写入量和死元组产生速度上。pg_stat_user_tables是观察表和索引膨胀情况的入口。重点看seq_scan、idx_scan、n_live_tup、n_dead_tup这几个字段。n_dead_tup长期居高不下说明autovacuum没有及时清走死元组seq_scan和idx_scan的比值可以帮助判断索引是否被有效使用。pg_stat_activity是最常用的实时状态视图。我一般按这几类条件查state字段看会话状态backend_start和xact_start看事务持续时长wait_event看等待事件。一条SQL就能把当前实例的“压力分布”拉出来再结合client_addr过滤来源IP判断连接到底是从哪来的。5.2 几个命令完成快速排查给出一套我实际用的“体检”SQL模板。查看连接和事务状态的命令SELECT pid, usename, state, wait_event_type, wait_event, now() - xact_start AS xact_age, now() - state_change AS state_age FROM pg_stat_activity WHERE backend_type client backend ORDER BY xact_age DESC NULLS LAST;查看checkpoint频率SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint, buffers_clean FROM pg_stat_bgwriter;如果checkpoints_req比checkpoints_timed还大说明WAL量触发的检查点比较多可能要考虑调整max_wal_size。查看事务年龄风险SELECT datname, age(datfrozenxid) AS frozen_xid_age FROM pg_database ORDER BY frozen_xid_age DESC;如果age超过15亿就要高度警惕超过18亿离强制清理非常近了。查看WAL目录大小的近似值可以直接在shell里执行du -sh $PGDATA/pg_wal配合流复制状态检查SELECT client_addr, state, sent_lsn, replay_lsn, pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag FROM pg_stat_replication;如果replay_lag一直增长赶紧查备库的磁盘和网络。5.3 两个小实验帮你建立直觉如果只看概念记不住我更建议在本地起一个测试实例自己做两个小实验。第一个实验用来感受MVCC和vacuum。建一张只有主键和几个普通字段的表插入10万行数据然后循环update同一条记录一千次每次提交。接着查pg_stat_user_tables里的n_dead_tup你会发现死元组数量迅速涨到几千甚至更多表文件也可能变大。再手动执行vacuum再查n_dead_tup发现它归零了。这个实验能让你直观理解“update不等于覆盖”和“vacuum到底清理了什么”。第二个实验用来感受checkpoint和WAL的关系。在业务开始前先查一下pg_wal目录的文件列表然后跑一个持续更新数据的大事务提交后再看pg_wal目录会发现文件增加了跑一次checkpoint命令之后再观察WAL文件数量变化。这个实验能帮你把WAL和checkpoint在头脑里串起来WAL一直在长checkpoint的作用是让数据文件赶上WAL的进度。在测试环境里你还可以试试把wal_level改成logical再重启观察有没有额外的WAL信息生成但这类实验千万别在生产上做改完重启并且要花时间观察稍有不慎会影响业务。写在最后的几个实战体会因为自己吃过亏所以有几句话想单独说。一是别迷信“最佳参数”我在多个环境里试过同样的配置结果完全不同。shared_buffers给多了操作系统缓存反而少了全表扫描类查询反而变慢work_mem给大了并发一高内存就报警。参数从来都是跟业务形态绑定的而业务形态只有通过pg_stat系列视图才能看清。二是对体系结构的理解一定要落到命令上。概念能帮你选对排查方向命令才是动手解决问题的工具。我每次接手新实例第一件事就是跑一遍第5小节的体检SQL哪怕暂时没发现问题至少心里有底知道这套系统现在处于什么状态。三是把那些“常年没人管的表”重视起来。MySQL里你可能靠后台线程就能把清理做掉大半PG里autovacuum虽然也在跑但大表、长事务、边缘情况仍然需要人工介入。给每个重要业务表建立单独的autovacuum参数定期看n_dead_tup把事务年龄当成监控项都是成本很低但能救命的好习惯。最后再分享一个小技巧给崩溃恢复留足预算。max_wal_size可以调大但心里要有数一旦宕机恢复时需要重放的WAL也更多。我一般会在测试环境模拟一次断电量一下实际的恢复时间再决定生产环境的max_wal_size和checkpoint_timeout怎么配。这种实验比看任何理论文档都直观做一次你对WAL和checkpoint的理解会彻底不一样。
以前我最烦听到“PostgreSQL体系结构”这种词总觉得又是一堆进程图、内存图看完就忘跟实际运维半毛钱关系没有。直到有一回某客户的环境半夜连接数从100涨到400整个库像被抽干了力气查询全变慢我翻遍参数文档也找不出原因最后硬着头皮把体系结构重新捋了一遍才发现问题根本不在某个参数而在于我对这套“进程内存文件”的协作方式理解得太表面。那次之后我彻底改了思路体系结构不是考试重点是排查问题时手里那张地图。这篇内容我想按实战的方式来聊不画教科书里的那种框图而是把PG那套进程模型、内存分配、WAL、MVCC与数据文件的关系拆成一个个“出问题之后你该怎么想”的片段。适合正在做PG运维、或者刚从MySQL转过来、总感觉PG的“脾气”对不上的同学。1. 一张结构图如何解释绝大多数PG运维问题先说结论PG的体系结构可以压缩成三句话——客户端连上来postmaster分配一个后端进程去服务后端进程干活时先在共享内存里找数据页找不到就去磁盘读所有修改先写WAL日志再由检查点慢慢刷回数据文件。这三句话能解释我遇到过的八成以上的故障。1.1 进程模型一个连接不止一个进程很多从MySQL过来的人刚开始最不适应的就是PG的进程模型。MySQL的线程模型里一个连接就是一条线程资源开销相对轻PG不一样每来一个客户端连接postmaster主进程都要fork出一个独立的postgres后端进程来为它服务。这个动作本身有代价所以“连接数太多”在PG里不是简单的数值问题而是实打实的内存和进程调度问题。我见过最典型的情况是应用侧用了很粗的连接池默认配了500个连接结果一上线连接数直接飙升。当时查pg_stat_activity看到一堆“idle in transaction”的会话后端进程每个都占着一小块私有内存加起来相当可观。所以排查连接问题时第一反应不应该是“调大max_connections”而应该是打开pg_stat_activity去查这些连接是从哪来的、在干什么、事务有没有正常提交。当然PG也不全是“一个连接一个进程”它还有一批后台进程在默默干活。普通运维至少要知道这几个角色的存在WAL写入进程负责把日志刷到磁盘检查点进程负责把脏页刷回数据文件autovacuum的launcher和worker负责自动清理统计收集进程负责采集系统视图的数据。这些后台进程一旦出问题比如日志刷得太慢、检查点持续时间过长、autovacuum长期不触发系统会表现出各种奇怪的症状而且不一定报错。1.2 共享内存与私有内存两个容易混的参数PG的内存结构分两大块共享内存和会话私有内存这两个概念不搞清楚参数调优就是盲人摸象。共享内存里最核心的是shared_buffers它是PG自己的数据页缓存区。所有后端进程要访问某个数据页时会先到shared_buffers里找找不到才去操作系统读磁盘。注意这里有个关键点shared_buffers不等于操作系统的page cache它是PG进程之间共享的一层和OS缓存是两层东西。很多文档推荐shared_buffers设置为物理内存的25%但对大内存机器设太高也有副作用比如和OS page cache争抢、checkpoint刷脏页的成本变大。实际中我见过因为shared_buffers设到物理内存的70%导致整个系统内存分配紧张、崩溃恢复时间特别长的案例。另一块是每个后端进程私有的内存大家最熟的就是work_mem。排序、哈希连接、聚合操作都会用到它。它是“按需分配、按会话私用”的意思是500个连接如果同时做排序每个都能吃到自己那份work_mem不是500个连接共享一个work_mem。这个账算下来很吓人work_mem默认4MB400个并发排序就是1.6GB的潜在开销如果再有人把work_mem调到64MB那就是25GB起步。所以遇到这类问题我一般会先用pg_stat_activity看实际有多少会话在跑涉及排序或哈希的查询再决定要不要调。1.3 数据目录中那些文件夹结构是有形的进程和内存是动态的结构数据目录则是静态的结构。哪个文件对应什么内容排查问题时价值极大。$PGDATA下面最常见的几个目录base是实际存储数据库数据的目录global存放全局系统表pg_wal是WAL日志目录pg_xact存放事务提交状态pg_tblspc是表空间的软链接目录。这些目录一旦理解了一些“神秘”现象就变得非常直白。比如你发现pg_wal目录暴涨那就不是某个表数据变大了而是WAL的生成和归档出了问题。再往细看一张普通表在磁盘上不止一个文件。一个表的物理文件通常有主文件存储数据本身、fsm文件记录空闲空间映射、vm文件可见性映射。索引和表类似。为什么PG要额外维护fsm和vm这两个“附属文件”fsm的作用是让后端进程能够快速找到页里的空闲空间去插入新数据vm的作用是让顺序扫描可以跳过那些“对任何事务都不可见”的页面。这两者在vacuum和index-only scan里的重要性后面聊到vacuum时会再次出现。2. WAL机制体系结构里的“记账本”思维如果说进程和内存决定了PG的“并发性格”那么WAL机制决定了它的“可靠性底线”。WAL全称是Write-Ahead Logging翻译过来就是“提前写日志”。PG坚信一条原则任何数据页的修改在写回数据文件之前必须先落到WAL日志里。2.1 为什么“先记账、后改账”反而更快第一次接触这个设计的人通常会问多写一次日志不是更慢吗这里的关键在于数据页修改是随机小写入而WAL是追加式顺序写。一次事务提交如果只改了几个数据页落盘时的停顿几乎可以忽略因为要刷的只是一个顺序追加的日志段而如果把数据页直接刷到磁盘那可能要随机写好几个地方。对机械硬盘来说顺序写和随机写的差距能到几十倍即使是NVMe固态随机小写入的延迟也不稳定。所以PG的取舍是提交事务时只保证WAL落了盘数据页可以留在shared_buffers里慢慢刷。这个机制用记账来打比方特别合适数据文件是账本明细WAL是流水日记。每天营业结束后账本明细可以慢慢整理但每一笔交易发生的那一瞬间必须先记到流水日记里。如果账本被烧了还能靠流水日记恢复出完整交易记录。WAL日志本身是一段一段写的。一落盘就代表事务成功即使后面操作系统崩溃、进程被杀数据文件里的旧内容还在PG会在重启时读取WAL把那些还没有刷入数据文件的修改重新应用一遍这个过程就是崩溃恢复。2.2 wal_level、归档与复制的连带决策理解了WAL是可靠性基石再去看wal_level参数就不会觉得它只是个“开关”了。wal_level有三个主要级别minimal、replica、logical。minimal级别写入量最低但很多功能会受限比如不能做流复制、某些操作的WAL会被尽可能省掉。replica是生产环境的常选支持只读备库和归档。logical是在replica基础上额外记录逻辑解析所需的信息用于逻辑复制或者需要把WAL解析成SQL流做数据同步的场景。归档和流复制也建立在WAL之上。archive_mode打开后归档进程会在WAL段切换时把日志文件另存一份交给archive_command指定的命令处理。这是做物理备份、基于时间点恢复的基础。流复制则不同主库通过wal_sender进程直接把WAL发给备库备库接收后应用。这里有个实践中常踩的坑如果备库因为网络或磁盘问题落后很多主库的pg_wal目录会一直保留着备库还没拿走的WAL段时间一长就把磁盘撑爆了。所以排查pg_wal膨胀时我会同时看pg_stat_replication里的备库状态而不只是盯着msg日志目录。2.3 checkpoint为什么高峰期会“卡一下”WAL的设计决定了数据页不会立即刷盘那就必须有一个机制把shared_buffers里的脏页最终写回数据文件否则WAL会无限膨胀崩溃恢复时间也会越来越长。这个机制就是checkpoint。checkpoint行为由几个参数控制checkpoint_timeout控制时间触发间隔默认是5分钟max_wal_size控制WAL量触发阈值默认1GB。只要到了时间或者WAL文件总大小接近max_wal_size检查点进程就会启动把shared_buffers里所有脏页刷回磁盘。很多人以为checkpoint是后台悄悄做的不会影响业务。但实际上checkpoint刷盘往往伴随着大量的顺序写IO如果在业务高峰期赶上WAL量达到max_wal_size触发检查点磁盘带宽会被瞬间占掉一大截SQL就会明显变慢。还有一点容易被忽略checkpoint之后WAL里记录的首次修改的页面需要写整页也就是full_page_writes参数的作用。它为了保证崩溃恢复时不会因为“页面是半个旧半个新”而损坏数据代价是checkpoint后的第一个修改周期WAL写入量会变大。这个参数一般不建议关哪怕是固态盘也不要轻易为了一点性能拿数据完整性去赌。观察checkpoint频率有一个很直接的方式查pg_stat_bgwriter视图看checkpoints_timed和checkpoints_req两个字段。前者是时间触发次数后者是WAL量触发次数。如果checkpoints_req占比很高说明业务写入量大max_wal_size相对偏小可以考虑调大如果磁盘IO压力大还可以通过延长checkpoint_timeout来降低检查点频率但代价是崩溃恢复时间变长。这是一个需要权衡的账。3. Vacuum不是“清理垃圾”是MVCC结构下的生存法则我第一次用PG时犯过一个错误拿MySQL的习惯去理解update以为update就是把数据覆盖掉。PG告诉你事情没这么简单。3.1 Update在结构层面做了什么PG的多版本并发控制MVCC核心是靠行版本的xmin和xmax字段来实现的。一个事务update一行时并不会去物理覆盖原来的行而是在数据页里插入一个新版本的行旧版本仍然留在页面里靠xmax标记“这个版本已经被删除或更新了”。这时就出现了一个结构上的必然结果更新越频繁旧版本堆积越多这些旧版本就是死元组。死元组会占着页面空间还会让索引里存在过期的条目如果一直不清理表和索引就会膨胀。清理动作就是vacuum所做的扫描数据页找到并移除对任何活跃事务都不可见的死元组释放页面空间并更新fsm空闲空间映射和vm可见性映射。这里要特别提一句PostgreSQL的vacuum和MySQL的purge线程有点像但PG把主动权给了DBA和autovacuum。如果你不让autovacuum跑或者让它在高峰期被取消死元组就会越积越多。更麻烦的是膨胀一旦形成单纯vacuum不一定能把空间还给操作系统它只是释放页面内部空间供后续新插入使用。只有表尾部的页面完全空出来vacuum才可能把文件尾部截断否则表文件大小不会降下来。这也是为什么有些表vacuum之后data目录不见小因为它只是“内部瘦身”文件尺寸没变。3.2 autovacuum参数不要拍脑袋autovacuum是PG内置的自动清理机制默认开启。它按什么标准触发两个参数autovacuum_vacuum_threshold默认50autovacuum_vacuum_scale_factor默认0.2。触发条件大致是死元组数大于 threshold 表行数 × scale_factor。看到这里你大概已经意识到一个问题对于小表50条死元组的阈值很快就能触发但对于大表比如1亿行的表需要死元组数超过2000万才触发autovacuum。这么高的阈值下等到autovacuum开始清理时表可能已经膨胀得很厉害了。所以生产环境里对超大的表要么调低scale_factor要么直接手动设置表的autovacuum_vacuum_scale_factor或者用分区表把单表行数控制在合理范围。autovacuum在跑的时候也有开销它会消耗IO和CPU还会短暂持有锁。很多人问要不要在高峰期关掉autovacuum我的建议是不要全局关更不要长时间关。如果实在担心影响业务可以调大autovacuum_vacuum_cost_delay让清理任务慢一点把IO压力摊开。想观察它跑没跑可以查pg_stat_activity看有没有autovacuum worker进程在执行也可以查pg_stat_user_tables里的last_autovacuum和vacuum_count。3.3 事务ID回卷体系结构里真正的“灭亡倒计时”在一堆性能话题里事务ID回卷是一个一旦发生就是灾难的问题。PG的事务ID是一个32位无符号整数大约能容纳21亿个事务。为了不让它真的到达上限PG引入了冻结机制当一个元组的事务年龄超过vacuum_freeze_min_age时vacuum就会把它标记为冻结相当于让它对任何事务都可见。但如果这个清理和冻结动作长期没发生就会出现事务ID回卷风险。数据库设计了一个强制防线当数据库最老的事务ID年龄接近autovacuum_freeze_max_age默认2亿时autovacuum会被强制拉起来做防回卷清理即使你在配置里关掉了autovacuum也会跑。如果连强制清理都失败数据库最终会拒绝执行正常事务日志里会出现类似“database is not accepting commands to avoid wraparound data loss”的告警整个库直接进入只读保护状态。这种故障我见过一次当时是某个业务表长期没人管数据库里又有一个非常老的backup进程一直不结束导致vacuum没法推进最老事务的进度。排查时查pg_database的datfrozenxid字段和age()函数就能看出来危险程度。这个问题的关键是提前监控把它当作和磁盘空间同等重要的日常巡检项。4. 常见故障的结构性解读前面把核心机制过了一遍这部分就是纯粹的“病例分析”。每一条都能对应到具体的结构元素也都有从实际故障里总结出来的排查路径。4.1 连接打满max_connections背后的内存账报错信息是“too many clients already”大多数人第一反应是调大max_connections。但在动手之前应该先算一笔账每个连接至少对应一个后端进程每个进程都会消耗一定的共享内存同时可能按需分配私有内存。max_connections不是一个可以随便拉高的数字它直接影响shared_buffers和进程内存的整体布局。把连接数从300调到1000内存容量不变后端进程数量翻了倍系统内存压力会明显增大。真正合理的排查路径是先查pg_stat_activity看连接到底处于什么状态。是按时间分布的“idle”连接还是大量“idle in transaction”卡着不提交的会话还是有大量的连接短时间内反复建立和销毁对第一种和第二种情况问题多半在应用侧是连接池配置或事务处理逻辑的问题调大max_connections治标不治本只有确实存在并发尖峰才需要考虑提高连接上限而且最好同时配合使用连接池中间件把后端实际连接数压下来。4.2 慢查询突然变慢别只盯着SQL一条执行计划很稳定的SQL某天突然从10ms变成200ms很多人的第一反应是看执行计划、加索引。但有一条从体系结构角度的排查思路可能更快先看它是不是被“抢了资源”。查pg_stat_activity里wait_event字段如果大量会话在等IO最可能的三个原因就是checkpoint正在集中刷脏页、autovacuum在跑大表、备库正在拖WAL导致主库磁盘带宽被占用。我处理过一次慢查询案例SQL本身执行计划完全没变但一到整点就变慢排查半天发现是归档命令到了整点触发了一次全量文件扫描把磁盘IO打满了。这属于外部IO竞争如果不看结构光优化SQL是没用的。另一种情况是锁等待。PG的锁机制里DDL锁、行锁、vacuum相关的锁都可能让查询进入阻塞状态。wait_event里出现了“lock”相关等待就要用pg_locks视图定位具体锁的持有者。有一个常见场景长事务持有旧快照autovacuum干到一半发现不能清理旧版本一直卡着所有相关表的新查询都受影响。4.3 磁盘空间报警表膨胀和pg_wal目录暴涨磁盘报警是运维日常里最常见的事故但PG这里有两类不同的问题。一类是数据表膨胀特征是某个表的死元组太多vacuum一直没跟上。另一类是pg_wal目录暴涨。很多新手看到pg_wal太大直接手动删文件这是很危险的操作PG的WAL文件是有编号的删除正在使用的WAL段会导致实例崩溃甚至无法恢复。pg_wal暴涨的常见原因有几种备库落后导致主库需要保留更多WAL段archive_command失败导致WAL无法归档日志堆积wal_keep_size或max_wal_size设置过大还有长事务一直没提交导致后续的WAL都不能被清理。正确的做法是先定位是哪种原因再针对性地处理。比如备库落后就检查备库状态和网络归档失败就看归档日志。除非确认WAL文件已经归档且不再需要否则绝对不建议手动删除。5. 体系结构知识的落地一套自检的命令组合讲了一堆原理最后还是得落到怎么用。这里分享一套我每到一个新环境都会做的“架构体检”不需要额外装插件只用系统自带的视图和命令十几分钟就能对实例的结构健康状况有一个基本判断。5.1 用pg_stat系列视图反推健康状况pg_stat_database里可以看到每个数据库的连接数、事务提交数、回滚数、缓存命中率等。特别推荐关注回滚数如果回滚率异常高说明应用层的事务逻辑可能有问题这会反映到WAL写入量和死元组产生速度上。pg_stat_user_tables是观察表和索引膨胀情况的入口。重点看seq_scan、idx_scan、n_live_tup、n_dead_tup这几个字段。n_dead_tup长期居高不下说明autovacuum没有及时清走死元组seq_scan和idx_scan的比值可以帮助判断索引是否被有效使用。pg_stat_activity是最常用的实时状态视图。我一般按这几类条件查state字段看会话状态backend_start和xact_start看事务持续时长wait_event看等待事件。一条SQL就能把当前实例的“压力分布”拉出来再结合client_addr过滤来源IP判断连接到底是从哪来的。5.2 几个命令完成快速排查给出一套我实际用的“体检”SQL模板。查看连接和事务状态的命令SELECT pid, usename, state, wait_event_type, wait_event, now() - xact_start AS xact_age, now() - state_change AS state_age FROM pg_stat_activity WHERE backend_type client backend ORDER BY xact_age DESC NULLS LAST;查看checkpoint频率SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint, buffers_clean FROM pg_stat_bgwriter;如果checkpoints_req比checkpoints_timed还大说明WAL量触发的检查点比较多可能要考虑调整max_wal_size。查看事务年龄风险SELECT datname, age(datfrozenxid) AS frozen_xid_age FROM pg_database ORDER BY frozen_xid_age DESC;如果age超过15亿就要高度警惕超过18亿离强制清理非常近了。查看WAL目录大小的近似值可以直接在shell里执行du -sh $PGDATA/pg_wal配合流复制状态检查SELECT client_addr, state, sent_lsn, replay_lsn, pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag FROM pg_stat_replication;如果replay_lag一直增长赶紧查备库的磁盘和网络。5.3 两个小实验帮你建立直觉如果只看概念记不住我更建议在本地起一个测试实例自己做两个小实验。第一个实验用来感受MVCC和vacuum。建一张只有主键和几个普通字段的表插入10万行数据然后循环update同一条记录一千次每次提交。接着查pg_stat_user_tables里的n_dead_tup你会发现死元组数量迅速涨到几千甚至更多表文件也可能变大。再手动执行vacuum再查n_dead_tup发现它归零了。这个实验能让你直观理解“update不等于覆盖”和“vacuum到底清理了什么”。第二个实验用来感受checkpoint和WAL的关系。在业务开始前先查一下pg_wal目录的文件列表然后跑一个持续更新数据的大事务提交后再看pg_wal目录会发现文件增加了跑一次checkpoint命令之后再观察WAL文件数量变化。这个实验能帮你把WAL和checkpoint在头脑里串起来WAL一直在长checkpoint的作用是让数据文件赶上WAL的进度。在测试环境里你还可以试试把wal_level改成logical再重启观察有没有额外的WAL信息生成但这类实验千万别在生产上做改完重启并且要花时间观察稍有不慎会影响业务。写在最后的几个实战体会因为自己吃过亏所以有几句话想单独说。一是别迷信“最佳参数”我在多个环境里试过同样的配置结果完全不同。shared_buffers给多了操作系统缓存反而少了全表扫描类查询反而变慢work_mem给大了并发一高内存就报警。参数从来都是跟业务形态绑定的而业务形态只有通过pg_stat系列视图才能看清。二是对体系结构的理解一定要落到命令上。概念能帮你选对排查方向命令才是动手解决问题的工具。我每次接手新实例第一件事就是跑一遍第5小节的体检SQL哪怕暂时没发现问题至少心里有底知道这套系统现在处于什么状态。三是把那些“常年没人管的表”重视起来。MySQL里你可能靠后台线程就能把清理做掉大半PG里autovacuum虽然也在跑但大表、长事务、边缘情况仍然需要人工介入。给每个重要业务表建立单独的autovacuum参数定期看n_dead_tup把事务年龄当成监控项都是成本很低但能救命的好习惯。最后再分享一个小技巧给崩溃恢复留足预算。max_wal_size可以调大但心里要有数一旦宕机恢复时需要重放的WAL也更多。我一般会在测试环境模拟一次断电量一下实际的恢复时间再决定生产环境的max_wal_size和checkpoint_timeout怎么配。这种实验比看任何理论文档都直观做一次你对WAL和checkpoint的理解会彻底不一样。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
