PostgreSQL 查询、事务与 VACUUM：从执行计划到长期容量治理

PostgreSQL 查询、事务与 VACUUM 工程诊断图把执行计划误判、MVCC 版本链、锁等待、VACUUM 回收和容量增长放进同一条证据链。高清原图

查询只改了一个条件，为什么数据库像换了一台机器

周一上午，订单检索接口的 P95 从 80 ms 升到 4 s。应用版本没有变化，CPU 没有打满，连接池还有余量；值班同学执行同一条 SQL，有时走索引，有时扫全表。紧接着，批量更新任务开始变慢，磁盘每天多出几十 GB，VACUUM FULL 又因为拿不到锁无法执行。

这不是三个孤立故障。PostgreSQL 的查询稳定性由一条连续链路决定：

统计信息把数据分布描述错了，Planner 就可能选错计划；UPDATE 生成的新旧 tuple 让表和索引持续增长；长事务抬高全局可见性边界，VACUUM 即使运行也不能回收旧版本；可见性映射没有及时恢复，原本的 Index Only Scan 又会退化为大量 Heap Fetch。架构师排查时不能只盯一条慢 SQL，也不能只改一个参数。

先确认自己连接的实例、数据库和角色，避免在错误环境做真实执行：

SELECT version();
SELECT current_database(), current_user, inet_server_addr(), inet_server_port();
SHOW transaction_isolation;
SHOW search_path;

涉及 EXPLAIN ANALYZE、批量 UPDATE、锁和 VACUUM 的命令都可能真实消耗资源。共享环境先使用只读 EXPLAIN，再在隔离副本或可丢弃实验库复现。

建立一个可重复、可删除的诊断实验

下面的实验使用一个本地 PostgreSQL 18 容器。团队若锁定其他受支持主版本，应替换镜像标签，并先用 SHOW server_version_num 确认行为；不要把 latest 当版本策略。

docker run --name pg-query-lab \
  -e POSTGRES_PASSWORD=lab_only_password \
  -e POSTGRES_DB=labdb \
  -p 55432:5432 \
  -d postgres:18

docker exec pg-query-lab pg_isready -U postgres -d labdb
docker exec pg-query-lab psql -U postgres -d labdb -c \
  "SELECT current_setting('server_version'), current_database();"

预期先看到 accepting connections，随后返回实际服务版本和 labdb。若端口冲突，改宿主机侧的 55432；若镜像拉取失败，检查镜像仓库代理和证书，不要把真实仓库凭证写进脚本。

创建独立 schema、20 万行有倾斜的数据和一张小型并发实验表：

CREATE SCHEMA lab;

CREATE TABLE lab.orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   INTEGER NOT NULL,
  status      TEXT NOT NULL,
  amount      NUMERIC(12,2) NOT NULL,
  note        TEXT,
  created_at  TIMESTAMPTZ NOT NULL,
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp()
) WITH (fillfactor = 85);

INSERT INTO lab.orders (tenant_id, status, amount, note, created_at)
SELECT CASE WHEN n <= 100000 THEN 1 ELSE 2 + n % 998 END,
       CASE
         WHEN n <= 90000 THEN 'PAID'
         WHEN n <= 100000 THEN 'CANCELLED'
         WHEN n % 20 = 0 THEN 'PAID'
         ELSE 'PENDING'
       END,
       (n % 100000) / 100.0,
       repeat('x', 40),
       clock_timestamp() - (n % 180) * interval '1 day'
FROM generate_series(1, 200000) AS g(n);

CREATE TABLE lab.account_balance (
  id INTEGER PRIMARY KEY,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO lab.account_balance VALUES (1, 100), (2, 100);

CREATE TABLE lab.on_call (
  doctor TEXT PRIMARY KEY,
  active BOOLEAN NOT NULL
);
INSERT INTO lab.on_call VALUES ('alice', true), ('bob', true);

ANALYZE lab.orders;

最小验收信号：

SELECT count(*) AS rows,
       count(*) FILTER (WHERE tenant_id = 1) AS tenant_one,
       count(*) FILTER (WHERE tenant_id = 1 AND status = 'PAID') AS target_rows
FROM lab.orders;

应返回 200000、100000 和 90000。这些确定的数据分布让后续“估算错了多少”可以被证据化，而不是凭感觉讨论。

Planner 和 Executor 分别负责什么

一条 SQL 进入 PostgreSQL 后，会经历解析、重写、规划和执行。Planner 不运行所有候选计划再挑最快的，而是用统计信息和代价参数估算扫描、连接、排序、聚合的成本；Executor 按选中的计划树拉取 tuple，并在 Heap 上完成 MVCC 可见性判断。

cost=0.00..123.45 不是毫秒；它是用于比较候选计划的内部代价单位。rows 是估算行数，actual rows 才是执行后观测到的行数。判断计划质量时优先回答五个问题：

估算行数与实际行数相差多少倍。节点执行了多少次，actual rows × loops 才是总处理量。数据来自缓存命中、真实读取还是临时文件。

过滤掉了多少行，排序或 Hash 是否溢出。慢在规划、执行、锁等待，还是客户端取数。

普通 EXPLAIN 不执行语句；EXPLAIN ANALYZE 会真实执行。修改语句必须包在事务中回滚：

BEGIN;
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, VERBOSE)
UPDATE lab.orders
SET note = 'plan_probe'
WHERE id BETWEEN 1 AND 100;
ROLLBACK;

确认输出中出现 actual time、rows、Buffers 和 WAL，随后验证没有留下修改：

SELECT count(*) FROM lab.orders WHERE note = 'plan_probe';

预期为 0。PostgreSQL 官方的 EXPLAIN 说明了 ANALYZE 的真实执行语义、BUFFERS 的块统计和 WAL 的记录统计。生产排查若只需要行数与缓冲区，可考虑 TIMING OFF 降低逐节点计时开销，但仍要先评估语句本身的影响。

先读行数，再争论扫描方式

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount
FROM lab.orders
WHERE tenant_id = 1
  AND status = 'PAID';

当前没有合适索引，Seq Scan 很可能是合理选择，因为目标行占整表近一半。看到 Seq Scan 不等于发现故障；如果返回 9 万行，随机访问索引再回表可能比顺序扫描更贵。

创建一个针对小租户近期订单的索引：

CREATE INDEX idx_orders_tenant_status_created
  ON lab.orders (tenant_id, status, created_at DESC)
  INCLUDE (amount);

ANALYZE lab.orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT created_at, amount
FROM lab.orders
WHERE tenant_id = 777
  AND status = 'PENDING'
  AND created_at >= CURRENT_TIMESTAMP - interval '30 days'
ORDER BY created_at DESC
LIMIT 20;

合理计划通常会使用新索引，并尽早通过 LIMIT 停止；实际节点名可能是 Index Scan 或 Index Only Scan，取决于可见性映射和缓存状态。验收重点不是强迫节点名一致，而是扫描行数接近返回行数、没有大规模排序落盘、缓冲区访问显著低于全表页数。

反向实验使用一个无法命中索引前导列的谓词：

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM lab.orders
WHERE status = 'PENDING';

即使索引包含 status，Planner 仍可能选择顺序扫描或并行顺序扫描，因为联合 B-tree 的前导列是 tenant_id，而且待处理数据占比很高。不要用 SET enable_seqscan = off 伪造“优化成功”；这个参数适合诊断候选路径，不是长期修复。

Heap、索引和 MVCC 为什么必须一起看

PostgreSQL 的普通表是 Heap。索引项通常保存键值和指向 Heap tuple 的 TID；索引本身不承担完整的事务可见性判断。UPDATE 一般不是原地覆盖，而是创建新 tuple 版本，再让旧版本等待所有可能看见它的快照结束。

一个 tuple 头部包含创建与删除/更新相关的事务标识。当前事务根据快照判断版本是否可见：

这解释了三个常见误区：

DELETE 提交后，磁盘不会立即缩小。索引命中后仍可能访问 Heap，因为要检查可见性或读取未包含列。长事务即使只读，也可能阻止旧版本回收。

HOT 不是“所有 UPDATE 都更快”

Heap-Only Tuple（HOT）可以避免为某些 UPDATE 创建新的普通索引项。根据 PostgreSQL 的 HOT 机制，核心条件是：更新没有修改被普通索引引用的列，并且原 Heap 页有足够空间容纳新版本。较低的 fillfactor 只是提高同页空间概率，不是保证。

建立一张专用表，分别更新非索引列和索引列：

CREATE TABLE lab.hot_demo (
  id BIGINT PRIMARY KEY,
  lookup_key INTEGER NOT NULL,
  payload TEXT NOT NULL
) WITH (fillfactor = 70);

INSERT INTO lab.hot_demo
SELECT n, n % 1000, repeat('a', 80)
FROM generate_series(1, 50000) AS g(n);

CREATE INDEX idx_hot_demo_lookup ON lab.hot_demo (lookup_key);
VACUUM (ANALYZE) lab.hot_demo;

SELECT n_tup_upd, n_tup_hot_upd
FROM pg_stat_user_tables
WHERE schemaname = 'lab' AND relname = 'hot_demo';

UPDATE lab.hot_demo
SET payload = repeat('b', 80)
WHERE id <= 10000;

SELECT pg_stat_clear_snapshot();
SELECT n_tup_upd, n_tup_hot_upd
FROM pg_stat_user_tables
WHERE schemaname = 'lab' AND relname = 'hot_demo';

统计视图刷新可能有短暂延迟，但第二次查询中 n_tup_hot_upd 应明显增加。再更新索引列：

UPDATE lab.hot_demo
SET lookup_key = lookup_key + 1000
WHERE id BETWEEN 10001 AND 20000;

SELECT pg_stat_clear_snapshot();
SELECT n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_ratio_pct
FROM pg_stat_user_tables
WHERE schemaname = 'lab' AND relname = 'hot_demo';

总更新数会继续增加，但第二批更新不能按普通 HOT 条件省掉该索引的维护。若第一批 HOT 比例仍很低，检查行宽、页内剩余空间、是否还有表达式或部分索引引用了被更新列；不要仅靠继续降低 fillfactor 解决，因为它会直接增加表体积和读放大。

可见性映射决定 Index Only Scan 是否真的“Only”

Visibility Map 为每个 Heap 页维护 all-visible 与 all-frozen 状态。Index Only Scan 仍需先查可见性映射；只有 all-visible 页才能跳过 Heap 可见性检查。官方维护说明 也明确说明 VACUUM 会维护这张映射。

VACUUM (ANALYZE) lab.hot_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM lab.hot_demo
WHERE id BETWEEN 30000 AND 32000;

计划若为 Index Only Scan，关注 Heap Fetches。刚 VACUUM 且页面没有后续写入时，它通常接近 0。破坏可见性后再次比较：

UPDATE lab.hot_demo
SET payload = payload || 'c'
WHERE id BETWEEN 30000 AND 32000;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM lab.hot_demo
WHERE id BETWEEN 30000 AND 32000;

VACUUM (ANALYZE) lab.hot_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM lab.hot_demo
WHERE id BETWEEN 30000 AND 32000;

中间一次的 Heap Fetches 往往会上升，最后一次又下降。若需要更细检查，可在受控环境安装 pg_visibility；其函数默认受高权限限制，不应授予应用角色。

统计信息错了，索引再多也救不了估算

Planner 默认收集单列统计，包括空值比例、不同值数量、最常见值和直方图。它无法自动理解所有列间相关性。实验数据中 tenant_id = 1 与 status = 'PAID' 高度相关，若按两个独立条件相乘，估算容易偏离实际 9 万行。

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM lab.orders
WHERE tenant_id = 1
  AND status = 'PAID';

记录聚合节点下方扫描节点的 rows 与 actual rows。再创建扩展统计：

CREATE STATISTICS lab.st_orders_tenant_status (dependencies, mcv)
ON tenant_id, status
FROM lab.orders;

ANALYZE lab.orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM lab.orders
WHERE tenant_id = 1
  AND status = 'PAID';

预期第二次的估算更接近 9 万，但具体数字会受抽样和版本影响。PostgreSQL 的 CREATE STATISTICS 支持 ndistinct、dependencies 和 mcv：

类型	解决的估算问题	不解决什么
dependencies	列之间近似函数依赖，避免把相关谓词完全当独立事件	不提供具体高频组合的完整分布
mcv	多列高频值组合与不存在组合	低频长尾仍依赖其他统计
ndistinct	多列组合的不同值数量，帮助 GROUP BY 等估算	不直接描述每个组合频率

扩展统计不替代索引，也不会改变数据；它让 Planner 更准确地估算。治理时记录统计对象服务的 SQL 指纹、估算偏差和撤销条件：

SELECT schemaname, statistics_name, attnames, kinds
FROM pg_stats_ext
WHERE schemaname = 'lab';

DROP STATISTICS IF EXISTS lab.st_orders_tenant_status;
ANALYZE lab.orders;

这组回滚会恢复原估算模型。若要提高单列采样精度，应只对分布复杂且影响关键谓词的列调整：

ALTER TABLE lab.orders ALTER COLUMN tenant_id SET STATISTICS 500;
ANALYZE lab.orders (tenant_id);

-- 回滚到继承数据库默认值
ALTER TABLE lab.orders ALTER COLUMN tenant_id SET STATISTICS -1;
ANALYZE lab.orders (tenant_id);

统计目标越高，ANALYZE 时间、统计体积和规划成本也会增加。先比较估算误差，再决定是否调整；不要全库统一改成一个“大数”。

事务隔离要用并发结果验收

PostgreSQL 实际提供 Read Committed、Repeatable Read 和 Serializable 三种不同语义；请求 Read Uncommitted 会按 Read Committed 处理。隔离级别的价值不是背现象表，而是证明业务不变量在并发下仍成立。

Read Committed：每条语句取得新的快照

打开两个 psql 会话：

docker exec -it pg-query-lab psql -U postgres -d labdb

会话 A：

BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM lab.account_balance WHERE id = 1;
-- 预期 100

会话 B：

UPDATE lab.account_balance SET balance = 80 WHERE id = 1;
-- 预期 UPDATE 1，并自动提交

回到会话 A：

SELECT balance FROM lab.account_balance WHERE id = 1;
-- 预期 80：同一事务中的后一条语句看到了新提交
ROLLBACK;

Read Committed 适合大多数短 OLTP 事务，但“先读后写”的业务校验必须使用原子 UPDATE、约束、行锁或可重试的 Serializable，不能假设事务内多次普通 SELECT 自动稳定。

Repeatable Read：快照稳定，不等于业务规则自动串行化

会话 A：

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM lab.account_balance WHERE id = 1;
-- 预期 80

会话 B：

UPDATE lab.account_balance SET balance = 60 WHERE id = 1;

会话 A：

SELECT balance FROM lab.account_balance WHERE id = 1;
-- 仍预期 80
ROLLBACK;

Repeatable Read 提供稳定快照，但两个事务更新不同的行，仍可能共同破坏跨行约束。用“至少一名医生值班”验证写偏差：

会话 A 与 B 都先执行：

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM lab.on_call WHERE active;
-- 两边都预期 2

会话 A：

UPDATE lab.on_call SET active = false WHERE doctor = 'alice';
COMMIT;

会话 B：

UPDATE lab.on_call SET active = false WHERE doctor = 'bob';
COMMIT;

SELECT * FROM lab.on_call ORDER BY doctor;

两个事务可能都提交，结果无人值班。清理并恢复测试数据：

UPDATE lab.on_call SET active = true;

Serializable：把失败纳入正常控制流

把上一个实验的隔离级别换成 SERIALIZABLE，保持相同并发顺序。至少一个事务应在提交或写入时失败，常见 SQLSTATE 为 40001：

ERROR: could not serialize access due to read/write dependencies among transactions

应用必须回滚并从事务起点重试整段业务，而不是只重试最后一条 UPDATE。指数退避、最大重试次数、幂等性和冲突指标都属于接入契约。事务隔离官方说明 解释了 Serializable Snapshot Isolation 和 SIReadLock；谓词锁用于发现危险依赖，并不等同于阻塞式行锁。

选型可以落到业务不变量：

业务形态	优先方案	失败处理
单行余额扣减	条件 UPDATE + CHECK 约束	受影响行数为 0 时业务失败
读取后修改同一行	SELECT ... FOR UPDATE 或原子 UPDATE	锁超时可重试
跨多行聚合约束	Serializable 或显式锁定共同保护对象	捕获 40001，重跑整个事务
长报表一致快照	只读 Repeatable Read，必要时导出快照	限制时长，隔离到只读节点

锁等待和死锁必须留下阻塞证据

“SQL 卡住了”不是根因。至少要找出等待者、阻塞者、锁类型、事务开始时间和 SQL 指纹。

会话 A：

BEGIN;
UPDATE lab.account_balance SET balance = balance + 1 WHERE id = 1;
SELECT pg_backend_pid();

会话 B：

SET lock_timeout = '2s';
UPDATE lab.account_balance SET balance = balance + 1 WHERE id = 1;

预期 B 在约 2 秒后返回 canceling statement due to lock timeout，SQLSTATE 为 55P03。第三个会话在超时前执行：

SELECT a.pid AS waiting_pid,
       pg_blocking_pids(a.pid) AS blocking_pids,
       a.wait_event_type,
       a.wait_event,
       age(clock_timestamp(), a.xact_start) AS xact_age,
       left(a.query, 120) AS query_sample
FROM pg_stat_activity AS a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0;

修复不是随手终止所有连接。先确认阻塞会话是否仍在正常提交、是否为迁移任务、终止会丢失什么工作；经授权后才选择：

SELECT pg_cancel_backend(<blocking_pid>);    -- 只取消当前语句
SELECT pg_terminate_backend(<blocking_pid>); -- 终止会话并回滚事务

示例中的占位符不能直接执行。实验结束在会话 A 执行：

ROLLBACK;

构造死锁，观察数据库如何选择受害者

先恢复余额：

UPDATE lab.account_balance SET balance = 100;

会话 A：

BEGIN;
UPDATE lab.account_balance SET balance = 101 WHERE id = 1;

会话 B：

BEGIN;
UPDATE lab.account_balance SET balance = 101 WHERE id = 2;

会话 A 再执行：

UPDATE lab.account_balance SET balance = 102 WHERE id = 2;
-- 等待 B

会话 B 再执行：

UPDATE lab.account_balance SET balance = 102 WHERE id = 1;

PostgreSQL 检测到环后会中止其中一个事务，返回 SQLSTATE 40P01。另一个事务仍需显式 COMMIT 或 ROLLBACK。两个会话最后都执行 ROLLBACK，然后验证：

SELECT * FROM lab.account_balance ORDER BY id;

治理死锁的首选手段是统一锁顺序、缩短事务、减少事务内外部调用，并为 40P01 设计整事务重试。调低 deadlock_timeout 会增加死锁检测开销，只适合诊断窗口，不应代替代码修复。锁模式和冲突关系可查阅 PostgreSQL 的显式锁定说明。

VACUUM 回收的是可复用空间，不一定是操作系统空间

标准 VACUUM 主要完成四类工作：回收可复用的旧 tuple 空间、维护 Planner 统计、维护可见性映射、冻结旧事务标识以防回卷。它通常不会把表文件中间的空洞归还操作系统；VACUUM FULL 会重写表并持有 ACCESS EXCLUSIVE 锁，还需要额外临时磁盘。

这条差异决定了事故判断：

现象	优先动作	不应直接做
dead tuple 增长但文件稳定	检查 autovacuum 是否追得上	立即 VACUUM FULL
文件增长且后续会复用空间	提高普通 VACUUM 频率，修复长事务	只看磁盘没有持续增长就停监控
一次性删除后必须归还磁盘	评估重写窗口、额外空间和锁	高峰期直接重写核心表
整表周期性清空	评估 TRUNCATE 语义和锁	DELETE 全表后反复 FULL

长事务怎样让 VACUUM 看得见却收不走

会话 A 建立旧快照：

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM lab.orders;
SELECT pg_backend_pid();

会话 B 制造旧版本并执行维护：

UPDATE lab.orders
SET note = note || 'u', updated_at = clock_timestamp()
WHERE id <= 50000;

VACUUM (VERBOSE, ANALYZE) lab.orders;

第三个会话检查回收边界和 dead tuple：

SELECT pid, usename, state, xact_start, backend_xmin,
       age(backend_xmin) AS xmin_age,
       left(query, 100) AS query_sample
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xact_start;

SELECT relname, n_live_tup, n_dead_tup,
       last_vacuum, last_autovacuum,
       vacuum_count, autovacuum_count
FROM pg_stat_user_tables
WHERE schemaname = 'lab' AND relname = 'orders';

只读长事务仍可能持有较老 backend_xmin。结束会话 A：

ROLLBACK;

随后再次执行：

VACUUM (VERBOSE, ANALYZE) lab.orders;

比较两次 VERBOSE 输出中的 removable / nonremovable tuple 线索和统计变化。统计视图是估算与累计计数，不能单凭 n_dead_tup 精确计算膨胀；需要精确页级证据时，在受控账号下评估 pgstattuple，但不要把高权限诊断扩展开放给应用角色。

Autovacuum 阈值必须按写入模型计算

普通触发思路可以简化为：

vacuum trigger ≈ autovacuum_vacuum_threshold
               + autovacuum_vacuum_scale_factor × reltuples

analyze trigger ≈ autovacuum_analyze_threshold
                + autovacuum_analyze_scale_factor × reltuples

对十亿行热点表，即使 scale factor 看起来很小，也可能要积累大量变更才触发；对频繁更新的小表，固定阈值又可能占主导。先读取实际设置和表级覆盖值：

SELECT name, setting, unit, source
FROM pg_settings
WHERE name LIKE 'autovacuum%'
   OR name IN ('track_counts', 'log_autovacuum_min_duration')
ORDER BY name;

SELECT relname, reltuples::bigint, reloptions
FROM pg_class
WHERE oid = 'lab.orders'::regclass;

只对被证据确认的热点表设置表级参数，例如：

ALTER TABLE lab.orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 5000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_analyze_threshold = 2500
);

这些数字只是实验起点，不是生产通用值。验收要观察写入峰值下 autovacuum 启动间隔、每轮处理速度、WAL 与 IO、dead tuple 稳态、查询延迟和 worker 排队。回滚表级覆盖：

ALTER TABLE lab.orders RESET (
  autovacuum_vacuum_scale_factor,
  autovacuum_vacuum_threshold,
  autovacuum_analyze_scale_factor,
  autovacuum_analyze_threshold
);

autovacuum_vacuum_cost_limit、worker 数量和 cost delay 共同决定维护吞吐与前台 IO 竞争。调高并发前先确认磁盘余量；维护速度慢不一定是 worker 少，也可能是长事务、锁冲突、I/O 饱和或单表本身太大。

Freeze 不是可选清洁动作

事务 ID 是有限空间。VACUUM 会把足够老的 tuple 标记为 frozen，避免 XID 回卷导致可见性灾难。即使普通空间回收压力很低，反回卷 autovacuum 仍会被强制触发。

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

SELECT c.oid::regclass AS relation,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS xid_age
FROM pg_class AS c
LEFT JOIN pg_class AS t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm')
ORDER BY xid_age DESC
LIMIT 20;

把最大 age 与实例自身的 autovacuum_freeze_max_age、增长速率和维护吞吐比较，而不是背固定告警值：

SHOW autovacuum_freeze_max_age;
SHOW vacuum_freeze_table_age;
SHOW vacuum_freeze_min_age;

若出现反回卷告警，先找旧 prepared transaction、长事务和持有旧 xmin 的复制槽，再执行合适的 VACUUM。不要用 VACUUM FULL 处理 XID 紧急状态；PostgreSQL 的日常 VACUUM 文档给出了 XID 风险、可见性映射和维护行为的官方解释。

分区裁剪要由执行计划证明

分区首先是生命周期和维护窗口工具，其次才可能改善查询。下面用三个 30 天时间桶和默认分区构造一组不依赖固定日历日期的实验数据：

CREATE TABLE lab.events (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  tenant_id INTEGER NOT NULL,
  event_day INTEGER NOT NULL,
  payload JSONB NOT NULL
) PARTITION BY RANGE (event_day);

CREATE TABLE lab.events_day_0_30 PARTITION OF lab.events
FOR VALUES FROM (0) TO (30);

CREATE TABLE lab.events_day_30_60 PARTITION OF lab.events
FOR VALUES FROM (30) TO (60);

CREATE TABLE lab.events_day_60_90 PARTITION OF lab.events
FOR VALUES FROM (60) TO (90);

CREATE TABLE lab.events_default PARTITION OF lab.events DEFAULT;

INSERT INTO lab.events (tenant_id, event_day, payload)
SELECT n % 100,
       n % 90,
       jsonb_build_object('n', n)
FROM generate_series(1, 90000) AS g(n);

ANALYZE lab.events;

正向查询直接给出分区键范围：

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM lab.events
WHERE event_day >= 30
  AND event_day <  60;

计划应只访问 events_day_30_60。反向写法把分区键包进函数：

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM lab.events
WHERE floor(event_day / 30.0) = 1;

这类谓词通常不能按原始范围边界完成同样的静态裁剪，计划可能访问多个分区。把查询改回半开区间，并用计划中的 Subplans Removed 或实际子分区扫描节点验收。PostgreSQL 的分区裁剪说明还区分规划期和执行期裁剪；参数化计划需要观察实际执行，而不能只看一份静态 SQL。

分区上线前至少回答：

分区键是否出现在主要查询和删除归档路径中。是否自动创建未来分区，并监控数据误入 default 分区。唯一约束是否包含全部分区键。

分区数量增长后，规划时间和 catalog 维护是否可控。DETACH PARTITION、归档、恢复和重新挂载是否演练过。

清理分区实验：

DROP TABLE lab.events;

这会级联删除其分区表，但不会影响 lab.orders。

建立容量与性能基线，而不是保存一张快照

一次 EXPLAIN 只能解释一个时刻。容量治理需要对相同业务窗口持续采样，并保留版本、参数、数据量和负载上下文。

数据、索引和死元组

SELECT n.nspname AS schema_name,
       c.relname,
       pg_size_pretty(pg_relation_size(c.oid)) AS heap_size,
       pg_size_pretty(pg_indexes_size(c.oid)) AS index_size,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
       s.n_live_tup,
       s.n_dead_tup,
       s.n_tup_upd,
       s.n_tup_hot_upd
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
LEFT JOIN pg_stat_user_tables AS s ON s.relid = c.oid
WHERE c.relkind = 'r'
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 30;

长期记录增长斜率，而不是用“表超过多少 GB 就分区”的固定阈值。容量预算至少包含 Heap、全部索引、TOAST、WAL 峰值、临时文件、备份、恢复临时空间和重写操作所需的额外副本。

数据库吞吐和缓存证据

SELECT datname, xact_commit, xact_rollback,
       blks_read, blks_hit,
       temp_files, temp_bytes,
       deadlocks,
       stats_reset
FROM pg_stat_database
WHERE datname = current_database();

两次采样的差值除以间隔时间，才能得到 TPS、物理读速率、临时文件速率和死锁速率。blks_hit / (blks_hit + blks_read) 不是完整缓存命中率，也不包含操作系统页缓存；它适合作为趋势信号，不能单独决定 shared_buffers。

SQL 指纹和延迟分位

pg_stat_statements 需要在 shared_preload_libraries 中启用并重启实例，随后由有权限的管理员创建扩展。应用账号只需获得受控的观测视图权限，不应拿 superuser：

SELECT queryid, calls, total_exec_time, mean_exec_time,
       rows, shared_blks_hit, shared_blks_read,
       temp_blks_read, temp_blks_written,
       left(query, 160) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

SQL 文本可能包含业务标识或未参数化值，采集、导出和截图必须脱敏；监控平台应按角色限制查询文本可见性，并设置保留周期。对外共享优先保留 queryid、指标和脱敏模板。

一次变更的验收表

维度	变更前	变更后	回滚信号
估算偏差	actual rows / estimated rows	同负载再次采样	核心 SQL 偏差扩大
延迟	P50 / P95 / P99	相同并发和数据量	P95 或尾延迟恶化
IO	shared read、temp bytes	相同冷/热缓存口径	读放大或临时文件上升
写放大	WAL bytes、索引大小	相同写入批次	WAL、索引维护成本失控
回收	dead tuple、vacuum 周期	至少覆盖一个业务峰值	backlog 持续增长
容量	Heap / index / WAL 增长率	按天或按周比较	预算窗口缩短

基线必须绑定业务版本、PostgreSQL 主次版本、参数摘要、节点规格、数据量、并发和采样时段。否则两份数字无法比较。

常见失败要沿证据链修复

加了索引，Planner 仍然顺序扫描

新索引存在，但计划仍是 Seq Scan。

检查返回比例、估算偏差、前导列、类型转换、函数包装、统计更新时间和索引有效状态。

SELECT indexrelid::regclass, indisvalid, indisready, indisunique
FROM pg_index
WHERE indrelid = 'lab.orders'::regclass;

SELECT last_analyze, last_autoanalyze, n_live_tup
FROM pg_stat_user_tables
WHERE relid = 'lab.orders'::regclass;

先更新统计并重测；高返回比例的 Seq Scan 可能正确。只有确认索引结构不匹配时才调整索引，不要全局关闭 Seq Scan。

比较估算行数、总缓冲区和执行时间，而不是只看节点名。

Index Only Scan 仍有大量 Heap Fetches

节点名是 Index Only Scan，但 Heap Fetches 很高。

检查表是否持续更新、最近 VACUUM、长事务和 all-visible 页比例。

先解决回收阻塞和 autovacuum 吞吐；不要为了节点名盲目手工 VACUUM 每分钟运行一次。

相同数据范围再次执行 EXPLAIN (ANALYZE, BUFFERS)，同时确认维护 IO 没有压垮前台。

Autovacuum 一直在跑，dead tuple 仍增长

pg_stat_activity 能看到 autovacuum，表膨胀仍扩大。

依次查旧 xmin、prepared transaction、复制槽、维护吞吐、磁盘 IO、表级参数和 worker 排队。运行不等于追得上。

结束异常长事务；修复废弃槽；按表调整阈值与维护预算；把超大批量 UPDATE 拆小；减少无收益索引。

至少跨过一次写入峰值，确认 dead tuple 回到稳定区间且查询尾延迟没有因维护竞争恶化。

事务偶发 40001 或 40P01

应用把序列化失败或死锁当未知 500。

区分 SQLSTATE。40001 是序列化失败，40P01 是死锁；两者都要求事务边界清晰，但根因不同。

40001 重试整个事务并校验幂等；40P01 先统一锁顺序和缩短事务，再保留有限重试。

并发测试必须能稳定触发冲突、记录重试次数，并证明最终业务不变量成立。

分区表查询扫描全部子表

查询只取一个月，计划却访问大量分区。

检查谓词是否能直接约束分区键、参数类型是否一致、是否存在函数包装，以及执行期裁剪是否发生。

改为与边界一致的半开区间；必要时重新评估分区键，而不是给每个分区继续堆索引。

计划中只留下目标分区，并检查 default 分区是否积累了异常数据。

架构选型：什么时候继续优化 PostgreSQL，什么时候拆负载

信号	优先决策	代价
单 SQL 估算偏差明显	统计、扩展统计、索引和 SQL 治理	增加统计维护与评审成本
高频更新导致版本与索引膨胀	HOT 友好设计、减少索引、调 autovacuum、拆批次	可能牺牲部分查询覆盖率
长报表拖住回收	只读副本、快照时限、分析平台	复制延迟与额外容量成本
历史数据有明确生命周期	分区、归档、冷热分层	分区自动化和恢复更复杂
OLTP 同时承担大扫描聚合	拆读模型、CDC 到 OLAP	数据时效、成本和一致性边界
单节点写入、存储或恢复窗口持续越界	垂直扩容、分片或分布式数据库评估	迁移、事务和运维复杂度显著上升

“换数据库”不能掩盖缺失的查询治理、容量基线和恢复能力。只有证据表明单节点资源、写入模型或维护窗口已经持续越界，并且团队能承担新的分布式一致性和运维成本时，替代方案才成立。

权限、敏感数据、成本和长期治理

诊断账号分层

应用角色只拥有业务 schema 的必要 DML 权限；迁移角色拥有受审查的 DDL；诊断角色按需授予 pg_read_all_stats 等预定义能力；安装扩展、终止会话、修改系统参数仍由平台管理员控制。

pg_stat_activity.query、pg_stat_statements.query、执行计划常量和错误日志都可能暴露租户 ID、邮箱、订单号或令牌。监控采集前参数化 SQL，导出时脱敏，截图前清理文本，禁止把生产连接串和真实凭证放入工单、文章或 AI 上下文。

变更必须有 owner 和退出路径

变更	上线证据	回滚
新索引	SQL 指纹、估算与 IO 对照、写放大预算	DROP INDEX CONCURRENTLY，确认无依赖
扩展统计	估算误差显著下降	DROP STATISTICS 后 ANALYZE
列统计目标	关键谓词估算改善，规划时间可接受	SET STATISTICS -1
表级 autovacuum	backlog 收敛且前台延迟稳定	ALTER TABLE ... RESET (...)
fillfactor	HOT 比例提升覆盖额外存储成本	恢复参数；已有页需重写才完全生效
分区	裁剪、创建、归档、恢复均通过	DETACH PARTITION 或回到旧表，需预演数据同步

索引并发创建失败后可能留下 invalid index，需要发现和清理；DROP INDEX CONCURRENTLY 也受事务块限制。任何重写表的操作都要预算双份磁盘、WAL、复制延迟和备份窗口。

维护成本必须进入容量模型

PostgreSQL 的成本不只有节点价格：

更多索引会增加写入、WAL、备份和 VACUUM 成本。更高统计目标会增加 ANALYZE 与规划开销。更激进 autovacuum 会占用 IO 和 CPU，但过慢又会造成膨胀。

分区会增加 catalog、自动建分区、归档和恢复复杂度。只读副本和分析链路会增加存储、网络与延迟治理成本。

每月复盘容量增长率、索引收益、慢 SQL 总耗时、autovacuum backlog、XID age、临时文件、复制延迟和恢复演练结果。没有 owner 的索引、统计对象、分区任务和告警，最终都会变成无人敢删的长期负担。

清理实验环境

只删除本地实验容器前，先确认名称和数据不承载其他工作：

docker ps -a --filter "name=^pg-query-lab$"
docker rm -f pg-query-lab

实验没有挂载宿主机 volume，删除容器会同时删除其中的 labdb 数据。若团队把实验改成了命名 volume，必须额外列出并人工确认后再删除，不能把清理命令复制到共享或生产环境。

已确认实例版本、数据库、角色、地址和当前隔离级别。EXPLAIN ANALYZE 只在可承受真实执行的环境和语句上运行。查询评审同时比较 estimated rows、actual rows、loops、Buffers、临时文件和锁等待。

索引服务明确 SQL 指纹，并记录写放大、容量和下线条件。HOT 比例、fillfactor、索引引用列和行宽一起评估。Index Only Scan 用 Heap Fetches 和可见性映射验证，不只看节点名。

单列统计或扩展统计的收益通过估算偏差证明。Read Committed、Repeatable Read、Serializable 的业务不变量用双会话实验验证。应用区分并处理 40001、40P01、55P03。

锁告警能定位等待者、阻塞者、事务年龄和 SQL 指纹。长事务、prepared transaction 和复制槽纳入 VACUUM 阻塞排查。Autovacuum 阈值按表规模与变更速率计算，并经过业务峰值验证。

XID / MXID age 有趋势监控和反回卷处置责任人。分区裁剪通过真实执行计划验证，default 分区有异常数据告警。容量模型包含 Heap、索引、TOAST、WAL、临时文件、备份和重写空间。

诊断视图、SQL 文本、计划和日志按敏感数据级别授权与脱敏。每项索引、统计、VACUUM 和分区变更都有 owner、验收指标和回滚命令。清理命令仅针对已确认的本地实验对象，不会误删共享数据。