27 精益求精：参数调优与资源治理

调优不是把一份“最佳参数”复制到 postgresql.conf。

同一个参数变化可能：

让一条查询更快
  但让十条并发查询触发 OOM

减少 WAL
  但增加 primary CPU 和 replica replay CPU

提高单条分析查询速度
  但占满 parallel worker，让 OLTP tail latency 变差

增加连接槽
  但把 pool 中可控的队列搬进数据库

在 benchmark 中提高 TPS
  但降低 durability、HA headroom 或 recovery predictability

本章用一条严格的推理链替代参数清单：

service objective
  -> observed bottleneck or resource risk
      -> parameter mechanism
          -> scope and precedence
              -> one-factor experiment
                  -> benefit + non-regression + failure behavior
                      -> rollback
                          -> ADR

如果中间缺了一环，默认动作不是“先改了看看”，而是补证据。

参数不是独立旋钮

PostgreSQL 参数形成资源系统：

shared_buffers
  <-> OS page cache
  <-> checkpoint dirty-page work
  <-> max_wal_size

work_mem
  × active operations
  × sessions
  × parallel processes
  × hash_mem_multiplier

max_connections
  <-> backend/shared memory
  <-> pool queue
  <-> lock/context-switch pressure

parallel workers
  <-> CPU
  <-> work_mem
  <-> worker pool
  <-> other queries and maintenance

checkpoint / WAL
  <-> write smoothing
  <-> WAL volume
  <-> archive/replica
  <-> crash recovery time

所以本章不按字母解释 GUC，而按资源预算和失效机制组织。

本章实验故意得到“拒绝改参”

第 26 章参考 run 提供了几个事实：

• c8 时 server work ratio 约 84%，client 未先饱和；
• workload 使用 prepared protocol；
• 六个 cell 的 temp bytes 全为零；
• L 档有 block read，但 server iowait 很低；
• 只有 c1/c8，精确 knee 未知；
• production sustainable TPS 仍为 null。

这些事实不支持：

increase work_mem
increase shared_buffers
increase max_connections
disable synchronous_commit

它们只使一个假设值得被证伪：

prepared OLTP 在 plan_cache_mode=auto 下是否仍承担了足够的 custom planning 成本，使 force_generic_plan 获得至少 2% 的稳定收益？

正式实验只改变这个参数，而且只用 PGOPTIONS 改 benchmark session：

baseline   plan_cache_mode=auto
candidate  plan_cache_mode=force_generic_plan

固定 M 数据、8 clients、prepared、50/30/20 mix；五组 paired repetition 使用相同 seed，A/B 顺序交错。共 10 次 12 秒 measured run、357,685 笔事务：

只看两个 median，candidate 好像快 2.35%。但正确的配对分析是：

paired TPS ratio median       1.00735
bootstrap 95% interval        [0.98082, 1.02775]
candidate / baseline p95      0.99110
required bootstrap lower      >= 1.02

candidate 没有证明至少 2% 的稳定收益。plan probe 还发现：

auto:
  first 5 custom + next 5 generic

force_generic_plan:
  10 generic + 0 custom

PostgreSQL 的 auto 已经为这两类稳定 plan 自动转向 generic。强制 generic 只省掉 少量早期 planning，却可能伤害参数敏感查询。最终 ADR：

decision                    reject-persistent-change
persistent change applied   false
production gate             pending

这不是“没有调优成果”。它避免了一项没有稳定收益、却扩大 plan risk 的持久变更。

公共结果见 tuning-run.json，完整安全边界见 lab-contract.md。

本章学习成果

完成本章后，你应该能：

1. 从 SLO、bottleneck 和 non-regression 指标写调优假设；
2. 区分参数、SQL/schema、workload 与 topology 问题；
3. 为 shared memory、per-operation memory、maintenance 与 OS 留出完整预算；
4. 解释 work_mem × 节点 × 并发 × parallel process 的放大；
5. 用 WAL、checkpointer、I/O 与 recovery evidence 调整 checkpoint；
6. 区分 effective_cache_size estimate 与真实 cache allocation；
7. 校准 cost parameter，而不是用 enable_seqscan=off 长期逼 planner；
8. 计算 parallel worker 的 cluster budget 和降级行为；
9. 把 connection limit 与 pool/admission、reserved slot 和 emergency access 联动；
10. 从 pg_settings.context/source/pending_restart 判断变更方式；
11. 从 pg_file_settings 找出 syntax error 与被后项覆盖的配置；
12. 理解 system、database、role、role-in-database、session、transaction 的覆盖关系；
13. 用 Pigsty template、pg_parameters 与 IaC 保持 desired state；
14. 在 reload/restart/rolling change 前写 failure、rollback 与 validation；
15. 接受“拒绝修改”也是合格 ADR。

本章目录

27.1 调优是一套实验方法

• 27.1.1 先定义目标、瓶颈和不可退化指标
• 27.1.2 一次改变一个机制并准备回退
• 27.1.3 参数变化不修复错误 SQL 和错误模型

27.2 内存预算

• 27.2.1 shared buffers、操作系统页缓存与双重缓存
• 27.2.2 work_mem 按节点、并发和并行放大
• 27.2.3 maintenance、autovacuum 与后台进程内存
• 27.2.4 OOM 风险必须用最坏并发估算

27.3 WAL、检查点与写入平滑

• 27.3.1 WAL 生成、刷盘与提交延迟
• 27.3.2 检查点频率、写突发与恢复时间
• 27.3.3 压缩、全页写与归档代价

27.4 规划器、并行与连接参数

• 27.4.1 成本参数只能用硬件和计划证据校准
• 27.4.2 并行 worker 的全局预算与退化条件
• 27.4.3 连接上限、超时和锁等待边界

27.5 参数作用域与变更方式

• 27.5.1 编译、初始化、启动、reload 与会话级
• 27.5.2 系统、数据库、角色与事务覆盖层
• 27.5.3 配置漂移、审计、回退和滚动风险

27.6 模板参数与集群变更

• 27.6.1 从模板生成实例配置
• 27.6.2 区分 reload、restart 与滚动执行
• 27.6.3 用 SQL 和文件事实验证最终生效值

27.7 实战：只调一个已证实的瓶颈

• 27.7.1 从 ch26 证据提出参数假设
• 27.7.2 比较收益、副作用和故障恢复
• 27.7.3 输出参数 ADR、回退条件与拒绝修改项

阅读路线

应用开发者：

27.1 -> 27.2.2 -> 27.4 -> 27.5.2 -> 27.7

重点是 per-query memory、plan、timeout、role/database scope 和 A/B。

平台工程师：

27.1 -> 27.2 -> 27.3 -> 27.5 -> 27.6 -> 27.7

重点是 cluster resource budget、WAL/recovery、配置来源、IaC 与 rolling risk。

两条路线必须合流：应用知道 transaction 与 query shape，平台知道 failure domain 与 global resource envelope；任何一方单独调参都容易优化局部、破坏整体。

实验文件

static/labs/ch27/
├── requirements.json
├── parameter-candidates.json
├── change-contract.json
├── negative-cases.json
├── topology.mmd
├── lab-contract.md
├── setup.sql
├── reset-run.sql
├── read-product.sql
├── read-order.sql
├── place-order.sql
├── plan-probe-counts.sql
├── plan-probe-product.sql
├── plan-probe-order.sql
├── remote_experiment.py
├── capture.py
├── exercise.py
├── validate.py
├── review.py
├── task.sh
└── tuning-run.json

实验：

• 只测试一个参数；
• 只在 benchmark session 生效；
• 不使用 ALTER SYSTEM、DCS edit、reload/restart；
• 保留 raw transaction log 与 plan-shape evidence；
• 用 28 个对抗变体验证 target、scope、配对、quantile、decision 和 cleanup；
• 专用 database、role 与远端临时目录全部清理；
• 不管 candidate 接受或拒绝，production gate 都保持 pending。

参考资料

• PostgreSQL 18 server configuration
• PostgreSQL 18 setting parameters
• PostgreSQL 18 resource consumption
• PostgreSQL 18 WAL configuration
• PostgreSQL 18 query planning
• PostgreSQL 18 pg_settings
• PostgreSQL 18 pg_file_settings
• Pigsty parameter optimization policy
• Pigsty PostgreSQL parameter scopes

上一章：胸有成竹：容量规划与压测基线 · 返回下卷导读 · 下一章：除旧布新：VACUUM、冻结与膨胀治理 · 查看全书目录 · 查看索引中心

27.1 调优是一套实验方法

参数调优回答的不是：

这个参数设多大最好？

而是：

在哪一个 workload、SLO、硬件、版本和失效状态下，改变哪个机制，能以可接受的 副作用改善哪一个目标？

这句话里每一项都不能省。

一个参数没有脱离上下文的最优值：

work_mem=256MB

对一条串行分析查询可能合理；对 300 个并发 backend、每条 plan 有多个 hash/sort、 每条还启动 4 个 parallel worker 的系统，可能是 OOM 设计。

调优因此和第 26 章容量实验使用同一套科学方法，只把 factor 从 workload/load 换成 GUC：

question
  -> mechanism hypothesis
      -> baseline
          -> one controlled change
              -> repeated measurement
                  -> effect + uncertainty
                      -> non-regression
                          -> accept / reject / inconclusive

27.1.1 先定义目标、瓶颈和不可退化指标

先写 objective function

“数据库变快”不可验收。把目标写成有 scope 的 metric：

workload: checkout-v7
traffic: 1800 offered requests/s
dataset: 1.2 TiB, tenant skew P95
objective:
  checkout_p95_ms: "<= 120"
  completion_ratio: ">= 99.95%"
constraints:
  replica_freshness_p95_s: "<= 2"
  archive_backlog_recovery_min: "<= 15"
  node_memory_available_gib: ">= 8"
  cpu_busy_p95: "<= 65%"
  durability: "local WAL flush before success"
failure_state:
  - normal
  - one_read_replica_lost

没有 constraints 的“优化”会把成本转移到别处。

metric 分四类

性能 A/B 必须同时校验结果正确。一次 hash join 因错误 filter 少处理 30% 行而“快了” 不是调优。

找 service center，不找最高指标

候选瓶颈：

CPU execution
planning/JIT
data read
WAL write/sync
lock and transaction queue
pool/admission queue
memory spill/reclaim
checkpoint/writeback
replica/archive backpressure
network/client

每个假设至少需要：

symptom
mechanism evidence
corroborating resource evidence
plausible counter-evidence

例如：

“CPU 84%”本身不是参数假设。它只说明 CPU 是候选 service center。还要问：

CPU 花在 executor、planner、JIT、compression、TLS、context switch，
还是 client/hypervisor 计费误差？

把 evidence 映射到 parameter mechanism

observed spill
  -> work_mem/hash_mem_multiplier candidate

requested checkpoints too frequent
  -> max_wal_size/checkpoint_timeout candidate

full-page-image WAL dominates
  -> checkpoint interval/wal_compression candidate

planner systematically misprices random I/O
  -> cost parameter candidate

worker launch starvation
  -> worker-pool budget candidate

反例：

slow query
  -> work_mem?

high CPU
  -> shared_buffers?

high connection count
  -> max_connections?

问号前缺少机制，不应进入变更。

不可退化指标要在实验前写

如果跑完才决定什么算 regression，人会自然挑对 candidate 有利的解释。预先写：

correctness equality
failure/timeout/skipped
p50/p95/p99/max
CPU and memory
temp and WAL bytes
lock waits/deadlocks
replica/archive lag
backup/maintenance duration
recovery behavior

某些指标是 hard gate：

wrong result             reject
durability changed       reject or separate product decision
OOM / restart            reject
unbounded WAL retention  reject
rollback unproven        reject

某些是 tradeoff：

batch 20% faster
OLTP p95 2% slower
CPU 8% higher

是否接受取决于预先声明的 objective 和 budget。

baseline 必须仍然存在

“改前数据是上个月 dashboard，改后是今天”不是 A/B。至少固定：

source/version
hardware/topology
data snapshot/generator
statistics
workload/arrival
connection path
background policy
measurement window

对于 restart 参数，不能同时：

升级 minor version
修改 kernel
换 storage
ANALYZE 全库
改参数

然后把差异归给参数。

27.1.2 一次改变一个机制并准备回退

one factor 不一定等于 one GUC

有些机制需要一组一致变化：

parallel worker budget:
  max_worker_processes
  max_parallel_workers
  max_parallel_workers_per_gather

它们可以作为一个 factor，但要明确：

• 为什么必须一起变化；
• 每项如何约束同一机制；
• 哪项是 hard upper bound；
• 如何整体回退。

相反，同时改：

work_mem
random_page_cost
max_connections
checkpoint_timeout

是四个机制，不能从结果归因。

优先用最小作用域证伪

PostgreSQL 的 parameter context 决定最小 scope。若参数允许 user context，先尝试：

BEGIN;
SET LOCAL work_mem = '32MB';

EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, FORMAT JSON)
SELECT ...;

ROLLBACK;

或为一次客户端进程：

env PGOPTIONS="-c plan_cache_mode=force_generic_plan" \
  pgbench ...

PostgreSQL 官方 Setting Parameters 说明，session SET 和 libpq PGOPTIONS 不影响其他 session。这使证伪成本和 blast radius 最小。

但“session 能设”不代表生产应由每个请求随便设。通过实验后仍要决定：

query/transaction
role-in-database
role
database
instance
cluster

哪个 scope 与 ownership 一致。

paired experiment

当环境噪声随时间变化，A/B 配对比“先跑十次 A，再跑十次 B”更可靠：

pair 1  A -> B, same seed/data
pair 2  B -> A, same seed/data
pair 3  A -> B
pair 4  B -> A

每对 effect：

$$ r_i

\frac{X_{B,i}}{X_{A,i}} $$

报告：

median(r_i)
bootstrap interval of median(r_i)

不是：

二者在配对噪声下可能不同。

最小显著收益

统计上可分辨不等于工程上值得。预声明：

minimum material gain  2%
measurement noise      assessed from repetition
tail regression budget <= 5%

若 candidate 提高 0.3%，却增加 configuration exception、plan risk 和 review burden， 通常不值得持久化。

第 27 章正式 run 的规则：

paired TPS ratio bootstrap 95% lower >= 1.02
candidate/baseline pooled p95 <= 1.05
plan shapes equal
failure/late/skipped = 0
global settings unchanged

结果 lower bound 0.9808，所以拒绝。

rollback 不是“把值改回去”

回退合同包含：

previous value and source
previous desired-state commit
change scope
whether reload/restart is needed
session/pool lifecycle
HA member order
data/WAL produced under candidate
stop condition
rollback validation

例子：把 synchronous_commit 改回 on，不会重新赋予此前已向客户端确认但尚未 flush 的 transaction durability。参数值能回退，历史语义不能。

增加 max_connections 后又减回去，若当前 session 已超过新上限，现有 session 不会 神奇消失；还要协调 pool 与 reconnect。

变更状态机

proposed
  -> static reviewed
      -> sandbox falsified
          -> canary
              -> observation window
                  -> accepted
                  -> rolled back
                  -> inconclusive

每一步有 evidence 和 owner。不要把“SQL 执行成功”当作 accepted。

stop condition

实验中发现以下任何一项立即停止：

• correctness mismatch；
• failed/late/skipped 越界；
• memory safety floor；
• replica/archive gap 无界增长；
• HA role/topology 变化；
• client saturation；
• config source/pending restart 异常；
• cleanup 不再可证明。

stop 后保留失败 evidence，不重新跑到“漂亮”为止。

27.1.3 参数变化不修复错误 SQL 和错误模型

参数是系统 policy，不是 query patch

慢查询的优先顺序通常是：

correctness and transaction semantics
  -> query shape
      -> schema/index/statistics
          -> workload/admission
              -> parameter policy
                  -> hardware/topology

不是所有问题都严格按此顺序解决，但越靠前的错误越不应由全局参数掩盖。

cardinality 错误

planner 估 10 行，实际 10,000,000 行，可能选择 nested loop。把：

enable_nestloop = off

设为全局只是在压制一个 plan type。更好的证据路径：

ANALYZE freshness
per-column statistics target
extended statistics
correlated predicates
parameter-sensitive plan
expression/cast mismatch

PostgreSQL Query Planning 也把 enable_* 描述为粗粒度临时影响手段，并优先建议统计与 cost calibration。

缺索引或错误索引

把 random_page_cost 调低不会创建缺失的 access path。它可能让 planner 更偏爱现有 索引，但：

• index key/order 不支持 predicate/order；
• expression/collation/type 不匹配；
• partial predicate 无法证明；
• selectivity 太低；
• index-only scan visibility 不足；

仍然存在。

无界查询

没有 pagination/limit、一次返回数百万行：

work_mem ↑
parallel workers ↑

可能让数据库更快地向网络和客户端制造巨大结果集，但没有修复 API contract。

长事务

idle_in_transaction_session_timeout 可以限制 idle transaction 伤害，却不应代替：

• 应用 transaction boundary；
• retry/timeout handling；
• connection return；
• cursor lifecycle；
• batch chunking。

timeout 是最后防线，触发时 transaction 被中断；它不是无副作用的清理器。

N+1 与 chatty workload

把 max_connections 从 100 提到 1,000，不会把 1,000 次 serial round trip 变成一条 set-based SQL。它只允许更多 N+1 同时进入。

数据模型不匹配

把大 JSON 文档塞进一行，再频繁修改一个字段，可能带来：

TOAST rewrite
WAL amplification
index expression cost
MVCC churn

wal_compression 或 checkpoint 参数只能改变代价的一部分，不能修复 update model。

durability 不是性能参数

以下变化会改变业务承诺：

fsync=off
full_page_writes=off
synchronous_commit=off
unlogged table

它们不能与普通性能参数放在同一 A/B 后，仅因 TPS 更高就接受。

synchronous_commit=off 在某些明确允许“数据库 crash 时丢失最近成功确认事务”的 业务中可以成为产品设计，但需要：

business owner
loss window
idempotent recovery
observability
failure drill

而不是 DBA 的秘密加速。

参数模板是起点

Pigsty 的 tiny、oltp、olap、crit profile 根据硬件和 workload 类别生成合理 起点；官方 parameter optimization policy 也按场景区分模板。模板不知道：

• 你的 query；
• tenant skew；
• SLO；
• batch overlap；
• failure budget；
• hardware/storage tail；
• 应用 pool 与 retry。

所以正确流程：

template baseline
  -> measure
      -> hypothesis
          -> scoped experiment
              -> ADR

不是：

internet parameter list
  -> production

一张问题分类表

本章后续所有参数都遵循这个约束：先解释机制和预算，再讲值。

返回本章目录 · 下一节：内存预算 · 查看全书目录 · 查看索引中心

27.2 内存预算

PostgreSQL 没有一个叫 total_memory_limit 的总开关。

内存来自多个 scope：

postmaster shared memory
backend private memory contexts
sort/hash/memoize operations
parallel workers
maintenance/autovacuum
logical decoding
temporary table buffers
extension/foreign data wrapper
kernel page cache
monitor/backup/agent

有的是启动时固定，有的是按需分配，有的是 per operation，有的是 per worker。内存 调优首先是预算模型，其次才是参数值。

27.2.1 shared buffers、操作系统页缓存与双重缓存

PostgreSQL 不是绕过 OS 的单一 cache

普通 buffered I/O 路径近似：

query
  -> PostgreSQL shared_buffers
      -> read()/write()
          -> kernel page cache
              -> filesystem/device

同一个 relation block 可能同时存在 shared buffers 和 OS page cache。它不是简单的 “浪费两份”：

• shared buffers 提供 PostgreSQL page、pin、lock、dirty 与 WAL 协调；
• OS cache 服务 filesystem read/write、readahead 和其他进程；
• checkpoint/background writer 把 dirty shared buffer 写给 kernel；
• kernel 决定何时 writeback 到设备；
• WAL 有自己的 buffer 与 flush 路径。

所以：

RAM - shared_buffers = OS cache

只是预算近似，不是内核保证。

shared_buffers 是启动参数

SELECT
    name,
    setting,
    unit,
    context,
    source,
    pending_restart
FROM pg_settings
WHERE name = 'shared_buffers';

参考沙箱：

setting          62592
unit             8kB
bytes            512,753,664
server RAM       2,048,679,936
ratio            about 25%
context          postmaster
source           configuration file

context=postmaster 表示需要 server restart 才能改变 effective value。reload 只能让 pending_restart=true，不能扩大正在运行的 shared memory。

PostgreSQL 官方 Resource Consumption 把 dedicated server 的约 25% 视为常见起点，并指出通常超过 RAM 40% 不太可能优于 较小值，因为系统仍依赖 OS cache。这是 starting heuristic，不是 capacity theorem。

为什么不是越大越好

增加 shared buffers 可能：

• 提高某些 working set 的 PostgreSQL cache residency；
• 减少 shared buffer miss；
• 增加启动 shared memory 与 page table；
• 压缩 kernel cache、backend private memory 和 agent headroom；
• 增加 checkpoint 要管理的 dirty buffer；
• 改变 writeback burst；
• 延长重启/预热行为；
• 在容器/cgroup limit 下增加 OOM 风险。

更大的 shared buffers 通常还要重新评估 max_wal_size 与 checkpoint write smoothing。只改 cache、不看 WAL/checkpoint，不是一个完整 factor。

cache hit 不等于不读磁盘

pg_stat_database.blks_hit 只表示 requested block 已在 PostgreSQL shared buffers； blks_read 表示 PostgreSQL 发起读取，不代表物理设备一定读了，因为 OS cache 可能 命中。

PostgreSQL hit
  -> no filesystem read for that access

PostgreSQL read
  -> may be OS-cache hit or device read

要组合：

SELECT *
FROM pg_stat_io
ORDER BY backend_type, object, context;

以及：

OS read bytes
device latency/queue
filesystem/cache state
query BUFFERS + I/O timing

aggregate hit ratio 99.9% 也可能掩盖一条关键 query 每次读取 10 GiB。

effective_cache_size 不分配内存

参考沙箱：

effective_cache_size = 187520 * 8KiB
                     = 1,536,163,840 bytes
                     ≈ 1.43 GiB

它是 planner 对“单条 query 可利用的 shared + kernel cache”的估计，不是：

• shared memory allocation；
• OS cache reservation；
• memory limit；
• 当前 cache occupancy。

官方 Query Planning 明确说明它只影响 estimate。把它从 4GB 改到 64GB 不会获得 60GB RAM，只可能让 planner 更相信 index access 的 cache 命中。

huge page 与 transparent huge page

PostgreSQL 主 shared memory 可以用 explicit huge pages：

SHOW huge_pages;
SHOW huge_pages_status;
SHOW huge_page_size;

huge_pages=try 会尝试，失败时回退；on 失败会阻止 server 启动。它是 restart parameter，需要同时验证 OS reservation、启动和 failover member。

Linux Transparent Huge Pages 与 explicit huge pages 不同。PostgreSQL 官方当前仍 提示 THP 曾对一些版本/环境造成性能退化，不应把“huge page 有益”推导为“THP 永远 开启”。

shared memory inventory

SELECT
    name,
    pg_size_pretty(allocated_size) AS allocated,
    pg_size_pretty(coalesce(free_size, 0)) AS free
FROM pg_shmem_allocations
ORDER BY allocated_size DESC;

它帮助解释 main shared memory 的用途，但不是 host 总内存视图。还要记录：

MemTotal/MemAvailable
cgroup memory.max/current/events
swap
process RSS/PSS
kernel slab/page tables
backup/exporter/sidecar

27.2.2 work_mem 按节点、并发和并行放大

work_mem 是 operation base limit

不是：

per query
per transaction
per session
preallocated fixed reservation

而是 sort/hash 等 query operation 在 spill 前可用的基础限额。官方说明一条复杂 query 可以同时有多个 operation，多 session 又会同时执行，因此总使用量可以是 work_mem 的许多倍。

涉及：

Sort: ORDER BY / DISTINCT / Merge Join
Hash: Hash Join / Hash Aggregate / IN processing
Memoize
parallel worker copies

对 hash operation：

$$ M_{\text{hash limit}}

\text{work_mem} \times \text{hash_mem_multiplier} $$

默认 hash_mem_multiplier=2.0 时，64MB work_mem 对一个 hash operation 的 limit 可能是 128MB。

放大公式

第一阶 upper-bound：

其中：

• $L_o$ 是 sort 的 work_mem 或 hash 的放大 limit；
• $P_o$ 是 leader + parallel workers 中实际拥有该 operation 的 process 数。

workload envelope：

• $A_q$：同时 active 的该类 query；
• $O_q$：可并存的 memory-intensive nodes；
• $P_q$：leader/worker 放大；
• $L_q$：相应 node limit。

它是 safety estimate，不代表每次都用满；但用：

max_connections × work_mem

也不够，因为忽略多个 nodes、hash multiplier 与 parallel workers。

参考沙箱的反例

参考事实：

RAM              ~1.91 GiB
shared_buffers   ~489 MiB
work_mem          64 MiB
max_connections  500

天真的：

$$ 500\times64\text{MiB}

31.25\text{GiB} $$

已经远超 RAM。若每个 active query 有两个 hash node，hash_mem_multiplier=2：

$$ 500\times2\times128\text{MiB}

125\text{GiB} $$

这不表示 PostgreSQL 启动就分配 125GiB，也不表示 500 个 connection 会同时跑满 两个 hash；它说明：

max_connections=500 与 work_mem=64MB 不能同时作为“安全可用上限”理解。

必须用 pool/admission 保证 active envelope，或按 role/query 缩小 work_mem。

plan nodes 不一定同时达到 peak

把 plan 中所有 Sort/Hash 的 limit 相加可能过度保守，因为父子 node 生命周期可能 错开；也可能低估，因为：

• sibling/subplan 同时存在；
• executor context 在 node 结束后仍保留一部分；
• parallel process 独立；
• multiple portals/cursors；
• function/extension 额外 memory；
• query 同时存在于多个 session。

方法：

1. 从 EXPLAIN (ANALYZE, BUFFERS, SETTINGS) 找真实 node、batch、disk；
2. 用 concurrency trace 找同时 active 的 query class；
3. 在 staging 观测 process/container memory；
4. 注入 worst credible concurrency；
5. 留 allocator、kernel 和 forecast headroom。

看 sort 与 hash 是否真的 spill

EXPLAIN (
    ANALYZE,
    BUFFERS,
    WAL,
    SETTINGS,
    FORMAT TEXT
)
SELECT ...;

关注：

Sort Method: quicksort  Memory: ...
Sort Method: external merge  Disk: ...
Hash: Buckets / Batches / Memory Usage
Buffers: temp read/written

累计旁证：

SELECT
    datname,
    temp_files,
    pg_size_pretty(temp_bytes) AS temp_bytes
FROM pg_stat_database
ORDER BY temp_bytes DESC;

以及 pg_stat_statements.temp_blks_read/written。注意累计 delta 与 reset/start time。

temp_bytes=0 的正确结论

第 26 章六个 cell 的 temp bytes 都为零。它支持：

do not raise work_mem to fix observed spill

不支持：

64MB is globally optimal
all application queries never spill
work_mem can safely be lowered globally

压测 mix 没覆盖分析、报表、DDL 和 tenant worst case。

用 scope 区分 OLTP 与分析

不要因为一条月报需要 512MB，把全局 default 改成 512MB：

BEGIN;
SET LOCAL work_mem = '512MB';
SET LOCAL statement_timeout = '15min';

SELECT ... monthly report ...;

COMMIT;

或 dedicated role/database：

ALTER ROLE dbuser_report
IN DATABASE analytics
SET work_mem = '256MB';

新 session 才获得 role/database default。connection pool 中已有 server connection 不会立即更新；transaction pooling 还要验证 pool 对 session state 的处理。

temp_file_limit 是 guard，不是内存预算

temp_file_limit 限制一个 process 可使用的 temp file 空间。它能阻止失控 query 写满磁盘，但：

• 超限会 cancel query；
• parallel worker/process 语义需测试；
• 不能防 OOM；
• 不能代替 statement/admission limit；
• 太小会杀死合法维护或分析。

把它作为 per-role safety policy，并演练错误处理。

27.2.3 maintenance、autovacuum 与后台进程内存

maintenance 不是一个 session

maintenance_work_mem 服务：

VACUUM
CREATE INDEX
ALTER TABLE ADD FOREIGN KEY
some maintenance operations

单个 session 通常一次只有一个相关 operation，因此它常可高于 work_mem；但 cluster 可能同时有：

manual CREATE INDEX
autovacuum workers
restore-created indexes
reindex jobs
multiple databases/tenants

预算要按并发 maintenance job。

autovacuum 放大

若：

autovacuum_work_mem = -1

每个 autovacuum worker 使用 maintenance_work_mem 作为上限。第一阶预算：

还没包含 worker base RSS、shared buffer 与 extension。

参考沙箱：

maintenance_work_mem  125952 kB ≈ 123 MiB
autovacuum_work_mem   -1

如果 3 个 worker 同时接近上限，仅这一项约 369MiB。不能因为“一次 VACUUM 很安全” 就忽略多 worker。

parallel maintenance 的语义不同

并行 query 通常按 process 应用 work_mem，而 parallel utility command 的 maintenance_work_mem 一般作用于整个 command，不简单按 worker 倍增；但 worker 仍消耗 CPU/I/O 与其他私有内存。PostgreSQL 官方明确区分这两种策略。

因此不要把：

maintenance_work_mem × max_parallel_maintenance_workers

机械当作精确值，也不要假设 parallel maintenance 没有额外资源。

其他后台预算

PostgreSQL 参数表只覆盖 server 进程，不覆盖：

Patroni
HAProxy
PgBouncer
pgBackRest
Prometheus exporters
node agents
kernel cache
SSH/Ansible

Pigsty 节点的 host memory budget 必须把整个平台加进去。

backend memory context

当前 backend：

SELECT
    name,
    type,
    path,
    total_bytes,
    free_bytes,
    used_bytes
FROM pg_backend_memory_contexts
ORDER BY total_bytes DESC
LIMIT 30;

它是瞬时、当前 session 视图。不要从一个 idle psql 推导所有 backend。

对另一 backend，可在授权与日志边界下使用 memory context logging function，但输出 进入 server log，可能很大，生产诊断要有采样和数据处理计划。

27.2.4 OOM 风险必须用最坏并发估算

总预算

一个实用模型：

$H$ 是 safety headroom。

active backend：

• $A_q$ 是 active concurrency，不是 connection count；
• $B_q$ 是 backend/query base；
• $W_q$ 是 node/worker working memory。

idle backend 也有成本，但不能假设都占满 work_mem。

credible worst case

不是所有理论 maximum 同时出现，但应至少模拟：

peak OLTP
+ report overlap
+ all autovacuum workers active
+ backup compression
+ one replica rebuilding/catching up
+ exporter/agent normal load
+ traffic retry after failover

把不可能重叠的场景排除时，要有 scheduler/admission 证据。例如：

report queue concurrency = 2
DDL window blocks reports
backup compression jobs = 1
pool active cap = 48

没有 enforcement 的“我们通常不会同时跑”不算边界。

OOM 可能杀谁

Linux OOM/cgroup 可能：

• kill 某个 backend；
• kill postmaster/Patroni；
• kill backup/exporter；
• 使 node reclaim/swap 抖动，p99 先失守；
• 导致 HA 将压力转移到 replica；
• 引发 reconnect storm。

所以 acceptance 不只是“benchmark 没被 kill”。要观察：

MemAvailable
cgroup memory.current/events
PSI memory
swap in/out
major faults
process RSS/PSS
OOM/kernel logs
HA state and reconnect

swap 不是免费 headroom

完全禁用或保留少量 swap 取决于平台 policy，但不能把可 swap 空间计作 database working set。发生 swap 前后，应以 latency/SLO 定义安全线；大量 backend memory 被换出后， 系统可能还活着却不可用。

pool 是 memory governor

PgBouncer/client pool 的关键作用：

many logical requests
  -> bounded active server connections
      -> bounded active query memory

pool size 应从 active workload/resource envelope 推导，而不是等于 max_connections。

还要保留：

reserved_connections
superuser_reserved_connections
monitor/replication/maintenance slots
emergency access

参数变更前的内存表

表填不完整时，不要把 work_mem 或 max_connections 翻倍。

何时接受

内存 candidate 至少通过：

• 目标 spill/batch/latency 明确改善；
• representative concurrency 没有 memory pressure；
• parallel 与 maintenance overlap 已测；
• pool/admission enforcement 可证；
• temp disk 与 OOM guard 合理；
• N+1/restart/failover 下仍有 headroom；
• session/role/global scope 正确；
• rollback 不依赖 OOM 后恢复。

内存参数的目标不是“尽可能不 spill”。spill 有成本，OOM 是失效；工程要在二者之间 找到可控边界。

上一节：调优是一套实验方法 · 返回本章目录 · 下一节：WAL、检查点与写入平滑 · 查看全书目录 · 查看索引中心

27.3 WAL、检查点与写入平滑

写事务的磁盘路径不是“把 table row 写到文件后提交”。

近似链路：

change shared buffer
  -> generate WAL record
      -> insert into WAL buffer
          -> write/flush WAL according to commit policy
              -> acknowledge commit
                  -> data page later written by backend/bgwriter/checkpoint

WAL 让 data page 可以延后写，却把 commit latency、checkpoint、archive、replication 和 crash recovery 连接成一个系统。调其中一项，必须看整条链。

27.3.1 WAL 生成、刷盘与提交延迟

write、flush 与 acknowledge

需要区分：

WAL generated
WAL copied/written to kernel
WAL flushed to durable storage
WAL sent to standby
WAL written/flushed/replayed on standby
client receives success

不同 synchronous_commit level 改变 acknowledge 等待的阶段。它不是“开/关性能”：

• on：本地 durable，并遵守 synchronous standby 配置；
• remote_apply：还等待 synchronous standby replay；
• remote_write：等待 standby 写入 OS；
• local：等待本地 flush，不等待 synchronous standby；
• off：允许本地 WAL 尚未 flush 就向 client 确认。

具体 durability 还受 synchronous_standby_names、synchronous_standby_slots、 standby availability 和 application route 影响。不要看到 on 就自动推导 multi-node zero-loss，也不要看到 off 就说“数据会损坏”；它改变的是 crash 时最近 成功确认 transaction 的 loss window。

fsync 与 full_page_writes 不是普通 candidate

fsync=off
full_page_writes=off

可能显著提高 benchmark TPS，但会改变 crash safety。PostgreSQL 官方 WAL settings 明确说明，在可能发生 OS/硬件 crash 的系统中关闭相关保护可能导致不可恢复或静默 损坏。

教学 benchmark 若关闭它们，测的是另一个产品合同。

group commit

多个 backend 可以共享一次 flush：

backend A commits
backend B commits nearby
  -> one WAL flush may make both durable

因此：

• 单 client commit latency 不代表多 client；
• TPS 与 fsync count 不一定 1:1；
• storage fsync tail 比平均 throughput 更重要；
• batch/transaction boundary 改变 group opportunity。

commit_delay/commit_siblings 可以人为等待以扩大 group，但等待本身增加 latency， 只有在高并发、flush 昂贵且实验支持时才考虑。默认不是越大越好。

测 WAL composition

累计层：

SELECT
    wal_records,
    wal_fpi,
    wal_bytes,
    wal_buffers_full,
    stats_reset
FROM pg_stat_wal;

PostgreSQL 18 把 WAL I/O bytes/timing 纳入 pg_stat_io；不要照抄旧版本 pg_stat_wal.wal_write_time/wal_sync_time 查询：

SELECT
    backend_type,
    object,
    context,
    reads,
    read_bytes,
    writes,
    write_bytes,
    fsyncs,
    write_time,
    fsync_time
FROM pg_stat_io
WHERE object = 'wal'
ORDER BY backend_type, context;

列与可用 timing 取决于 PostgreSQL major version 与 track_wal_io_timing。升级时先查 system view schema。

statement 层：

SELECT
    queryid,
    calls,
    wal_records,
    wal_fpi,
    wal_bytes
FROM pg_stat_statements
ORDER BY wal_bytes DESC
LIMIT 20;

不要公开 query text；用 queryid 对回内部 catalog。

分母要有业务语义

WAL bytes / mixed transaction
WAL bytes / write transaction
WAL bytes / order
WAL bytes / changed row

不是同一个指标。

第 26 章 mixed workload 只有 20% place-order，约 158–196 WAL bytes/mixed tx。 不能把它写成“每个订单 196 bytes”。需要按成功 write operation 归一，并把 background activity 与 full-page image phase 作为误差。

WAL buffer full

wal_buffers_full 增加表示 WAL buffer 空间不足时 backend 被迫写 WAL，但不能只看到 counter 就把 wal_buffers 手工调大：

• 默认 -1 会基于 shared buffers 自动选择；
• upper bound 通常一个 WAL segment；
• 写入/flush device 可能才是限制；
• checkpoint/FPI 或大 transaction 可能改变 burst；
• counter 是累计 delta。

先对齐发生时间与 WAL generation/write/fsync。

commit latency 的证据链

client transaction latency
  -> server wait event
      -> WAL I/O timing/count
          -> device fsync latency/queue
              -> synchronous standby wait
                  -> network/replay

如果 backend 等 SyncRep，加快本地 WAL device 未必改善；如果 server CPU 已饱和， 启用更重 WAL compression 可能反而恶化。

27.3.2 检查点频率、写突发与恢复时间

checkpoint 做什么

checkpoint 建立 recovery 起点，并把需要的 dirty buffer 写出，使 crash recovery 不必从无限久之前 replay WAL。它不是“定期 fsync 一次”这么简单。

触发：

time: checkpoint_timeout
WAL volume: max_wal_size soft threshold
manual/requested: CHECKPOINT or operational action
shutdown/recovery transitions

PostgreSQL 18 的证据：

SELECT *
FROM pg_stat_checkpointer;

SELECT *
FROM pg_stat_bgwriter;

checkpointer 与 bgwriter 已分开统计。关注：

num_timed / num_requested / num_done
write_time / sync_time
buffers_written
backend writes/fsync
checkpoint warnings/log duration

不要用旧版本列名硬编码跨版本 dashboard。

max_wal_size 是 soft limit

它不是：

pg_wal hard cap
archive backlog cap
slot retention cap
disk safety guarantee

在 archive failure、replication slot、wal_keep_size、重负载等条件下，WAL 可超过 max_wal_size。把 disk provision 写成 max_wal_size + 10% 是错误模型。

太小的症状

requested/WAL-driven checkpoints frequent
checkpoint_warning
WAL FPI rate high
write/sync burst
backend writes increase
throughput/latency periodic sawtooth

因为每次 checkpoint 后，某页第一次修改需要 full-page image（在保护开启时），过于 频繁会增加 WAL。

太大的代价

增加 max_wal_size/checkpoint_timeout 可能：

• 减少 checkpoint 频率；
• 降低 checkpoint-induced FPI；
• 让 dirty work 有更长时间平滑；
• 增加 crash recovery 要 replay 的 WAL；
• 增加 pg_wal working space；
• 延后 dirty page write，扩大 failure/burst 状态；
• 改变 archive/replica catch-up。

优化点不是“checkpoint 越少越好”，而是满足：

foreground SLO
write smoothness
crash RTO
WAL disk headroom
archive/replica behavior

checkpoint_completion_target

它控制 checkpoint 在 checkpoint interval 中用于完成的目标比例。默认较高是为了把 I/O 分散到大部分 interval。降低会让 checkpoint 更快完成：

higher instantaneous write rate
  -> then longer quiet period

通常不是想要的平滑。参考 Pigsty 沙箱为 0.95；改变它前要看 checkpoint progress、 device latency 与 SLO，而不是复制旧时代 0.7。

写入平滑不是平均值

画 time series：

WAL bytes/s
checkpoint begin/end
data write bytes/s
fsync latency
dirty buffers
backend writes
archive rate/gap
replica receive/replay gap
OLTP p95/p99

平均 20MB/s 可能是：

持续 20MB/s

或：

每 10 秒 200MB/s，其余为 0

设备和 tail latency 面对的是后者。

恢复时间要实测

粗略：

replay throughput 受：

WAL record mix
CPU
data/WAL storage
full-page images
compression/decompression
extension
prefetch
recovery conflicts

影响。不能用 WAL device sequential bandwidth 代替。

checkpoint 调整 acceptance 应含 crash/restart 或 replica rebuild drill，而不是只跑 steady primary。

不要为 benchmark 强制 checkpoint

每次 run 前：

CHECKPOINT;

会人为同步 phase：

• 一开始 full-page image 更多；
• dirty state 被清空；
• I/O burst 与 production natural phase 不同。

如果研究 checkpoint phase，应把 “immediately after checkpoint / mid-cycle” 作为显式 factor；否则让背景系统自然运行并记录 checkpoint。

27.3.3 压缩、全页写与归档代价

full-page image 的正确性作用

当 full_page_writes=on，checkpoint 后某页第一次被修改时，WAL 通常记录整页 image， 以防 torn page 让 recovery 无法还原。于是：

frequent checkpoint
  -> more first modifications
      -> more FPI
          -> more WAL

但关系受 working set 与 write locality 影响。修改同一小批 hot page 与随机修改巨大 dataset 的 FPI 比例不同。

wal_compression

PostgreSQL 18 支持的 method 取决于 build：

off
pglz
lz4
zstd

它主要压缩 full-page image，不是压缩所有 WAL record。tradeoff：

less WAL bytes
  vs
more primary compression CPU
more replay decompression CPU
different archive/network/storage load

参考沙箱：

wal_compression = lz4
source          = configuration file
context         = superuser

第 26 章 c8 已有 CPU pressure，却没有证明 WAL I/O 是 throughput limit。因此“开启更强 compression 减少 WAL”不符合当前 service center evidence。

compression 实验矩阵

至少比较：

只看 wal_bytes 降低不能决定接受。

archive 与 slot 可能让 WAL 无界

WAL generated
  -> pg_wal recycle/remove eligibility
      depends on checkpoint
      + archive completion
      + replication consumers/slots
      + wal_keep_size
      + backup/recovery needs

若 archive throughput $A$ 小于 WAL generation $W$：

$$ \text{backlog growth}

W-A $$

增加 max_wal_size 只推迟 disk full，不修复 consumer。

若 slot inactive：

SELECT
    slot_name,
    slot_type,
    active,
    restart_lsn,
    confirmed_flush_lsn,
    wal_status,
    safe_wal_size
FROM pg_replication_slots;

列随版本变化，先查当前 view。还要配置/验证 max_slot_wal_keep_size 等 guard，但 guard 触发可能使 consumer 无法继续，属于 recovery/runbook 决策。

min_wal_size 与 recycle

min_wal_size 让一定量旧 WAL segment 被 recycle 供未来使用，减少 burst 时反复创建/ 删除。它不是最小 WAL generation，也不是 retention policy。

filesystem 类型可能影响：

wal_init_zero
wal_recycle

尤其 CoW filesystem。但这些是 storage-specific candidate，应以文件系统、allocation 行为和 crash test 证明，不能把云盘经验搬到本地 SSD。

归档压缩与 WAL compression 是两层

wal_compression
  compresses full-page images inside WAL records

archive/backup compression
  compresses WAL files/backup objects during transport/storage

二者可叠加，CPU 消耗发生在不同进程/节点/阶段。容量模型要测最终 archive object bytes 和两侧 CPU，不要把 primary wal_bytes ratio 当作备份压缩率。

参数 ADR

WAL/checkpoint 变更应写：

symptom:
  requested_checkpoints_per_hour: ...
  wal_fpi_ratio: ...
hypothesis:
  parameter: max_wal_size
  mechanism: reduce WAL-driven checkpoint frequency
baseline:
  checkpoint_interval: ...
  wal_bytes_s: ...
  crash_recovery_p95: ...
candidate:
  value: ...
benefit_gate:
  foreground_p95: ...
non_regression:
  pg_wal_peak: ...
  archive_gap: ...
  replica_replay: ...
  crash_recovery: ...
rollback:
  value/source: ...
  restart_or_reload: ...

没有 recovery/retention 证据的 checkpoint 优化，只完成了一半。

上一节：内存预算 · 返回本章目录 · 下一节：规划器、并行与连接参数 · 查看全书目录 · 查看索引中心

27.4 规划器、并行与连接参数

规划器参数决定 PostgreSQL 如何比较候选 plan；并行参数决定 plan 可请求多少 worker； 连接参数决定多少 backend 能同时竞争资源。

三者形成一条链：

cost/statistics
  -> chosen plan and requested workers
      -> active backend/worker population
          -> CPU/memory/I/O/lock demand
              -> pool queue and SLO

把它们分开调，容易得到一个单 query 很快、cluster 却更慢的系统。

27.4.1 成本参数只能用硬件和计划证据校准

cost 是相对单位

常见：

seq_page_cost
random_page_cost
cpu_tuple_cost
cpu_index_tuple_cost
cpu_operator_cost
parallel_setup_cost
parallel_tuple_cost

它们不是毫秒，默认以 sequential page cost 为相对基准。把所有 cost 同乘 10，plan 通常不变；重要的是相对值。

random_page_cost

降低它会让 random/index access 相对便宜，可能使 planner 更偏向 index scan。正确 证据：

actual storage random vs sequential latency
cache residency
concurrent workload
tablespace/storage difference
representative plans and actual time/buffers

PostgreSQL 官方指出，默认 4.0 已隐含一部分 random access 会命中 cache；完全 cached 时较低值可合理，random 物理 I/O 昂贵时则可能需要较高值。

不能这样校准：

query uses seq scan
  -> random_page_cost = 1.0

seq scan 可能就是正确 plan，或根因是 statistics/index/predicate。

tablespace 可有不同 cost

若 hot index 在 NVMe、archive table 在 HDD，可在 tablespace scope 设置 page cost， 不必用一个 cluster-global average 强迫两种 storage：

ALTER TABLESPACE fast_ssd
SET (
    random_page_cost = 1.1,
    seq_page_cost = 1.0
);

仍需把 filesystem/cache 与 production I/O 测量纳入。

effective_io_concurrency

它表达 PostgreSQL 可以向 storage 发起的并发 I/O 提示能力；合理值取决于 device/ filesystem/RAID/cloud volume 和 PostgreSQL I/O implementation。大值不是免费吞吐：

• device queue 可能更深；
• tail latency 可能变差；
• shared storage 邻居受影响；
• query/scan type 可能不使用；
• OS/backend 的实现随 major version 演进。

参考沙箱为 200、source 是 configuration file；这不能直接复制到 HDD 或网络块存储。

effective_cache_size

只影响 planner estimate，不分配 memory。校准时考虑：

shared_buffers
+ PostgreSQL data 可实际使用的 kernel cache
- concurrent queries sharing cache
- same blocks duplicated in two caches

不要写成 host RAM 总量，也不要当作 cache guarantee。

statistics 优先于 cost hack

如果 estimated rows 错数个数量级：

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT ...;

先查：

ANALYZE recency/sample
default_statistics_target
ALTER COLUMN SET STATISTICS
extended statistics: dependencies/ndistinct/mcv
expression statistics
partition statistics
parameter/cast/collation

成本模型在错误 cardinality 上做得再精细，也会比较错误规模的 plan。

enable_* 是诊断探针

SET LOCAL enable_hashjoin = off;
SET LOCAL enable_nestloop = off;
SET LOCAL enable_seqscan = off;

可以回答：

planner 若被迫使用另一路径，实际是否更好？

不应默认成为永久 global 配置。某种 plan type 对当前一条 query 不好，不代表对全库 无用。

plan cache

prepared statement 有：

custom plan
  parameter value known
  planning repeated
  can adapt to skew

generic plan
  reusable
  lower planning cost
  cannot adapt to specific value

plan_cache_mode=auto 通常先生成若干 custom plan，再比较 generic cost。可以用：

SELECT
    name,
    generic_plans,
    custom_plans,
    parameter_types
FROM pg_prepared_statements;

第 27 章实验中，两个 probe 在 auto 下均为：

custom_plans  5
generic_plans 5

说明自动策略已经切换。强制 generic 的长期 TPS 益处没有达到 2% 置信下界。

对于 tenant skew：

tenant small -> index/nested loop
tenant huge  -> bitmap/hash/seq alternative

强制 generic 可能让其中一类严重退化。probe 必须覆盖高低/MCV/边界，而不是只用 id=1。

JIT

JIT 有 compilation/setup cost，也可能加快长 CPU-intensive execution。阈值由 plan estimated cost 决定：

jit_above_cost
jit_inline_above_cost
jit_optimize_above_cost

OLTP point query 常低于阈值；把 jit=off 后看到无差异不证明 JIT 没用，只说明当前 query 没触发或收益不足。分析 query 要把：

planning/JIT generation time
execution time
repetition/cache
CPU

分开。

calibration report

只从两条 query 推导 cluster cost constants 很危险。官方文档也指出，没有定义良好的 “理想 cost”求法，应把它视为 workload average。

27.4.2 并行 worker 的全局预算与退化条件

三层上限

max_worker_processes
  hard pool for background workers

max_parallel_workers
  cluster parallel operation subset

max_parallel_workers_per_gather
  one Gather/Gather Merge request

还包括：

max_parallel_maintenance_workers
extension background workers
logical replication workers

提高 per-gather 而不提高上层 pool，可能没有效果；提高上层 pool 又会增加 cluster CPU/ memory contention。

plan request 不等于实际 worker

Workers Planned: 4
Workers Launched: 2

worker unavailable 时，query 通常以更少 worker 运行；某些 parallel plan 在 worker 不足时效率很差。要记录：

planned
launched
launch wait/starvation
leader participation
other concurrent parallel queries

PostgreSQL 18 的 database statistics 还提供 parallel worker launch 相关累计信息； system view 随版本变化，先查当前列。

leader 也可能工作

parallel_leader_participation=on 时 leader 可以执行 parallel plan，也要负责读取 worker tuple。若 worker 输出大量 tuple，leader 可能主要消耗在汇总/传输；parallel speedup 不会等于 worker 数。

Amdahl：

$$ S(N)

\frac{1} {(1-P)+P/N+\text{parallel overhead}} $$

serial fraction、launch、tuple transfer 与 skew 限制 speedup。

memory 按 process 放大

PostgreSQL 官方给出的关键边界：parallel query 的 resource limit 通常按 worker process 应用。4 个 worker 加 leader，某些 node 的内存/CPU/I/O footprint 可接近串行的 5 倍。

因此：

max_parallel_workers_per_gather=4
work_mem=256MB

不能解释成“一条 query 最多 256MB”。

全局 CPU budget

若 host 有 $C$ cores、需要为 OLTP 保留 $R$ cores：

并行分析的 admission：

不是“每条报表允许 8 worker，所以十条报表都允许 8”。

什么时候 parallel 反而慢

• query 太短，launch/setup 占比高；
• output 太大，leader/tuple transfer 成为瓶颈；
• worker skew，一人做绝大多数工作；
• storage 已饱和；
• memory spill 按 worker 放大；
• concurrent OLTP 被抢 CPU；
• worker pool 不足；
• parallel-unsafe/restricted function；
• serialization/ordering overhead。

接受时比较：

single-query duration
cluster throughput
OLTP tail
CPU/I/O/memory
worker availability
N+1 state

维护 worker

parallel CREATE INDEX/VACUUM 与 parallel query 的 memory limit 语义不完全相同， 但仍争抢 CPU/I/O/worker。DDL window 中提高 maintenance worker，可能缩短单项任务， 却把 replica/archive 与 OLTP 推过 SLO。

角色级治理

分析角色：

ALTER ROLE dbuser_analytics
SET max_parallel_workers_per_gather = 4;

OLTP 角色：

ALTER ROLE dbuser_app
SET max_parallel_workers_per_gather = 0;

只是示意，不是默认建议。要验证新连接、pool lifecycle 和 query class。role scope 比 global 更贴近 ownership，但一个 role 内也可能混合 workload。

27.4.3 连接上限、超时和锁等待边界

connection slot 不是并发目标

max_connections 是 server 能接纳的 backend 上限。提高它：

• 预留更多 shared structures；
• 允许更多 private backend；
• 扩大 active query/memory/lock population；
• 增加 context switch；
• 可能降低每条 query cache locality；
• 使 overload 更深。

它不增加 CPU、memory bandwidth、IOPS 或 lock throughput。

参考沙箱：

max_connections = 500
context         = postmaster
source          = command line
RAM             ~1.91 GiB

这是 platform/template fact，不表示 500 个 64MB-work-mem query 可同时 active。

连接预算

同时：

两条都要满足。通常：

logical client population
  > pool client connections
  > active server connections

reserved slots

PostgreSQL 18：

reserved_connections
superuser_reserved_connections

前者供拥有 pg_use_reserved_connections 的角色，后者是 superuser 最后保留。设计：

• emergency role 最小权限；
• pool 不耗尽 reserved；
• monitoring/replication 配额；
• incident 时实际演练能连接；
• standby 的 max_connections 不低于 primary。

把 emergency slot 留给日常应用，相当于没有 reserve。

pool queue 比 database overload 更可控

在 pool：

queue depth
wait time
timeout
admission/fairness

可以观察和限制。把 server connection 上限提高，会让更多 request 同时进入 executor/ lock/memory，queue 仍然存在，只是搬到了更危险的位置。

pool sizing 要配合第 22 章 transaction/session semantics：

• session state；
• prepared statement；
• temp table；
• advisory lock；
• LISTEN/NOTIFY；
• transaction pooling reset。

statement_timeout

从 command 到达 server 开始计，extended protocol 对 Parse/Bind/Execute/Sync 有具体 边界。它终止 statement，不等于 HTTP request deadline。

不要设置一个过于激进的 global value杀掉：

DDL
backup catalog
maintenance
replica diagnostic
legitimate report

优先按 role/database：

ALTER ROLE dbuser_app
IN DATABASE app
SET statement_timeout = '2s';

新 session 生效。

lock_timeout

只在等待 lock 时计时，并且每次 lock acquisition 单独应用。若它等于或大于 statement_timeout，往往 statement timeout 先触发。

常见关系：

lock_timeout < statement_timeout <= request deadline

但 transaction 内多条 statement、client network 和 retry 仍需 budget。

DDL migration 常用短 lock_timeout 来避免排队阻塞业务：

BEGIN;
SET LOCAL lock_timeout = '500ms';
SET LOCAL statement_timeout = '5min';
ALTER TABLE ...;
COMMIT;

失败要退出/重试，不应无限 loop。

idle 与 transaction timeout

idle_in_transaction_session_timeout
  session 在 open transaction 中 idle
  防止长期持锁/阻碍 vacuum

idle_session_timeout
  无 transaction 的 idle session
  pool 中要谨慎，middleware 未必处理意外关闭

transaction_timeout
  整个 transaction 存活时间
  prepared transaction 不受其约束

PostgreSQL 18 官方指出，不建议把某些 timeout 粗暴写成影响所有 session 的 postgresql.conf default。scope 应跟 workload。

timeout 不是 cancel 后就结束

应用必须：

observe SQLSTATE
rollback failed transaction
release/replace connection
respect outer deadline
limit retries
preserve idempotency

否则 database cancel 后，client 立即重试可能制造 retry storm。

lock diagnosis

不要为了更快报 deadlock，把 deadlock_timeout 全局调到 1ms。deadlock check 有成本， 普通 lock wait 不是 deadlock。

调查：

SELECT
    pid,
    pg_blocking_pids(pid) AS blockers,
    wait_event_type,
    wait_event,
    xact_start,
    query_start
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

配合 log_lock_waits 与适当 deadlock_timeout，在诊断窗口使用。最终修复通常是：

• transaction order；
• shorten critical section；
• index/access path；
• hot-key design；
• admission；
• application retry；

而不是无限提高 timeout。

一份连接/超时合同

application_deadline: 2500ms
pool_wait_timeout: 200ms
connect_timeout: 300ms
lock_timeout: 400ms
statement_timeout: 1800ms
transaction_timeout: 2200ms
idle_in_transaction_timeout: 30s
retry:
  max_attempts: 2
  budget_included_in_deadline: true
server_connections:
  active_cap: 48
  reserved_platform: 12
  emergency: 5

数值只是示意。关键是所有 timeout 和 slot 在同一个 end-to-end budget 中，不互相 矛盾。

上一节：WAL、检查点与写入平滑 · 返回本章目录 · 下一节：参数作用域与变更方式 · 查看全书目录 · 查看索引中心

27.5 参数作用域与变更方式

一个参数变更有三个不同状态：

desired
  inventory/template/DCS/database-role policy 想要什么

configured
  file/catalog/command line 中写了什么

effective
  当前 server/session 实际用了什么

它们可以不同。

最常见事故不是参数值本身，而是：

• 改错 scope；
• 被更高优先级覆盖；
• reload 了一个必须 restart 的参数；
• 只改 primary，failover 后消失；
• 手工 ALTER SYSTEM 被下一次 IaC 覆盖；
• 改了 role default，却继续复用旧 pool session；
• pending_restart 长期无人处理。

27.5.1 编译、初始化、启动、reload 与会话级

先问“这个属性什么时候还能改变”

从最早到最晚：

build/compile
  -> initdb
      -> postmaster startup
          -> SIGHUP reload
              -> backend startup
                  -> superuser/session
                      -> transaction

越靠左，变更成本、兼容性和回退风险通常越大。

build-time

有些物理属性来自 build：

block size
some segment/page layout options
compiled features/libraries
architecture/compiler

查询：

SHOW block_size;
SELECT version();

这些不是普通 GUC。改变 block size 通常意味着不同 binary/cluster physical format， 不能用 reload/restart 改现有集群。

initdb-time

cluster 创建时固定或高度绑定：

encoding
locale/ICU provider and version choices
data checksums
WAL segment size
system identifier

例如：

SHOW data_checksums;
SHOW wal_segment_size;
SELECT datname, encoding, datcollate, datctype
FROM pg_database;

某些能力可能有离线工具/特定版本转换路径，但不能把它当作普通 GUC rollout。 参数 ADR 要标注：

new cluster / migration / offline conversion

而不是写“restart”。

pg_settings.context

SELECT DISTINCT context
FROM pg_settings
ORDER BY context;

典型语义：

context=user 只表示权限/生命周期允许，不表示业务上可随意改。

restart parameter

shared_buffers
max_connections
max_worker_processes
shared_preload_libraries
huge_pages

写进 config 后 reload：

SELECT name, setting, pending_restart
FROM pg_settings
WHERE pending_restart;

pending_restart=true 表示 file 中的新值尚未成为 effective value。不能把 config diff 当作运行事实。

reload parameter

SIGHUP：

SELECT pg_reload_conf();

或平台命令。reload：

• 重新读取配置；
• 不停止 server；
• 不保证每个参数/每个 backend 立即按你想象生效；
• 不证明配置无 syntax/semantic error；
• 不处理 postmaster parameter。

先查 pg_file_settings.error，再 reload，随后查 effective/source。

backend-start parameter

一些设置只在新 backend 建立时取得。reload 后：

new sessions see candidate
old sessions retain previous

connection pool 可让“旧 session”存活很久。变更计划要包括：

• pool recycle/drain；
• prepared/session state；
• transaction 不中断；
• 新旧 session 混合窗口；
• verification 分别取样。

session 与 transaction

SHOW work_mem;

SET work_mem = '32MB';
-- 当前 session 后续 statement 使用

BEGIN;
SET LOCAL work_mem = '128MB';
-- 仅当前 transaction
COMMIT;
-- 回到 session value

RESET work_mem;
-- 回到 session default

SET LOCAL 在 transaction 外没有你想要的持久语义。transaction rollback 也影响配置 变化；用 connection pool 时必须测试 reset 行为。

查单位与规范化值

pg_settings.setting 常是 base unit：

SELECT
    name,
    setting,
    unit,
    vartype,
    min_val,
    max_val,
    enumvals
FROM pg_settings
WHERE name IN ('work_mem', 'shared_buffers', 'checkpoint_timeout');

不要把：

shared_buffers setting=62592

读成 bytes；unit 是 8kB。

27.5.2 系统、数据库、角色与事务覆盖层

global 配置层

来源可能包括：

compiled boot value
postgresql.conf + includes
postgresql.auto.conf / ALTER SYSTEM
postmaster command-line -c
environment/client startup

postgresql.auto.conf 在普通 config 后读取；server command-line setting 又可覆盖 file。 参考沙箱：

max_connections = 500
source          = command line

所以在 postgresql.conf 写 200、reload/restart 后，若 Patroni/postmaster 仍用 -c max_connections=500，effective 仍可能是 500。

database/role defaults

ALTER DATABASE app
SET statement_timeout = '5s';

ALTER ROLE dbuser_app
SET work_mem = '16MB';

ALTER ROLE dbuser_app
IN DATABASE app
SET statement_timeout = '2s';

新 login 的优先关系可概括为：

global
  < database-specific
  < role-specific
  < role-in-database-specific
  < session SET / startup option
  < transaction SET LOCAL

database 与 role 的精确冲突规则：role-in-database 最具体；role-specific 覆盖 database-specific。

官方 Setting Parameters 强调 ALTER DATABASE/ALTER ROLE 只在新 session建立时应用。SET ROLE 不会重新 加载目标 role 的配置 default。

catalog 事实

这些 default 存在：

SELECT
    setdatabase::regdatabase,
    setrole::regrole,
    setconfig
FROM pg_db_role_setting
ORDER BY setdatabase, setrole;

需要处理 OID=0 的 all database/all role 显示；不要直接把系统 catalog 结果发给不该 看到 role policy 的用户。

current session 的 source

SELECT
    name,
    setting,
    unit,
    source,
    sourcefile,
    sourceline,
    reset_val,
    boot_val
FROM pg_settings
WHERE name = 'statement_timeout';

sourcefile 只对有 pg_read_all_settings 等权限的用户可见。公共报告不应发布主机 绝对路径。

注意：

source

在当前 session 被 SET 后会显示 session source；要验证 cluster default，需要新建 干净 session 或查 catalog/file，不要在已被测试脚本修改的 session 中判断。

ALTER SYSTEM

ALTER SYSTEM SET work_mem = '64MB';
SELECT pg_reload_conf();

写入 postgresql.auto.conf。它适合某些 standalone 管理模式，但在 IaC/Patroni/Pigsty 中会产生第二个 desired-state writer。

Pigsty 官方 parameter scopes 指出，受管集群的 postgresql.auto.conf 可由 pg_parameters 管理，手工 ALTER SYSTEM 可能被下一次 playbook 覆盖。生产持久变更应回到 inventory/desired state，除非有明确 break-glass 流程和回写。

ALTER SYSTEM RESET ALL 很危险

它不是“恢复 PostgreSQL 默认”，而是清空 postgresql.auto.conf 中 ALTER SYSTEM 设置；文件可能还有平台管理内容。不要为了撤一项变更执行 RESET ALL。

精确回退：

ALTER SYSTEM RESET work_mem;

仍要确认 lower-priority value 是预期值。

startup packet / PGOPTIONS

libpq：

env PGOPTIONS="-c statement_timeout=2s -c plan_cache_mode=auto" \
  psql ...

只影响连接 session。第 27 章实验用它确保 candidate 不落盘。

风险：

• application 可覆盖平台 default；
• pool 连接建立时固定；
• 不允许的 GUC 会导致连接失败；
• connection string/log/env 可能泄露；
• startup setting provenance 易被忽略。

应用允许的 startup option 应纳入 policy。

自定义 GUC 与 extension

extension 可能增加：

shared_preload_libraries
extension.parameter
custom namespace

参数在 extension 未加载/版本变化时可能无效或阻止启动。升级前检查：

available extension version
preload library presence
pg_file_settings errors
standby binary parity
rollback binary compatibility

27.5.3 配置漂移、审计、回退和滚动风险

一条事实查询

SELECT
    name,
    setting,
    unit,
    context,
    source,
    sourcefile,
    sourceline,
    pending_restart
FROM pg_settings
ORDER BY name;

它回答 effective/session fact。配置文件事实：

SELECT
    sourcefile,
    sourceline,
    seqno,
    name,
    setting,
    applied,
    error
FROM pg_file_settings
ORDER BY seqno;

官方 pg_file_settings 指出：

• 每条 file entry 一行；
• invalid/syntax error 出现在 error；
• 被后续同名项覆盖时 applied=false，不一定是 error；
• 它反映当前文件内容，不是 last-applied runtime。

两张 view 要一起看。

duplicate setting

postgresql.conf:100  work_mem=4MB
included/app.conf:20 work_mem=16MB
postgresql.auto.conf work_mem=64MB
command line         none

只 grep 第一处会误判。用 seqno/applied/source 还原 precedence。

drift 类型

每种 remediation 不同。

配置 snapshot 不要泄密

pg_settings 里可能有：

• file path；
• library/path；
• connection-like extension setting；
• topology；
• logging destination。

私密 evidence 保存完整；公共报告使用 allowlist：

name
normalized setting
unit
context
coarse source
pending_restart

不发布 sourcefile absolute path、secret 或 raw extension config。

变更前检查

target cluster/member/role
current desired commit
current effective values on every member
file errors
pending_restart
HA health/lag
backup/recovery health
resource headroom
active DDL/maintenance
pool/session lifecycle
rollback value and command

参数名相同不代表 primary/replica 应完全相同，例如 delayed replica；但差异必须是 desired，而不是 drift。

reload 风险

reload 低于 restart，不等于零风险：

• logging 参数可制造 I/O storm；
• autovacuum 参数可启动更多工作；
• timeout 可中断新 workload；
• HBA/SSL/config error 可影响连接；
• query cost 可在新 planning 时改变 plan；
• backend-start setting 造成混合。

reload 后观察：

config log
pg_settings effective/source
new and old session sample
query/latency/resource
HA/replica/archive

rolling restart 风险

restart parameter 在 HA cluster 中通常逐 member：

replica 1
  -> restart
  -> recover/catch up/validate
replica 2
  -> ...
planned switchover if needed
old primary

但是否安全取决于 parameter：

• standby max_connections 不应低于 primary，否则 recovery query 限制；
• max_worker_processes standby 需要与 primary 相容；
• shared_preload_libraries 的 extension/binary 每台都要存在；
• protocol/physical compatibility；
• restart 期间 N+1 capacity；
• failover 在 mixed-version/mixed-config 窗口的行为。

不能一概写“滚动无中断”。

rollback 也可能 restart

若 candidate 是 postmaster：

apply candidate -> rolling restart
regression -> restore desired -> another rolling restart

这段时间风险是两倍操作，不是一条 git revert。change window 必须预留 rollback 时长和 N+1 capacity。

failover 中的 source of truth

Patroni 管理的参数可能来自 DCS/postmaster command line。只编辑 local postgresql.conf：

• Patroni 可能重写；
• failover 后 candidate 消失；
• replica effective 不同；
• automation reconcile 回旧值。

变更前先确定：

who owns this parameter?
template, inventory, DCS, auto.conf, role catalog, or application?

一个参数只能有一个持续 desired-state owner。

configuration ADR

parameter: ...
owner: ...
current:
  desired: ...
  configured: ...
  effective: ...
  source: ...
context: postmaster|sighup|user|...
scope: cluster|instance|database|role|session
hypothesis: ...
members:
  - name: ...
    before: ...
apply:
  method: ...
  order: ...
  observation: ...
rollback:
  method: ...
  order: ...
  maximum_time: ...
failure:
  mixed_state_behavior: ...
  failover_behavior: ...
validation:
  native_sql: ...
  file_fact: ...
  Pigsty: ...

参数值只是 ADR 中一行；scope、owner、effective evidence 与 mixed-state behavior 同样重要。

上一节：规划器、并行与连接参数 · 返回本章目录 · 下一节：模板参数与集群变更 · 查看全书目录 · 查看索引中心

27.6 模板参数与集群变更

Pigsty 把 PostgreSQL 参数放进：

hardware/workload template
  + cluster inventory
      + instance override
          + Patroni dynamic configuration
              + database/role defaults

这解决的是：

如何从同一份 desired state 可重复生成、分发、验证集群配置？

它不自动回答：

这个值是否适合我的 workload？

模板负责起点，实验负责偏离模板的理由。

27.6.1 从模板生成实例配置

四类起点

当前 Pigsty 官方模板：

示例：

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }
    10.10.10.12: { pg_seq: 2, pg_role: replica }
  vars:
    pg_cluster: pg-test
    pg_conf: oltp.yml
    node_tune: oltp

模板会根据 CPU、memory、disk/workload profile 计算多项参数。Pigsty optimization policy 当前以 25% memory 作为 shared buffer 默认起点，并按 profile 处理 connection、 parallel、vacuum、WAL 与 timeout。

profile 不是标签

选择 olap 不只是：

work_mem larger

还可能改变：

connections
parallel workers
maintenance
vacuum
timeout
I/O settings
logging

因此把 existing production cluster 从 oltp.yml 换到 olap.yml 是 multi-factor change。不要用一次切换来“测试 OLAP 参数”；先生成 diff，拆成可归因变更。

hardware 与 profile 必须匹配

Pigsty 官方把 tiny 用于小型/受限节点；oltp/olap 文档面向更大的常规实例。 如果 1C2G 节点使用 aggressive profile：

• template 仍可能生成 syntactically valid 配置；
• server 仍可能启动；
• 但 worst-concurrency memory/worker budget 可能不安全。

参考沙箱就是一个值得审阅的事实：

server RAM       ~1.91 GiB
max_connections  500
work_mem          64 MiB
shared_buffers   ~489 MiB

这不代表当前 idle/8-client workload 已 OOM；它表示 platform limit 不能被应用当作 “500 条复杂 query 的安全并发”。

先预览 recommendation

当前 pig CLI 提供 tuning output：

pig pg tune
pig pg tune -p tiny
pig pg tune -p olap
pig pg tune -c 8 -m 32768 -d 500 -o yaml

这些命令用于生成/查看 recommendation；先确认本机安装版本的 pig pg tune --help。 输出不是自动批准的生产变更。保存：

Pigsty version
PostgreSQL major
detected/overridden CPU memory disk
profile
generated output hash

同一 profile 在不同 Pigsty release 可能演进，升级后要 diff。

pg_parameters 显式覆盖

pg-test:
  hosts:
    10.10.10.11:
      pg_seq: 1
      pg_role: primary
    10.10.10.12:
      pg_seq: 2
      pg_role: replica
      pg_parameters:
        recovery_min_apply_delay: '5min'
  vars:
    pg_cluster: pg-test
    pg_conf: oltp.yml
    pg_parameters:
      log_min_duration_statement: 250
      track_io_timing: on

用途：

template baseline
  + reviewed cluster exception
      + reviewed instance exception

不是把所有 template 输出再复制一遍。重复复制会失去 template upgrade 能力。

inventory precedence

Pigsty 的 inventory 可以在 global、cluster、host 定义 pg_parameters，越具体的 inventory 变量覆盖越通用。还要叠加 PostgreSQL 自己的 file/DCS/catalog/session precedence。

因此两层问题：

Ansible variable resolution
  -> rendered configuration
      -> PostgreSQL/Patroni precedence
          -> effective session value

只看 YAML 不能证明最后生效。

list 参数的 YAML quoting

pg_parameters:
  shared_preload_libraries: 'timescaledb, pg_stat_statements, auto_explain'
  search_path: '"$user", public, app'

list-like GUC 必须作为一个 string 正确引用，避免 YAML 误解析或引号层级错误。render 后还要用 pg_file_settings 检查。

database 与 role 参数

某个 workload 特有的：

statement_timeout
work_mem
max_parallel_workers_per_gather
search_path
default_transaction_read_only

优先落到 Pigsty business object 的 database/user parameter，而不是 cluster global。 它们最终进入 pg_db_role_setting，新 session 生效。

scope 要匹配：

all workload on cluster     cluster/instance
one database                database
one application identity    role-in-database
one job                     transaction/session

参数 exception 的元数据

YAML 本身不保存“为什么”。在 ADR/注释/变更系统记录：

parameter: work_mem
scope: role dbuser_report in database analytics
value: 256MB
reason: report-v4 spill experiment
evidence_run: ...
owner: data-platform
expires/review: 2026-10-01
rollback: 32MB

没有 expiry 的 exception 会永久累积。

27.6.2 区分 reload、restart 与滚动执行

先从 context 生成动作

SELECT
    name,
    context,
    setting,
    pending_restart
FROM pg_settings
WHERE name = ANY (ARRAY[
    'work_mem',
    'checkpoint_timeout',
    'shared_buffers',
    'max_connections',
    'shared_preload_libraries'
])
ORDER BY name;

动作矩阵：

应用 pg_parameters

Pigsty 官方当前给出的 instance 参数应用入口：

./pgsql.yml -l pg-test -t pg_param

它会把 pg_parameters 渲染到受管配置。执行前：

确认 inventory 与 limit
查看 playbook version/help
做 diff/preview
确认 target 是 cluster 而非全环境
确认 secrets 不进命令行/log

执行后仍需 reload/restart 语义验证；“Ansible changed=1”不是 effective。

Patroni dynamic configuration

Patroni 的 cluster dynamic config 存在 DCS，由所有 member 消费；local config 又可能 覆盖 DCS。改变 Patroni 管理的 PostgreSQL 参数时要识别 owner。

Pigsty/Patroni 文档说明：

• dynamic config 会传播到成员；
• 非 restart 参数随后 reload；
• postmaster 参数会标 pending_restart/restart_pending；
• local Patroni config 可能优先于 dynamic；
• bootstrap DCS config 只用于初始建群，之后应改 dynamic config。

不要只编辑最初 inventory 里的 bootstrap fragment，期待现有 DCS 自动变化。

reload

集群：

pg reload pg-test

本机 PostgreSQL：

pig pg reload

命令面与版本有关，执行前看 --help。两者 scope 不同：一个通过 Patroni 面向 cluster/member，一个是本机 service 操作。

reload 后：

SELECT pg_reload_conf();

返回 true 只表示 signal 发出，不等于每项应用成功。查：

SELECT * FROM pg_file_settings WHERE error IS NOT NULL;
SELECT name, setting, source, pending_restart
FROM pg_settings
WHERE name IN (...);

restart

restart 会断开本 member 的 session；HA cluster 中可能由 replica 承载重启，但：

• primary restart 仍需 switchover/connection behavior；
• replica 重启时丧失一份冗余；
• catch-up 产生 I/O/WAL load；
• sync quorum 可能变化；
• pool/client 会 reconnect；
• session state/prepared statement 消失。

执行前必须有：

healthy replica count
lag within gate
backup/archive healthy
N+1 capacity
restart duration/RTO
client retry budget
rollback restart time

immediate restart 会触发 crash recovery，不是普通快速捷径。

rolling 顺序

典型而非万能：

1. one replica
2. wait streaming/caught-up + effective validation
3. next replica
4. controlled switchover if primary must change
5. former primary
6. end-to-end service validation

每一步 gate：

Patroni role/state
timeline/LSN
replication slots
archive
service route
pending_restart
SLO/resource

若 candidate 导致 member 起不来，不应继续下一个。

mixed-config window

滚动期间：

member A candidate
member B baseline

要回答：

• replication compatible？
• failover 到 A/B 各如何？
• read route 结果/性能不同？
• logical worker/preload plugin compatible？
• monitoring/alert 能区分？
• rollback 是否仍可启动？

若不能容忍 mixed state，就不能称为 rolling change，需要 maintenance/migration design。

canary member 的局限

在 replica 测 work_mem/planner 参数：

• read-only workload 可以；
• primary write/WAL/commit 行为不能；
• cache、data freshness、route 不同；
• replica conflict/recovery 干扰；
• promote 后 workload 变化。

canary 必须代表目标 mechanism。

27.6.3 用 SQL 和文件事实验证最终生效值

desired inventory

保存：

git commit
inventory path
cluster/member limit
profile and explicit overrides
render/playbook version
review/approval

不要在 public artifact 中发布 secret inventory。

configured file

SELECT
    sourcefile,
    sourceline,
    seqno,
    name,
    setting,
    applied,
    error
FROM pg_file_settings
WHERE name IN (...) OR error IS NOT NULL
ORDER BY seqno;

它能发现：

syntax error
unknown parameter
invalid value
duplicate overridden entry
restart-required entry not applied to runtime

但 view 反映 file 当前内容，不是 last applied。

effective server/session

SELECT
    inet_server_addr() AS server,
    current_setting('cluster_name') AS cluster,
    pg_is_in_recovery() AS in_recovery,
    name,
    setting,
    unit,
    context,
    source,
    pending_restart
FROM pg_settings
WHERE name = ANY (ARRAY[
    'shared_buffers',
    'work_mem',
    'max_connections',
    'checkpoint_timeout',
    'max_wal_size',
    'plan_cache_mode'
])
ORDER BY name;

每个 member、每种 service path、新旧 session 都要取样。

normalized units

比较时用 canonical bytes/ms：

SELECT
    name,
    current_setting(name) AS display,
    setting,
    unit
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'checkpoint_timeout');

setting=62592, unit=8kB 与 489MB 可能同值。字符串 diff 会制造假 drift。

Patroni/HA fact

cluster config in DCS
member pending_restart
member role/state/timeline/lag
PostgreSQL effective setting

四层要对齐。DCS 有 candidate 但 member 仍 pending restart，不算完成；PostgreSQL effective candidate 但 inventory 仍 baseline，也不算完成。

workload fact

变更生效不等于 hypothesis 成立。继续验证：

objective
non-regression
resource
failure/recovery
observation window

第 27 章实验同时保存：

• global settings before/after；
• session requested/effective plan_cache_mode；
• prepared custom/generic count；
• representative plan-shape hash；
• raw transaction latency；
• cleanup。

所以能证明：

candidate was actually tested
global config was not changed
auto already selected generic after early custom plans
material gain rule did not pass

验收矩阵

少任意一层，都只能标记 partially applied 或 pending。

配置变更的最终原则

Pigsty template gives a reviewed starting point
IaC gives repeatability
Patroni gives HA-aware distribution
PostgreSQL views give runtime truth
experiment gives causal confidence
ADR gives memory and accountability

任何单层都不能替代其余层。

上一节：参数作用域与变更方式 · 返回本章目录 · 下一节：实战：只调一个已证实的瓶颈 · 查看全书目录 · 查看索引中心

27.7 实战：只调一个已证实的瓶颈

本节从第 26 章继续，不新造一个“参数一改、TPS 翻倍”的玩具例子。

第 26 章已经证明：

c8 server work ratio   about 84%
c8 client work ratio   below 30%
throughput vs c1       about 1.9x
p95 vs c1              about 4.4x
temp bytes             0
failures/late          0
exact knee             unknown
production TPS         null

可确认的是：

• c8 进入较高 server CPU/queue 区域；
• load generator 没有先饱和；
• 并发提高的 tail 代价很大。

不可确认的是：

• CPU 花在 planning 还是 execution；
• I/O、WAL 或 lock 是否已经限制 throughput；
• 哪个 GUC 能改善；
• c8 是否是 knee。

严格说，第 26 章只证实了 service-center pressure，没有证实 parameter-specific root cause。所以本节的正确目标不是“必须调成”，而是只允许 一个可逆参数假设进入 A/B，并接受拒绝。

27.7.1 从 ch26 证据提出参数假设

候选矩阵

这张表的关键不是挑中了 plan_cache_mode，而是拒绝了五项不能归因的变化。

hypothesis

question: >
  Does forcing generic plans materially improve the prepared OLTP mix
  without changing representative plan shapes or tail latency?
parameter: plan_cache_mode
baseline: auto
candidate: force_generic_plan
mechanism: avoid repeated custom planning
scope: benchmark session only
apply: PGOPTIONS
rollback: session end

预测若成立：

auto repeatedly custom-plans
  -> visible planning CPU
force generic
  -> same plan shapes
  -> lower CPU/service demand
  -> stable TPS gain
  -> no tail regression

可证伪点：

auto already changes to generic
planning is too small
generic shape differs by key
TPS gain is noise
tail latency regresses

为什么不用 pg_stat_statements total planning time 直接判

它可以作为证据，但：

• track_planning policy/overhead；
• shared cumulative state；
• prepared custom/generic lifecycle；
• queryid aggregation；
• workload 重叠；
• planning 减少不一定转成 end-to-end SLO。

所以本实验直接测 client outcome，并用 prepared counters/plan probe 解释机制。

作用域

PGOPTIONS="-c plan_cache_mode=auto" pgbench ...
PGOPTIONS="-c plan_cache_mode=force_generic_plan" pgbench ...

不使用：

ALTER SYSTEM
pg_parameters
Patroni DCS
reload/restart
ALTER ROLE

因为本轮只回答 hypothesis，不创建持久 desired state。

safety target

environment  pg36-l2-vagrant teaching sandbox
client       pg-meta-1
server       pg-test-1 primary
database     pg36_tuning
role         dbuser_pg36tune
data         synthetic only
risk         L2 bounded

exercise 会真实制造 CPU/I/O/WAL/replication load，不能在 production 运行。

一次性连接边界

临时 database 创建为：

ALLOW_CONNECTIONS false
  -> REVOKE CONNECT FROM PUBLIC
      -> GRANT CONNECT only to disposable benchmark role
          -> ALLOW_CONNECTIONS true

为什么多这一步？

Pigsty exporter 可自动发现 database 并建立观测连接。若先开放 PUBLIC，再跑数分钟， exporter 的 idle connection 会让普通 DROP DATABASE 安全拒绝。runner 不应：

DROP DATABASE ... WITH (FORCE)
terminate unrelated monitoring sessions

先收紧 connect privilege，从源头避免 observer race。

database 与 role 都写：

pg36-ch27-disposable-tuning-fixture-v1:<run-id>

清理精确比对 marker；只允许等待 autovacuum 自然退出，不强杀。

数据与 workload

M scale：

customers          80,000
products           16,000
historical orders  800,000

mix：

每个 arm：

8 clients / 2 jobs
prepared protocol
closed-loop / zero think time
5-second natural warm-up
12-second measured run
250ms latency limit
max-tries=1

这组短 run 仍是教学证据，不是 production capacity characterization。

配对和 counterbalance

r1 auto  -> force, same seed
r2 force -> auto,  same seed
r3 auto  -> force
r4 force -> auto
r5 auto  -> force

每次 measured run 前：

• truncate live orders；
• reset inventory data；
• 不 reset shared statistics；
• 不 drop cache；
• 不 force checkpoint；
• 不 restart；
• 不暂停 autovacuum/replication/archive。

每对相同 seed 降低 workload sampling noise；顺序交错降低时间趋势偏差。

predeclared rule

candidate 进入更大 canary 的必要条件：

failure/late/skipped = 0
representative plan shapes equal
global settings unchanged
candidate pooled p95 / auto pooled p95 <= 1.05
paired TPS ratio bootstrap 95% lower >= 1.02

最后一条同时表达：

gain is material
gain is supported under measured repetition uncertainty

通过也只代表 “candidate-worthy-for-larger-canary”，不是 production apply。

27.7.2 比较收益、副作用和故障恢复

运行

静态检查：

static/labs/ch27/task.sh lint

完整实验：

export PG36_EVIDENCE_DIR=/absolute/private/new-empty/ch27-run
static/labs/ch27/task.sh all

PG36_EVIDENCE_DIR 必须是不存在或为空的私密绝对目录。all：

capture L0 preflight
  -> exercise L2
      -> verify L0
          -> review L0

正式 run identity

run id         efb7efec-beb2-4237-8535-6864de2a2e4e
preflight      d06c94b2-efb0-4688-86bf-b4e6ca92b90f
measured runs  10
transactions   357,685
raw tx logs    20

所有 run：

failures    0
late        0
skipped     0
deadlocks   0
temp bytes  0

arm 聚合

latency 单位 ms。p50/p95/p99 从每个 arm 的 raw transaction sample 合并后重算。

为什么不能用 median TPS 除法

直接：