企业官网建设流程全解析

1. 先看四组数字：慢数据库的第一轮体检

很多人拿到一台慢得离谱的 PostgreSQL 数据库，第一反应就是冲进postgresql.conf里一顿改参数，shared_buffers 拉满、work_mem 调大、max_connections 干到 2000。我干这行十多年，这类"开局盲调"的案例见得太多了，十有八九最后都调出了新问题。

先说一个真实场景。前年我接手过一个电商业务库，服务器配置其实不差——16 核 CPU、64GB 内存、SSD 磁盘，但 PostgreSQL 装完基本就是默认配置跑起来的。业务跑了两三个月，平时看着还行，某次促销活动流量一上来，数据库 CPU 直接飙到 98%，慢查询日志里一大堆两秒以上的 SQL，连监控页面打开都转圈。我上去做的第一件事不是改参数，而是先跑了一轮体检，把下面这四组数字全部看了一遍。

1.1 第一组：连接数与活跃会话

连接数是最容易被忽视的"先兆指标"。很多人只知道看 CPU 和内存，却不知道数据库其实是被"挤死"而不是被"压死"的。先用下面这条 SQL 看一下当前连接状态：

SELECT state, wait_event_type, wait_event, count(*) FROM pg_stat_activity GROUP BY state, wait_event_type, wait_event ORDER BY count(*) DESC;

输出里重点看几个 state：

• active：正在执行 SQL 的会话。
• idle：连接还在，但啥事没干。这种连接本身不消耗多少 CPU，但占着连接名额。
• idle in transaction：事务开了，不提交也不回滚，一直挂着。这是最坑的状态，后面我会单独讲。
• wait_event_type = Lock：说明会话在等锁，排队排到怀疑人生。

那次排查时我看到的数字是：总共 280 个连接，其中 active 只有 30 个左右，idle in transaction 占了 90 多个，剩下的全是 idle。数据库自身能处理的并发其实远没有那么高，大部分会话都堵在事务没提交和锁等待上。

1.2 第二组：缓存命中率

缓存命中率这个概念，我可以给你一句话解释：PostgreSQL 要读一个数据页时，先去自己的内存缓冲区（shared buffers）里找，找到了就是命中，找不到就得去磁盘上翻，翻磁盘的速度比内存慢好几个数量级。

查缓存命中率用这条 SQL：

SELECT blks_hit AS 内存命中次数, blks_read AS 磁盘读取次数, round(blks_hit::numeric / (blks_hit + blks_read) * 100, 2) AS 缓存命中率 FROM pg_stat_database WHERE datname = current_database();

99% 以上算正常，低于 99% 就得警惕了。如果是 95% 甚至更低，优先怀疑两件事：shared_buffers 配得太小，或者某些大表在频繁做全表扫描，根本没机会复用缓存。我见过一个运营报表库，命中率常年 93%，查出来原因是有人每天凌晨跑全量汇总任务，把所有表的缓存都冲了个干干净净，白天业务来查就又得重新去磁盘搬数据。

1.3 第三组：慢查询与锁等待

慢查询这块，先确认你有没有开日志记录：

SHOW log_min_duration_statement;

如果这个值是-1，说明慢查询日志根本没开，等于让数据库裸奔。建议一开始先设为 1000ms，也就是超过 1 秒的 SQL 全部记录下来，跑上一两天再根据实际情况收紧到 500ms 或者更短。

锁等待则是很多慢查询的"幕后黑手"。SQL 本身可能只要 20ms，但前面有个事务一直锁着那行数据，它就得在后面排队等，一等等个十几秒。用这条 SQL 看所有等待中的会话：

SELECT pid, datname, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event_type IS NOT NULL;

wait_event_type = Lock 的就是锁等待，后面pg_stat_activity里会显示它具体在哪把锁上排队，配合 pg_locks 可以把它等的那把锁精确找出来。这个排查链路我在第 4 部分细讲。

1.4 第四组：系统层指标

数据库自己的指标看完了，还得看操作系统层面。我的习惯是，不管远程还是本机，先跑这么几步：

free -h iostat -x 1 3 uptime

free 看有没有 swap 使用——一旦开始 swap，数据库性能会断崖式下跌，那种延迟你从应用层的请求耗时曲线上能看得一清二楚。iostat 看磁盘的%util和await，如果%util长期在 90% 以上，说明磁盘已经是瓶颈了，这时候你调数据库参数意义不大，得先想想是不是该降低 IO 量、加内存或者换磁盘。uptime 看 load average，如果 load 比 CPU 核数还高很多，基本上就是 CPU 排队了。

这四组数字看完，我心里基本对数据库的状态有个谱了。什么状态该动参数、什么状态该改 SQL、什么状态该先处理连接和事务，方向就出来了。

2. postgresql.conf 里真正值得动手的几项

体检做完，确认不是连接堆积、不是磁盘 IO 爆掉，再往下走就是配置文件调优。你要知道，PostgreSQL 默认配置的定位是"在任何环境下都能跑起来"，它保证的是下限，不是最优。对一台确定规格的服务器，下面这几个参数才是真正值得动手的。

2.1 shared_buffers 与 effective_cache_size：先盘明白内存预算

shared_buffers 是 PostgreSQL 自己的共享内存缓冲区，所有表数据和索引的页都会经过这里。默认值只有 128MB，这在小内存 VPS 上没问题，但对真正的业务服务器来说明显不够。

业界一个比较稳妥的经验值：物理内存的 25% 左右，上限一般不超过 8~10GB，再往上收益会明显递减，因为 PostgreSQL 还需要依赖操作系统页缓存来兜底。假设服务器是 64GB 内存，shared_buffers 设 16GB 是合理的；如果是 8GB 的小机器，设 2GB 左右就行。

另一个很容易被忽略的参数是effective_cache_size。它跟 shared_buffers 完全不是一回事——它告诉 PostgreSQL 查询规划器："这台机器上操作系统页缓存大概能给你提供多少内存。"说白了，这是一个估算值，用于帮助规划器判断走索引还是走全表扫描更划算。通常可以设成物理内存的 60%~75%，比如 64GB 内存就设 48GB。

这两个参数搞明白了，很多新手就不会再把它们混着调。shared_buffers 管的是 PostgreSQL 自己的缓存池，effective_cache_size 管的是"给规划器的信心值"。

2.2 work_mem 与 maintenance_work_mem：排序和索引的代价

work_mem 是用来做排序、哈希连接、临时表的会话级内存。注意关键词：会话级。也就是说，每个连接执行涉及排序或哈希操作时，都可能单独分配一份 work_mem 大小的内存。这不是全局值，不能简单按"服务器内存大就调大"来理解。

默认 4MB 对大多数 OLTP 查询来说够用，但碰上一些需要排序几十万行的查询，4MB 很快就会溢出到临时文件，性能断崖式下跌。我曾经处理过一个报表查询，排序量很大，临时文件写了几百 MB，查询跑了 27 秒。把 work_mem 从 4MB 调到 64MB 之后，临时文件直接消失，查询降到 1.2 秒。

但这里有个必须算的账。假设一台 8GB 内存的服务器，max_connections = 200，你把 work_mem 调到 64MB，极端情况下 200 个连接同时做排序，内存占用就是200 × 64MB = 12.8GB，再加上 shared_buffers 和其他开销，8GB 内存瞬间爆掉。所以 work_mem 的合理值不是拍脑袋定的，我一般这么算：

安全值 ≈ (可用内存 - shared_buffers - 系统预留) / 预期并发排序连接数

大多数通用业务场景，work_mem 处在 16MB 到 64MB 之间是比较稳的一个区间，前提是你的连接数没有失控。连接池做好、并发压下来，work_mem 可以适当放大；连接数失控的话，任何 work_mem 都救不回来。

maintenance_work_mem 则不一样，它专门用于维护性操作——VACUUM、CREATE INDEX、添加外键等。这些操作通常一次并发量不高，可以把值给大一些，64MB 到 1GB 都常见。索引重建和 VACUUM 的快慢非常依赖这个值，我一般直接设 256MB 起步，大表环境直接 1GB。

2.3 checkpoint 与 WAL：磁盘 IO 抖动的根源

讲 WAL 和 checkpoint，我尽量用大白话。PostgreSQL 任何修改数据的操作，会先写入 WAL 日志（预写日志），数据页本身先在内存里变脏，等到 checkpoint 或 buffer 写满时才刷到磁盘。checkpoint 就是那个"把脏页刷下去"的动作。

问题在于，如果 checkpoint 触发得太频繁或者刷得不够分散，磁盘会在某个时间点突然承受一大波写入压力，表现出来就是数据库每隔一段时间就有一次 IO 尖峰，应用层请求延迟跟着一起抖动。这个现象我调试过好多次，现象是"CPU 不高、内存不高，但每过几分钟就卡一下"。

关键参数：

• checkpoint_timeout：默认 5 分钟，一般不宜低于 5 分钟，很多生产库用 10~15 分钟。
• max_wal_size：默认只有 1GB，这个值太小时，checkpoint 会被 WAL 写满"逼着"提前发生。建议按你的业务写入量调大，8GB 以上都常见。
• checkpoint_completion_target：控制 checkpoint 期间脏页刷盘要刷多快，默认 0.9 已经很合理，表示尽量分散在整个 checkpoint 周期内刷完。

还有wal_buffers，默认 16MB 其实是够用的，不用刻意动。除非你明确知道自己写了什么大事务，否则这个参数很少是瓶颈。

2.4 autovacuum：延迟炸弹

autovacuum 可能是最容易被忽视、爆起来又最致命的系统。PostgreSQL 的 MVCC 机制决定了，更新和删除的行并不会立刻从物理文件中消失，而是标记为"死亡版本"（dead tuple）。autovacuum 的职责就是清理这些死元组，保持表和索引不会持续膨胀。

默认的autovacuum_vacuum_scale_factor是 0.2，意思是表中 20% 的元组变成死亡版本时触发一次 vacuum。对大表来说，这个触发条件太迟钝了。比如一张 10 亿行的订单表，20% 就是 2 亿行死元组，在触发之前表已经膨胀得不成样子，索引变得巨大，查询性能直线下降。

我处理过不少这样的案例：一个表行数没怎么涨，查询却越来越慢，查pg_stat_user_tables里的n_dead_tup发现都两三千万了。这个问题的修复不是马上跑VACUUM FULL（那会对业务造成锁等待和磁盘暴增），而是：

1. 先把 autovacuum 的触发阈值调敏感，比如autovacuum_vacuum_scale_factor = 0.05。
2. 或者在很小的表上直接用autovacuum_vacuum_threshold控制最小触发量。
3. 再手动对热点表补一轮VACUUM。

关于 autovacuum 的参数调整，我的建议是："默认值不适合大表，调大autovacuum_work_mem（或 maintenance_work_mem）来加快 vacuum 速度，调低 scale_factor 来提高清理频率。"具体数值没有银弹，得看着监控数据调，比如 vacuum 进程经常出现在慢查询里，就说明它干得太吃力了。

2.5 改动参数的顺序与生效方式

参数改完不是全部要重启。shared_buffers这类需要重启，work_mem、effective_cache_size、max_wal_size这类是 SIGHUP 级别的，reload一下就行：

pg_ctl reload

修改完用SHOW验证一下有没有实际生效：

SHOW shared_buffers; SHOW work_mem; SHOW max_wal_size;

我的经验是，这些参数千万不要一次性全部改完。每改一组，用 pgbench 或者业务压测脚本跑一轮，记录响应时间、TPS、IO 指标，观察一两天再继续动下一组。一次动太多，出了问题你根本不知道是哪个参数造成的。这个习惯救过我很多次，也建议所有做 DBA 相关工作的人养成。

3. 查询与索引：从慢 SQL 实测中总结的几种典型问题

参数调优是给数据库"搭好地基"，但地基好了，SQL 本身不行照样白搭。一个 PG 性能调优的检查清单里，查询侧和索引侧的排查，有时候比参数调整带来的收益更大、见效也更快。

3.1 EXPLAIN ANALYZE 应该怎么读

很多刚入门的朋友看到 EXPLAIN 输出就懵。我的建议是：其他列可以暂时不看，先把actual time和rows看懂。

下面是我造的一个简单例子。假设订单表orders有 500 万行，我们要查某个客户最近一个月的订单：

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345 AND order_time >= '2024-06-01' ORDER BY order_time DESC;

执行计划里如果看到这样一段：

Seq Scan on orders (cost=0.00..131234.00 rows=1 width=100) (actual time=12.834..384.210 rows=158 loops=1) Filter: ((customer_id = 12345) AND (order_time >= '2024-06-01')) Rows Removed by Filter: 4999842

翻译过来就是：数据库把 500 万行全部怼了一遍，最终只剩了 158 行，其余 4999842 行都被 filter 干掉了。actual time 从 12ms 到 384ms，说明它扫完之后还要逐个过滤，整体耗时 384ms 就是这么来的。

看到这种计划，如果你的业务经常查某个客户的数据，第一反应应该是：customer_id上有没有索引？没有的话建一个：

CREATE INDEX idx_orders_customer_time ON orders (customer_id, order_time DESC);

重建完再 EXPLAIN ANALYZE，执行计划会变成：

Index Scan using idx_orders_customer_time on orders (cost=0.43..217.40 rows=158 width=100) (actual time=0.038..0.162 rows=158 loops=1)

从 384 毫秒降到 0.16 毫秒，接近 2000 倍的提升，而且不是靠什么高深技术，就是加了一条复合索引。这种例子在真实业务里太多了，所以我才强调，排查慢 SQL 的第一个动作一定是 EXPLAIN ANALYZE，亲眼看一下数据库到底是"怎么干的"。

3.2 索引失效的高频场景

索引建了但没走，也是经常让人抓狂的事。我总结过最常见的几种"索引失效"：

函数包裹列：WHERE DATE(order_time) = '2024-06-01'这种写法，如果order_time上建了普通索引，索引用不上，因为数据库必须先对每一行执行DATE()函数才能比较。解决方案是改写成范围查询：WHERE order_time >= '2024-06-01' AND order_time < '2024-06-02'，或者建表达式索引：CREATE INDEX ON orders (DATE(order_time))。

隐式类型转换：比如WHERE customer_id = '12345'，如果customer_id是 bigint 而 '12345' 是字符串类型，PostgreSQL 默认会把列类型转成字符串再做比较（或者反过来），一旦列被转换，索引就失效了。建议保持参数类型和列类型一致，或者用::bigint显式转换。

左模糊LIKE '%abc'：普通 B-tree 索引无法加速"-- 你懂的前导通配符查询"这类需求，因为索引是有序排列的，只有知道了前缀才能快速定位。LIKE 'abc%'前缀匹配能走索引，LIKE '%abc'就走不了。必须做这种查询的话，考虑 pg_trgm 的 GIN 索引。

OR 条件：WHERE a = 1 OR b = 2且 a 和 b 分别有独立索引时，规划器未必能很好地把两个索引合并起来。比较稳妥的办法是写成UNION ALL或者建复合索引，具体情况要 EXPLAIN 验证。

3.3 统计信息过期与 ANALYZE

还有一类情况是：索引建了、SQL 写法也没有问题，执行计划依然选错。这时候多半是统计信息过期了，规划器不知道真实的数据分布。

PostgreSQL 的规划器依赖pg_statistic里的统计信息来估算每个过滤条件能筛掉多少行。如果表刚经历了大范围的 update/delete，统计信息还停留在很久之前，规划器就可能高估或低估结果集大小，然后选一个烂计划。

我自己踩过一次很深的坑：一张表每天夜里批量更新一批行的状态字段，但统计信息三个月没更新。结果有一条 SQL 按状态字段过滤，实际只匹配 300 行，规划器却估算出 500 万行，于是老老实实做了全表扫描，每次查询都要扫两三分钟。运行ANALYZE之后，执行计划变成走索引，查询 30 毫秒完成。

所以如果你开着 autovacuum，但查询仍然莫名变慢，先手动跑一下：

ANALYZE orders;

然后再 EXPLAIN ANALYZE，确认执行计划和执行时间有没有变化。这个动作不用花钱、不用重启、几乎无风险，却是我在日常调优中使用频率最高的手段之一。

3.4 索引不是越多越好

最后必须泼一盆冷水：索引不是越多越好。每个索引都在占用磁盘空间，每次写入（INSERT/UPDATE/DELETE）都要同步维护所有相关索引，写入放大是实打实的。

我的原则是：先通过慢查询日志找到真正需要优化的 SQL，再针对性地建索引；建完用 EXPLAIN ANALYZE 验证，确认收益后保留，没收益就删掉。一个表如果索引超过 5~6 个，你就要开始怀疑是否有冗余了。特别是那些"为了可能用上而建的"索引，淘汰掉往往能让写入性能明显回升。

4. 连接、并发与连接池：数据库不是被查死的，是被挤死的

前面说了，我接手那个电商库时，连接数 280，但很多连接都挂着不动。数据库为什么会被人为"挤死"，这个机制很有必要讲透。

4.1 max_connections 调大为什么是饮鸩止渴

PostgreSQL 的连接模型是"每个连接一个进程"。也就是说，每个连接都会占用一块真实的内存：解析上下文、排序缓存、各种游标、临时内存等等，加起来通常会达到几 MB，复杂场景更高。200 个连接就是 200 个进程，哪怕它们什么都不干，内存管理、进程调度、上下文切换的成本也相当可观。

更关键的是，数据库的并发能力是有上限的。CPU 有核数，IO 有带宽，能同时高效执行的查询数量有限。连接数 100 时可能有 20 个并发查询正在执行；连接数调到 1000 时，大部分连接只是在排队等待 CPU 和锁，响应时间反而更差，因为调度开销变大了。

所以，max_connections我的习惯是按业务实际需要来定，不要把上限开得太大。这是数据库的一个"天花板"，真正要解决的是"应用层怎么合理复用连接"，而不是"让数据库多扛一倍的连接"。

4.2 连接池的正确姿势

从应用的视角看，一次请求建一条新连接是最浪费的做法。正确姿势是使用连接池——应用内连接池 + 数据库前端的连接池代理。

• 应用层连接池：Java 的 HikariCP、Go 的 pgxpool、Python 的 SQLAlchemy 连接池等，让应用复用有限数量的长连接。很多数据库连接数爆棚，本质上是应用层忘记配连接池，或者连接池配太大了。
• 中间层连接池：比如 pgbouncer，它在数据库前面当代理，应用连 pgbouncer，pgbouncer 再去连 PostgreSQL。可以把大量短连接聚合成少量长连接，特别适合那种"每个请求都开一个连接"的脚本场景。

我在那个电商库里做的其中一个改动，就是把应用层的连接池最大连接数从 200 压到 50，再叠加 pgbouncer 把后端连接控制在 40 个左右。数据库的连接数从 280 降到 80 以内，CPU 负载反而明显下降了——因为省下的全是进程切换和内存管理开销。

4.3 锁等待与 idle in transaction

回到那个 90 多个 idle in transaction 的案例。当时的现象是：有一个定时任务，每跑一次就开一个事务，中途因为某些业务数据问题抛异常退出了，但事务没有 catch 住，没有回滚也没有提交，连接就这么一直挂着。这个事务持有了一批行级锁。其他会话更新这些行时，就得在后面排队等它释放锁。

这类问题的清理和预防有一套组合拳：

1. 马上找出 idle in transaction 的会话，评估后可以pg_terminate_backend(pid)强制终结掉。
2. 应用层加超时控制，防止事务无限挂起。
3. 数据库层加上idle_in_transaction_session_timeout，比如设 60 秒，超时自动断开。这个参数不影响正常短事务，但对排查问题、保护现场非常有用。
4. 所有涉及锁的操作加上lock_timeout，一般几千毫秒就够了，防止无期限地排队等锁。

排查锁等待我习惯用这么一条经典 SQL，找出"到底是谁堵着谁"：

SELECT blocking.pid AS blocker_pid, blocked.pid AS blocked_pid, blocked.query AS blocked_query, blocking.query AS blocking_query FROM pg_locks blocked JOIN pg_stat_activity blocked_act ON blocked.pid = blocked_act.pid JOIN pg_locks blocking ON blocking.locktype = blocked.locktype AND blocking.database IS NOT DISTINCT FROM blocked.database AND blocking.relation IS NOT DISTINCT FROM blocked.relation AND blocking.page IS NOT DISTINCT FROM blocked.page AND blocking.tuple IS NOT DISTINCT FROM blocked.tuple AND blocking.pid != blocked.pid JOIN pg_stat_activity blocking_act ON blocking.pid = blocking_act.pid WHERE NOT blocked.granted;

这个 SQL 输出的就是：某个会话正握着锁不放，另一个会话在后面排队。看到它，你也就明白为什么"数据库明明没满，业务却卡死"了——问题根本不在查询性能，而在锁调度。

5. 实用速查清单：20 条检查项表格

前面讲了很多原理和案例，最后我把平时排查 PostgreSQL 性能问题时会过一遍的关键项整理成一张速查表。这张表不是理论清单，是我在这些年的实践中一步步筛出来的，基本覆盖了从操作系统到查询语句的各个层面。你可以把它当作一个"体检报告模板"，每次遇到性能问题就逐项过一遍。

这张表你在排查时对着走一遍，大概率能找到问题的大方向。不过"基线"并不是硬性规定，具体业务要具体修正。比如一个内存只有 2GB 的轻量应用，shared_buffers 就不可能 25% 地去配，要给操作系统留足余量；一个写密集型系统，对 WAL 参数的感受也会和读多写少的系统完全不同。

这套检查项配合一个习惯效果会更好：把pg_stat_statements开起来。它会把所有 SQL 的调用次数、总耗时、平均耗时、IO 开销等数据统计出来，按月分析哪个 SQL 是真正拉低整体性能的。很多人在性能优化上费了好大劲，结果发现优化的目标 SQL 根本不是业务里最常跑的那条，原因就是没有数据支撑的时候，谁都容易拍脑袋。

开起来很简单，shared_preload_libraries里加上pg_stat_statements，重启，再CREATE EXTENSION pg_stat_statements;，剩下的就交给时间去积累数据。我每接手一个新的 PostgreSQL 实例，第一个开启的扩展几乎都是它，这比任何调优参数都更早、更准确地告诉我系统真正的压力在哪。