企业官网建设流程全解析

“把屁股数据库优化到底”这个标题，我第一眼看到就笑了。老 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_libraries：

shared_preload_libraries = 'pg_stat_statements'

重启数据库后生效。这个扩展的价值在于：它把“慢 SQL”从被动等待日志发现问题，变成主动按聚合视角看趋势。我每次做性能巡检，第一张表就是查询 pg_stat_statements 里 calls、total_time、mean_time 排序靠前的 SQL。

8. 常见问题与排查技巧实录

优化做多了，会发现很多问题其实是重复出现的。我这里把几种高频问题和对应的排查思路整理一下，方便你直接对号入座。

另外，再分享一个排查技巧。当数据库瞬间变慢、又查不出具体 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 优化到让团队放心的状态。