PostgreSQL 配置调优（postgresql.conf）

内存、连接、WAL、日志与并行查询的核心 GUC 起步值，附按机型规格的配置模板，以及先测量再调优的验证流程（PostgreSQL 18）

默认的 postgresql.conf 取值保守，是为了让 PostgreSQL 几乎在任何硬件上都能启动。这意味着默认值不适合专用服务器，但反过来也不存在一组放之四海皆准的"最佳配置"。下面的数值是 PostgreSQL 18 的起步值，来自常用经验法则：先应用，再针对自己的负载逐项验证。

先测量，再调优

没有基线就改 GUC，结果往往是把一个慢系统变成另一种慢系统。动手之前：

启用 pg_stat_statements（加入 shared_preload_libraries 并重启，然后 CREATE EXTENSION pg_stat_statements;），用它按总耗时和 shared_blks_read / shared_blks_hit 给查询排序；
记录当前的延迟、I/O 和 checkpoint 行为，保证之后每个改动都能归因；
每次只改一组参数，改完重新测量。

SELECT query, calls, total_exec_time, mean_exec_time,
       shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

如果 pg_stat_statements 显示热点查询是缺索引或在小表上全表扫描，任何内存参数都救不了它。测量指向哪里，就调哪里。

核心内存 GUC

shared_buffers

PostgreSQL 自己的 page cache，启动时从共享内存分配。修改需要重启。

ALTER SYSTEM SET shared_buffers = '4GB';

专用数据库服务器上常见的起步值是内存的 25%。流传的"25%，不要超过 40%"上限是经验之谈而非文档中的硬性限制——写入多的负载可能受益于更大的值，而热数据已经能放进 shared_buffers 的读多负载未必需要。想调大就用 pg_buffercache 命中率和端到端延迟验证，不要默认越大越好。

effective_cache_size

它不分配内存，只是告诉规划器 shared_buffers 加上 OS page cache 大概能缓存多少数据，主要影响 index scan 与 seq scan 之间的选择。reload 即生效。

ALTER SYSTEM SET effective_cache_size = '12GB';

合理的起步值是内存的 50–75%。设得远高于实际可缓存的内存会让规划器对 index scan 过于乐观，调整前后用执行计划对比确认。

work_mem

单个排序、hash 等操作的内存上限——一条复杂查询可能同时消耗好几份，再乘以并发连接数。它是全局调大时最容易导致 OOM 的参数。

ALTER SYSTEM SET work_mem = '16MB';

默认 4MB 会让很多排序和 hash 落盘；log_temp_files（见下文日志）可以直接告诉你是否正在发生。
重分析型用户按角色或会话单独调大，不要全局调：ALTER ROLE analytics SET work_mem = '256MB';
全局上限的经验值约为 内存 × 0.25 / max_connections，而且这已经假设每个连接同时只跑一个大操作。

maintenance_work_mem

供 VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY 等维护操作使用。

ALTER SYSTEM SET maintenance_work_mem = '1GB';

调大可以加快建索引和 vacuum。注意并行建索引和并行 vacuum 的每个 worker 会各自申请一份，总量可能是该值的好几倍。

连接数与连接池

ALTER SYSTEM SET max_connections = 200;

PostgreSQL 每个连接是一个独立的后端进程，占用数 MB 内存和调度开销，所以 max_connections 是预算而不是吞吐量旋钮。应用需要的并发超过数据库能承载的连接数时，正确做法是在前面放连接池，而不是无限调大这个值——PgBouncer 的取舍（包括 transaction 模式下哪些特性会失效）见 PostgreSQL 生产工具栈与高可用。

SELECT count(*), state FROM pg_stat_activity GROUP BY state;

idle in transaction 数量大说明应用长时间不提交事务，应该先在应用侧修复，而不是调连接数上限。max_connections 修改需要重启。

WAL 与 checkpoint

wal_compression              = on
max_wal_size                 = 4GB
min_wal_size                 = 1GB
checkpoint_timeout           = 15min
checkpoint_completion_target = 0.9

max_wal_size 太小会导致 checkpoint 频繁、I/O 抖动；太大则拉长崩溃恢复和 WAL 回放时间。写入量大的系统常见取值在 4GB 到 16GB 之间——根据自己测得的 WAL 产生速率（pg_stat_bgwriter、pg_stat_wal）来定，而不是照抄表格。wal_buffers 默认 -1（按 shared_buffers 自动计算），一般不需要显式设置。

规划器与 I/O 成本参数

random_page_cost           = 1.1     # SSD；默认值 4.0 是 HDD 时代的遗留
effective_io_concurrency   = 200     # SSD/NVMe 能支撑远高于默认值 16 的并发
jit                        = off     # JIT 利好长时间分析查询，OLTP 场景常常是负优化

默认的 random_page_cost = 4.0 假设随机读比顺序读贵四倍。在 SSD/NVMe 上这会让规划器避开本该使用的 index scan。1.1 是 SSD 上广泛使用的取值，但请用自己的查询跑 EXPLAIN (ANALYZE, BUFFERS) 确认，不要盲信任何固定数字。

default_statistics_target 默认 100，很少需要全局修改。个别倾斜严重的列按列调整：ALTER TABLE t ALTER COLUMN c SET STATISTICS 1000;，然后执行 ANALYZE t;。

jit 在 PostgreSQL 18 中默认开启。负载以短 OLTP 查询为主时，全局关掉是合理的起步选择；真正受益的报表查询可以在会话里单独开启。

修改配置的三种方式

-- 1. ALTER SYSTEM：写入 postgresql.auto.conf，重启后仍然保留
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

-- 2. 直接编辑 postgresql.conf，然后
--    sudo systemctl reload postgresql

-- 3. 会话级或事务级，用于一次性任务
SET work_mem = '256MB';
SET LOCAL work_mem = '256MB';   -- 仅当前事务

持久化修改优先用 ALTER SYSTEM：改动可审计（pg_settings 里 source 会指向 auto.conf），也不用维护手改的配置文件。撤销用 ALTER SYSTEM RESET name;。

不是所有参数 reload 都生效，改之前先查 context：

SELECT name, setting, context FROM pg_settings
WHERE context IN ('postmaster', 'superuser-backend')
ORDER BY name;
-- context = 'postmaster' 需要完整重启

常见需要重启的参数：shared_buffers、max_connections、shared_preload_libraries。

按机型规格的起步模板

以下模板面向 SSD/NVMe 存储上的专用 PostgreSQL 18 服务器，是配合上文测量流程验证的起步值，不是最终答案。

4 vCPU / 16 GB

shared_buffers                  = 4GB
effective_cache_size            = 12GB
maintenance_work_mem            = 1GB
work_mem                        = 16MB
max_connections                 = 100
wal_compression                 = on
max_wal_size                    = 4GB
random_page_cost                = 1.1
effective_io_concurrency        = 200
max_parallel_workers            = 4
max_parallel_workers_per_gather = 2

8 vCPU / 32 GB

shared_buffers                  = 8GB
effective_cache_size            = 24GB
maintenance_work_mem            = 2GB
work_mem                        = 32MB
max_connections                 = 200
wal_compression                 = on
max_wal_size                    = 8GB
random_page_cost                = 1.1
effective_io_concurrency        = 200
max_parallel_workers            = 6
max_parallel_workers_per_gather = 4

16 vCPU / 64 GB

shared_buffers                  = 16GB
effective_cache_size            = 48GB
maintenance_work_mem            = 4GB
work_mem                        = 64MB
max_connections                 = 500    # 前面需要挂连接池
wal_compression                 = on
max_wal_size                    = 16GB
random_page_cost                = 1.1
effective_io_concurrency        = 200
max_parallel_workers            = 12
max_parallel_workers_per_gather = 6

想要第二份参考，可以用 PGTune 按机器规格生成模板对照。

生产环境建议开启的日志

log_min_duration_statement  = '500ms'
log_checkpoints             = on
log_connections             = on
log_disconnections          = on
log_lock_waits              = on
log_temp_files              = 0
log_autovacuum_min_duration = 0
log_line_prefix             = '%t [%p] %u@%d %a '

log_temp_files = 0 记录每一次临时文件创建，是 work_mem 不够用的直接信号。log_autovacuum_min_duration = 0 让 autovacuum 行为可审计，配合 autovacuum 与表膨胀中的查询一起用。log_connections / log_disconnections 在连接池场景下开销很小，但短连接极多时日志量会很大，按需取舍。完整的监控体系见监控与日志。

并行查询

max_worker_processes            = 8    # 后台 worker 总数（含逻辑复制等）
max_parallel_workers            = 6    # 并行查询可用的 worker 总数
max_parallel_workers_per_gather = 4    # 单条查询单个节点能用几个
min_parallel_table_scan_size    = '8MB'

max_worker_processes 是所有后台 worker（并行查询、逻辑复制 apply worker、扩展）的全局预算，必须不小于 max_parallel_workers 加上复制和扩展的需求；它和 max_parallel_workers 一般都按 vCPU 数量来定。并行主要在大扫描上受益；min_parallel_table_scan_size 保持默认 8MB 或以上时，小型 OLTP 查询很少触发并行。

查看当前配置

-- 所有偏离默认值的参数及其来源
SELECT name, setting, unit, source
FROM pg_settings
WHERE source <> 'default'
ORDER BY name;

-- 当前值与启动时取值的差异（等待重启的改动）
SELECT name, setting, boot_val, pending_restart
FROM pg_settings
WHERE setting <> boot_val OR pending_restart;

pending_restart = true 表示 ALTER SYSTEM 或配置文件的修改要等重启才生效——下结论说"改了没用"之前先查这一列。

AI prompt：按硬件生成配置

为我的服务器生成 postgresql.conf

帮我起草一份 PostgreSQL 18 的 postgresql.conf 调优配置。

环境：
- 服务器：<vCPU 数> vCPU / <内存> GB
- 存储：<NVMe SSD / 云盘 / HDD>，预计 IOPS <X>
- OS：<Ubuntu 24.04 / RHEL 9>
- 负载：<OLTP / OLAP / 混合>，预计 <QPS>、<并发连接数>
- 已开启的功能：<逻辑复制 / pgvector / PostGIS / 分区表>

输出：
1. postgresql.conf 片段，每个非默认值带注释说明原因。
2. 等价的 ALTER SYSTEM SET 语句。
3. 系统层检查项：vm.swappiness、huge pages、ulimit。
4. 验证方案：上线前后对比哪些 pg_stat_statements / pg_stat_bgwriter / pg_stat_io 查询。

对 AI 的产出和本页模板采取同样的态度：它是待验证的假设，不是成品配置。