PostgreSQL 调优

摘要：PG 调优抓四个点：shared_buffers 缓存、work_mem 排序内存、连接管理（配合连接池）、autovacuum 清理。本文给出参数取值建议与用系统视图验证的方法。

适用环境

bash

free -g; nproc
psql -U postgres -c "SHOW shared_buffers; SHOW work_mem; SHOW max_connections; SHOW autovacuum;"
psql -U postgres -c "SELECT pg_size_pretty(pg_database_size('shopdb'));"

操作步骤

1. shared_buffers：PG 自己的缓存

ini

# postgresql.conf
shared_buffers = 4GB              # 独占机器建议物理内存的 25%，不建议超过 40%
effective_cache_size = 12GB       # 供优化器估算 OS 缓存，设为内存的 50%~75%

sql

-- 用扩展查看缓存命中率（应接近 99%）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS hit_ratio
FROM pg_statio_user_tables;

2. work_mem：排序与哈希内存（最容易踩坑）

ini

work_mem = 16MB                   # 单个排序操作可用内存
maintenance_work_mem = 1GB        # VACUUM / CREATE INDEX 使用

危险

work_mem 是每个排序操作的上限，一条 SQL 可能有多个排序、一个连接可能并行多个排序。work_mem × 并发排序数 会成倍吃内存，盲目调大会导致 OOM。

sql

-- 找出消耗最高的 SQL（需先启用 pg_stat_statements）
SELECT substring(query, 1, 80) AS q, calls, round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS avg_ms
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

3. 连接管理

ini

max_connections = 200             # PG 每连接开销较大，不要设很大

sql

-- 观察连接使用与空闲情况
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction';

高并发场景应在应用与 PG 之间放 PgBouncer 等连接池，而不是把 max_connections 调到上千。

4. autovacuum：防止表膨胀

ini

autovacuum = on
autovacuum_max_workers = 4
autovacuum_vacuum_scale_factor = 0.05      # 大表建议调小，例如 0.01
autovacuum_vacuum_cost_limit = 2000

sql

-- 查看死元组比例与上次 vacuum 时间
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / GREATEST(n_live_tup,1), 3) AS dead_ratio,
       last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- 对膨胀严重的表手动处理
VACUUM (VERBOSE, ANALYZE) orders;

5. 让配置生效

sql

SELECT pg_reload_conf();          -- 部分参数在线生效

bash

sudo systemctl restart postgresql  # shared_buffers 等需重启

验证

bash

# 用 pgbench 压测对比
pgbench -i -s 50 shopdb            # 初始化
pgbench -c 20 -j 4 -T 60 shopdb    # 20 并发跑 60 秒，记录 tps

sql

-- 压测后复查缓存命中率与 top SQL
SELECT sum(heap_blks_hit)/(sum(heap_blks_hit)+sum(heap_blks_read)) FROM pg_statio_user_tables;

常见坑

WARNING

shared_buffers 过大并不总是更好，PG 依赖操作系统页缓存做二级缓存，两者要留出平衡。

WARNING

长时间不 vacuum 会导致表膨胀和事务 ID 回卷（严重时数据库拒绝写入）；监控 n_dead_tup 与最老事务年龄。

DANGER

idle in transaction 的连接会阻塞 vacuum 清理死元组，长期存在会造成表持续膨胀；应用侧必须设置语句/事务超时。

PostgreSQL 调优 ​

适用环境 ​

操作步骤 ​

验证 ​

常见坑 ​

参考资料 ​

PostgreSQL 调优

适用环境

操作步骤

验证

常见坑

参考资料