PostgreSQL 配置与性能优化基础

PostgreSQL 调优应从指标和业务负载出发，而不是复制一份“万能参数”。参数之间相互影响，错误地放大连接数或单查询内存，可能让高并发时的总内存远超机器容量。

查看当前配置#

1
SHOW config_file;
2
SHOW data_directory;
3
SHOW max_connections;
4
SHOW shared_buffers;
5
SHOW work_mem;
6
SHOW maintenance_work_mem;
7
SHOW effective_cache_size;

查看参数来源：

1
SELECT name, setting, unit, source, pending_restart
2
FROM pg_settings
3
WHERE name IN (
4
  'max_connections',
5
  'shared_buffers',
6
  'work_mem',
7
  'maintenance_work_mem',
8
  'effective_cache_size'
9
);

连接数#

max_connections 不是吞吐量旋钮。每个连接都有资源成本，过多活跃连接会增加上下文切换和内存压力。

优先考虑：

应用端设置连接池上下限；
为连接和查询设置超时；
使用 PgBouncer 等连接池代理处理大量短连接；
查看真实活跃连接，而不是只看总连接数。

1
SELECT state, count(*)
2
FROM pg_stat_activity
3
GROUP BY state
4
ORDER BY state;

内存参数#

shared_buffers：PostgreSQL 共享缓存，不应占满系统内存。
work_mem：每个排序或哈希操作可能使用的内存，不是每个连接只分配一次。
maintenance_work_mem：VACUUM、CREATE INDEX 等维护操作可用内存。
effective_cache_size：规划器对系统缓存可用量的估计，不会直接分配内存。

调整 work_mem 前先查看执行计划中的磁盘排序，并按并发峰值估算总量。

慢查询日志#

1
log_min_duration_statement = 500ms
2
log_line_prefix = '%m [%p] %u@%d %r '

更精细的统计可以使用 pg_stat_statements：

1
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
2

3
SELECT
4
  calls,
5
  total_exec_time,
6
  mean_exec_time,
7
  rows,
8
  query
9
FROM pg_stat_statements
10
ORDER BY total_exec_time DESC
11
LIMIT 20;

总耗时高的高频 SQL 和单次耗时极高的 SQL 都值得关注。

自动清理与统计#

1
SELECT
2
  relname,
3
  n_live_tup,
4
  n_dead_tup,
5
  last_autovacuum,
6
  last_autoanalyze
7
FROM pg_stat_user_tables
8
ORDER BY n_dead_tup DESC;

自动清理长期跟不上时，应先分析写入模式、长事务、表规模和实际触发频率，再针对单表调整：

1
ALTER TABLE events SET (
2
  autovacuum_vacuum_scale_factor = 0.05,
3
  autovacuum_analyze_scale_factor = 0.02
4
);

不要轻易关闭 autovacuum。它不仅清理死元组，也关系到事务 ID 回卷安全。

WAL 与检查点#

频繁检查点可能带来 I/O 抖动。观察日志和统计：

1
SELECT * FROM pg_stat_bgwriter;
2
SELECT * FROM pg_stat_checkpointer;

具体可用视图取决于 PostgreSQL 版本。调整 max_wal_size、checkpoint_timeout 等参数前，应了解恢复时间、磁盘容量和复制需求。

长事务与锁#

1
SELECT pid, usename, state, xact_start, query_start, wait_event_type, wait_event, query
2
FROM pg_stat_activity
3
WHERE xact_start IS NOT NULL
4
ORDER BY xact_start;

查看阻塞关系：

1
SELECT
2
  blocked.pid AS blocked_pid,
3
  blocking.pid AS blocking_pid,
4
  blocked.query AS blocked_query,
5
  blocking.query AS blocking_query
6
FROM pg_stat_activity blocked
7
JOIN pg_stat_activity blocking
8
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

优化前先解决异常长事务、空闲事务和锁等待，它们可能让 VACUUM、DDL 和正常请求都受影响。

调优原则#

建立 CPU、内存、磁盘、连接、延迟和错误率基线。
从最耗时 SQL 和等待事件定位瓶颈。
一次只改变少量参数。
记录修改前后指标和执行计划。
在接近生产数据规模和并发下验证。
保留回滚值，并确认哪些参数需要重启。

开发动态