配置与资源:每个参数对应一个机制

5.1配置心法:别抄神奇配置

调一个参数前,先说出它影响的是优化器、内存、WAL 还是 VACUUM 哪个机制——说不出来就别调。

这张图是本章的骨架:左边是可调参数,右边是前四章讲过的机制。调任何一个参数,都先在图上找到它指向的那个机制,问一句"这是要影响这个机制的什么行为"。

5.2内存四件套

四个内存参数喂四个不同的机制:页缓存、单节点排序/哈希、维护操作、优化器对缓存的认知。

把"内存"当成一个旋钮去调,是最常见的误区。PostgreSQL 的内存分四块,各喂一个机制,调错地方既不提速,还会把内存推向爆掉。逐个拆开:

shared_buffers — PostgreSQL 自己的页缓存

shared_buffers 是 PostgreSQL 在共享内存里维护的页缓存,所有后端进程共用。一个数据页被读进来后留在这里,下次命中就不必再碰磁盘。这正是 01 章 Buffers: shared hit 里 hit 的来源:hit 多说明页在 shared_buffers 命中,read 多说明缓存没接住、落到了磁盘。默认 128MB 对生产机偏小;常见起点是物理内存的 25%。再大未必更好——PostgreSQL 同时也依赖操作系统的页缓存(见 effective_cache_size),两层缓存不必都给满。

work_mem — 每个排序 / 哈希 / 位图节点的内存

work_mem 是 02 章 hash join / sort 节点能用的内存上限。一个 Hash Join 要把小表建成哈希表,如果哈希表装不进 work_mem,就会落盘——执行计划里出现 external merge Disk 或 Batches: > 1,速度断崖式下跌。默认 4MB,排序稍大的结果集就溢出。

maintenance_work_mem — 维护操作的工作区

maintenance_work_mem 是 04 章 VACUUM、CREATE INDEX、ALTER TABLE 等维护操作的工作区。VACUUM 用它缓存待回收的 dead tuple 标识,越大则一遍扫过的死元组越多、大表 VACUUM 越快。默认 64MB。它只在维护时占用,且同一时刻这类操作通常不多,所以可以比 work_mem 大方得多。

effective_cache_size — 给优化器的"缓存有多大"提示

effective_cache_size 是四件套里唯一不分配任何内存的:它只是告诉优化器"操作系统页缓存 + shared_buffers 加起来大约有多少可用",作为 02 章代价估算的输入。这个数越大,优化器越相信"重复的索引扫描多半会命中缓存",从而更愿意选索引扫描而非顺序扫描。它不影响实际内存占用,设错了只会让优化器估价偏移,不会 OOM。常设为物理内存的 50–75%。

5.3WAL 与 checkpoint

WAL 先写日志再写数据页,保证崩溃可恢复;checkpoint 周期性把脏页刷盘——调参的目标是把这次刷盘从尖峰摊成平缓。

痛点:checkpoint 一触发,大量脏页要在短时间内集中写盘,造成 I/O 尖峰——磁盘被打满,期间所有查询都变慢。这对应 01 章 wait events 里的 LWLock(等 WAL / 缓冲区相关的内部锁)。两个参数决定 checkpoint 多频繁、多集中:

• max_wal_size(默认 1GB):WAL 累积到这个量会强制触发 checkpoint。设太小,WAL 很快写满,checkpoint 被频繁逼出,刷盘尖峰一个接一个。调大它(如 4GB)能让 checkpoint 主要由 checkpoint_timeout(默认 5 分钟)按时间从容触发,而不是被 WAL 体积逼着仓促刷。
• checkpoint_completion_target:把一次 checkpoint 的刷盘工作摊到整个周期的多大比例里完成。设 0.9 表示用 90% 的周期慢慢刷,而不是一上来全力写——尖峰被摊平成平缓的斜坡。

5.4代价参数与 I/O

random_page_cost 直接改优化器顺序扫描 vs 索引扫描的切换点;effective_io_concurrency 控制预取并发——两者都把硬件特性喂给优化器。

这一节的参数不分配内存,而是校准 02 章的代价模型,让那套抽象单位贴近你的真实硬件。

random_page_cost — 最值得改的代价参数

random_page_cost 默认 4.0,含义是"随机读一页的代价约等于顺序读一页(seq_page_cost=1.0)的 4 倍"。这个 4 倍是机械盘假设——磁头寻道使随机读远慢于顺序读。但 SSD 和云盘几乎没有寻道代价,随机读和顺序读相差无几,此时 4.0 让优化器系统性高估索引扫描(随机读)的代价,本该走索引却退回顺序扫描。SSD/云盘应把它降到约 1.1。这正是 02 章那条"顺序扫描 vs 索引扫描切换点"背后的旋钮:降低 random_page_cost,切换点左移,优化器更早地愿意走索引。

# 机械盘默认:随机读贵 4 倍
random_page_cost = 4.0
seq_page_cost    = 1.0

# SSD / 云盘:随机读和顺序读差别很小
random_page_cost = 1.1
seq_page_cost    = 1.0

effective_io_concurrency — 预取并发

effective_io_concurrency 控制 PostgreSQL 一次能并发发起多少个磁盘预取请求,主要作用于 Bitmap Heap Scan 这类需要按位图回表读多页的场景:并发预取多页,而不是读一页等一页。

jit — JIT 编译

jit 控制是否对代价超过 jit_above_cost(默认 100000)的查询做即时编译,把表达式求值编译成机器码。它对扫描海量行的分析型查询有收益,但 JIT 编译本身有固定开销。

5.5连接管理与连接池

每个连接是一个独立后端进程,有固定内存开销且会放大 work_mem;答案是连接池复用少量连接,不是堆 max_connections。

痛点:遇到"连接不够用"就把 max_connections(默认 100)调到几千,不是答案。几千个后端进程带来沉重的调度开销,还把 work_mem 的放大系数推到内存撑不住的地步——5.6 的挑战题就是这个事故的算术。

解法:用连接池(典型是 PgBouncer)的 transaction 模式。应用连到池子,池子只对后端维持一小撮真实连接(如几十个),在事务粒度上把它们复用给成百上千的应用连接。后端进程数被压到很小,work_mem 的放大系数随之可控。

5.6一份起点配置(收拢全章)

给一台 16GB、OLTP 负载的机器一组起点值——每个值都标出它对应前面哪个机制。

把全章收拢成一份可抄的起点配置。注意右列:每个数字旁边都写着它牵动哪个机制、出自哪一章。不带这一列的配置就是 5.1 警告过的"神奇配置"。

# 内存四件套
shared_buffers = 4GB                  # 物理内存 25%,页缓存
effective_cache_size = 12GB           # ~75%,只是给优化器的提示
work_mem = 32MB                       # per-node per-connection,留意放大
maintenance_work_mem = 1GB            # VACUUM / CREATE INDEX

# 代价参数(SSD / 云盘)
random_page_cost = 1.1                # 默认 4.0 是机械盘假设
# effective_io_concurrency = 16       # PG18 默认已是 16

# WAL / checkpoint
max_wal_size = 4GB                    # 减少 checkpoint 尖峰
checkpoint_completion_target = 0.9    # PG14 起默认,摊平刷盘

# 连接:不堆大,前面放 PgBouncer
max_connections = 100