“把屁股数据库优化到底”这个标题我第一眼看到就笑了。老 DBA 都懂所谓“屁股数据库”就是指 PostgreSQL因为这俩词的拼音首字母都是 PG圈子里互相调侃惯了。但你别说越是拿 PG 开玩笑的人越知道这玩意儿的厉害——真要把它调明白了能扛的事儿比你想象的多得多。我这两年接手过好几个“数据库越来越慢、CPU 经常打满、半夜爬起来看告警”的项目无一例外都是 PG。说句实在话PG 本身性能并不差绝大多数慢、卡、堵都是配置、SQL、索引和运维习惯没跟上导致的。这篇文章我就把这几年在 PG 优化上踩过的坑、验证过的方案、总结出的套路一次讲清楚。不管你是刚接触 PG 的开发者还是被慢 SQL 折磨的运维按这个思路去排查和调优都能少走一大截弯路。1. 先搞清楚“优化”到底在优化什么很多人一上来就问PG 怎么优化给几个参数呗。你要是真把参数丢给他大概率第二天就出事。因为优化不是调参而是先定位瓶颈。就好比一个人胖你不能上来就让他吃减肥药得先搞清楚是吃多了、动少了还是内分泌出了问题。1.1 数据库性能瓶颈的三个层次我习惯把 PG 的性能问题拆成三层看第一层是硬件与操作系统层。CPU、内存、磁盘 IO、网络带宽这些是物理底座。底座的瓶颈不解决上层怎么调都是隔靴搔痒。第二层是 PostgreSQL 实例配置层。shared_buffers 多大、work_mem 给多少、WAL 怎么刷、checkpoint 怎么调度这些参数直接决定了 PG 在现有硬件上的发挥上限。第三层是 SQL 与数据模型层。索引建没建对、SQL 写得好不好、表结构设计合理不合理。这一层问题最隐蔽也是优化收益最大的地方。我见过不少案例硬件配置高得吓人32 核 128G 内存结果跑个报表查询要十几秒一查 EXPLAIN全表扫描走了顺序扫描还不自知典型的“好马配了个破鞍”。1.2 优化前必须做的事建立性能基线没有基线就没有优化方向。接手任何一套系统我的第一件事就是采集当前性能数据包括但不限于数据库版本、配置参数、运行时长当前连接数、活跃查询、慢查询日志CPU、内存、磁盘 IO 的使用率曲线最耗时的 Top SQL 列表表的膨胀率、索引使用率这些数据说白了就是给数据库拍个“体检片”。有了它你才知道哪些地方改完是有效果的哪些地方改完是自我安慰。提示很多人优化前不拍片子改完参数自我感觉良好结果过了两个星期数据库又变卡了最后发现根因根本没找到。基线数据是后续所有判断的依据这一步绝对不能省。2. 配置参数优化的核心逻辑配置参数是 PG 优化里最容易被误解、也最容易被滥用的一环。我见过有人把 shared_buffers 直接调到 64GB结果数据库启动都费劲。也见过 work_mem 调到 2GB并发一上来内存直接爆掉。这些都是对参数原理不理解导致的。2.1 共享缓冲区与内存结构PostgreSQL 的内存结构大致分两块共享内存和会话内存。共享内存里最重要的就是 shared_buffers它相当于 PG 自己的页缓存用来缓存数据页和索引页。很多人觉得这个值越大越好实际上并不是。shared_buffers 太大会导致 PG 内核在做缓冲区管理时的开销变大而且在某些操作系统上频繁的数据刷盘会让整个系统变慢。我一般建议shared_buffers 设置为物理内存的 25% 左右。例如服务器物理内存 64GB那么 shared_buffers 可以设置 16GB。这是一个经过大量线上验证的经验值既能保证 PG 有足够的缓存空间又不会因为过大导致管理开销失控。2.2 work_mem 的陷阱会话内存与并发的关系work_mem 可能是 PG 里最容易被“坑”的参数。它控制的是单个会话在排序、哈希连接等操作时能使用的内存上限。听起来很美好但问题是每个会话、每个排序操作都会申请这么大一块内存。也就是说如果 work_mem 设成 1GB同时有 100 个会话在做排序那光是排序可能就要消耗 100GB 内存——服务器直接被打爆。所以我的原则是work_mem 宁可保守不要激进。默认 4MB 起步结合业务慢慢上调。如果发现某类查询频繁使用磁盘排序可以按会话数量估算一下分批往上加。比如 64GB 内存的机器平时并发 50 左右work_mem 设置 64MB 到 128MB 是比较稳妥的区间。注意work_mem 不是越大越好而是要跟你实际的并发情况匹配。调大 work_mem 后一定要观察一个周期的内存使用率别一上来就把并发峰值算漏了。2.3 日志、检查点与写入性能PG 的写入链路里WAL 日志和 checkpoint 是绕不开的两个环节。先看 WAL。wal_buffers 决定 WAL 日志在内存中的缓冲大小默认值其实够用不大需要频繁调整。重点在于 commit 时的 fsync 策略。如果业务对数据一致性要求极高保持默认的 on 就行。如果是内部系统允许丢失极少量的最近事务也可以考虑调低但我不建议这么做——一旦宕机丢数据背锅的永远是你自己。再看 checkpoint。checkpoint 周期太短会导致频繁刷脏页影响性能太长又会导致崩溃恢复时间变长而且故障时可能丢的数据更多。我一般建议 checkpoint_timeout 设为 15 分钟checkpoint_completion_target 设为 0.9。这样既能让刷盘平滑一些又能控制恢复时间在一个可接受的范围。这个参数的调整逻辑是让 checkpoint 尽量在系统低峰期完成避免高峰期大面积刷盘对 IO 负载的平抑有明显帮助。3. SQL 优化慢 SQL 的定位与改写配置参数调整是打地基地基打好了真正的性能差距其实是 SQL 写出来的。同一个需求写法不同性能差几十倍甚至上百倍都很正常。这一节重点讲怎么定位慢 SQL以及改写时最关键的几个思维。3.1 先从日志里把慢 SQL 捞出来PG 默认是不记录慢 SQL 的你需要打开开关。在 postgresql.conf 里设置log_min_duration_statement 1000这个参数表示执行时间超过 1000 毫秒的 SQL 会被记录到日志。线上建议先设成 500ms跑一段时间看日志把出现频率高、执行时间长、影响业务重的 SQL 列出来排个优先级再逐个用 EXPLAIN 分析。到这里要提醒一句只开日志不分析日志等于白开。我见过不少朋友开了慢查询日志结果几个月也没人看数据库慢了也不知道去哪查。建议最少每周抽样一次日志作为常规巡检项。3.2 EXPLAIN 里最值得关注的四个信息拿到一条慢 SQL 之后第一时间执行 EXPLAIN ANALYZE。注意不是单独的 EXPLAIN而是 EXPLAIN ANALYZE因为后者会真实执行一遍 SQL给你返回真实耗时、扫描行数、返回行数。重点看四个东西执行计划里的“实际启动时间”和“实际总时间”判断瓶颈在哪个节点。“实际行数”和“预估行数”的偏差偏差太大往往意味着统计信息过期需要执行 ANALYZE 或调整 default_statistics_target。是否出现“Seq Scan”顺序扫描如果一个大表在关键查询里走了 Seq Scan通常意味着索引没建对或者没有被使用。是否出现“Sort”或“Hash Join”且对应的内存预估很大这时候要考虑 work_mem 不够或者 SQL 本身有优化空间。3.3 一个真实优化案例从 12 秒到 80 毫秒我之前调过一条报表 SQL逻辑很简单从订单表里按用户和时间范围查汇总。表数据量大概 2000 万行SQL 跑了 12 秒多。拿 EXPLAIN ANALYZE 一看问题很明显订单表在 user_id 上没有索引每次查询都全表扫描而且在时间过滤之前就把大量行 join 进来了。我的优化步骤是这样的第一步确认查询模式。SQL 是WHERE user_id ? AND create_time BETWEEN ? AND ?于是建了一个多列索引CREATE INDEX idx_user_time ON orders (user_id, create_time DESC);第二步清理统计信息让优化器拿到准确的行数估算ANALYZE orders;第三步重新执行 EXPLAIN ANALYZE确认执行计划已经从 Seq Scan 变成了 Index Scan扫描行数从 2000 万降到了几千行。最终查询耗时稳定在 80 毫秒左右。这个案例其实没有什么高深技巧难点在于定位到问题、并确认索引模型符合查询模式。很多时候慢 SQL 优化没效果就是因为“索引建了但跟查询不匹配”。比如你建了单列索引但查询条件里是多列组合PG 只能用到其中一部分甚至完全用不上。3.4 改写 SQL 的几条常见陷阱索引建对了SQL 写法不对也一样白搭。我这里列几个高频踩坑点在索引列上做函数运算比如WHERE DATE(create_time) 2025-01-01这样会导致索引失效正确写法是WHERE create_time 2025-01-01 AND create_time 2025-01-02。隐式类型转换比如 varchar 列和数字比较PG 会做类型转换索引大概率用不上。使用SELECT *查出大量不需要的列导致回表次数增加IO 开销成倍上涨。OR 条件里只要有一个分支不走索引整个查询就可能变全表扫描要结合实际情况考虑改写成 UNION 或使用更合适的索引。提示判断一条 SQL 写得好不好别只看它能不能查出正确结果还要看它能不能用上索引、能不能减少回表、能不能在数据库层把数据过滤到最小集合。优化 SQL 的本质就是减少数据库做无用功。4. 索引优化建对、建精、不冗余SQL 优化到一定阶段瓶颈就会转移到索引上。PG 的索引类型比很多数据库都丰富用对了收益巨大用错了非但没有帮助还会拖慢写入和增加存储成本。4.1 不同索引类型的选择PG 的默认索引是 B-tree适合等值查询和范围查询也可以支持排序日常 90% 的场景都用它。但遇到一些特殊场景B-tree 不是最优解。如果业务里有大量 JSONB 字段查询可以试试 GIN 索引。GIN 索引对包含操作符比如、?优化效果非常明显。如果数据量极大、并且查询主要集中在时间范围上BRIN 索引值得关注。它按物理存储块记录最大值和最小值体积小、维护成本低非常适合日志类、时序类的数据表。如果需要支持全文检索PG 内置的 GIN 配合 tsvector 是标准方案比全表扫描效率高一个量级。4.2 索引设计的原则设计索引不能拍脑袋得回到 SQL 的执行模式。我的经验是三个步骤第一步找出高频 SQL分析 WHERE 条件、JOIN 条件、ORDER BY 和 GROUP BY 字段。 第二步根据查询模式设计复合索引字段顺序要考虑等值条件在前、范围条件在后的原则。 第三步验证执行计划确认是否真正走了索引以及是否有回表过多的问题。还有一个很容易忽视的点复合索引不要把区分度低的字段放在前面。比如性别字段只有两个值放在索引最前面不但不能有效缩小扫描范围反而会让索引体积变大。4.3 索引维护膨胀与无效索引索引不是建完就完事了。PG 的索引和表一样会有膨胀问题频繁的更新和删除操作会让索引页产生大量空洞导致扫描效率下降。所以定期重建索引是有必要的REINDEX INDEX idx_user_time;同时建议定期检查无效索引。很多项目上线几年索引建了一堆但真正被用到的没几个。索引本身占用磁盘空间还会拖慢写入性能不用的索引该删就删。这个可以用 pg_stat_user_indexes 视图去查索引的扫描次数长期为 0 的索引基本可以考虑清理。5. VACUUM 与膨胀PG 专属的体检项目如果说 MySQL 开发者刚上手 PG 时最不适应的点VACUUM 机制绝对排第一。PG 的 MVCC 机制决定了每更新一行数据旧版本并不会立刻删除而是标记为“死元组”。如果这些死元组一直不清除表和索引的体积就会不断膨胀查询性能跟着下降。5.1 自带的 autovacuum 够用吗PG 默认开了 autovacuum理论上它会自动清理死元组。但是默认参数在写入频繁、并发高的场景下经常不够用。我调得最多的两个参数是autovacuum_vacuum_scale_factor默认 0.2意味着表超过 20% 的行是死元组才触发清理对大表来说这个阈值太迟钝了。autovacuum_vacuum_threshold默认 50 行小表不受影响大表也等于没设。我给大表的建议是调低 scale_factor或者干脆对大表单独设置存储参数。比如ALTER TABLE orders SET (autovacuum_vacuum_scale_factor 0.05);让大表的 autovacuum 触发更频繁一些防止死元组积累过多。5.2 手动 VACUUM 和 VACUUM FULL 的取舍日常维护中我会在主从低峰期对大表执行手动 VACUUM配合 ANALYZE 一起更新统计信息VACUUM ANALYZE orders;这里必须强调VACUUM 和 VACUUM FULL 是两个完全不同的操作。VACUUM 是常规清理可以并发执行不阻塞读写VACUUM FULL 则是重写整张表会锁表期间所有对该表的写操作都会阻塞对超大表甚至会阻塞很久。所以 VACUUM FULL 只能放在停机维护窗口执行不能日常乱跑。5.3 膨胀率的监控方法膨胀率怎么量化我经常用 pgstattuple 扩展来估算表的实际使用空间和死元组比例CREATE EXTENSION pgstattuple; SELECT * FROM pgstattuple(orders);如果 dead_tuple_percent 长期居高不下说明 VACUUM 的频率跟不上更新速度。这时候要么调高 autovacuum 频率要么考虑业务层面减少高频 UPDATE比如把频繁更新的字段拆到单独的关联表里。6. 连接管理并发撑不住数据库不背锅很多 PG 性能问题的真实原因不是 SQL 慢而是连接数爆炸。PG 的并发连接数和 MySQL 有些不一样每个连接都会占用固定的内存资源连接数多了以后上下文切换和锁竞争都会明显加剧。我见过一台 8 核 16G 的机器连接数飙到 500 多数据库 CPU 使用率直接 100%查什么都卡。6.1 max_connections 应该如何设置max_connections 默认 100实际生产环境我一般结合硬件配置调整。一个简单的估算公式每个连接大约占用 5MB 到 10MB 的会话内存。如果机器内存 32GB留出 8GB 给系统、shared_buffers 和文件缓存剩下约 24GB 可以支撑 2000 到 3000 个连接但这是理论上限。实际上 PG 在几百个连接时性能就开始下降所以我更建议限制连接数在 200 到 300 之间把多余的连接交给连接池处理。6.2 使用 PgBouncer 管理连接池连接池是解决连接数问题的标准方案。PgBouncer 是一个轻量级的连接池中间件它把前端应用和后端数据库之间的连接做了一层代理复用数据库连接从而大幅减少数据库端的真实连接数。在项目里引入 PgBouncer 后数据库端实际连接数可以稳定在几十个即使应用端有几百个并发后端也不会有压力。部署不难核心配置就是 pool_mode 设置为 transaction这样每个事务结束就释放连接适配绝大多数 OLTP 业务。注意加了连接池并不是万事大吉。如果业务里长事务特别多事务型连接池也可能撑爆。这时候要从业务层面减少长事务必要时结合 max_connections 做硬性限制。7. 监控系统与日常巡检优化是一个持续过程不是今天调完就结束。没有监控你很难判断优化是否有效更不可能在问题发生前提前预警。我的建议是搭一套基础监控不用太复杂抓住几个关键指标就够了。7.1 必须加的监控指标CPU、内存、磁盘 IO用系统层监控工具比如 Prometheus node_exporter。活跃连接数超过 max_connections 的 70% 就要预警。慢 SQL 数量这个直接对应日志分析结果趋势上升就要追查原因。事务 ID 回绕进度PG 的事务 ID 超过 20 亿会强制冻结如果监控不到位可能导致数据库不可用这是大事故。复制延迟如果做了主从副本延迟时间直接影响读扩展和容灾能力。7.2 必装的 PG 拓展有些拓展一定要装上比如 pg_stat_statements 和 pg_stat_monitor。前者是统计 SQL 执行情况的经典扩展可以查看每类 SQL 的总耗时、平均耗时、调用次数帮助快速定位需要优化的 SQL。启用方法很简单CREATE EXTENSION pg_stat_statements;还需要在 postgresql.conf 里设置 shared_preload_librariesshared_preload_libraries pg_stat_statements重启数据库后生效。这个扩展的价值在于它把“慢 SQL”从被动等待日志发现问题变成主动按聚合视角看趋势。我每次做性能巡检第一张表就是查询 pg_stat_statements 里 calls、total_time、mean_time 排序靠前的 SQL。8. 常见问题与排查技巧实录优化做多了会发现很多问题其实是重复出现的。我这里把几种高频问题和对应的排查思路整理一下方便你直接对号入座。现象可能原因排查方法查询突然变慢执行计划发生劣化用 EXPLAIN 对比前后计划检查统计信息是否过期CPU 居高不下大量 SQL 并发扫描大表查 pg_stat_activity 里的活跃查询逐条分析执行计划数据库连接数告警应用未使用连接池检查 max_connections 实际使用率接入 PgBouncer表膨胀严重autovacuum 触发过慢查看 pgstatvacuuminfo调整 scale_factor 参数写入慢、磁盘 IO 高checkpoint 频繁刷盘观察 ftail 的时间分布调大 checkpoint 间隔复制延迟持续增长备库性能不足或主库负载过高检查备库 CPU、磁盘评估是否需要提升备库规格另外再分享一个排查技巧。当数据库瞬间变慢、又查不出具体 SQL 问题时我习惯用这条命令快速定位当前正在执行的查询和状态SELECT pid, state, wait_event_type, wait_event, query_start, query FROM pg_stat_activity WHERE state active ORDER BY query_start;重点看 wait_event_type 和 wait_event。如果大量会话卡在 ClientRead通常是应用端响应慢不是数据库的问题如果卡在 DataFileRead说明磁盘 IO 是瓶颈如果卡在 Lock说明有锁等待需要进一步查锁的源头。9. 一条完整的优化路径总结每次接到新的 PG 性能问题我都会按一套固定的流程走分享出来供你参考第一步采集基线性能数据。建立体检报告包括系统资源、PG 配置、慢 SQL、连接数、表膨胀率。第二步按优先级排查系统层问题。CPU、内存、磁盘 IO、连接数有没有明显瓶颈有就先解决。第三步定位慢 SQL。从慢查询日志和 pg_stat_statements 里找出 Top SQL逐个 EXPLAIN ANALYZE确认问题根因。第四步规范化 SQL 写法与索引。能改 SQL 解决的优先改 SQL需要建索引的再建索引注意复合索引字段顺序。第五步调整 PG 配置参数。shared_buffers、work_mem、checkpoint、autovacuum 这些结合硬件和业务特征去调整一次只改一两个参数观察效果后再继续。第六步建立持续监控机制。让优化效果可观测让新问题能提前暴露。这套路径看起来朴素但特别实用。我几乎每次都是靠它把数据库从“勉强能用”带到“稳如老狗”的状态。最后再分享一点个人体会PG 优化真不是调几个参数那么简单但也没有难到无从下手。你只要愿意沉下心看执行计划、理解它背后的缓冲区管理机制和 MVCC 原理很多问题想不通的地方自然就通了。希望这篇文章能帮你少踩几个坑真正把自己的 PG 优化到让团队放心的状态。