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：

$$ M_{\text{query work}} \approx \sum_{o \in \text{concurrent operations}} L_o \times P_o $$

其中：

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

workload envelope：

$$ M_{\text{workload}} \approx \sum_{q} A_q \times O_q \times P_q \times L_q $$

$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。

方法：

从 EXPLAIN (ANALYZE, BUFFERS, SETTINGS) 找真实 node、batch、disk；
用 concurrency trace 找同时 active 的 query class；
在 staging 观测 process/container memory；
注入 worst credible concurrency；
留 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 作为上限。第一阶预算：

$$ M_{\text{autovacuum}} \le \text{autovacuum_max_workers} \times \text{effective autovacuum 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 没有额外资源。

其他后台预算

组件	参数/上限	风险
logical decoding	`logical_decoding_work_mem` × decoder	spill/WAL retention
temp table	`temp_buffers` per session, on demand	many sessions
WAL buffers	`wal_buffers` shared	start-time/shared
worker process	`max_worker_processes`	extensions + parallel
prepared xact	`max_prepared_transactions` shared structures	lock/WAL lifetime
replication	sender/receiver/plugin	queue/buffer/plugin
backup	pgBackRest process/buffer/compression	DB 外 host memory
monitoring	exporter/query	DB 外或 backend memory

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 风险必须用最坏并发估算

总预算

一个实用模型：

$$ M_{\text{host}} \ge M_{\text{shared}} + M_{\text{active backends}} + M_{\text{query work}} + M_{\text{maintenance}} + M_{\text{replication/extensions}} + M_{\text{platform}} + M_{\text{kernel}} + H $$

$H$ 是 safety headroom。

active backend：

$$ M_{\text{active backends}} \approx \sum_q A_q \times (B_q+W_q) $$

$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

参数变更前的内存表

项目	credible concurrency	evidence
shared memory	1	`pg_shmem_allocations`
OLTP work		plans + active
report work		spill test
parallel		workers launched
autovacuum		workers
DDL/restore		runbook
platform/kernel	1 host	OS/Pigsty
headroom		failure model

表填不完整时，不要把 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、检查点与写入平滑 · 查看全书目录 · 查看索引中心

最后更新于 2026-07-30

27.1 调优是一套实验方法 27.3 WAL、检查点与写入平滑

27.2 内存预算

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

PostgreSQL 不是绕过 OS 的单一 cache

shared_buffers 是启动参数

为什么不是越大越好

cache hit 不等于不读磁盘

effective_cache_size 不分配内存

huge page 与 transparent huge page

shared memory inventory

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

work_mem 是 operation base limit

$$ M_{\text{hash limit}}

放大公式

参考沙箱的反例

$$ 500\times64\text{MiB}

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

plan nodes 不一定同时达到 peak

看 sort 与 hash 是否真的 spill

temp_bytes=0 的正确结论

用 scope 区分 OLTP 与分析

temp_file_limit 是 guard，不是内存预算

27.2.3 maintenance、autovacuum 与后台进程内存

maintenance 不是一个 session

autovacuum 放大

parallel maintenance 的语义不同

其他后台预算

backend memory context

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

总预算

credible worst case

OOM 可能杀谁

swap 不是免费 headroom

pool 是 memory governor

参数变更前的内存表

何时接受

`shared_buffers` 是启动参数

`effective_cache_size` 不分配内存

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

`work_mem` 是 operation base limit

`temp_bytes=0` 的正确结论

`temp_file_limit` 是 guard，不是内存预算