技术小册

主题

性能调优：先定位等待，再动参数 ​

一句话：参数只能放大或限制已有计划的质量；先找到慢在扫描、排序、锁还是连接，再改对应的那一项。

适用 PostgreSQL 16/17。默认值以官方文档为准；改参数前先记录基线。

为什么需要 ​

PostgreSQL 的默认配置偏保守：shared_buffers 只有 128MB，work_mem 只有 4MB，max_connections 只有 100。这些默认值是为了让数据库在任何机器上都能启动，不是为生产负载调的。

但调参不是越多越好。work_mem 是每个排序/哈希操作的上限，调大后并发一高就可能 OOM；shared_buffers 超过物理内存的合理比例，会挤占页缓存。真正有效的顺序是：先看慢 SQL 和等待事件，再决定改哪个参数。

核心机制 ​

四层内存 ​

  • shared_buffers：PostgreSQL 自己的共享缓存，所有连接共享。默认 128MB（PostgreSQL 16/17），常见起点是物理内存的 25%。
  • effective_cache_size：告诉优化器「操作系统页缓存大概有多大」，不分配内存，只影响代价估算。默认 4GB。
  • work_mem：每个排序、哈希、去重操作的上限，不是每个查询。默认 4MB。一个查询有 3 个 Sort/Hash，就可能占 3 份。
  • maintenance_work_mem：VACUUM、CREATE INDEX、ALTER TABLE 等维护操作的内存。默认 64MB。

代价模型 ​

优化器用 seq_page_cost=1.0、random_page_cost=4.0、cpu_tuple_cost=0.01 估算计划成本。SSD 的随机读远没有 HDD 那么贵，random_page_cost 默认 4.0 会让优化器低估索引扫描、偏向全表扫描。常见做法是调到 1.1–1.5，然后用 EXPLAIN 对比计划，不要一次改多个参数。

连接模型 ​

PostgreSQL 每个连接一个后端进程。连接越多，内存和进程切换成本越高。生产通常用 PgBouncer 的 pool_mode=transaction，把大量应用连接复用到少量数据库连接。

每连接内存没有固定值。不要用「4–8MB」或「约 10MB」这类口径做容量规划；在目标会话里执行：

sql
-- PostgreSQL 14+；在要测量的会话中执行
SELECT sum(total_bytes) / 1024 / 1024 AS mb
FROM pg_backend_memory_contexts;

先跑一段典型查询，再测当前后端的内存上下文，得到的值才有意义。

参数表 ​

参数=默认值（版本）设置命令影响面
shared_buffers=128MB（16/17）ALTER SYSTEM SET shared_buffers='8GB'; 重启共享缓存；过大会挤占 OS 页缓存
effective_cache_size=4GB（16/17）ALTER SYSTEM SET effective_cache_size='24GB'; 重载只影响优化器估算，不分配内存
work_mem=4MB（16/17）SET work_mem='16MB';每个 Sort/Hash 的上限；按并发数估算总内存
maintenance_work_mem=64MB（16/17）SET maintenance_work_mem='1GB';VACUUM/建索引；只影响当前会话
max_connections=100（16/17）ALTER SYSTEM SET max_connections=300; 重启连接上限；每连接都有内存成本
random_page_cost=4.0（16/17）SET random_page_cost=1.1;SSD 上偏高会让优化器偏向全表扫描
seq_page_cost=1.0（16/17）通常不改顺序读的基准成本
wal_buffers=-1（16/17，自动）ALTER SYSTEM SET wal_buffers='16MB'; 重启WAL 缓冲；高写入可适当加大
checkpoint_timeout=5min（16/17）ALTER SYSTEM SET checkpoint_timeout='15min'; 重载检查点间隔；拉长可减少刷盘抖动，但恢复变慢
max_wal_size=1GB（16/17）ALTER SYSTEM SET max_wal_size='8GB'; 重载触发检查点的 WAL 上限；越大检查点越少
autovacuum_max_workers=3（16/17）ALTER SYSTEM SET autovacuum_max_workers=6; 重启并发 autovacuum 数；大表多时调大
autovacuum_vacuum_scale_factor=0.2（16/17）ALTER TABLE big SET (autovacuum_vacuum_scale_factor=0.01);死元组触发比例；大表按表调小
log_min_duration_statement=-1（16/17）ALTER SYSTEM SET log_min_duration_statement=1000; 重载记录超过 1 秒的 SQL；默认关闭
track_io_timing=off（16/17）ALTER SYSTEM SET track_io_timing=on; 重载让 EXPLAIN (BUFFERS) 显示 IO 时间；有少量开销
jit=on（16/17）SET jit=off;短查询可能因编译开销变慢；长分析查询通常受益
max_parallel_workers_per_gather=2（16/17）SET max_parallel_workers_per_gather=4;单条查询的并行 worker；小查询不要开

改完用 SELECT pg_reload_conf(); 让 SIGHUP 类参数生效；shared_buffers、max_connections、wal_buffers、autovacuum_max_workers 需要重启。

最小上手 ​

sql
-- 需要 shared_preload_libraries='pg_stat_statements' 并重启
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 总耗时最高的 SQL（PostgreSQL 13+ 用 total_exec_time）
SELECT queryid,
       calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       round(100.0 * shared_blks_hit
             / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS hit_pct,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
sql
-- 当前活跃会话与等待事件
SELECT pid,
       now() - xact_start AS xact_age,
       wait_event_type,
       wait_event,
       state,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY xact_age DESC
LIMIT 20;
sql
-- 死元组比例与最近一次 autovacuum
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup
             / GREATEST(n_live_tup + n_dead_tup, 1), 2) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

PgBouncer 的最小配置：

ini
[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 50
reserve_pool_size = 10
server_idle_timeout = 600

transaction 模式下，会话级特性要小心：SET、LISTEN/NOTIFY、会话级 advisory lock、部分 prepared statement 用法可能在连接复用后失效。需要跨语句保持状态时，用事务级机制或单独的 session 池。

生产动作 ​

按「现象 → 命令/指标 → 判断」排查：

  • CPU 高、查询慢：pg_stat_statements 找 total_exec_time 最高的 SQL → EXPLAIN (ANALYZE, BUFFERS) → 看 Seq Scan、Rows Removed by Filter、连接方式。
  • 排序慢：计划里出现 Sort Method: external merge → work_mem 不够或并发太高 → 先按查询调 SET LOCAL work_mem，不要全局拉满。
  • 缓存命中低：Buffers: shared read 很多、hit_pct 下降 → 数据量超过内存或冷数据多 → 评估 shared_buffers、数据分区、只读副本。
  • 表/索引膨胀：n_dead_tup/n_live_tup 超过 20%、last_autovacuum 很久以前 → autovacuum 追不上 → 按表调小 scale_factor、增加 worker，必要时 pg_repack（不要高峰做 VACUUM FULL，它拿 ACCESS EXCLUSIVE）。
  • 连接打满：pg_stat_activity 按 state 计数接近 max_connections → 连接泄漏或池配置错误 → 修池，不要只调大 max_connections。
  • 锁等待：wait_event_type='Lock'、pg_blocking_pids(pid) 非空 → 看 12 篇；先找阻塞者，不要直接 pg_terminate_backend。
  • 事务 ID 回卷余量：SELECT age(datfrozenxid) FROM pg_database; 接近 autovacuum_freeze_max_age=200000000 → 立即安排 VACUUM FREEZE，不要等只读保护。
  • 参数生效方式：ALTER SYSTEM 写入 postgresql.auto.conf；SIGHUP 类用 SELECT pg_reload_conf();，postmaster 类必须重启。改完用 SHOW 参数名; 确认。

边界 ​

  • 调参不能修复坏 SQL/坏索引：先看计划，再改参数；work_mem 再大也救不了全表扫描。
  • work_mem 不是查询级：一个查询多个 Sort/Hash 各占一份，并发 100、每个 3 个 Sort、work_mem=64MB，峰值可能到 19GB。
  • shared_buffers 不是越大越好：超过物理内存的合理比例会挤占 OS 页缓存；还要给连接、维护操作留内存。
  • effective_cache_size 不分配内存：它只是优化器的假设；设得离谱会让计划判断失真。
  • random_page_cost 依赖存储：HDD 和 SSD、云盘和本地 NVMe 差异很大；改之前先跑基准，改之后看计划。
  • 不要关闭 autovacuum：它同时负责死元组清理和事务 ID 冻结；关掉只是把问题推迟到更严重。
  • PgBouncer 事务模式不是透明代理：会话状态、advisory lock、prepared statement 都可能有兼容问题；上线前用真实驱动压测。

讲清楚 ​

PostgreSQL 调参的核心是分清四层内存：shared_buffers 是共享缓存，effective_cache_size 只是给优化器的假设，work_mem 是每个排序/哈希操作的上限，maintenance_work_mem 管 VACUUM 和建索引。最常见的错误是把 work_mem 当成查询级、把 effective_cache_size 当成实际分配，或者用固定值估算每连接内存。正确顺序是先用 pg_stat_statements 和 pg_stat_activity 找到慢 SQL 与等待事件，再看 EXPLAIN (ANALYZE, BUFFERS) 判断是扫描、排序、锁还是连接问题，最后才改对应参数。连接数要靠池解决，表膨胀要靠 autovacuum 和 pg_repack 解决，事务 ID 回卷要靠 age(datfrozenxid) 提前监控。参数是放大器，不是修复器。

把一门技术讲透到既懂也会用

技术小册 · 持续更新