从 ACID 到生产排障：PostgreSQL 的重要机制

订单只写了一半，为什么？                 → Atomicity / 事务边界
两个请求都扣了最后一件库存，为什么？       → Isolation / 原子更新
数据库突然变慢，为什么所有请求都在等？     → Lock / 长事务 / 连接池
重启后数据还在，但恢复很久，为什么？       → Durability / WAL / Checkpoint
表越来越大，磁盘快满了，为什么？           → MVCC / VACUUM / 膨胀
一条 SQL 昨天很快，今天突然变慢，为什么？   → Planner / 统计信息 / 索引

业务事务
   ↓
约束 + 原子写入                  Consistency / Atomicity
   ↓
MVCC 快照 + 锁                   Isolation
   ↓
WAL flush + COMMIT               Durability
   ↓
Checkpoint / Replication
   ↓
VACUUM 回收旧版本，Planner 使用统计信息

BEGIN;

INSERT INTO orders (order_no, user_id)
VALUES ('O-1001', 42);

UPDATE products
SET stock = stock - 1
WHERE id = 7 AND stock > 0;

INSERT INTO payments (order_no, status)
VALUES ('O-1001', 'pending');

COMMIT;

SQL
 ↓
Parser / Analyzer
 ↓
Planner：选择 Seq Scan、Index Scan、Join 算法
 ↓
Executor：读取 heap / index，检查快照与约束
 ↓
MVCC：判断哪些 tuple 对当前事务可见
 ↓
Lock：协调相互冲突的修改
 ↓
WAL：记录可以重放的变更
 ↓
COMMIT：WAL 按持久性策略 flush 后提交
 ↓
后续事务看到新的已提交版本

BEGIN → 写订单 → 扣库存 → 写支付意图 → COMMIT

→ 三步一起对外可见
→ 任一步失败，整个事务回滚

BEGIN;
INSERT INTO users (email) VALUES ('[email protected]');
-- duplicate key error

SELECT * FROM users;
-- ERROR: current transaction is aborted

BEGIN;
SAVEPOINT optional_step;

-- 可能失败的操作
ROLLBACK TO SAVEPOINT optional_step;

-- 继续执行事务的其他部分
COMMIT;

数据库事务
  ├── orders
  ├── inventory
  └── outbox_events
COMMIT
       ↓
异步 worker 调用支付网关或消息系统

CREATE TABLE memberships (
  user_id bigint REFERENCES users(id),
  team_id bigint REFERENCES teams(id),
  role text NOT NULL CHECK (role IN ('admin', 'member')),
  PRIMARY KEY (user_id, team_id)
);

PRIMARY KEY → 身份唯一
FOREIGN KEY → 引用不能悬空
UNIQUE      → 业务键不能重复
CHECK       → 当前行必须满足表达式
NOT NULL    → 字段必须存在

INSERT INTO orders (order_no, user_id)
VALUES ($1, $2)
ON CONFLICT (order_no) DO NOTHING;

单行不变量       → CHECK / NOT NULL
唯一性           → UNIQUE / PRIMARY KEY
引用关系         → FOREIGN KEY
竞争资源         → 原子 UPDATE 或 SELECT FOR UPDATE
复杂跨行规则     → 事务 + 锁 / Serializable

UPDATE products
SET stock = stock - 1
WHERE id = 42
  AND stock > 0
RETURNING id, stock;

旧 Tuple
id=1, balance=1000
      │ UPDATE
      ▼
新 Tuple
id=1, balance=900

Heap
├── balance=1000 ← 某些快照仍可见
└── balance=900  ← 新版本，提交后对后续快照可见

Read Committed
  → 每条语句看到当时已提交的世界

Repeatable Read
  → 一个事务里的普通读取看到同一个快照

Serializable
  → 最终结果必须等价于某个串行执行顺序

T1: BEGIN;
T1: SELECT balance ...;  -- 看到 1000

T2: UPDATE accounts SET balance = balance - 100 WHERE id = 1;
T2: COMMIT;

T1: SELECT balance ...;  -- 这条语句可能看到 900
T1: COMMIT;

UPDATE products
SET stock = stock - 1
WHERE id = 42 AND stock > 0
RETURNING stock;

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 读取、判断、修改
COMMIT;

ERROR: could not serialize access due to concurrent update
SQLSTATE: 40001

Isolation Level
→ 一个事务能看到哪些版本，如何处理并发依赖

Row Lock
→ 多个事务如何协调修改同一行

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

WITH picked AS (
  SELECT id FROM jobs
  WHERE status = 'pending'
  ORDER BY id
  FOR UPDATE SKIP LOCKED
  LIMIT 10
)
UPDATE jobs AS j
SET status = 'processing'
FROM picked
WHERE j.id = picked.id
RETURNING j.*;

SQL 修改
   ├─ shared buffers 中的脏页
   └─ WAL buffer → WAL 文件 → WAL flush
                              ↓
                         COMMIT 返回

Checkpoint：把部分脏页写回数据文件，缩短恢复时需要重放的 WAL 范围

B-tree → key → TID → Heap Page → tuple visibility check

CREATE INDEX idx_jobs_pending_created
ON jobs (status, created_at) INCLUDE (id)
WHERE status = 'pending';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at
FROM jobs
WHERE tenant_id = 10 AND status = 'pending'
ORDER BY created_at
LIMIT 10;

长事务持有旧快照
          ↓
新版本不断产生
          ↓
VACUUM 不敢回收旧 tuple
          ↓
表、索引和磁盘逐渐膨胀

请求变慢，但 CPU 很高       → 查询计划、计算量
请求变慢，但大量 waiting    → 锁、IO、连接池
数据库连接打满             → 连接池、长事务、连接泄漏
磁盘持续增长               → 表膨胀、WAL、复制槽
重启恢复或副本延迟         → WAL、Checkpoint、IO
错误集中出现               → 约束、死锁、序列化失败

1. 范围：单条 SQL、单租户、全部请求，还是只有副本？
2. 状态：谁 active，谁 waiting，谁 idle in transaction？
3. 阻塞：是否有长事务或锁环？
4. 计划：行数、IO、join 和排序是否符合预期？
5. 维护：autovacuum、dead tuple、统计信息、checkpoint 是否异常？
6. 资源：CPU、内存、磁盘 IO、空间和连接数是否到上限？
7. 变更：是否刚发布代码、改参数、加索引或改变流量？

SELECT pid, usename, datname, state,
       wait_event_type, wait_event,
       now() - query_start AS query_age,
       now() - xact_start AS xact_age,
       left(query, 160) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

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

SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocker.pid AS blocker_pid,
  blocker.query AS blocker_query,
  blocked.wait_event_type,
  blocked.wait_event,
  now() - blocker.xact_start AS blocker_transaction_age
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS p(blocker_pid)
JOIN pg_stat_activity AS blocker
  ON blocker.pid = p.blocker_pid;

事务 A 持有 row 1，等待 row 2
事务 B 持有 row 2，等待 row 1

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

active 很多         → 查询真的在执行，检查 CPU、IO、计划
idle in transaction → 开启事务后没有提交或回滚
大量 idle           → 连接池过大或连接没有及时归还

SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze,
       autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

SELECT pid, usename, state,
       now() - xact_start AS xact_age,
       backend_xmin,
       left(query, 160) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

dead tuple 增长
  ├─ 有长事务 → 先处理事务生命周期
  ├─ autovacuum 很少运行 → 检查参数、资源和权限
  ├─ 更新非常频繁 → 调整表设计、fillfactor、索引与批处理
  └─ 已严重膨胀 → 评估 VACUUM FULL、重建或在线工具

SELECT application_name, client_addr, state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

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

23505 unique_violation          → 幂等冲突或并发重复写入
23503 foreign_key_violation     → 引用不存在或删除顺序错误
23514 check_violation           → 数据违反业务约束
40P01 deadlock_detected         → 统一加锁顺序，有限重试
40001 serialization_failure     → 缩短事务，有限重试
25P02 in_failed_sql_transaction → 先 rollback 当前事务

BEGIN
  │
  ├─ INSERT orders
  ├─ UPDATE products ... WHERE stock > 0
  ├─ INSERT payments(status = 'pending')
  │
  ├─ 约束：订单号唯一、库存不能为负、引用必须存在
  ├─ MVCC：普通读不会看到半个事务
  ├─ 锁：竞争同一库存行时协调并发更新
  ├─ WAL：崩溃恢复时重放已记录的修改
  └─ COMMIT：事务作为整体对后续快照可见

Atomicity   → 订单、库存、支付意图一起成功或一起回滚
Consistency → 约束和原子 UPDATE 保护业务不变量
Isolation   → MVCC 提供快照，锁协调竞争资源
Durability  → WAL 和提交策略保护已提交结果