PostgreSQL 入门

1　先看全貌

四层，一条贯穿全文的线索，以及这一页的输出是怎么来的。

PostgreSQL 的很多“怪现象”，其实出自同一个原因。UPDATE 让表变大，建了索引却不走，读不阻塞写，COMMIT 很快却不丢数据，一条 ALTER TABLE 让全站卡住：把一行数据在磁盘上的样子看清楚，再一层一层往上叠，这些现象都能自己推出来，不用背规则。

全篇的主角是订单 42。它出生在 orders 表第 0 页的第 42 个槽位，后面每一节都会回到这一行。左侧目录按四层分组，读到哪一层，哪一层会亮起来。

这一页的输出是怎么来的

页面里的 SQL 不是示意。构建页面时，所有语句按页面顺序在同一个会话里逐条执行，输出原样写进页面。执行它们的是 PGlite，也就是编译成 WebAssembly 的 PostgreSQL 18.6。联网时你可以在文末把同一个引擎启动在浏览器里，重放任何一段并改着玩。

从 MongoDB 过来，先换这几个直觉

第一个物理事实：一个连接就是一个进程

大促当晚 20:07　接口成片报错，日志里是 FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute。数据库的 CPU 并不高。

PostgreSQL 为每个客户端连接启动一个独立的后端进程（backend process），到 18 仍然如此。进程比线程贵，所以连接数有上限，默认值很小：

SELECT current_setting('server_version') AS server_version,
       current_setting('block_size') AS block_size,
       current_setting('max_connections') AS max_connections,
       current_setting('transaction_isolation') AS default_isolation;

默认的 100 个连接里，有 3 个留给超级用户救急。普通角色把其余的用完，就会看到上面那条报错；如果应用用的就是超级用户，则要等 100 个全满，报的是 FATAL: sorry, too many clients already。两条的错误码都是 53300。

所以应用要用连接池（connection pool），而且池要小、要有上限。需要多少连接可以估算：

请求数没变，只是每个请求慢了 100 倍，需要的连接就多了 100 倍。连接耗尽往往不是“流量太大”，而是某条语句变慢或者某个事务没结束。后面的第 3、7、9 节讲的三种事故，最后都会走到这里。

习惯连接池设上限，并在数据库角色上设好超时（见清单）。出现 53300 时，先找慢的语句和没结束的事务，不要先去调大 max_connections。

2　存：一行订单，和它住的那一页

类型决定一个值怎么存，约束决定它能不能写进来，页决定它放在哪。

月初对账　财务按北京时间数出 3 月 1 日有 38 张订单。BI 的报表用同一条 SQL，数出来是 39 张，金额也对不上。

青禾书店的库里有六张表：门店 stores、会员 members、图书 books、库存 inventory、订单 orders、支付回调 payment_events。完整的建表语句在文末，这里只看 orders：

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  member_id   integer REFERENCES members,   -- NULL: guest checkout
  store_id    integer REFERENCES stores,    -- NULL: online shop
  status      text NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'completed', 'cancelled',
                                              'refunding', 'refunded')),
  total_cents integer NOT NULL CHECK (total_cents >= 0),
  created_at  timestamptz NOT NULL,
  version     integer NOT NULL DEFAULT 1
) WITH (fillfactor = 90);

表里有两万张订单。状态的取值和流转沿用状态机那一篇，version 就是 DDD 那一篇里聚合的版本号。最后一行的 fillfactor 先放着，第 6 节会用到。

选类型：三个最常见的坑

钱不用浮点数。二进制浮点数表示不了 0.1，加几次就露馅。金额存成整数的“分”，或者用精确的 numeric：

SELECT 0.1::float8 + 0.2::float8 AS float_sum,
       0.1::numeric + 0.2::numeric AS numeric_sum,
       1990 + 2900 AS cents_sum;

时间用 timestamptz，并且知道它不记时区。它存的是一个绝对时刻，显示时才按会话的 TimeZone 设置换算。同一个时刻、同一条 SQL，换一个会话时区，“哪一天”就变了。先看本页的会话，时区是 Asia/Shanghai：

SELECT id, created_at FROM orders WHERE id = 42;

SELECT count(*) AS orders, sum(total_cents) AS revenue_cents
FROM orders WHERE created_at::date = date '2026-03-01';

把会话时区换成 UTC，再执行完全相同的两条：

SET TIME ZONE 'UTC';

SELECT id, created_at FROM orders WHERE id = 42;

SELECT count(*) AS orders, sum(total_cents) AS revenue_cents
FROM orders WHERE created_at::date = date '2026-03-01';

SET TIME ZONE 'Asia/Shanghai';

订单 42 还是那个时刻，只是显示成了另一个钟点。日报却从 38 单变成了 39 单，因为 created_at::date 按会话时区切日，零点前后那几个小时的订单被划到了另一天。连接池里的会话时区取决于驱动和服务器的配置，不该让报表依赖它。把一天写成带时区偏移的半开区间，结果就和会话无关了：

SELECT count(*) AS orders, sum(total_cents) AS revenue_cents
FROM orders
WHERE created_at >= timestamptz '2026-03-01 00:00+08'
  AND created_at <  timestamptz '2026-03-02 00:00+08';

半开区间（左闭右开）还有一个好处：它是对列本身的比较，索引用得上。第 5 节会看到 created_at::date = … 用不上。

字符串用 text，自增主键用 identity。text、varchar(n)、char(n) 在磁盘上是同一种变长格式，varchar(255) 不会更快也不会更省。真要限长，用 CHECK (char_length(x) <= n) 把它写成业务规则。自增主键写 GENERATED ALWAYS AS IDENTITY，不再用旧式的 serial。对外暴露的编号可以用 uuid，PostgreSQL 18 内置了按时间有序的 uuidv7() 18 新增。

还有一条和类型无关、但 ORM 用户常踩的：不加引号的标识符一律折叠成小写。用 ORM 建出 "OrderItem" 这样带引号的驼峰表名之后，手写 SELECT … FROM OrderItem 会报 relation "orderitem" does not exist。表名、列名统一用小写的 snake_case，驼峰交给 ORM 做映射。

NULL：既不等于，也不“不等于”

orders 里有两个可空的列：游客下单时 member_id 为 NULL，线上商城的订单 store_id 为 NULL。NULL 的意思是“不知道”，和任何值比较的结果都还是“不知道”，而 WHERE 只留下结果为真的行。想统计“不是 3 号店的订单”：

SELECT count(*) AS all_orders,
       count(*) FILTER (WHERE store_id <> 3) AS not_equal_3,
       count(*) FILTER (WHERE store_id IS DISTINCT FROM 3) AS is_distinct_from_3,
       count(*) FILTER (WHERE store_id IS NULL) AS online_orders
FROM orders;

store_id <> 3 只数出 12222 张，6666 张线上订单被悄悄丢掉了，因为 NULL <> 3 不是真。把 NULL 当作一个普通的“不同值”来比，要写 IS DISTINCT FROM。

同一个原因让 NOT IN 成了有名的陷阱。找“从没下过单的会员”：

SELECT (SELECT count(*) FROM members m
        WHERE m.id NOT IN (SELECT member_id FROM orders)) AS not_in,
       (SELECT count(*) FROM members m
        WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.member_id = m.id)) AS not_exists;

子查询的结果里只要有一个 NULL（这里是游客订单），NOT IN 对每一行都得不出“真”，结果是 0 行，而且不报错。NOT EXISTS 给出的 500 才是对的。凡是“不在某个集合里”的查询，都用 NOT EXISTS。

约束：写进数据库的规则，谁也绕不过

库存不能为负，支付回调必须指向一张存在的订单。这类规则写成约束（constraint）之后，由数据库在每次写入时检查：

UPDATE inventory SET qty = qty - 5 WHERE store_id = 1 AND book_id = 1;

INSERT INTO payment_events (provider_event_id, order_id, amount_cents)
VALUES ('evt_typo', 999999, 4098);

两条语句都被拒绝，并各自带着一个五位的错误码 SQLSTATE：23514 是违反 CHECK，23503 是违反外键。程序里判断错误要用这个码，不要去匹配报错文字。

应用层的校验只管得住经过它的请求。约束管得住所有写入方：后台脚本、另一个服务、两个同时到达的请求。第 8 节会看到，“最后一本书卖给两个人”这种事，应用层检查拦不住；只要扣减写成 qty = qty - 1，CHECK (qty >= 0) 至少能把第二次扣减变成一个报错。

一个细节：CHECK 的结果为“不知道”时算通过，所以 CHECK (price_cents > 0) 拦不住 NULL，要同时写 NOT NULL。

这一行住在哪：页、槽位和 ctid

现在看物理的一面。每一行都有一个隐藏的系统列 ctid，是它的物理地址：

SELECT ctid, xmin, xmax, id, status, total_cents FROM orders WHERE id IN (41, 42, 43) ORDER BY id;

订单 42 的 ctid 是 (0,42)，读作“第 0 页，第 42 个槽位”。xmin 和 xmax 是另外两个系统列，记录哪个事务创建了这一行、哪个事务删改了它，第 6 节会用到。现在只需要知道：建库时的那个事务号是 763，所以这几行的 xmin 都是它。

表在磁盘上是一个文件，叫堆（heap），切成固定 8 kB 的页（page）。两万张订单占了多少页：

SELECT relname, relpages, reltuples::bigint AS reltuples, pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class WHERE relname IN ('orders', 'orders_pkey') ORDER BY relname;

206 页，1648 kB。旁边的 orders_pkey 是主键的索引，它是另一个文件，57 页。“表”和“索引”分开存放，这是下一层的前提。

一页内部的布局是这样的：

• 页的开头是 24 字节的页头，后面跟着一排行指针（line pointer），每个 4 字节，从前往后长。“第 42 个槽位”说的就是第 42 个行指针。
• 行的内容从页尾往前放。一行在 PostgreSQL 内部叫元组（tuple），每个元组前面还带着二十多字节的头，xmin、xmax 就存在那里。
• 两头往中间长，中间剩下的是空闲空间。这一页放了 98 行，还空着 824 字节。它没被塞满，是因为建表时写了 fillfactor = 90：每页留一成，给以后的新版本用。

SELECT lower, upper, upper - lower AS free_bytes FROM page_header(get_raw_page('orders', 0));

SELECT lp, lp_off, lp_len FROM heap_page_items(get_raw_page('orders', 0)) WHERE lp BETWEEN 41 AND 43;

SELECT count(*) AS tuples_on_page_0 FROM heap_page_items(get_raw_page('orders', 0));

两件事要记住。第一，堆里的行不按主键排序，新行放进有空位的页。MySQL 的 InnoDB 把表本身组织成主键的 B+ 树，PostgreSQL 没有这种结构，所有查找都靠另外的索引。第二，ctid 是“这一个版本”此刻的位置，UPDATE 之后会变，槽位回收后还会被别的行复用。它不能当主键，也不能拿来做分页游标。

习惯金额存整数分；时间用 timestamptz，查询某一天时写带偏移的半开区间；可空的列上出现 <> 或 NOT IN，先想一遍 NULL。

3　找：顺序扫描、B-tree，和读懂 EXPLAIN

没有索引时只能一页一页读。索引是另一份排好序的文件，它告诉你行在哪一页。

上线三个月后　“我的订单”页从 5 毫秒变成了 5 秒。代码没改过，只是订单表变大了。

没有索引：从第 0 页读到最后一页

查会员 295 的订单。在语句前面加上 EXPLAIN，PostgreSQL 会给出它打算怎么执行，也就是执行计划；再加 ANALYZE，它会真的执行一遍，并把实际行数填进去：

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT id, total_cents FROM orders WHERE member_id = 295;

Seq Scan on orders  (cost=0.00..456.00 rows=10 width=12) (actual rows=10.00 loops=1)
  Filter: (member_id = 295)
  Rows Removed by Filter: 19990

这是全文第一份计划，逐项看：

• Seq Scan on orders 是顺序扫描（sequential scan）：把 orders 的 206 页从头读到尾，对每一行判断 Filter 里的条件。
• cost=0.00..456.00 是规划器估的代价，前一个数是拿到第一行之前的开销，后一个是全部做完的开销。单位是它自己的计量单位，大致等于“顺序读一页”，不是毫秒。
• rows=10 是估计会返回的行数，actual rows=10.00 是实际返回的行数。这两个数差得远，往往就是问题所在（第 4 节）。
• Rows Removed by Filter: 19990：读了两万行，丢掉 19990 行，只为了留下 10 行。这一行最值得先看。

点计划里的任意一行，下面会给出这一行的解读。全文所有的计划都可以这样点；用键盘时，Tab 进入一份计划，上下方向键换行，回车查看。

有索引：三页就够

按主键找订单 42，计划完全不同：

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT id, total_cents FROM orders WHERE id = 42;

Index Scan using orders_pkey on orders  (cost=0.29..8.30 rows=1 width=12) (actual rows=1.00 loops=1)
  Index Cond: (id = 42)
  Index Searches: 1

Index Scan using orders_pkey：走主键索引。建表时写了 PRIMARY KEY，PostgreSQL 就自动建了这棵索引，UNIQUE 约束也是同样的做法。代价从 456.00 降到 8.30。

默认的索引类型是 B-tree。它是一个独立的文件，里面是一棵按键排序的树：根页指向叶子页，叶子页里按顺序放着“键 → ctid”。orders_pkey 一共 57 页：1 页元信息、1 页根、55 页叶子。下面这个面板里的页号和条目，都是用 pageinspect 从这棵索引里读出来的：

缓冲区统计能直接证实“碰了几页”。去掉 BUFFERS OFF，对比两种查法：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, total_cents FROM orders WHERE id = 42;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id, total_cents FROM orders WHERE member_id = 295;

Index Scan using orders_pkey on orders (actual rows=1.00 loops=1)
  Index Cond: (id = 42)
  Index Searches: 1
  Buffers: shared hit=3

Seq Scan on orders (actual rows=10.00 loops=1)
  Filter: (member_id = 295)
  Rows Removed by Filter: 19990
  Buffers: shared hit=206

Buffers: shared hit=3 对 shared hit=206：按主键找读了 3 页，顺序扫描读了全部 206 页。表再大十倍，前者几乎不变（B-tree 很矮，上亿行通常也只有四五层），后者跟着涨十倍。“5 毫秒变 5 秒”就是这么来的。

外键不会自动带索引

回到会员 295。member_id 是指向 members 的外键，很多人以为外键会自动建索引，就像主键那样。并不会：PostgreSQL 只给主键和唯一约束自动建索引，引用别人的那一列要自己建。上一小节那次顺序扫描就是证据。现在建上：

CREATE INDEX orders_member_idx ON orders (member_id);

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT id, total_cents FROM orders WHERE member_id = 295;

Bitmap Heap Scan on orders  (cost=4.37..37.88 rows=10 width=12) (actual rows=10.00 loops=1)
  Recheck Cond: (member_id = 295)
  Heap Blocks: exact=10
  ->  Bitmap Index Scan on orders_member_idx  (cost=0.00..4.36 rows=10 width=0) (actual rows=10.00 loops=1)
        Index Cond: (member_id = 295)
        Index Searches: 1

计划变成了两个节点，从里往外读（缩进更深、带 -> 的先执行）：

• Bitmap Index Scan：先在索引里找出所有 member_id = 295 的条目，得到 10 个 ctid，按页号记在一张位图里。
• Bitmap Heap Scan：再按页号从小到大去表里取行，每页只读一次。Heap Blocks: exact=10 是说读了 10 个表页。

索引里只有键和 ctid，行的其余内容还在表里，所以拿到 ctid 之后要回表（heap fetch）再读一次。这 10 张订单分散在 10 个不同的页上（把上面的面板切到第二个页签可以看到），先收齐地址再按页读，比找到一条就去读一页要省。这就是位图扫描（bitmap scan），介于“逐条走索引”和“整表顺序读”之间。

习惯外键引用列、常出现在 WHERE 和 JOIN 里的列，自己建索引。看计划时先找 Rows Removed by Filter，再比估计行数和实际行数。

4　找：规划器的账本

有索引不等于会走索引。规划器按统计信息估行数，再比较各种走法要读多少页。

代码评审　“status 上明明建了索引，查 completed 还是 Seq Scan，索引是不是坏了？”

给 status 建一个索引，然后看 PostgreSQL 对这一列了解多少：

CREATE INDEX orders_status_idx ON orders (status);

SELECT most_common_vals, most_common_freqs
FROM pg_stats WHERE tablename = 'orders' AND attname = 'status';

这是统计信息（statistics）。ANALYZE 命令对表采样，为每一列记下最常见的值和它们的占比，存在 pg_stats 里。这里它知道 completed 占 90%，cancelled 只占 0.5%。规划器（planner）拿这些占比去估“这个条件会留下多少行”，这个比例叫选择性（selectivity）。

同一条查询，换五个取值，各执行一次：

规划器并不偏爱索引，它在比较代价。默认的代价参数里，顺序读一页记 1（seq_page_cost），随机读一页记 4（random_page_cost）。走索引意味着随机读：

• completed 有 18000 行，几乎每一页上都有。逐条回表要随机读遍整张表，不如顺序读 206 页，所以是 Seq Scan。这个选择是对的，索引没有坏。
• cancelled 只有 100 行，是 Index Scan。行少还不够：第 3 节的会员 295 只有 10 行，用的却是位图扫描。统计信息里还有一项 correlation，记的是列值的顺序和行在表里的先后有多一致，status 是 0.81，member_id 只有 0.02。这个值高，规划器才估计沿索引逐条取的随机读不多。
• 中间的三个取值用位图扫描：行不算少，先把页号收齐再读，避免同一页读好几次。

估错了会怎样：一张没有统计信息的新表

规划器的选择只和估计一样好。把 3 月的订单另存成一张新表，马上拿它做报表：

CREATE TABLE march_orders AS
SELECT * FROM orders
WHERE created_at >= timestamptz '2026-03-01 00:00+08' AND created_at < timestamptz '2026-04-01 00:00+08';

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT m.tier, count(*) FROM march_orders o JOIN members m ON m.id = o.member_id
WHERE o.status = 'completed' GROUP BY m.tier;

ANALYZE march_orders;

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT m.tier, count(*) FROM march_orders o JOIN members m ON m.id = o.member_id
WHERE o.status = 'completed' GROUP BY m.tier;

DROP TABLE march_orders;

GroupAggregate  (cost=76.37..76.44 rows=2 width=13) (actual rows=2.00 loops=1)
  Group Key: m.tier
  ->  Sort  (cost=76.37..76.39 rows=7 width=5) (actual rows=828.00 loops=1)
        Sort Key: m.tier
        Sort Method: quicksort  Memory: 17kB
        ->  Hash Join  (cost=33.69..76.27 rows=7 width=5) (actual rows=828.00 loops=1)
              Hash Cond: (m.id = o.member_id)
              ->  Seq Scan on members m  (cost=0.00..35.01 rows=2001 width=9) (actual rows=2001.00 loops=1)
              ->  Hash  (cost=33.60..33.60 rows=7 width=4) (actual rows=828.00 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 27kB
                    ->  Seq Scan on march_orders o  (cost=0.00..33.60 rows=7 width=4) (actual rows=1086.00 loops=1)
                          Filter: (status = 'completed'::text)
                          Rows Removed by Filter: 120

HashAggregate  (cost=98.06..98.08 rows=2 width=13) (actual rows=2.00 loops=1)
  Group Key: m.tier
  Batches: 1  Memory Usage: 32kB
  ->  Hash Join  (cost=60.02..93.95 rows=823 width=5) (actual rows=828.00 loops=1)
        Hash Cond: (o.member_id = m.id)
        ->  Seq Scan on march_orders o  (cost=0.00..31.08 rows=1086 width=4) (actual rows=1086.00 loops=1)
              Filter: (status = 'completed'::text)
              Rows Removed by Filter: 120
        ->  Hash  (cost=35.01..35.01 rows=2001 width=9) (actual rows=2001.00 loops=1)
              Buckets: 2048  Batches: 1  Memory Usage: 79kB
              ->  Seq Scan on members m  (cost=0.00..35.01 rows=2001 width=9) (actual rows=2001.00 loops=1)

看第一份计划最里面的 Seq Scan on march_orders：估计 rows=7，实际 actual rows=1086.00，差了一百多倍。新表还没有统计信息，规划器只能套用默认的选择性去猜。执行 ANALYZE 之后，第二份计划的估计值和实际值对上了，连接的方向和汇总的方式也跟着变了。

这次估错没有造成大麻烦，因为表小。表再大些、再多连接几张表，估少了一百倍就可能选出慢上千倍的计划。真实服务器上，autovacuum 进程会在表变化到一定程度后自动补做 ANALYZE，但批量导入之后马上跑的查询赶不上它，临时表它也不会管。

习惯觉得计划不对，先比同一个节点上的 rows= 和 actual rows=。相差几十倍以上，多半是统计信息的问题：批量导入或建完新表之后手动 ANALYZE。相差不大，那就相信它。

5　找：把索引修成查询的形状

索引帮得上多少忙，取决于它的列顺序和查询的写法对不对得上。

客服工单　市图书馆的采购账号说“我的订单”页越来越慢。这个账号有 689 张订单，页面只显示最近 20 张。

复合索引：列的顺序就是排序的顺序

“我的订单”要的是某个会员最近的 20 张。用第 3 节建的单列索引：

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT id, status, total_cents FROM orders
WHERE member_id = 2001 ORDER BY created_at DESC LIMIT 20;

Limit  (cost=242.57..242.62 rows=20 width=29) (actual rows=20.00 loops=1)
  ->  Sort  (cost=242.57..244.30 rows=689 width=29) (actual rows=20.00 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 19kB
        ->  Bitmap Heap Scan on orders  (cost=9.63..224.24 rows=689 width=29) (actual rows=689.00 loops=1)
              Recheck Cond: (member_id = 2001)
              Heap Blocks: exact=206
              ->  Bitmap Index Scan on orders_member_idx  (cost=0.00..9.46 rows=689 width=0) (actual rows=689.00 loops=1)
                    Index Cond: (member_id = 2001)
                    Index Searches: 1

从里往外读：索引找出这个会员全部 689 张订单，回表读了 206 个页（Heap Blocks），全部取出来之后排序（Sort），最后只留 20 张（Limit）。为了 20 行读了 689 行。

换成复合索引（composite index），先按会员、再按下单时间排：

CREATE INDEX orders_member_created_idx ON orders (member_id, created_at);
DROP INDEX orders_member_idx;

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT id, status, total_cents FROM orders
WHERE member_id = 2001 ORDER BY created_at DESC LIMIT 20;

Limit  (cost=0.29..24.90 rows=20 width=29) (actual rows=20.00 loops=1)
  ->  Index Scan Backward using orders_member_created_idx on orders  (cost=0.29..848.23 rows=689 width=29) (actual rows=20.00 loops=1)
        Index Cond: (member_id = 2001)
        Index Searches: 1

Sort 节点消失了。索引里同一个会员的条目本来就按 created_at 排着，从最新的一头往回走（Index Scan Backward），取够 20 条就停，actual rows=20.00（同一行的 rows=689 是按“全部取完”估的，上层有 Limit 时这两个数对不上不算估错）。总代价从 242.62 降到 24.90。原来的单列索引也可以删掉了：复合索引的第一列同样能服务 WHERE member_id = ?。

列顺序的规则：等值条件的列放前面，排序或范围条件的列放后面。顺便一提，同样的查询换成只有 10 张订单的会员 295，计划里仍然有 Sort：10 行不够 20 行的上限，没法提前停，规划器判断取回来排一下更便宜。它是按代价选的，不是按规则选的。

不带第一列的查询：跳跃扫描

门店后台常按“某家店某一天”查订单，于是有了 (store_id, created_at) 这个索引。那么不指定门店、只按日期查的全店日报，用得上它吗？先看建索引之前，再看之后：

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM orders
WHERE created_at >= timestamptz '2026-03-01 00:00+08' AND created_at < timestamptz '2026-03-02 00:00+08';

CREATE INDEX orders_store_created_idx ON orders (store_id, created_at);

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM orders
WHERE created_at >= timestamptz '2026-03-01 00:00+08' AND created_at < timestamptz '2026-03-02 00:00+08';

Aggregate  (cost=506.10..506.11 rows=1 width=8) (actual rows=1.00 loops=1)
  ->  Seq Scan on orders  (cost=0.00..506.00 rows=39 width=0) (actual rows=38.00 loops=1)
        Filter: ((created_at >= '2026-03-01 00:00:00+08'::timestamp with time zone) AND (created_at < '2026-03-02 00:00:00+08'::timestamp with time zone))
        Rows Removed by Filter: 19962

Aggregate  (cost=56.61..56.62 rows=1 width=8) (actual rows=1.00 loops=1)
  ->  Index Only Scan using orders_store_created_idx on orders  (cost=0.29..56.52 rows=39 width=0) (actual rows=38.00 loops=1)
        Index Cond: ((created_at >= '2026-03-01 00:00:00+08'::timestamp with time zone) AND (created_at < '2026-03-02 00:00:00+08'::timestamp with time zone))
        Heap Fetches: 0
        Index Searches: 15

用上了，而且是 Index Only Scan，连表都没碰。关键在最后一行 Index Searches: 15：store_id 的取值很少，只有 12 家店外加 NULL。PostgreSQL 对每一个取值分别进一次索引，跳着查；比 13 多出来的 2 次，是在探还有没有别的取值。这叫跳跃扫描（skip scan） 18 新增，17 及更早的版本做不到，旧教程里“不带第一列就用不上复合索引”的说法因此不再绝对。

但它只在第一列的不同取值很少时才划算。(member_id, created_at) 的第一列有 1501 个不同的值，只按日期查时就帮不上忙：上面第一份计划执行时这个索引已经在了，规划器仍然选了顺序扫描。设计索引时还是按上一小节的规则来。

别把索引列包进函数

第 2 节说过，查某一天要写成半开区间。现在可以看到另一个理由：

EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders WHERE store_id = 3 AND created_at::date = date '2026-03-01';

EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders
WHERE store_id = 3
  AND created_at >= timestamptz '2026-03-01 00:00+08' AND created_at < timestamptz '2026-03-02 00:00+08';

Aggregate
  ->  Index Only Scan using orders_store_created_idx on orders
        Index Cond: (store_id = 3)
        Filter: ((created_at)::date = '2026-03-01'::date)

Aggregate
  ->  Index Only Scan using orders_store_created_idx on orders
        Index Cond: ((store_id = 3) AND (created_at >= '2026-03-01 00:00:00+08'::timestamp with time zone) AND (created_at < '2026-03-02 00:00:00+08'::timestamp with time zone))

第一种写法里，日期条件落在 Filter 上：索引只帮忙定位了 3 号店，然后把这家店的每一条都拿出来算一遍 ::date 再比。第二种写法里，三个条件全在 Index Cond 上，索引直接跳到那一天。列一旦被包进函数或类型转换，索引里存的那个顺序就对不上了。

深翻页：记住上一页的最后一行

后台的订单列表按编号倒序，翻到第 751 页：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT id, status FROM orders ORDER BY id DESC LIMIT 20 OFFSET 15000;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT id, status FROM orders WHERE id < 5001 ORDER BY id DESC LIMIT 20;

Limit (actual rows=20.00 loops=1)
  ->  Index Scan Backward using orders_pkey on orders (actual rows=15020.00 loops=1)
        Index Searches: 1

Limit (actual rows=20.00 loops=1)
  ->  Index Scan Backward using orders_pkey on orders (actual rows=20.00 loops=1)
        Index Cond: (id < 5001)
        Index Searches: 1

OFFSET 15000 的意思是“先数出 15000 行再扔掉”，所以索引扫描实际走了 15020 行。第二种写法叫键集分页（keyset pagination）：带上一页最后一行的编号，从它后面接着取，只走了 20 行，翻到多深都一样快。排序键不唯一时（比如按下单时间），把主键补进排序键，两列同一个方向：ORDER BY created_at DESC, id DESC；下一页用行值比较 (created_at, id) < ($1, $2)，索引也建成 (created_at, id)。

索引不是免费的

还有三种形状值得知道名字：部分索引（partial index）只收录满足条件的行，比如 ON orders (created_at) WHERE status = 'paid' 就是一份又小又准的待发货队列；表达式索引（expression index）为 lower(email) 这类写法服务；INCLUDE 可以让索引多带几列，凑成上面那种不回表的 Index Only Scan。反过来，查询里只要多要一列索引里没有的，就得回表，所以 SELECT * 会让这类优化落空。

每多一个索引，每次 INSERT 就多写一处。UPDATE 的代价更隐蔽，要到下一层才看得清。现在 orders 上有 4 个索引，记住这个数。

习惯复合索引里等值列在前，排序和范围列在后；不在索引列上套函数或类型转换；深翻页用键集，不用大 OFFSET。

6　改：UPDATE 其实是再写一行

发货那一刻，订单 42 没有被改写。页上多了一个新版本，旧版本还在原地。

一个常被问到的问题　这条 UPDATE 只改了一个字段、影响 1 行，表文件为什么变大了？

先记下第 0 页上五张订单现在的样子。它们是这一节和下一节的全部演员：

SELECT ctid, xmin, xmax, id, status, total_cents, version
FROM orders WHERE id BETWEEN 42 AND 46 ORDER BY id;

订单 42 发货

用状态机那一篇的写法，把“必须是已付款”写进 WHERE，同时让版本号加一。RETURNING 里的 old 和 new 18 新增 可以把改之前和改之后的值一起拿回来：

UPDATE orders SET status = 'shipped', version = version + 1
WHERE id = 42 AND status = 'paid'
RETURNING old.ctid AS old_ctid, new.ctid AS new_ctid, old.xmin AS old_xmin, new.xmin AS new_xmin,
          new.status, new.total_cents, new.version;

订单 42 的位置从 (0,42) 变成了 (0,99)。行没有在原地被改写，而是在同一页的第 99 个槽位写了一份新的。每个改数据的事务都会领到一个递增的事务号（transaction ID，xid），这次更新的事务号是 822，新的一行把它记在了 xmin 里。

旧的那一行呢？直接看页面。orders_page0 是建库时准备的一个视图，把 pageinspect 读出来的第 0 页整理成了好读的样子：

SELECT * FROM orders_page0 WHERE lp IN (42, 99);

两行都在。这两行是同一张订单的两个行版本（row version）：

• 槽位 42 是旧版本。它的 xmax 被填上了 822，意思是“事务 822 用新版本取代了我”，next_version 指向 (0,99)。
• 槽位 99 是新版本。它的 xmin 是 822，意思是“事务 822 创建了我”。

规则只有三条：INSERT 写一个带 xmin 的新行；DELETE 只是给行填上 xmax；UPDATE 是两者合起来，给旧版本填 xmax，再插入一个新版本。这种做法叫多版本并发控制（MVCC，multi-version concurrency control）。开头那个问题的答案就在这里：UPDATE 总是在增加内容，不管改了几个字段，写下的都是完整的一行。

索引也受了牵连。主键 id 没有变，但主键索引里现在有两条 42：

-- leaf page 1 of the primary-key index; '2a' is 42 in hexadecimal
SELECT htid AS points_to FROM bt_page_items('orders_pkey', 1) WHERE data = '2a 00 00 00 00 00 00 00';

一条指向旧版本，一条指向新版本。索引条目记的是 ctid，新版本换了位置，orders 上的 4 个索引就各自多了一条。这就是上一节末尾说的“UPDATE 的隐蔽代价”。

面板的八步（第 0 步是起点），就是这一节和下一节按顺序执行的那些语句。读正文时可以把它停在对应的一步，对照着看。

一条查询路过：顺手清理

现在像普通读者那样查一次订单 42，再看页面：

SELECT ctid, xmin, xmax, status, version FROM orders WHERE id = 42;

SELECT * FROM orders_page0 WHERE lp IN (42, 99);

查询拿到的是新版本。同时槽位 42 变成了 dead，里面的数据已经被清掉。这是页内剪枝（pruning）：任何访问到这一页的语句，发现页里有对谁都没用的旧版本、空间又偏紧，就会顺手收拾一下。槽位本身还得留着当“墓碑”，因为刚才看到的那几条索引条目还指着它，要等 VACUUM 来把索引里的条目一并删掉（下一节）。

订单 43 改金额：不动索引的更新

给订单 43 的金额加 100 分。total_cents 这一列没有任何索引：

UPDATE orders SET total_cents = total_cents + 100
WHERE id = 43
RETURNING old.ctid AS old_ctid, new.ctid AS new_ctid, new.xmin AS new_xmin, new.status, new.total_cents, new.version;

SELECT * FROM orders_page0 WHERE lp IN (43, 100);

同样是写了一个新版本，但标志位不同：旧版本带 HOT_UPDATED，新版本带 ONLY_TUPLE。再看主键索引里的 43：

SELECT htid AS points_to FROM bt_page_items('orders_pkey', 1) WHERE data = '2b 00 00 00 00 00 00 00';

只有一条，仍然指着旧位置 (0,43)。这是 HOT 更新（heap-only tuple）：新版本和旧版本在同一页，索引一条都不用加，读的时候先按索引找到旧位置，再顺着页内的指针走到新版本。它需要两个条件同时成立：

• 这次更新没有改变任何被索引的列的值。订单 42 改的是 status，上面有索引，所以走不了 HOT。
• 同一页里放得下新版本。fillfactor = 90 留的那一成空间，就是给它准备的。

这给了“要不要给某一列建索引”一个新的考量：给 status 建索引，意味着状态机每流转一次，就要多写 4 处索引。另外两个和 ORM 有关的事实：把所有字段原样写回去并不妨碍 HOT，看的是被索引列的值有没有变；但一次什么都没改的保存照样会写出一个新版本，所以保存之前先判断有没有变化。

订单 44：改了又回滚

BEGIN;
UPDATE orders SET total_cents = total_cents + 500
WHERE id = 44
RETURNING old.ctid AS old_ctid, new.ctid AS new_ctid, new.xmin AS new_xmin, new.status, new.total_cents, new.version;
ROLLBACK;

SELECT lp, slot, xmin, xmax, pg_xact_status(xmax::text::xid8) AS xmax_status
FROM orders_page0 WHERE lp = 44;

事务 824 把订单 44 的新版本写到了 (0,101)，然后回滚了。回滚之后再看页面：槽位 44 的 xmax 仍然是 824。ROLLBACK 没有回头去擦任何东西，它只是在事务状态的记录里把 824 标成了 aborted。此后谁读到这两个版本，都会去查这个状态：创建者回滚了的版本不算数，删改者回滚了的 xmax 也不算数。

所以在 PostgreSQL 里回滚几乎是瞬间完成的，不管事务改了多少行。代价是页上留下了没人要的版本，要等以后清理。

谁看得见哪个版本：快照

一页上同时躺着新旧版本、已提交和已回滚的版本，读的人怎么知道该看哪个？每条查询开始时会拍一张快照（snapshot），记下“此刻哪些事务已经结束”。一个版本对这张快照可见，要同时满足：

• 创建它的事务（xmin）在拍快照之前已经提交；
• 没有人删改它，或者删改它的事务（xmax）在拍快照时还没提交，或者已经回滚。

例外只有一个：自己事务里先前的语句做的修改，自己看得见。把面板里的“读者”切来切去，可以看到同一页对不同时刻的快照是不同的内容。

快照什么时候拍，是隔离级别（isolation level）的核心区别，第 8 节的冲突全都由它而来：

• 默认的 READ COMMITTED：每条语句拍一张新的。同一个事务里两次 SELECT，可能看到别人在中间提交的结果。
• REPEATABLE READ：事务里第一条查询或修改语句开始时拍一张，之后一直用它。注意不是在 BEGIN 那一刻。

别人没提交的修改：读的人不用等

会话 B 给订单 45 加了 1000 分，然后就停在那里，不提交：

BEGIN;
UPDATE orders SET total_cents = total_cents + 1000
WHERE id = 45
RETURNING old.ctid AS old_ctid, new.ctid AS new_ctid, new.xmin AS new_xmin, new.status, new.total_cents, new.version;
PREPARE TRANSACTION 'session_b';

我们（会话 A）这时去读订单 45：

SELECT ctid, xmin, xmax, total_cents FROM orders WHERE id = 45;

立刻返回，没有等待，读到的是旧金额 27855，位置还是 (0,45)。B 的新版本已经躺在 (0,101) 了，但创建它的事务 825 还没提交，按上面的规则对我们不可见。这就是 MVCC 最实用的性质：读不阻塞写，写也不阻塞读。报表不必躲着写入高峰。

顺带纠正一个常见的误读：这一行的 xmax 是 825，不是 0，但它明明还在、还能读到。xmax 不为 0 只说明有事务动过它，可能还没提交（订单 45），可能回滚了（订单 44），也可能只是加了行锁（第 9 节）。

MongoDB 的 WiredTiger 引擎也是多版本的，但旧版本不和当前文档放在一起。PostgreSQL 把旧版本直接留在表的页里，所以读旧版本不用去别处找，回滚不用做任何事，而清理就成了一件必须有人做的事。

习惯没有变化就不要发 UPDATE；频繁更新的列，建索引前想一想 HOT；永远用主键而不是 ctid 认行。

7　改：谁来收旧版本

旧版本要等到对所有人都没用了才能回收。一个不结束的事务，会让这一刻一直不来。

周一早上　磁盘告警。库存表只有 6000 行，文件却是上周的三倍。追查下去，是周五晚上一个开着没提交的事务。

会话 B 还停在那里。我们继续干活，给订单 46 加 100 分，这是一次普通的、马上提交的更新：

UPDATE orders SET total_cents = total_cents + 100
WHERE id = 46
RETURNING old.ctid AS old_ctid, new.ctid AS new_ctid, new.xmin AS new_xmin, new.status, new.total_cents, new.version;

再看一眼此刻的快照长什么样：

SELECT pg_current_snapshot();

三段用冒号隔开。第一段 825：比它小的事务都结束了。第二段 827：从它开始的事务，在拍快照时都还没结束，一律当作看不见。第三段是夹在两者之间、当时还在进行的事务，这里正是会话 B 的 825。只要 B 不结束，此后每个人拍的每张快照里都会有它。

VACUUM 做什么

清理旧版本的命令是 VACUUM。它扫描表，把对谁都不可见的版本删掉，把索引里指向它们的条目也删掉，然后把腾出的空间登记为可复用。在此之前先看一个和它有关的现象。查会员 295 的下单时间，要的两列都在索引里，所以是不回表的 Index Only Scan：

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT member_id, created_at FROM orders WHERE member_id = 295;

Index Only Scan using orders_member_created_idx on orders  (cost=0.29..4.46 rows=10 width=12) (actual rows=10.00 loops=1)
  Index Cond: (member_id = 295)
  Heap Fetches: 2
  Index Searches: 1

说是不回表，却有 Heap Fetches: 2。原因是索引条目里没有 xmin、xmax，光看索引判断不了一个版本可不可见。PostgreSQL 为每张表另外维护一张可见性映射（visibility map），每个表页占两位，其中一位表示“这一页上的所有版本对所有人都可见”。这一位只有 VACUUM 会设上，页上任何修改都会把它清掉。第 0 页刚被我们改了好几次，所以落在这一页的条目必须回表确认。

现在执行 VACUUM，然后看第 0 页：

-- INDEX_CLEANUP ON: with so few dead items the default (AUTO) would skip the index pass
VACUUM (INDEX_CLEANUP ON) orders;

SELECT * FROM orders_page0 WHERE lp BETWEEN 42 AND 46 OR lp >= 99 ORDER BY lp;

对照上一节的结果，有的收了，有的没收：

• 槽位 42 从 dead 变成了 unused。订单 42 的旧版本是被事务 822 取代的，索引里指向它的条目已经删掉，槽位可以给新行用了。
• 槽位 46 还是 normal。它是订单 46 的旧版本，取代它的事务 826 明明已经提交，新读者谁也不会再看它，VACUUM 却没有动它。

差别在于会话 B。事务 825 开始得比 826 早，而且还没结束。VACUUM 的规则很保守：只要还有一个更早开始的事务没结束，它就不能确定这个事务以后会不会需要看到旧版本，于是凡是在 825 之后才“死”的版本，一个都不能收。订单 42 的旧版本死在 B 开始之前，所以不受影响。

会话 B 结束之后

管理员终于断开了那个会话，它的事务随之回滚。再执行一次 VACUUM：

ROLLBACK PREPARED 'session_b';

VACUUM (INDEX_CLEANUP ON) orders;

SELECT * FROM orders_page0 WHERE lp BETWEEN 42 AND 46 OR lp >= 99 ORDER BY lp;

这回收干净了。槽位 46 变成 redirect，指向 102：索引条目还指着 46，由它把来访者转到同页的新版本，这是 HOT 更新之后的常态。B 留下的那个版本所在的槽位 101 变成了 unused。五张订单现在各自只剩一个有效版本：

SELECT ctid, xmin, xmax, id, status, total_cents, version
FROM orders WHERE id BETWEEN 42 AND 46 ORDER BY id;

订单 44 和 45 上的 xmax 是回滚了的事务留下的痕迹，不影响任何人。最后回到刚才那条查询：

EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT member_id, created_at FROM orders WHERE member_id = 295;

Index Only Scan using orders_member_created_idx on orders  (cost=0.29..4.46 rows=10 width=12) (actual rows=10.00 loops=1)
  Index Cond: (member_id = 295)
  Heap Fetches: 0
  Index Searches: 1

Heap Fetches 从 2 回到了 0。VACUUM 把第 0 页重新标成了“全部可见”，仅索引扫描名副其实了。

膨胀：VACUUM 不会让文件变小

把订单表复制一份，对每一行各更新一次，量一量文件大小：

CREATE TABLE bloat_demo AS SELECT * FROM orders;
SELECT pg_size_pretty(pg_relation_size('bloat_demo')) AS freshly_loaded;

UPDATE bloat_demo SET version = version + 1;
SELECT pg_size_pretty(pg_relation_size('bloat_demo')) AS after_updating_every_row;

VACUUM bloat_demo;
SELECT pg_size_pretty(pg_relation_size('bloat_demo')) AS after_vacuum;

UPDATE bloat_demo SET version = version + 1;
SELECT pg_size_pretty(pg_relation_size('bloat_demo')) AS after_second_update;

VACUUM FULL bloat_demo;
SELECT pg_size_pretty(pg_relation_size('bloat_demo')) AS after_vacuum_full;

DROP TABLE bloat_demo;

• 每一行都更新一次，文件从 1536 kB 涨到 3008 kB，差不多翻倍：每行都多了一个版本。
• VACUUM 之后仍是 3008 kB。它把旧版本占的空间标成了可复用，但不把文件还给操作系统。唯一的例外是表末尾整页空出来的那几页，这里表的末尾住着新版本，一页也截不掉。
• 再全表更新一次，文件没有继续涨：新版本住进了刚腾出来的空间。
• VACUUM FULL 把表重写成一个新文件，才回到 1480 kB。

所以一张经常更新的表，稳定下来的大小大约是“有效数据加上两次清理之间产生的旧版本”，这是正常的。失控的增长叫膨胀（bloat），几乎总是因为清理跟不上，或者像开头那个事故一样被挡住了。VACUUM FULL 能缩回去，但它全程锁住整张表，读写都进不来，还要临时多占一份磁盘，不能当作日常手段。DELETE 也是同理：它只填 xmax，文件不会变小。要清空整张表用 TRUNCATE；要按时间淘汰旧数据，用分区表，到期直接删掉整个分区。

平时是谁在做这件事

真实的服务器上有一组后台进程叫 autovacuum，它盯着每张表的变化量，默认在旧版本超过“50 行加上表的 20%”时自动清理，并顺便更新统计信息。大多数时候不用管它，但有两件事要知道：

• 不要关掉它。除了回收空间，它还负责“冻结”很老的行。事务号只有 32 位，会循环使用，不冻结的话，足够老的行有一天会被误判成“来自未来”。这件事 PostgreSQL 会强制去做，拖得越久代价越大。
• 它会被挡住。挡住它的都是“还可能需要旧版本的人”：写过数据或者用 REPEATABLE READ 开着、却一直不提交的事务（连接状态显示为 idle in transaction）、跑了几个小时的查询、没人消费的复制槽、被遗忘的预备事务。被挡住期间，全库所有表新产生的旧版本都收不走。

最常见的来源是应用自己：在事务中间调用外部 HTTP 接口，在事务里等用户确认，或者从连接池借了连接、开了事务却没有走到提交那一行。数据库这边可以设两个兜底的超时，idle_in_transaction_session_timeout 和 transaction_timeout（17 起），超时就断开连接。它们默认都不启用。

习惯事务要短，事务里不等任何外部的东西；给应用的数据库角色设上面两个超时；不关 autovacuum，不把 VACUUM FULL 排进日常任务。

8　守：两个请求同时改一行

读的人各看各的版本，写的人必须分出先后。分完先后接着怎么办，由隔离级别决定。

大促零点　一号店最后一本《三体》同时被两位顾客下单。两个请求都返回了“下单成功”，没有任何报错。

上一层说读不用等。写就不同了：两个事务要改同一行时，后到的那个必须等先到的事务结束。等完之后的事，三个隔离级别各不相同：

标准里的第四个级别 READ UNCOMMITTED，在 PostgreSQL 里的行为和 READ COMMITTED 相同。任何级别都读不到别人没提交的数据。

把规则写进 WHERE，然后数行数

面板前两个场景的结论是同一句话：不要先读出来、在代码里判断、再写回去，要把判断条件放进修改语句本身。等待之后的那次“重新判断”会替你把关。它在单个会话里看起来是这样的。订单 42 确认收货，同一个请求发两次：

UPDATE orders SET status = 'completed', version = version + 1
WHERE id = 42 AND status = 'shipped';

UPDATE orders SET status = 'completed', version = version + 1
WHERE id = 42 AND status = 'shipped';

第一次 UPDATE 1，第二次 UPDATE 0。并发时后到的那个请求得到的就是这个 UPDATE 0。它不是报错，所以应用必须检查影响行数，为 0 就告诉调用方“状态已经变了”。DDD 那一篇里的乐观锁 WHERE id = … AND version = … 是同一个套路。

卖最后一本书也一样，把“还有货”写进条件：

UPDATE inventory SET qty = qty - 1
WHERE store_id = 1 AND book_id = 1 AND qty >= 1
RETURNING old.qty AS qty_before, new.qty AS qty_after;

UPDATE inventory SET qty = qty - 1
WHERE store_id = 1 AND book_id = 1 AND qty >= 1
RETURNING old.qty AS qty_before, new.qty AS qty_after;

第一个请求把库存从 1 扣到 0，第二个请求一行也没改到，应用据此回答“已售罄”。如果有人忘了写 AND qty >= 1，第 2 节那条约束还会再拦一道：

UPDATE inventory SET qty = qty - 1 WHERE store_id = 1 AND book_id = 1;

不过约束只能兜住 qty = qty - 1 这种在数据库里算的写法。如果应用先读到 1，自己算出 0，再发 SET qty = 0，两个请求写的都是合法的 0，约束无从发现。开头那次事故就是这样发生的。

这一招的边界

守卫式的 UPDATE 之所以安全，是因为条件和被改的数据在同一行上：等待结束后，PostgreSQL 只对它等的那一行重新判断条件。有些规则不属于任何一行，这一招就用不上：

• “每位会员限购一本”“会员卡号不能重复”：规则说的是“不能有第二行”。用唯一约束，让数据库去拦第二次插入。
• “创始会员最多 100 名”：两个请求各自数到 99，各插一行，就成了 101 名。两边读的是旧数据，写的是不同的行，谁也不用等谁，在 REPEATABLE READ 下也都能提交。这种异常叫写偏斜（write skew）。挡住它的办法有三种：用 SERIALIZABLE 并重试；在默认的 READ COMMITTED 下，让两个事务先去锁同一行（比如活动表里代表这次活动的那一行，用 SELECT … FOR UPDATE），拿到锁再数（REPEATABLE READ 下这样不够：快照在等锁之前就拍好了，数到的还是 99）；或者把规则改写成约束，比如把人数记在活动那一行上，加 CHECK (joined <= 100)。

由此可见，REPEATABLE READ 不等于“事务像一个接一个执行那样”。能给出这个保证的只有 SERIALIZABLE，代价是更多的 40001 和重试。

事务里一条语句失败之后

一笔收款要同时做两件事：记下订单，记下支付回调。和记账里一笔分录的借贷两行一样，它们必须一起成功或者一起不存在，所以放进同一个事务。看第二条语句失败时会发生什么：

BEGIN;
INSERT INTO orders (store_id, status, total_cents, created_at)
VALUES (1, 'paid', 5900, timestamptz '2026-07-01 10:00+08')
RETURNING id;
INSERT INTO payment_events (provider_event_id, order_id, amount_cents)
VALUES ('evt_0001', 999999, 5900);
SELECT count(*) FROM orders;
COMMIT;

三件事值得记住：

• 外键报错之后，这个事务就进入了“已中止”状态。后面的语句不管对错，一律报 25P02，直到事务结束。PostgreSQL 不会只撤销出错的那一条然后继续。
• 最后那条 COMMIT 没有报错，但返回的是 ROLLBACK：整个事务被回滚了，那张订单并没有留下。在 ORM 的事务回调里吞掉异常、继续往下写，得到的就是这个结果。
• 预料到某一步可能失败又想继续，用保存点：先 SAVEPOINT，失败后 ROLLBACK TO SAVEPOINT，事务就恢复可用。ORM 的“嵌套事务”一般就是这么实现的。

还有一个副作用。事务里的那张订单拿到了编号 20001，事务回滚了，再插一张：

INSERT INTO orders (store_id, status, total_cents, created_at)
VALUES (1, 'paid', 5900, timestamptz '2026-07-01 10:00+08')
RETURNING id;

新订单是 20002，20001 这个号永远空着。自增编号从序列（sequence）里取，取号不参与回滚，否则并发插入就得互相等。所以自增主键有空洞是正常的；发票号这类必须连续的编号，要另外设计。

重复的请求：让唯一约束来判

支付平台的回调会重复投递。把平台给的事件编号设为主键，重复的那次自然插不进去，ON CONFLICT DO NOTHING 让它安静地变成“0 行”而不是报错：

INSERT INTO payment_events (provider_event_id, order_id, amount_cents)
VALUES ('evt_0001', 20002, 5900)
ON CONFLICT (provider_event_id) DO NOTHING;

INSERT INTO payment_events (provider_event_id, order_id, amount_cents)
VALUES ('evt_0001', 20002, 5900)
ON CONFLICT (provider_event_id) DO NOTHING;

INSERT 0 1 是插入了 1 行，INSERT 0 0 是 0 行。应用看到 0 行，就知道这个事件处理过了。两次回调同时到达也没问题：后到的那个会等先到的事务结束，再决定是插入还是跳过。

带回 Node 代码：同一个连接，整体重试

在 Node 里最容易写错的是这一行：await pool.query('BEGIN')。连接池每次 pool.query 都可能换一个连接，于是 BEGIN 落在一个连接上，后面的语句落在别的连接上各自提交，那个执行了 BEGIN 的连接则带着一个不结束的事务回到池里。事务必须从头到尾用同一个连接。下面的写法同时处理了上表里“整个事务重做”的要求：

// The parts of node-postgres' Pool and PoolClient this helper relies on.
interface Queryable {
  query(text: string, values?: unknown[]): Promise<{ rows: any[]; rowCount: number | null; command: string }>;
}
interface PooledClient extends Queryable {
  release(destroy?: boolean): void;
}
interface ConnectionPool {
  connect(): Promise<PooledClient>;
}

// serialization_failure and deadlock_detected: the transaction did nothing wrong, run it again
const RETRYABLE = new Set(['40001', '40P01']);

async function inTransaction<T>(
  pool: ConnectionPool,
  work: (tx: Queryable) => Promise<T>,
  maxAttempts = 3,
): Promise<T> {
  for (let attempt = 1; ; attempt += 1) {
    // BEGIN, the work and COMMIT must all travel on this one connection
    const client = await pool.connect();
    let broken = false;
    try {
      await client.query('BEGIN');
      const result = await work(client);
      const committed = await client.query('COMMIT');
      // COMMIT in a transaction that already failed answers ROLLBACK instead of raising an error
      if (committed.command === 'ROLLBACK') throw new Error('a statement inside work() failed; nothing was committed');
      return result;
    } catch (error) {
      // a connection that cannot even roll back must not return to the pool
      await client.query('ROLLBACK').catch(() => { broken = true; });
      const code = (error as { code?: string }).code;
      if (code === undefined || !RETRYABLE.has(code) || attempt >= maxAttempts) throw error;
    } finally {
      client.release(broken);
    }
    // back off only after the connection is back in the pool
    await new Promise(resolve => setTimeout(resolve, 2 ** attempt * 25 + Math.random() * 25));
  }
}

// Ship order 42: the guard lives in WHERE, the row count says whether we won.
async function shipOrder(pool: ConnectionPool, orderId: number): Promise<boolean> {
  return inTransaction(pool, async tx => {
    const updated = await tx.query(
      "UPDATE orders SET status = 'shipped', version = version + 1 WHERE id = $1 AND status = 'paid'",
      [orderId],
    );
    return updated.rowCount === 1;
  });
}

COMMIT 后面那一行检查，防的就是上面说的“吞掉异常接着写”：这时 COMMIT 不报错，只是回答 ROLLBACK。重试的是整个 work，包括里面读数据、做判断的代码，而不是只重发失败的那条 SQL：上一轮读到的东西已经过时了。因此 work 里不能有发邮件、调外部接口这类重做一遍就出事的操作。判断错误用 code（就是 SQLSTATE），不用报错文字。值一律用 $1 这样的参数传，不拼进 SQL 字符串。

习惯判断条件写进 UPDATE … WHERE 并检查影响行数；跨行的规则交给约束；40001 和 40P01 重试整个事务；事务从头到尾用同一个连接。

9　守：锁，和不锁表的迁移

行锁记在行上，表锁排成队。迁移出事故，多半出在排队上。

周二 20:00　发布脚本给订单表加一列。这条语句本身只要几毫秒，全站却卡了 80 秒。

行锁写在行里

想先读后改又不想被人插队，可以用 SELECT … FOR UPDATE 把行锁住。锁住之后看看锁记在哪里：

BEGIN;
SELECT id, status FROM orders WHERE id = 43 FOR UPDATE;

SELECT locktype, relation::regclass AS relation, mode
FROM pg_locks
WHERE pid = pg_backend_pid() AND (relation = 'orders'::regclass OR locktype = 'transactionid');

SELECT xmax, pg_current_xact_id() AS my_xid FROM orders WHERE id = 43;
COMMIT;

pg_locks 是内存里的锁表。里面只有两条和我们有关：orders 上一把表级的 RowShareLock，以及我们自己事务号上的一把锁，没有任何一条代表“订单 43 这一行”。行锁在第三个结果里：订单 43 的 xmax 变成了 837，正是我们的事务号。行锁就写在行自己身上，所以锁一百万行也不占内存；想锁同一行的人，等的是 837 这个事务结束。这也是第 6 节说的“xmax 不为 0 不代表被删”的第三种情况。

行锁只挡两种人：要改这一行的，和也想锁这一行的。普通的 SELECT 不受影响。下面请会话 C 出场来验证，它读过 orders，扣了 2 号店一本《三体》的库存，然后停住不提交：

BEGIN;
SELECT count(*) FROM orders WHERE store_id = 3;
UPDATE inventory SET qty = qty - 1 WHERE store_id = 2 AND book_id = 1 RETURNING qty;
PREPARE TRANSACTION 'session_c';

我们读那一行库存，立刻返回，看到的是 C 扣之前的数：

SELECT qty FROM inventory WHERE store_id = 2 AND book_id = 1;

想锁它就不行了。正常情况下这条语句会一直等到 C 结束；加上 NOWAIT，它改成立刻报错，正好让我们看到冲突：

SELECT qty FROM inventory WHERE store_id = 2 AND book_id = 1 FOR UPDATE NOWAIT;

还有第三种态度，SKIP LOCKED：被别人锁住的行直接跳过。

SELECT store_id, qty FROM inventory
WHERE book_id = 1 AND store_id IN (1, 2, 3)
ORDER BY store_id
FOR UPDATE SKIP LOCKED;

三家店里只返回了 1 号和 3 号，被 C 锁着的 2 号店那一行被跳过了。这正是任务队列想要的：多个工作进程各自取“下一个没人在处理的任务”，谁也不等谁。除此之外的普通查询不要用它，它返回的是一份不完整的数据。

表级锁：平时感觉不到，直到有人要独占

每条语句还会在它碰到的表上拿一把表级锁（table-level lock），一直持有到事务结束。模式一共八种，日常遇到的是这四种：

名字里带 ROW 的也是表级锁，名字是历史遗留。读和写拿的锁互不冲突，所以平时感觉不到它们的存在。会话 C 只是读过 orders，这时如果有人想独占这张表：

BEGIN;
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE NOWAIT;
ROLLBACK;

拿不到。看看 C 手里握着什么：

SELECT relation::regclass AS relation, mode
FROM pg_locks
WHERE pid IS NULL AND locktype = 'relation' AND relation IN ('orders'::regclass, 'inventory'::regclass)
ORDER BY 1, 2;

orders 上那把 AccessShareLock，是 C 在事务里执行过一条 SELECT 留下的。查询早就跑完了，但事务没结束，锁就不放。一个只读不写、发着呆的会话，足以挡住所有要 ACCESS EXCLUSIVE 的语句，大多数 ALTER TABLE 都在其中。让 C 退场：

ROLLBACK PREPARED 'session_c';

锁队列：一条等锁的 DDL 会挡住所有人

被挡住的 ALTER TABLE 自己等一会儿倒也罢了，麻烦在于等锁是排队的。它排进队里之后，后来的普通查询要的锁和它冲突，只能排在它后面。于是一个发呆的会话加一条几毫秒的 DDL，就能让整张表停摆：

不锁表的迁移

先看一条 ALTER TABLE 到底做了多重的事。pg_relation_filenode 返回表在磁盘上的文件编号，编号变了就说明整张表被重写了一遍：

BEGIN;
SELECT pg_relation_filenode('orders') AS file_before;

ALTER TABLE orders ADD COLUMN source text NOT NULL DEFAULT 'web';
SELECT pg_relation_filenode('orders') AS file_after_constant_default;
SELECT mode FROM pg_locks WHERE relation = 'orders'::regclass AND pid = pg_backend_pid();

ALTER TABLE orders ADD COLUMN public_id uuid NOT NULL DEFAULT uuidv7();
SELECT pg_relation_filenode('orders') AS file_after_volatile_default;
ROLLBACK;

SELECT count(*) AS new_columns_left FROM information_schema.columns
WHERE table_name = 'orders' AND column_name IN ('source', 'public_id');

• 加一列并带常量默认值，文件编号没变（16442）：只改了表的定义，两万行一行都没动。很多旧资料说“加带默认值的列会重写整张表”，那是 PostgreSQL 11 之前的事。
• 默认值换成每行都不同的 uuidv7()，文件编号变成了 16571：整张表被重写。重写期间一直握着那把 AccessExclusiveLock，大表上就是一次事故。
• 最后的 ROLLBACK 把两列都撤掉了，查询结果是 0。PostgreSQL 的 DDL 是事务性的，建表、改表可以和数据修改一起回滚，迁移做到一半失败不需要手工收拾。代价是锁要到事务结束才放，所以迁移事务要短。

由此得到一套上线迁移的做法：

1. 每条 DDL 之前 SET lock_timeout = '3s'。拿不到锁就失败，退出队列，过一会儿重试。这一条防的就是上面的锁队列。
2. 建索引用 CREATE INDEX CONCURRENTLY。它不挡写入，但慢一些，并且不能放在事务里（见下）。中途失败会留下一个标着 INVALID 的索引，要删掉重建。
3. 加列时默认值用常量。需要每行不同的值，就先加一个可空的列，分批回填，最后再补约束。
4. 给已有的列补约束，分两步。先 ADD CONSTRAINT … NOT VALID，只约束此后写入的行，不扫描存量数据，所以很快；再 VALIDATE CONSTRAINT 检查存量数据，这一步不挡读写。外键和 CHECK 一直可以这样做，NOT NULL 是 18 才支持的 18 新增。

BEGIN;
CREATE INDEX CONCURRENTLY orders_created_idx ON orders (created_at);
ROLLBACK;

很多迁移工具默认把每个迁移文件包在一个事务里，遇到 CONCURRENTLY 就会得到这个 25001，需要为那一个文件关掉事务包装。

死锁

上一节面板的第四个场景是死锁（deadlock）：两个事务各握着对方想要的行。PostgreSQL 会在等待超过 deadlock_timeout（默认 1 秒）后检查有没有成环，有就中止其中一个，报 40P01，中止哪一个不能预期。预防的办法是让所有代码按同一个顺序加锁，比如一次要改多本书的库存时，先按 book_id 排序。即便如此，仍然把 40P01 当成可以重试的错误来处理，上一节的 inTransaction 已经这样做了。

习惯迁移脚本里先设 lock_timeout；建索引用 CONCURRENTLY；补约束走 NOT VALID 再 VALIDATE；多行更新按固定顺序。

10　守：COMMIT 到底保证了什么

先写日志，后写数据页。提交的承诺兑现在日志上。

前面改的那些页，改的都是内存里的副本。把每次修改立刻写回表文件太慢，而且写到一半断电会留下半张坏页。PostgreSQL 的办法是先把“改了什么”追加到一份日志里，叫预写式日志（WAL，write-ahead log）：

1. 改内存里的页同时生成一条描述这次修改的 WAL 记录。
2. COMMIT把 WAL 记录连同一条提交记录刷到磁盘，然后才向客户端返回成功。
3. 稍后写数据页被改过的页留在内存里，由后台进程和检查点陆续写回表文件。
4. 断电后重放重启时从最近一次检查点开始，把 WAL 里的修改重做一遍。

所以 COMMIT 返回成功，保证的是“这个事务的日志已经在磁盘上”，不是“表文件已经更新”。日志是顺序追加的，比到处改数据页快得多，提交因此可以很快。

WAL 里实际写了什么可以直接看。先做一次检查点（checkpoint），也就是把内存里的脏页全部写回磁盘、给日志划一个新的起点，然后在同一页上先后做两次小修改：

-- demo only: a checkpoint here keeps the list of WAL records short
CHECKPOINT;

UPDATE orders SET total_cents = total_cents + 1 WHERE id = 47;
UPDATE orders SET total_cents = total_cents + 1 WHERE id = 48;

SELECT resource_manager, record_type, record_length, fpi_length
FROM pg_get_wal_records_info((pg_control_checkpoint()).redo_lsn, pg_current_wal_lsn())
WHERE resource_manager IN ('Heap', 'Heap2', 'Btree', 'Transaction')
ORDER BY start_lsn;

两次修改几乎一样，记录长度却差了一百倍：第一条 7529 字节，第二条 71 字节。差别在 fpi_length 那一列：检查点之后第一次修改某一页，PostgreSQL 会把整页的内容（7464 字节，页中间那段空闲区不用记）一起记进日志，叫整页镜像（full-page image）。万一这一页写回磁盘时只写了一半，恢复时可以拿这份完整的镜像做底。同一页之后的修改只记变化本身。两条 COMMIT 记录各 34 字节。中间那条 PRUNE_ON_ACCESS 是第 6 节的页内剪枝，它也要记日志。

崩溃恢复（crash recovery）不需要“撤销”这一步。重放日志会把没提交的事务写下的版本也恢复到页上，但那些事务没有提交记录，它们的版本按第 6 节的规则对谁都不可见，和一次普通的回滚没有区别。

三条边界：

• synchronous_commit = off 让 COMMIT 不等日志刷盘就返回。数据库不会因此损坏，但数据库崩溃或者断电时，可能丢掉最后几百毫秒里已经回答“成功”的事务。只适合丢了也无妨的写入。
• fsync = off 是另一回事：断电后数据库可能无法恢复。生产环境不能关。（本页的引擎跑在浏览器和构建脚本里，它是关着的，这也是它不能当真数据库用的原因之一。）
• 复制不是备份。备库靠重放主库的 WAL 来同步，一条误删的 DELETE 会原样在备库上再执行一遍。备份要么是基础备份加上持续归档的 WAL（可以恢复到任意时间点），要么是定期的 pg_dump，并且要真的演练过恢复。

11　复盘：一个发呆的会话拖垮网店

把四层连起来用一次。下面这次事故里的每一环，前面都单独见过。

1. 19:40数据分析的同事在客户端里开了一个事务，对 orders 跑了一条查询，然后去吃饭了。连接的状态是 idle in transaction。第 9 节
   查询早跑完了，但事务没结束，它在 orders 上的那把 ACCESS SHARE 锁一直握着。
2. 20:00发布脚本执行 ALTER TABLE orders ADD COLUMN …。它要 ACCESS EXCLUSIVE，和 19:40 那把锁冲突，于是排队等。第 9 节
3. 20:00:01此后每一个读写 orders 的请求，要的锁都和排队中的 ALTER 冲突，全部排在它后面。“我的订单”、下单、支付回调，统统不返回。
4. 20:00:30每个卡住的请求都占着一个连接不放。连接池占满，新请求在池里排队直到超时；各个实例的池加起来超过数据库的连接上限时，数据库还会报 53300。第 1 节
   监控上看到的是“连接数打满、接口超时”，数据库的 CPU 却很闲。
5. 同时如果那个事务是 REPEATABLE READ 的，或者里面写过数据，那么从 19:40 起全库的旧版本都收不走，表在悄悄变大。第 7 节
6. 20:01:10值班同事按事务开始时间给会话排序，排在最前面的是一个 21 分钟前开的事务，状态是 idle in transaction。
7. 20:01:20先取消排队中的 ALTER，读写立刻恢复；再断开那个空闲的会话；迁移改天带着 lock_timeout 重做。

排查用的三条查询

-- 1. who has had a transaction open the longest?
SELECT pid, state, xact_start, wait_event_type, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND xact_start IS NOT NULL
ORDER BY xact_start;

-- 2. who is waiting, and which sessions are in the way?
SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- 3. any forgotten prepared transactions?
SELECT gid, prepared FROM pg_prepared_xacts ORDER BY prepared;

第一条的 state 列里，idle in transaction 就是“开着事务在发呆”。第二条的 pg_blocking_pids 既包括握着冲突锁的会话，也包括排在前面等锁的会话，所以顺着它能从卡住的请求找到 ALTER，再从 ALTER 找到那个发呆的会话。

止血有两个函数：pg_cancel_backend(pid) 只取消那个会话正在执行的语句，pg_terminate_backend(pid) 直接断开连接，它的事务随之回滚。先取消排队的 DDL，因为那是见效最快的一步。

事先设好的四个超时

四个默认都是 0，也就是不限时。给应用用的数据库角色设上，用 ALTER ROLE app SET statement_timeout = '5s' 这种写法；人手工连库用的角色至少设上 idle_in_transaction_session_timeout，19:40 那个会话就是这样连进来的。不要写进全局配置：全局的值对所有会话生效，管理员手动建索引、跑 VACUUM、修数据时也会被掐掉。具体取值看业务，这里的 5 秒只是示例。

上线前清单

每一条后面是它出自哪一节。

1. 连接池有上限；事务从头到尾用同一个连接。（1、8）补充一条正文没讲的：应用和数据库之间还隔着 PgBouncer 这类事务级的代理时，会话级的 SET、LISTEN 和会话级的咨询锁（advisory lock）都不可靠。
2. 应用用普通角色连接，不用超级用户；这个角色上设好四个超时。（1、11）
3. 金额存整数分，时间用 timestamptz；按天查询写带偏移的半开区间；“不在集合里”用 NOT EXISTS。（2）
4. 规则能写成约束的就写成约束：NOT NULL、CHECK、唯一、外键。（2、8）
5. 外键引用列建索引；复合索引等值列在前；新查询上线前看一眼 EXPLAIN (ANALYZE)，改数据的语句包在 BEGIN … ROLLBACK 里看。（3、5）
6. 批量导入之后手动 ANALYZE；不关 autovacuum；监控开得最久的事务。（4、7）
7. 修改语句把条件写进 WHERE，并检查影响行数；值用 $1 参数传。（8）
8. 40001 和 40P01 重试整个事务；事务里不调外部接口、不等人。（7、8）
9. 迁移：先 SET lock_timeout，失败重试；CREATE INDEX CONCURRENTLY；约束先 NOT VALID 再 VALIDATE；加列的默认值用常量。（9）
10. 有备份，并且演练过恢复；复制不算备份。（10）补充一条正文没讲的：找慢查询靠 pg_stat_statements 扩展和 log_min_duration_statement 参数，拿到 SQL 再去 EXPLAIN（第 3 节）。

和旧教程的出入

本页以 PostgreSQL 18 为准。下面这些地方，照着旧资料会看到不一样的结果。

另外三项和日常写 SQL 关系不大，知道名字即可：18 的新集群默认开启数据校验和；18 新增了异步 I/O；18 给 autovacuum 的触发阈值加了上限 autovacuum_vacuum_max_threshold。

本页的引擎和真实服务器的差别

• 只有一个连接，没有 autovacuum、检查点等后台进程，各种超时的计时器不触发。
• 为了演出“会话 B”，把 max_prepared_transactions 设成了 4。它的默认值是 0，也就是不允许预备事务。
• 数据库的排序规则是 C，按字符编码排序：ORDER BY 中文不是拼音顺序，LIKE 'abc%' 可以直接用普通索引。生产库常用的语言排序规则下，前缀匹配要给索引加 text_pattern_ops。
• fsync 是关着的，数据在内存里，刷新页面就没了。

术语表

中文叫法不统一的词，查英文更可靠。

堆heap

表的物理形态：一个不按任何顺序存放行的文件。

页page / block

表和索引文件的基本单位，固定 8 kB。

元组tuple

页里的一行。带一个记着 xmin、xmax 的头。

行指针line pointer

页头后面的槽位数组，每个指向页内的一个元组。

ctid

一个行版本的物理地址：（页号, 槽位号）。会变，不能当主键。

事务号transaction ID, xid

每个改数据的事务领到的递增编号。

行版本row version

同一行在不同时刻的副本。UPDATE 写新版本，不改旧版本。

多版本并发控制MVCC

靠保留多个行版本，让读和写互不阻塞的机制。

快照snapshot

“此刻哪些事务已经结束”的记录，决定一条查询看得见哪些版本。

隔离级别isolation level

规定快照何时拍、写冲突如何处理。默认 READ COMMITTED。

HOT 更新heap-only tuple

新版本留在同一页、不新增索引条目的更新。

页内剪枝pruning

普通语句访问一页时，顺手清掉页内无人可见的旧版本。

死元组dead tuple

对任何快照都不再可见、等待回收的行版本。

VACUUM

回收死元组及其索引条目，把空间标为可复用。

autovacuum

自动执行 VACUUM 和 ANALYZE 的后台进程。

膨胀bloat

表或索引文件失控地变大：旧版本收不走，或者腾出的空间一直用不上。

可见性映射visibility map

每个表页两位的标记，记录该页是否对所有人全部可见。

B-tree

默认的索引类型：按键排序的平衡树，叶子里是“键 → ctid”。

顺序扫描sequential scan

从头到尾读完整张表，逐行判断条件。

位图扫描bitmap scan

先从索引收齐行的位置，再按页号顺序回表。

仅索引扫描index-only scan

所需的列都在索引里时不回表，前提是可见性映射允许。

跳跃扫描skip scan

复合索引第一列没有条件时，对它的每个取值分别查找。18 新增。

规划器planner

为一条 SQL 比较多种执行方式的代价并选出一种。

统计信息statistics

ANALYZE 采样得到的各列取值分布，规划器靠它估行数。

选择性selectivity

一个条件预计留下的行占全表的比例。

键集分页keyset pagination

用上一页最后一行的排序键作为下一页的起点。

行锁row-level lock

记在行的 xmax 上的锁，只阻塞对同一行的修改和加锁。

表级锁table-level lock

每条语句在表上拿的锁，共八种模式，持有到事务结束。

死锁deadlock

两个事务互相等待对方的锁。PostgreSQL 会中止其中一个。

序列化失败serialization failure

SQLSTATE 40001。事务没有错，重做一遍通常就能成功。

写偏斜write skew

两个事务各读旧数据、各写不同的行，合起来破坏了规则。

保存点savepoint

事务内的回退点，可以只撤销它之后的修改。

预写式日志WAL

所有修改先追加到这份日志，再写数据页。

检查点checkpoint

把内存里的脏页全部写回磁盘，并给崩溃恢复定一个起点。

小测验

十道题，答完每题都有解释。

重做

在线运行

把同一个引擎启动在你的浏览器里。

这一步需要联网：页面会从 jsDelivr 下载固定版本的 PGlite 0.5.16，大约 15 MB。下载完成后的一切都在浏览器里执行，数据不离开这台机器。不启动它，页面上的内容也是完整的。

SET TIME ZONE 'Asia/Shanghai';

CREATE TABLE stores (
  id    integer PRIMARY KEY,
  code  text NOT NULL UNIQUE,
  city  text NOT NULL
);

CREATE TABLE members (
  id         integer PRIMARY KEY,
  name       text NOT NULL,
  tier       text NOT NULL CHECK (tier IN ('basic', 'gold')),
  joined_on  date NOT NULL
);

CREATE TABLE books (
  id          integer PRIMARY KEY,
  isbn        text NOT NULL UNIQUE,
  title       text NOT NULL,
  price_cents integer NOT NULL CHECK (price_cents > 0)
);

CREATE TABLE inventory (
  store_id integer NOT NULL REFERENCES stores,
  book_id  integer NOT NULL REFERENCES books,
  qty      integer NOT NULL CHECK (qty >= 0),
  PRIMARY KEY (store_id, book_id)
);

-- fillfactor 90 keeps a tenth of every page free for later row versions
CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  member_id   integer REFERENCES members,   -- NULL: guest checkout
  store_id    integer REFERENCES stores,    -- NULL: online shop
  status      text NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'completed', 'cancelled',
                                              'refunding', 'refunded')),
  total_cents integer NOT NULL CHECK (total_cents >= 0),
  created_at  timestamptz NOT NULL,
  version     integer NOT NULL DEFAULT 1
) WITH (fillfactor = 90);

CREATE TABLE payment_events (
  provider_event_id text PRIMARY KEY,       -- idempotency key chosen by the payment provider
  order_id          bigint NOT NULL REFERENCES orders,
  amount_cents      integer NOT NULL
);

INSERT INTO stores (id, code, city)
SELECT i, 'S' || lpad(i::text, 2, '0'), (ARRAY['杭州', '上海', '苏州', '南京'])[1 + (i - 1) % 4]
FROM generate_series(1, 12) AS i;

INSERT INTO books (id, isbn, title, price_cents)
SELECT i, '9787' || lpad(i::text, 9, '0'),
       CASE i WHEN 1 THEN '三体' WHEN 2 THEN '活着' WHEN 3 THEN '额尔古纳河右岸' ELSE '书目 ' || i END,
       2900 + (i * 37) % 9000
FROM generate_series(1, 500) AS i;

INSERT INTO members (id, name, tier, joined_on)
SELECT i, '书友 ' || i, CASE WHEN i % 10 = 0 THEN 'gold' ELSE 'basic' END, date '2024-01-01' + (i * 7) % 600
FROM generate_series(1, 2000) AS i;

-- one account that orders far more than anyone else
INSERT INTO members (id, name, tier, joined_on) VALUES (2001, '市图书馆采购', 'gold', date '2024-03-01');

-- store S01 holds the last copy of book 1
INSERT INTO inventory (store_id, book_id, qty)
SELECT s, b, CASE WHEN s = 1 AND b = 1 THEN 1 ELSE (s * 13 + b * 7) % 20 END
FROM generate_series(1, 12) AS s, generate_series(1, 500) AS b;

INSERT INTO orders (member_id, store_id, status, total_cents, created_at)
SELECT CASE WHEN i % 29 = 0 THEN 2001 WHEN i % 4 = 0 THEN NULL ELSE 1 + (i * 7) % 2000 END,
       CASE WHEN i % 3 = 0 THEN NULL ELSE 1 + (i / 3) % 12 END,
       CASE WHEN i % 200 = 0 THEN 'cancelled'
            WHEN i % 200 BETWEEN 1 AND 3 THEN 'pending'
            WHEN i % 200 BETWEEN 40 AND 45 THEN 'paid'
            WHEN i % 200 BETWEEN 100 AND 109 THEN 'shipped'
            ELSE 'completed' END,
       1500 + (i * 7919) % 30000,
       timestamptz '2025-01-01 09:00+08' + i * interval '37 minutes'
FROM generate_series(1, 20000) AS i;

CREATE EXTENSION pageinspect;
CREATE EXTENSION pg_walinspect;

-- a readable window onto page 0 of the orders table
CREATE VIEW orders_page0 AS
SELECT lp,
       (ARRAY['unused', 'normal', 'redirect', 'dead'])[lp_flags + 1] AS slot,
       CASE WHEN lp_flags = 2 THEN lp_off END AS redirect_to,
       t_xmin AS xmin, t_xmax AS xmax, t_ctid AS next_version,
       ARRAY(SELECT replace(flag, 'HEAP_', '')
             FROM unnest((heap_tuple_infomask_flags(t_infomask, t_infomask2)).raw_flags) AS flag
             WHERE flag IN ('HEAP_HOT_UPDATED', 'HEAP_ONLY_TUPLE', 'HEAP_XMAX_LOCK_ONLY',
                            'HEAP_XMIN_COMMITTED', 'HEAP_XMIN_INVALID', 'HEAP_XMAX_COMMITTED', 'HEAP_XMAX_INVALID')) AS flags
FROM heap_page_items(get_raw_page('orders', 0));

-- VACUUM cannot run inside a transaction block, so it is sent on its own
VACUUM ANALYZE;