数据库基础体系 · 第 103/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

PostgreSQL 参数与性能：内存、WAL、Planner、连接和 Autovacuum

PostgreSQL 的性能参数很少是“调大就更快”的独立旋钮。一个参数通常改变的是某个组件的资源边界，随后通过共享内存、操作系统缓存、WAL、锁、后台进程或统计信息影响其他组件。

例如：

work_mem 增大，可能减少排序落盘，但并发排序过多时会耗尽内存；
shared_buffers 增大，可能减少数据文件读取，但不会直接提高所有查询速度；
max_connections 增大，可能缓解连接拒绝，却会增加进程、内存和锁管理压力；
autovacuum 变慢，短期内可能减少 I/O，长期却会造成表膨胀、索引膨胀和事务 ID 回卷风险；
random_page_cost 改小，可能让 Planner 更愿意使用索引，但错误的代价模型也可能使大量查询选择更差的执行计划。

本文以 PostgreSQL 官方稳定版本公开语义为准。除特别说明外，讨论单个 PostgreSQL 实例；复制、云厂商托管参数和连接池会改变部分边界。示例默认使用普通表、读写事务和常见的 Linux 部署，但参数语义属于 PostgreSQL 本身。

一、先建立整体模型：一次查询会经过哪些资源边界

一个典型查询的路径可以简化为：

客户端连接
    ↓
PostgreSQL backend 进程
    ↓
Planner 读取统计信息并选择计划
    ↓
Executor 执行扫描、连接、排序、聚合
    ↓
shared_buffers / 操作系统页缓存 / 磁盘
    ↓
必要时生成 WAL
    ↓
事务提交等待 WAL 刷盘或复制确认

后台还有另一条持续运行的路径：

autovacuum launcher
    ↓
autovacuum worker
    ↓
扫描表、清理死元组、更新统计信息、推进冻结状态

因此，性能问题至少需要区分以下几类时间：

计划时间：Planner 计算执行计划所需的时间。
CPU 时间：执行表达式、连接、排序、聚合等所需的 CPU。
数据访问时间：从 shared_buffers、操作系统缓存或存储设备获取页面。
WAL 时间：生成、复制和刷写 WAL 的时间。
锁等待时间：等待行锁、表锁、事务结束或其他资源。
后台维护时间：Vacuum、Analyze、Checkpoint、归档和复制等任务消耗的资源。

调参之前，必须先确认瓶颈属于哪一类。否则，把 WAL 等待问题误判为内存不足，或把统计信息错误误判为索引缺失，都会得到表面有效、长期失控的结果。

二、内存：共享内存、进程内内存和缓存估计

2.1 PostgreSQL 中的内存不是一个池

PostgreSQL 的内存主要分为三类。

2.1.1 共享内存

shared_buffers 是 PostgreSQL 共享缓冲区，用于缓存表、索引等数据页。多个 backend 可以访问这部分缓存。

SHOW shared_buffers;

如果页面已经在 shared_buffers 中，执行器可以直接访问；如果不在，则 PostgreSQL 通常需要从操作系统文件缓存或存储设备获取页面。

shared_buffers 并不等于 PostgreSQL 使用的全部内存，也不等于操作系统页缓存。

2.1.2 每个 backend 或每个操作的内存

每个连接通常对应一个 PostgreSQL backend 进程。backend 可能为排序、哈希连接、哈希聚合、位图操作等分配工作内存。

其中最容易误解的是 work_mem：

work_mem 是单个查询操作在写临时文件之前可使用的内存上限，不是单个连接、单个查询或整个实例的总上限。

一个查询可能同时拥有多个需要工作内存的节点。例如：

Hash Join
 ├─ Hash
 ├─ Sort
 └─ Aggregate

如果一个复杂查询同时执行多个排序或哈希操作，可能分别消耗 work_mem。

并行查询还会让每个 worker 分别拥有工作内存，因此粗略估算应考虑：

M_{\text{work}} \approx C \times O \times W \times \text{work\_mem}

其中：

$C$ ：同时执行这类查询的 backend 数量；
$O$ ：每个查询中可能同时活跃的内存操作数；
$W$ ：参与执行的进程数量，非并行查询通常接近 1，并行查询可能大于 1；
work_mem：每个操作的内存边界。

这不是 PostgreSQL 的严格内存分配公式，而是容量估算的上界模型。实际使用量取决于数据量、执行路径和实现细节。

2.1.3 后台维护进程的内存

maintenance_work_mem 用于 VACUUM、CREATE INDEX、ALTER TABLE ... ADD FOREIGN KEY 等维护操作。它不是普通查询的 work_mem。

Autovacuum worker 默认可以使用由 autovacuum_work_mem 指定的内存；如果该参数为 -1，则使用 maintenance_work_mem。

需要注意：

maintenance_work_mem × 同时运行的维护任务

也可能形成较大的总内存占用。特别是 autovacuum worker 数量增加后，不能只看单个 worker 的参数值。

2.2 `shared_buffers` 与操作系统缓存的关系

PostgreSQL 访问一个数据页时，可能经历：

页面已在 shared_buffers 中；
页面不在 shared_buffers，但文件页面仍在操作系统页缓存中；
页面不在两级缓存中，需要从存储设备读取。

因此：

增大 shared_buffers 可以减少 PostgreSQL 自己的缓存缺失；
操作系统仍可能缓存 PostgreSQL 数据文件；
effective_cache_size 不是实际分配的缓存；
PostgreSQL 不会因为 effective_cache_size 增大就立刻占用更多内存。

effective_cache_size 是 Planner 对“一个查询可利用的缓存规模”的估计，通常包括 PostgreSQL 缓冲区以及操作系统可能保留的文件缓存。它影响成本估算，不是内存限制。

例如，Planner 估计索引扫描需要随机读取很多页面。如果 effective_cache_size 较大，Planner 可能认为更多索引页已经在缓存中，索引路径的估计成本会下降。但这只是估计；如果机器实际内存不足，查询仍可能发生大量物理 I/O。

2.3 `work_mem` 的溢出过程

排序超过 work_mem 时，PostgreSQL 通常会使用临时文件进行外部排序，而不是无限增长内存。

可以用下面的 SQL 构造一个可能需要排序的查询：

CREATE TABLE sort_demo AS
SELECT
    g AS id,
    md5(g::text) AS payload
FROM generate_series(1, 1000000) AS g;

ANALYZE sort_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM sort_demo
ORDER BY payload;

如果内存不足，计划中可能出现类似：

Sort Method: external merge  Disk: ...

如果内存足够，可能出现：

Sort Method: quicksort  Memory: ...

这里有三个重要边界：

work_mem 控制的是排序节点的工作空间，不是结果集大小；
EXPLAIN (ANALYZE) 中的 Memory 反映某个节点的实际信息，不代表整个查询内存；
临时文件大小不一定等于所需内存大小，排序算法会产生中间文件和额外结构。

可以临时提高单个会话的排序内存：

BEGIN;

SET LOCAL work_mem = '256MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM sort_demo
ORDER BY payload;

ROLLBACK;

SET LOCAL 只在当前事务内生效，事务结束后恢复。它比直接修改全局配置更适合验证单个问题，但仍要确认该事务不会同时执行大量并发操作。

2.4 哈希操作与 `hash_mem_multiplier`

哈希连接和哈希聚合使用 work_mem，并可能受到 hash_mem_multiplier 影响。该参数允许哈希操作使用相对于 work_mem 的更高内存倍数。

这不是“所有查询内存乘以该值”，而是针对哈希相关节点的内存策略。提高它可能减少哈希批次和临时文件，但也会增加并发场景下的内存风险。

当执行计划出现：

Batches: 1

通常表示哈希表没有分批；出现更大的批次数，说明哈希数据无法在预计内存中一次容纳。批处理可能增加磁盘 I/O 和 CPU。

不过，不能仅凭“批次数大”就直接提高参数。还应检查：

统计信息是否准确；
Planner 是否错误估计了行数；
连接条件是否合理；
是否存在数据倾斜；
并发是否允许提高内存。

2.5 内存参数的生命周期

参数修改方式取决于 context：

SELECT name, setting, unit, context, pending_restart
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'work_mem',
    'maintenance_work_mem',
    'autovacuum_work_mem',
    'effective_cache_size'
);

常见 context 含义：

user：用户可以通过会话级 SET 修改；
sighup：修改配置后 reload 生效；
postmaster：需要重启实例；
superuser 或类似限制：只有特定权限可以设置。

例如：

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

ALTER SYSTEM 会修改 postgresql.auto.conf，适合需要持久化的实例配置，但不应把临时实验直接写入全局配置。实验性修改最好先使用 SET 或 SET LOCAL。

三、WAL：持久性、恢复和写放大的共同约束

3.1 WAL 解决什么问题

WAL，即 Write-Ahead Logging，写前日志，核心规则是：

在数据页写入持久存储之前，描述该数据页变化的 WAL 必须先持久化到满足事务语义的介质。

假设事务修改了页面 P：

1. 修改内存中的页面 P
2. 生成描述修改的 WAL 记录
3. 事务提交时等待必要的 WAL 刷盘
4. 以后页面 P 可以再被写入数据文件

数据库崩溃后，恢复过程从 WAL 中重放已经记录但尚未写入数据文件的修改。

WAL 因此同时影响：

事务提交延迟；
Checkpoint 和恢复时间；
主从复制延迟；
归档容量；
更新、删除、索引维护的写放大。

WAL 不是普通日志文件。删除、更新、索引页变化等操作会产生 WAL；即使最终数据文件尚未刷新，WAL 也可能已经消耗大量存储带宽。

3.2 WAL 缓冲区与提交路径

wal_buffers 是用于 WAL 的共享内存缓冲区。大多数负载不需要频繁手工调整它，但高并发写入可能使 WAL 生成速度很高。

事务提交时，行为受 synchronous_commit 影响：

on：提交通常等待 WAL 刷到持久存储；
off：服务器可以较早向客户端报告提交成功，WAL 刷盘由后台过程完成；崩溃时，最近事务可能丢失，但数据库不会因此产生逻辑不一致；
local：在当前服务器语义下类似同步提交，但不等待远程同步副本；
remote_write、remote_apply 等模式还涉及同步复制下远程 standby 的写入或应用状态。

off 不是“关闭 WAL”，也不是“允许数据库损坏”。它改变的是客户端收到提交成功响应的时机和崩溃丢失窗口。

示例：

BEGIN;

SET LOCAL synchronous_commit = off;

INSERT INTO event_log(event_time, payload)
VALUES (clock_timestamp(), 'best effort');

COMMIT;

这类设置适合可以接受少量崩溃丢失的日志或批量数据。它不适合订单确认、资金扣款等必须在响应成功时保证持久性的事务。

3.3 Checkpoint 为什么影响性能

Checkpoint 将一定范围内的脏数据页写入数据文件，并记录恢复起点。相关参数包括：

checkpoint_timeout：两次自动 checkpoint 的时间上限；
max_wal_size：触发 checkpoint 的 WAL 使用规模目标之一；
checkpoint_completion_target：尝试把 checkpoint 写入工作分散到 checkpoint 间隔中的比例；
full_page_writes：在 checkpoint 后页面第一次修改时，是否写入完整页面镜像，以防止部分页面写入造成损坏。

如果 checkpoint 过于频繁，可能出现：

checkpoint starting: time
checkpoint complete: wrote ... buffers ...

并在日志中看到 checkpoints are occurring too frequently 一类提示。频繁 checkpoint 会造成：

更多脏页集中写出；
checkpoint 后更多页面需要产生 full-page image；
写 I/O 和 WAL 生成同时升高；
查询和提交受到存储带宽竞争。

可以通过以下视图观察 checkpoint：

SELECT *
FROM pg_stat_checkpointer;

具体列会随版本演进，生产脚本应按目标版本确认列名。传统版本中也可从 pg_stat_bgwriter 看到部分相关统计。

查看 WAL 统计：

SELECT *
FROM pg_stat_wal;

可以重点关注 WAL 生成量、WAL 缓冲区写入和 WAL 写入时间等指标。不要直接把“WAL 多”解释成“WAL 配置错误”：大量更新、索引维护、全页写入和复制需求都可能是合理来源。

3.4 `full_page_writes` 与数据安全边界

存储设备或操作系统可能以小于 PostgreSQL 页面大小的单位写入页面。如果数据库在页面写入过程中崩溃，数据文件可能包含“半个旧页面和半个新页面”。

full_page_writes 通过在 checkpoint 后第一次修改页面时写入完整页面镜像，使恢复过程可以用 WAL 中的完整页面覆盖损坏页面。

关闭它可能减少 WAL，但会破坏 PostgreSQL 针对部分页面写入的保护假设。除非底层存储明确提供等价保证并经过严格验证，否则不能把它当作普通性能开关。

3.5 用 WAL 速率估算容量

如果实例每秒产生 $R_{\text{wal}}$ 字节 WAL，保留窗口为 $T$ 秒，则仅按生成速率估算：

S_{\text{wal}} \approx R_{\text{wal}} \times T

实际所需空间还要加上：

复制槽未消费的 WAL；
归档失败；
备份窗口；
峰值写入；
checkpoint 和全页写入波动；
监控和故障处理余量。

复制槽尤其危险。某个 standby 或逻辑复制消费者停止消费时，主库可能持续保留所需 WAL。检查复制槽：

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

不要随意删除复制槽。删除前必须确认对应消费者已经废弃，否则会破坏该复制链路的恢复能力。

3.6 WAL 性能问题的诊断路径

当写入延迟升高时，可以按因果顺序检查：

SELECT pid, wait_event_type, wait_event, state, query
FROM pg_stat_activity
WHERE wait_event IS NOT NULL;

如果等待事件与 WAL 写入或刷盘有关，再结合：

SELECT *
FROM pg_stat_wal;

以及数据库日志中的 checkpoint、归档和复制信息判断：

是磁盘刷盘本身慢；
是 checkpoint 集中写出；
是同步复制副本延迟；
是归档命令失败；
是复制槽造成 WAL 堆积；
还是事务本身产生了异常大量 WAL。

提高 wal_buffers 或关闭 fsync 并不能解决所有这些问题。fsync=off 会允许 PostgreSQL 在操作系统或硬件故障时丢失数据甚至造成数据库损坏，生产系统不应以此换取性能。

四、Planner：成本模型、统计信息和执行计划

4.1 Planner 不执行查询，它预测查询

Planner 根据 SQL、表统计信息、索引、约束和参数配置生成候选路径，并估算成本。成本不是毫秒，而是相对单位。

常见成本参数包括：

seq_page_cost：顺序读取一个页面的估计成本；
random_page_cost：随机读取一个页面的估计成本；
cpu_tuple_cost：处理一个元组的估计 CPU 成本；
cpu_index_tuple_cost：处理索引元组的估计成本；
cpu_operator_cost：执行操作符的估计成本；
effective_cache_size：可被查询利用的缓存规模估计。

Planner 的目标不是证明某个计划实际最快，而是在成本模型下选择估计成本最低的计划。

4.2 从选择率到计划成本

假设表有 $N$ 行，谓词选择率为 $s$ ，则 Planner 估计结果行数约为：

\hat{R} = N \times s

如果统计信息认为 status = 'paid' 的选择率为 1%，表有 1,000,000 行，那么估计结果约为 10,000 行。

索引路径通常需要：

读取索引页；
找到匹配元组；
读取对应堆表页面；
执行过滤和投影。

顺序扫描通常需要读取大量表页面，但访问模式连续。于是，某个谓词是否使用索引，不由“有索引”单独决定，而取决于：

估计结果行数；
表和索引的物理规模；
页面相关性；
缓存估计；
随机读取和 CPU 成本；
是否可以使用 Index Only Scan；
是否需要回表过滤。

反例：低选择率不一定适合索引

如果一个布尔列只有两种值，查询：

SELECT *
FROM orders
WHERE is_deleted = false;

若绝大多数行都是 false，索引会找到大量行，随后仍需访问大量堆表页面。顺序扫描可能更快。

反例：有索引也可能无法用于表达式

CREATE INDEX orders_created_at_idx
ON orders(created_at);

下面的谓词与索引列直接比较：

WHERE created_at >= now() - interval '1 day'

而下面的写法对列应用函数：

WHERE date(created_at) = current_date

后者可能无法使用普通 created_at 索引完成理想的范围扫描。可以改写为时间范围，或建立与表达式匹配的表达式索引。关键不是“Planner 不喜欢索引”，而是索引键的排序结构是否能支持该谓词。

4.3 统计信息决定选择率

ANALYZE 会收集列的统计信息，例如：

最常见值；
直方图边界；
不同值数量估计；
NULL 比例；
扩展统计信息中的多列依赖或相关性。

查看表和列统计：

SELECT
    schemaname,
    tablename,
    last_analyze,
    last_autoanalyze,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

SELECT
    tablename,
    attname,
    null_frac,
    n_distinct,
    most_common_vals,
    most_common_freqs
FROM pg_stats
WHERE tablename = 'orders';

如果两个列存在相关性，单列统计可能错误估计组合条件：

WHERE country = 'CN'
  AND currency = 'CNY'

Planner 在缺少多列统计时，可能近似把两个选择率相乘；如果现实中 country 和 currency 高度相关，这个估计会偏离实际。可以考虑：

CREATE STATISTICS orders_country_currency_stats
    (dependencies, ndistinct, mcv)
ON country, currency
FROM orders;

ANALYZE orders;

创建扩展统计信息不会自动解决所有问题。必须先确认查询确实存在多列相关性，并通过 EXPLAIN 验证估计行数是否改善。

4.4 `EXPLAIN ANALYZE` 的正确读法

示例：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

重点比较每个节点：

rows=估计行数
actual rows=实际行数
loops=执行次数
Buffers: shared hit=... read=...

实际处理总行数大致是：

R_{\text{actual,total}} = \text{actual rows} \times \text{loops}

同样，某个节点 actual time 在有多次 loop 时，不能简单当作整个节点只执行了一次。

一个常见问题是：

Index Scan
  rows=10
  actual rows=100000

这说明 Planner 严重低估了行数，后续可能选择了嵌套循环，结果产生大量重复索引访问。

执行 EXPLAIN ANALYZE 会真正执行语句：

EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'archived'
WHERE ...

会修改数据。对写语句进行分析应使用事务包裹并回滚：

BEGIN;

EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'archived'
WHERE id = 123;

ROLLBACK;

但即使回滚，语句执行期间仍可能产生锁、WAL、临时文件和缓存扰动，因此不能在生产高峰期无条件运行。

4.5 成本参数不是执行时间校准器

把 random_page_cost 从 4 改成 1，并不意味着随机 I/O 真的变成了顺序 I/O 的同等速度。它只是告诉 Planner：“我认为随机访问的相对代价较低。”

错误降低该参数可能造成：

对低选择率估计错误的查询过度使用索引；
大范围扫描使用随机索引访问；
物理上相关性差的表产生大量随机 I/O。

参数应通过代表性查询和实际 EXPLAIN (ANALYZE, BUFFERS) 验证，而不是根据单条慢 SQL 直接全局修改。

4.6 预处理语句的 generic plan 与 custom plan

预处理语句可能使用：

custom plan：结合当前参数值生成计划；
generic plan：忽略具体参数值，复用一个通用计划。

如果数据分布倾斜，例如：

tenant_id = 1       有数百万行
tenant_id = 999999  只有几行

同一个 SQL 对不同租户的最佳计划可能不同。通用计划可能对大租户使用索引，对小租户又不理想，反之亦然。

可以在会话中测试：

SET LOCAL plan_cache_mode = force_custom_plan;

该参数适用于诊断或特定场景，不应在没有测量的情况下全局强制 custom plan，因为反复生成计划也有 CPU 和延迟成本。

五、连接：连接数不仅是并发数，也是进程和资源上限

5.1 PostgreSQL 的连接模型

传统 PostgreSQL 使用每个客户端连接对应一个 backend 进程的模型。连接建立后，backend 需要：

进程或会话资源；
本地内存上下文；
文件描述符；
事务和锁状态；
可能的 work_mem、临时表和临时文件；
与共享内存中锁表、事务状态等结构的交互。

因此，max_connections 不是简单的“允许多少用户登录”。

查看连接：

SELECT
    state,
    wait_event_type,
    wait_event,
    count(*)
FROM pg_stat_activity
GROUP BY state, wait_event_type, wait_event
ORDER BY count(*) DESC;

查看连接上限和保留槽位：

SHOW max_connections;
SHOW superuser_reserved_connections;

普通用户可用连接数大致受：

max_connections - 保留连接槽 - 已被其他会话占用的连接

限制。保留槽的目的，是让管理者在连接耗尽时仍有机会登录处理故障。

5.2 `idle` 与 `idle in transaction`

idle 表示连接当前没有执行查询，不等于连接泄漏。连接池可能有一批空闲连接等待复用。

idle in transaction 更危险：

SELECT pid, usename, xact_start, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

一个事务即使没有继续执行 SQL，只要没有提交或回滚，它仍可能：

持有锁；
阻止旧版本回收；
延长其他事务的可见性边界；
使 autovacuum 无法清理某些死元组；
造成表膨胀和事务 ID 冻结推进缓慢。

可以设置：

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';

这会在事务空闲超过时间后终止会话。风险是应用可能收到连接断开并需要重试，因此必须与应用事务重试和连接池策略配合。

statement_timeout 限制单条语句执行时间；lock_timeout 限制等待锁的时间；idle_in_transaction_session_timeout 针对的是事务处于空闲状态的时间。三者不能混为一谈。

5.3 为什么连接池通常比无限增加连接更有效

假设数据库允许 1,000 个连接，但实际只有 16 个 CPU 核、有限存储带宽和有限内存。1,000 个 backend 同时执行查询会造成：

CPU 上下文切换；
shared buffer 和本地内存竞争；
更多锁竞争；
更多并发排序和哈希；
存储队列堆积；
延迟尾部变长。

连接池把“客户端连接数”和“数据库活动会话数”分离：

大量客户端连接
        ↓
连接池排队与复用
        ↓
较少数据库 backend

但连接池有边界：

session pooling：一个客户端会话长期绑定一个数据库连接，兼容性较好，但复用程度较低；
transaction pooling：事务结束后连接归还池，复用率高，但依赖会话状态的功能需要额外处理，例如临时表、会话变量、某些预处理语句；
statement pooling：复用更激进，但对事务语义要求更高。

连接池不会消除数据库内部锁，也不会让单条查询更快；它主要控制并发和连接生命周期。

5.4 连接上限与内存估算

一个更现实的内存估算至少包括：

M_{\text{total}} \approx M_{\text{shared}} + C \times M_{\text{backend,base}} + C \times O \times W \times \text{work\_mem} + M_{\text{maintenance}} + M_{\text{OS}}

其中：

$M_{\text{shared}}$ ：共享内存，包括 shared_buffers 及其他共享结构；
$C$ ：活跃 backend 数；
$M_{\text{backend,base}}$ ：每个 backend 的基础开销；
$O$ 、 $W$ ：前文所述的操作数和 worker 数；
work_mem：单操作工作内存；
$M_{\text{maintenance}}$ ：维护进程消耗；
$M_{\text{OS}}$ ：操作系统和其他进程开销。

这个公式不是精确计费模型，但足以说明一个事实：

max_connections × work_mem 仍然低估了实际风险，因为单个连接可能同时执行多个内存操作，而且并行 worker、维护进程和共享内存也在消耗资源。

六、Autovacuum：MVCC 清理、统计信息和冻结安全

6.1 为什么 PostgreSQL 必须 Vacuum

PostgreSQL 使用 MVCC。更新一行时，通常不是直接覆盖旧版本，而是生成新版本，并让旧版本在仍可能被事务看到时保留。

简化状态如下：

事务 T1 更新行
    ↓
旧版本：xmin=旧事务，xmax=T1
新版本：xmin=T1
    ↓
T1 提交
    ↓
旧版本对未来事务不可见
    ↓
当不存在更老的快照时，旧版本才可回收

旧版本称为 dead tuple 的候选对象。它不能在生成新版本后立即删除，因为仍可能有长事务或旧快照需要读取它。

Vacuum 的主要职责包括：

回收已经对所有相关事务不可见的旧元组空间；
清理索引中指向不可见元组的项；
更新页面中的可见性信息；
推进元组冻结状态，防止事务 ID 回卷；
在适当情况下触发或配合统计信息更新。

Vacuum 通常把空间留给同一张表未来复用，不会自动把文件截断到操作系统并返还全部空间。

6.2 Autovacuum 的触发条件

Autovacuum launcher 周期性检查表，并启动 worker。对普通 vacuum，常见触发判断可以表示为：

D > \text{autovacuum\_vacuum\_threshold} + \text{autovacuum\_vacuum\_scale\_factor} \times R

其中：

$D$ ：自上次相关维护以来估计的 dead tuple 数量；
$R$ ：表的估计行数；
threshold：固定阈值；
scale factor：按表大小增长的比例。

例如：

表估计行数 R = 1,000,000
vacuum threshold = 50
vacuum scale factor = 0.2

触发阈值约为：

50 + 0.2 \times 1{,}000{,}000 = 200{,}050

如果表只有 1,000 行，默认比例项产生的阈值约为 250；如果表有 1 亿行，阈值会达到约 2,000 万。大型高更新表因此常需要单独设置较低的 scale factor。

Analyze 的触发判断使用另一组参数，核心形式类似：

U > \text{autovacuum\_analyze\_threshold} + \text{autovacuum\_analyze\_scale\_factor} \times R

这里的 $U$ 是自上次 Analyze 后发生变化的行数估计。Vacuum 触发和 Analyze 触发不是同一个条件；一张表可能需要频繁 Analyze，却暂时不需要清理大量 dead tuple，反之亦然。

较新的 PostgreSQL 版本还提供面向大量插入表的 vacuum insert threshold 和 scale factor，用于在主要由 INSERT 增长的表上更早维护可见性和相关状态。具体参数名和语义应以目标版本 pg_settings 和官方文档为准。

6.3 Autovacuum 的关键参数

常见参数包括：

autovacuum：是否启用自动维护；
autovacuum_max_workers：最多同时运行的 autovacuum worker 数；
autovacuum_naptime：launcher 检查表的间隔；
autovacuum_vacuum_threshold；
autovacuum_vacuum_scale_factor；
autovacuum_analyze_threshold；
autovacuum_analyze_scale_factor；
autovacuum_vacuum_cost_limit；
autovacuum_vacuum_cost_delay；
autovacuum_work_mem；
与冻结相关的 autovacuum_freeze_max_age 等参数。

autovacuum_max_workers 增大后，更多表可以并行维护，但总 I/O、CPU 和内存也会增加。worker 数不是越大越好，尤其是在存储带宽已经饱和时。

Vacuum cost delay 会让 Vacuum 主动休眠，以减少对前台查询的干扰；代价是清理速度变慢。不同版本对 autovacuum cost limit 的继承和分配有实现细节，生产调优应以目标版本文档为准，不应简单把全局 limit 除以 worker 数后当作严格行为。

6.4 每表设置通常比全局设置更安全

对写入频繁的大表，可以降低 scale factor：

ALTER TABLE orders
SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_analyze_threshold = 1000
);

假设表有 10,000,000 行，新的 Vacuum 阈值约为：

1000 + 0.02 \times 10{,}000{,}000 = 201{,}000

这比默认比例产生的阈值小得多，意味着更早启动维护。

但更早启动不等于更快完成。如果 worker 受 I/O 限制，低阈值可能导致维护任务频繁运行。应结合：

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze,
    vacuum_count,
    autovacuum_count,
    analyze_count,
    autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

观察 dead tuple 是否下降、维护是否持续追不上写入速度。

6.5 `VACUUM`、`VACUUM FULL` 和 `ANALYZE` 的区别

普通 `VACUUM`

VACUUM (VERBOSE, ANALYZE) orders;

普通 Vacuum 通常：

清理可回收旧版本；
尽可能复用表内空间；
持有的锁不会像 VACUUM FULL 那样阻塞所有普通访问；
不会把大部分表文件完全重写成紧凑布局。

`VACUUM FULL`

VACUUM FULL 会重写表，通常可以显著收缩文件，但需要更强的锁，并且需要额外磁盘空间。它不是日常 autovacuum 的替代品。

风险包括：

阻塞并发访问；
需要容纳旧表和新表的额外空间；
重写期间产生大量 I/O 和 WAL；
大表执行时间长。

`ANALYZE`

ANALYZE 更新 Planner 统计信息：

ANALYZE orders;

它不负责清理 dead tuple。反过来，Vacuum 也不应被理解为一定会产生足够新的统计信息。更新频繁但数据分布改变明显的表，可能需要关注 autoanalyze 是否及时执行。

6.6 可见性映射与 Index Only Scan

Vacuum 会维护 visibility map。对某些页面，如果页面中的元组都对所有当前和未来事务可见，页面可以被标记为 all-visible。

Index Only Scan 依赖这类信息：

从索引取得所需列或元组定位信息；
检查 visibility map；
如果页面已标记 all-visible，可以不访问堆表；
否则仍需回表确认元组可见性。

因此，即使索引包含查询所需列，Index Only Scan 也不一定完全避免堆访问。频繁更新的表 visibility map 可能经常失效，Vacuum 及时性会直接影响 Index Only Scan 的实际收益。

6.7 冻结与事务 ID 回卷

PostgreSQL 的事务 ID 空间有限。旧事务 ID 如果无限增长并发生回卷，系统可能无法正确判断元组可见性。

冻结的基本思想是：当某个元组足够老，且确认不再有事务需要依赖其原始事务 ID 时，把它标记为冻结语义，使它不受事务 ID 回卷影响。

如果某些表长期不被 Vacuum，可能出现：

日志提示必须进行防回卷 Vacuum；
普通业务 Vacuum 被更紧急的冻结维护挤压；
最终为了安全，数据库拒绝执行可能导致回卷的事务。

检查冻结进度可以关注：

SELECT
    c.oid::regclass AS table_name,
    age(c.relfrozenxid) AS xid_age,
    age(t.relfrozenxid) AS multixact_age
FROM pg_class AS c
JOIN pg_namespace AS n
  ON n.oid = c.relnamespace
CROSS JOIN pg_database AS d
JOIN pg_class AS t
  ON t.relnamespace = n.oid
WHERE c.relkind IN ('r', 'm')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

上面的查询示意了 relfrozenxid 监控，但不同版本、对象类型和监控口径可能需要进一步细化。更直接的表级诊断还应结合数据库级年龄：

SELECT
    datname,
    age(datfrozenxid) AS xid_age,
    age(datminmxid) AS multixact_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

autovacuum_freeze_max_age 不是“超过该值就发生数据损坏”的阈值，而是触发更积极防回卷维护的控制点。绝不能通过无限增大它来掩盖 Vacuum 长期不运行的问题。

七、把参数联系起来：一个写入频繁表的完整分析

假设有订单表：

CREATE TABLE orders (
    id          bigint PRIMARY KEY,
    customer_id bigint NOT NULL,
    status      text NOT NULL,
    created_at  timestamptz NOT NULL,
    payload     jsonb
);

业务特点：

持续 INSERT；
订单状态频繁 UPDATE；
查询经常按 customer_id 和时间过滤；
高峰期连接数很多；
存储设备对随机 I/O 敏感。

7.1 先判断 Planner 是否估计错误

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, created_at
FROM orders
WHERE customer_id = 42
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

观察：

估计行数与实际行数差距；
是否使用合理的索引；
shared hit 和 shared read；
是否发生排序落盘；
是否产生大量 loop；
是否使用 Index Only Scan。

若估计严重错误，先执行：

ANALYZE orders;

如果 customer_id 与 status 相关，再考虑多列统计信息，而不是先修改 random_page_cost。

7.2 再判断内存是否是问题

如果计划中出现：

Sort Method: external merge

说明该排序使用了临时文件。可以在受控会话中测试：

BEGIN;
SET LOCAL work_mem = '64MB';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;

ROLLBACK;

如果排序不再落盘，应继续判断：

该查询是否高并发；
是否同时存在多个排序或哈希；
提高 work_mem 是否会让总内存超限；
是否更适合增加索引，减少排序数据量。

例如：

CREATE INDEX CONCURRENTLY orders_customer_created_idx
ON orders (customer_id, created_at DESC);

CREATE INDEX CONCURRENTLY 可以减少对普通读写的阻塞，但执行时间更长、会产生额外开销，并且不能放在事务块中。索引是否真正改善查询，仍需用 EXPLAIN (ANALYZE, BUFFERS) 验证。

7.3 然后观察更新带来的 dead tuple

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    last_autovacuum,
    autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'orders';

如果 n_dead_tup 持续增长，可能原因包括：

autovacuum 没有及时触发；
worker 不足；
Vacuum 受到长事务阻塞；
cost delay 过高；
表写入速度超过维护速度；
存储 I/O 饱和；
表级参数覆盖了预期全局设置。

检查长事务：

SELECT
    pid,
    usename,
    application_name,
    xact_start,
    state,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

这里不能只杀最老的会话。先确认它是否正在执行重要事务、是否属于备份或复制流程，并按应用重试能力设计恢复措施。

7.4 最后判断写入延迟是否由 WAL 或 Checkpoint 引起

SELECT
    pid,
    state,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE state <> 'idle';

再查看：

SELECT *
FROM pg_stat_wal;

如果 WAL 生成速率很高，应进一步拆分来源：

UPDATE 是否修改了大量索引；
是否频繁产生 full-page image；
是否存在批量 DELETE；
是否启用了同步复制；
归档是否积压；
复制槽是否长期不消费。

如果是提交等待 WAL 刷盘，应测量存储延迟和同步复制状态，而不是单纯扩大 shared_buffers。

八、常见误区与失败表现

8.1 “把 `work_mem` 调大，所有查询都会更快”

错误原因：

很多查询不需要排序或哈希；
低效 SQL 可能只是处理了过多行；
高并发下内存乘数效应会放大；
适当索引可能比更多排序内存更有效。

正确验证方式是查看具体计划是否发生临时文件溢出，并针对单个角色、数据库或会话测试。

8.2 “`effective_cache_size` 越大，数据库缓存越大”

它只影响 Planner 成本估计，不分配内存。设置过大可能使 Planner 过度相信数据在缓存中，从而选择实际 I/O 很重的路径。

8.3 “有索引就不该使用顺序扫描”

当结果集占表的大部分、随机回表成本高，顺序扫描可能是正确选择。判断标准是实际成本和数据访问模式，而不是计划节点名称。

8.4 “Autovacuum 会自动把表变小”

普通 Vacuum 主要让空间在表内部复用，并不保证把文件空间返还给操作系统。文件持续变大可能是：

更新和删除速度超过清理速度；
长事务阻止旧版本回收；
索引膨胀；
表本身持续增长；
需要重写才能紧缩物理文件。

不能因为磁盘占用变大就直接执行 VACUUM FULL。先确认阻塞源、dead tuple、索引大小和业务停机窗口。

8.5 “增加 `max_connections` 就能处理更多流量”

如果瓶颈是 CPU、锁或存储，更多连接只会增加排队和上下文切换。连接池通常更适合控制数据库内的并发活动数。

8.6 “`synchronous_commit=off` 等于没有事务安全”

它仍然生成 WAL，也仍然维护事务一致性；改变的是客户端收到提交确认和 WAL 持久化之间的窗口。这个窗口对不同业务的风险完全不同。

8.7 “关闭 `fsync` 可以解决写入慢”

fsync=off 会放弃 PostgreSQL 依赖的持久性保护，故障后可能丢失数据或损坏数据库。它只能用于可随时重建的数据、受控测试或临时基准实验，不能作为生产优化方案。

九、生产诊断的最小闭环

一个可重复的调优闭环应当包含：

9.1 记录配置和生效范围

SELECT
    name,
    setting,
    unit,
    source,
    sourcefile,
    sourceline,
    context,
    pending_restart
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'work_mem',
    'maintenance_work_mem',
    'effective_cache_size',
    'max_connections',
    'autovacuum_max_workers',
    'checkpoint_timeout',
    'max_wal_size',
    'synchronous_commit'
);

source 和 sourcefile 能帮助确认参数来自哪个配置文件、是否被 ALTER ROLE、ALTER DATABASE 或会话级设置覆盖。

9.2 记录查询计划和实际资源

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

SETTINGS 可以显示影响该查询的非默认 Planner 相关设置，适合排查“同一 SQL 在不同环境计划不同”的问题。

9.3 观察等待而不是只看平均耗时

SELECT
    wait_event_type,
    wait_event,
    count(*)
FROM pg_stat_activity
WHERE wait_event IS NOT NULL
GROUP BY wait_event_type, wait_event
ORDER BY count(*) DESC;

平均延迟无法说明查询是在：

CPU 上运行；
读取磁盘；
等待 WAL；
等待锁；
等待客户端发送下一批数据。

等待事件和 EXPLAIN (ANALYZE, BUFFERS) 必须结合使用。

9.4 观察表维护状态

SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    last_autovacuum,
    last_autoanalyze,
    autovacuum_count,
    autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

如果 dead tuple、查询估计误差和锁等待同时出现，问题可能不是三个独立问题，而是长事务导致 Vacuum 受阻，统计信息也长期未更新，最终 Planner 和存储访问共同恶化。

9.5 观察容量和复制约束

WAL 目录或归档空间异常增长时，同时检查：

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

某些列只存在于较新的版本，因此跨版本监控脚本需要根据目标版本适配。核心判断始终是：是否有复制槽、归档或备库阻止 WAL 回收。

十、最终的因果关系

这些参数可以用一条主线串起来：

连接数
  ↓
并发 backend 数
  ↓
work_mem、临时文件、CPU、锁竞争
  ↓
查询实际执行速度和尾延迟

统计信息、成本参数、缓存估计
  ↓
Planner 选择扫描、连接、排序和聚合方式
  ↓
CPU、内存、随机 I/O、WAL 产生量

INSERT / UPDATE / DELETE
  ↓
dead tuple、索引变化、WAL
  ↓
Autovacuum、Checkpoint、复制和归档压力
  ↓
表膨胀、查询计划、提交延迟和恢复时间

因此，参数调优的正确问题不是“某个参数应该设置成多少”，而是：

组件当前的状态是什么；
它依据什么条件进入这个状态；
哪个资源边界正在限制它；
修改参数后会把压力转移到哪里；
如何用计划、等待事件、统计信息、WAL 和 Vacuum 指标验证结果；
如果结果恶化，能否恢复原配置并处理已经产生的副作用。

PostgreSQL 性能优化最终不是参数数值竞赛，而是让 Planner 的估计、执行器的资源、WAL 的持久性路径、连接并发和 MVCC 维护保持一致。只有这些机制同时处于可控状态，单个参数的改动才可能稳定地产生收益。

系列导航与关联阅读

系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
上一篇：PostgreSQL 监控与诊断：pg_stat、锁、膨胀、慢查询和容量
下一篇：PostgreSQL PostGIS：空间类型、坐标系、空间索引和查询优化
延伸：PostgreSQL MVCC 与 Vacuum：Tuple 可见性、膨胀、冻结和诊断

官方资料

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。