PostgreSQL 性能调优

我接过一个工单:订单列表打开要 8 秒,产品同学第一反应是"是不是该换台更大的数据库了?"。我看了一眼 RDS 监控,CPU 30%、内存还剩一半、IOPS 也远没打满。加机器?这台 8 核 32G 的库根本没吃满。真正的原因是某条查询在 2000 万行的订单表上做了 Seq Scan,加上应用层一个写歪了的 N+1,8 秒里 7.5 秒在数据库里转圈。

做了这么多年 PG 调优,我有个强烈印象:80% 的性能问题,根因是查询、索引、配置,而不是硬件。加 CPU、加内存、上 SSD,是最后一步,不是第一步。这篇文章讲一套"先定位再优化"的方法:从慢查询日志到 EXPLAIN,从 shared_buffers 到 VACUUM,一层层说清楚。

一、性能问题别急着加硬件:先把锅甩对

遇到数据库慢,最常见的直觉是"加机器"。这个直觉往往是错的,原因有三:

硬件掩盖问题,不解决问题。一条没索引的查询在 4 核机器上跑 8 秒,换到 16 核大概跑 6 秒——提升有,但 Seq Scan 还是 Seq Scan,数据量翻倍它又回来了。钱花出去了,问题只是被推迟。
云数据库加配置特别贵。RDS / Aurora 从 4 核升到 8 核,月账单可能直接翻倍。而这个钱本来一张索引就能解决。
真正的瓶颈往往不在硬件。我经手过的案例里大致分布是:索引缺失或没用上(40%)、查询写得烂(25%)、配置没调(10%)、连接打爆(10%)、统计信息过期/VACUUM 没做好(5%)、硬件真的不够(10%)。硬件不够,是排除了前面所有原因之后的最后结论。

所以第一步永远是问一句:慢在哪? 不回答这个问题就花钱加机器,等于不诊断就开药。

二、定位方法:三件套——pg_stat_statements、EXPLAIN ANALYZE、锁等待

1. pg_stat_statements:找出最慢的查询

这是 PG 性能调优的第一入口。它是扩展模块,默认没开,先在 postgresql.conf 里加:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

重启后建扩展,然后查:

-- 平均耗时最高的 10 条查询
SELECT query,
       calls,
       mean_exec_time AS mean_ms,
       total_exec_time AS total_ms,
       rows,
       shared_blks_hit * 100.0
         / NULLIF(shared_blks_hit + shared_blks_read, 0) AS hit_ratio
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

这一查能立刻告诉你:最痛的查询是哪几条、被调用了多少次、缓存命中率多少。hit_ratio 低于 90% 基本是缓存没命中,要么数据量太大、要么 shared_buffers 太小。

别只盯 mean_exec_time。total_exec_time 高 + calls 高的查询才是真正的总耗时大头——一条调用 10 万次、每次 5ms 的查询,比一条调用 1 次、每次 5 秒的查询更值得优化。

2. EXPLAIN ANALYZE:看一条查询到底在干嘛

拿到慢查询,下一步就是 EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 12345 ORDER BY created_at DESC;

读执行计划,我关注几件事:

Seq Scan on orders:全表扫描。小表无所谓,大表看到 Seq Scan 基本是索引没建对或没用上。
Index Scan / Index Only Scan:用了索引。Index Only Scan 是最优态,意味着索引覆盖了查询,连表都不用回。
Sort:排序。如果带 Sort Method: external merge Disk,说明 work_mem 不够,排序溢出到磁盘,慢得离谱。
Buffers: shared hit=12 read=4:read 多说明缓存没命中,要读磁盘。
rows 估计值 vs 实际值:差太多大概率是统计信息过期(见第六节)。

一个典型反例:

Seq Scan on orders  (cost=0.00..485231.20 rows=1 width=212) (actual time=1.2..6234.8 rows=2)
  Filter: (status = 'paid' AND created_at > '2025-01-01')
  Rows Removed by Filter: 19999998
  Buffers: shared hit=1245 read=482311

扫了 2000 万行,过滤掉 19999998 行,最后返回 2 行——这是索引严重缺失的铁证。

3. 锁等待和连接堆积

有时候"慢"不是查询慢,是等锁或等连接:

-- 当前正在等待的查询
SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS wait_time, query
FROM pg_stat_activity
WHERE state != 'idle';

-- 阻塞链:谁挡了谁
SELECT blocked.pid     AS blocked_pid,
       blocked.query   AS blocked_query,
       blocking.pid    AS blocking_pid,
       blocking.query  AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

wait_event_type = 'Lock' 一堆,基本是行锁或表锁冲突,通常是长事务挡住了后面的写入。连接数打满(连接数逼近 max_connections)是另一个独立话题,见第五节。

三、配置调优:四个参数决定八成的内存表现

PG 默认配置是"在任何机器上都能跑起来"的保守值。一台 32G 的服务器开默认 shared_buffers=128MB,纯属暴殄天物。调这几个:

参数	建议值	说明
`shared_buffers`	内存的 25%	PG 自己管理的缓存,数据页缓存
`effective_cache_size`	内存的 75%	告诉优化器"系统大概有这么多缓存可用",影响是否选 Index Scan
`work_mem`	4–16MB	排序、哈希的内存,按连接数算总量
`maintenance_work_mem`	256MB–1GB	VACUUM、CREATE INDEX 用的内存
`max_connections`	100–200	别开太大,大了反而慢,该用连接池

# 32G 内存服务器示例
shared_buffers = 8GB              # 25%
effective_cache_size = 24GB       # 75%
work_mem = 16MB                   # 每连接每操作 16MB,小心总量
maintenance_work_mem = 1GB
max_connections = 100             # 配合 PgBouncer,这个值别再加

几个实战注意点:

shared_buffers 不是越大越好。超过内存的 40% 收益急剧下降,因为 OS 的 page cache 也在做同样的事。25% 是经验最优。
work_mem 要按"最大并发数 × 单查询操作数"算总量。100 个连接每个 16MB,最坏情况 1.6GB——还行;设成 64MB 就是 6.4GB,直接顶满。先小后大,看 EXPLAIN 有没有排序溢出磁盘再调。
effective_cache_size 只影响优化器决策,不分配内存。设小了优化器会误以为没缓存,从而避开 Index Scan 选 Seq Scan。这个值保守是常见错误。
max_connections 别盲目调大。PG 是进程模型,300+ 连接光 context switch 就能把 CPU 吃光。结论:生产 PG 直连 100~200,再多就上 PgBouncer(见第五节)。

四、查询优化:索引、SELECT *、N+1、子查询

定位到慢查询后,优化手段按 ROI 排序:

1. 加索引——但要加对

最常见的"慢查询"就是缺索引。加之前先确认它真没用上(EXPLAIN 是 Seq Scan),加完之后再 EXPLAIN 验证一次:

-- 单列索引
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 复合索引:顺序很关键,等值条件 + 高选择性在前
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- 部分索引:只索引热数据,体积小、命中率高
CREATE INDEX idx_orders_unpaid ON orders(created_at)
  WHERE status = 'pending';

三个坑要记住:

复合索引的列顺序。(user_id, created_at) 能服务 WHERE user_id=? 和 WHERE user_id=? ORDER BY created_at,但服务不了 WHERE created_at=?。索引是左前缀匹配,顺序定生死。
索引不是越多越好。每个索引都是写入代价——INSERT/UPDATE/DELETE 每条索引都要更新。一张 10 个索引的表,写入会比 2 个索引慢好几倍。只加查询真用得上的索引,定期用 pg_stat_user_indexes 清理从没被读过的索引。
LIKE '%xxx' 用不上普通索引。前缀匹配 LIKE 'xxx%' 可以,模糊匹配要上 pg_trgm 扩展或全文检索。

2. 别 SELECT *

-- 烂:拉 200 列回应用层,网络、内存全浪费
SELECT * FROM users JOIN profiles ON ... WHERE ...;

-- 好:只要 3 列
SELECT u.id, u.email, p.avatar FROM users u JOIN profiles p ON ... WHERE ...;

宽表(几十上百列)上,SELECT * 的网络传输和反序列化开销可能比查询本身还大。再加上列出字段能配合 Index Only Scan——索引覆盖查询时,PG 连表都不回。

3. 消灭 N+1

ORM 是 N+1 的温床:

// 烂:1 次查用户 + N 次查订单
const users = await db.user.findMany();
for (const u of users) {
  u.orders = await db.order.findMany({ where: { userId: u.id } });
}

// 好:ORM 一次 JOIN 或一次 IN 搞定
const users = await db.user.findMany({ include: { orders: true } });

定位 N+1 最简单的方法:开 ORM 的 query log,看一次请求里同一种查询是不是被重复调了几十次。

4. 子查询与 CTE 的坑

PG 12 之前 CTE (WITH) 是优化栅栏——查询会被物化,外层过滤进不去。12+ 默认 NOT MATERIALIZED,大多数情况没问题了,但复杂 CTE 里如果有重复扫描,该物化还是得手动 MATERIALIZED。另外 IN (SELECT ...) 在某些版本下会退化成 Seq Scan,改成 EXISTS 或 JOIN 常常有意外提速。

五、连接池:别让 PG 处理太多直连

这块在 PostgreSQL 连接池与 PgBouncer 那篇里有完整推演,这里只给核心结论:

PG 是进程模型,连接昂贵。每条连接一个 backend 进程,空连接都占 5~10MB。
应用侧用 ORM 自带的 Pool(Prisma / Drizzle / pg),池大小 10 左右就够,别贪大。
数据库前加一层 PgBouncer,用 transaction pooling 模式,把几百条应用连接复用到几十条 PG 连接上。

PG 性能调优到一定程度,连接管理是绕不开的下一站。直连打爆的案例我见过太多了,全是不上连接池的。

六、VACUUM/ANALYZE:MVCC 的代价你得认

PG 用 MVCC 实现并发——UPDATE/DELETE 不删除旧行,而是标记为"死 tuple"(dead tuple),留着给并发事务读。这些死 tuple 占磁盘、拖慢扫描,必须靠 VACUUM 回收。

-- 看每张表的死 tuple 比例
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY dead_ratio DESC;

dead_ratio 超过 10%~20%,这张表就该 VACUUM 了。

VACUUM 之外还有 ANALYZE——它不清理数据,只更新统计信息。优化器靠统计信息估算"这个条件大概命中多少行",估算错了,执行计划就跑偏(典型症状:明明有索引却选了 Seq Scan)。大批量 UPDATE/DELETE 之后必须 ANALYZE,否则后续查询计划基于过时统计。

生产上靠 autovacuum 自动跑,但默认参数偏保守(怕影响业务),经常跟不上写入速度。调几个关键值:

autovacuum = on
autovacuum_vacuum_scale_factor = 0.1    # 死 tuple 达到 10% 触发(默认 20%)
autovacuum_analyze_scale_factor = 0.05  # 变更达到 5% 重算统计
autovacuum_naptime = 30s                # 检查频率

对写入特别猛的大表,可以单独把这张表的触发阈值调低:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  toast.autovacuum_vacuum_scale_factor = 0.02
);

一个真实反例:曾经有张 1 亿行的日志表,autovacuum 跟不上,死 tuple 累积到 60%,顺序扫描代价翻倍,顺便把缓存污染了——一次手动 VACUUM FULL(注意锁表)清完,查询时间从 12 秒掉到 800ms。这种事别等线上发作,监控 pg_stat_user_tables.n_dead_tup 提前预警。

七、硬件:SSD 是底线,内存越大越好,别迷信 CPU

如果前面六层都做完了还慢,再考虑硬件:

存储:SSD 是底线。机械硬盘跑 PG 在 2025 年属于犯罪。具体地,随机读 IOPS 决定小查询延迟,顺序读写决定大扫描和 WAL。NVMe SSD 是当前最优解,云上选 Provisioned IOPS 或通用 SSD 要按峰值算够。
内存:越大越好,直到 shared_buffers 占 25% 之后边际递减。剩余内存交给 OS page cache,PG 高度依赖它。内存大的直接收益是缓存命中率高——一个"热数据全在内存"的库,延迟可以从几十毫秒压到 1ms 以内。
CPU:对排序、聚合、哈希有帮助,但 PG 单查询很难用满多核(并行查询默认不开,且对很多场景效果有限)。CPU 的价值更多在"能扛更多并发连接",而不是"单查询更快"。所以与其堆主频,不如关注连接数和查询本身。

一句话:硬件是 PG 性能的地基,但地基之上盖的房子烂,再好的地基也救不了。先调好前面六层,再考虑这一层。

八、系统方法:先定位,再优化,最后测

把前面串起来,这是一套可复用的排查流程:

pg_stat_statements 找出最耗时的查询。看 mean_exec_time 和 total_exec_time × calls,挑出真正的大头。
EXPLAIN (ANALYZE, BUFFERS) 分析执行计划。看 Seq Scan、Sort 溢出、缓存命中率、估计行数偏差。
对症下药:缺索引 → 加索引(并验证用上了);查询烂 → 改写(列出列、消灭 N+1);配置保守 → 调 shared_buffers / work_mem;统计过期 → ANALYZE;死 tuple 堆积 → VACUUM;连接打爆 → 上 PgBouncer。
再测一遍。改完之后重新跑 EXPLAIN ANALYZE 和压测,确认真的有提升——没测量就别上线改动。
建立长期监控。pg_stat_statements 持续记录,慢查询日志(log_min_duration_statement = 200)抓新冒出来的慢查询,死 tuple 比例纳入告警。

这套流程的核心原则:每一次优化都要有测量、有假设、有验证。靠"感觉调一调"上线,既说不清是不是真有用,下次出问题也复盘不出来。

小结

80% 的 PG 性能问题不在硬件,而在查询、索引、配置。加机器是最后一步,不是第一步——先回答"慢在哪",再谈"怎么办"。
定位三件套:pg_stat_statements 找最慢的查询,EXPLAIN (ANALYZE, BUFFERS) 看执行计划,pg_stat_activity + 锁等待查阻塞。这三板斧能定位九成问题。
配置调优集中在四个参数:shared_buffers(25%)、effective_cache_size(75%)、work_mem(小心总量)、max_connections(别贪大)。默认值是给"能跑起来"准备的,生产必须改。
查询优化按 ROI 排序:加对索引(复合索引看列顺序、部分索引省空间)> 别 SELECT * > 消灭 N+1 > 改写烂子查询。每改完一次都用 EXPLAIN 验证。
MVCC 的代价要主动管:死 tuple 靠 VACUUM 回收、统计信息靠 ANALYZE 更新,autovacuum 参数对大表要单独调。监控 n_dead_tup,别等拖垮线上。
硬件:SSD 是底线、内存越大缓存命中率越高、CPU 主要为扛并发而非加速单查询。地基之上房子盖烂了,加地基没用。

一句话收尾:PostgreSQL 性能调优的核心不是某条黑魔法参数,而是"先定位、再优化、最后测"的工程纪律——大部分慢,都能靠一张索引、一条 EXPLAIN、一次 VACUUM 解决,用不着花钱加机器。