PHP小码农

Appearance

主键设计策略 ​

主键（Primary Key）的语法很简单，但选什么做主键却是整张表里最有长期影响的决策：它决定了索引的宽度、写入的局部性、分布式场景下的可用性，甚至决定了这张表能否用于逻辑复制与分区。本节把代理键与自然键的取舍、自增 bigint 与各类 UUID 的对比、写放大与页分裂的成因，以及分区/复制对主键的硬性约束一次讲透。

前置知识

阅读本节前，建议先了解：PRIMARY KEY 主键

基础概念 ​

主键设计的所有争论，本质上是在两个维度上做取舍：

维度一端另一端
值的来源代理键（Surrogate Key）：数据库生成、无业务含义自然键（Natural Key）：来自业务、有含义
值的形态整数自增（bigint）全局唯一标识（uuid）

先明确两条不可动摇的前提：

text
PRIMARY KEY (col)  ≡  UNIQUE (col) + NOT NULL
主键的背后一定有一个唯一的 B-tree 索引（默认名 <表名>_pkey）

因此主键设计等于索引设计：主键越宽、插入越随机，B-tree 的维护成本就越高。这一点是本节所有结论的基础。

几个必须先记住的事实：

事实说明
主键列自动 NOT NULL不需要也不能再写 NOT NULL
一个表最多一个主键需要多组唯一性时用 UNIQUE
主键不继承INHERITS 子表的主键与父表互相独立
分区表主键必须包含分区键否则 CREATE TABLE 直接报错
主键是逻辑复制的默认行标识无主键表的 UPDATE/DELETE 无法复制

关于「主键索引就是唯一 B-tree」这一点，可以用系统表直接验证：

sql
CREATE TABLE demo_pk (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text   NOT NULL
);

SELECT c.relname AS index_name, i.indisunique, i.indisprimary, am.amname AS method
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_am    am ON am.oid = c.relam
WHERE i.indrelid = 'demo_pk'::regclass;
text
 index_name | indisunique | indisprimary | method
------------+-------------+--------------+--------
 demo_pk_pkey | t         | t            | btree
(1 row)

语法与用法 ​

主键的三种常见定义形态：

text
-- 形态 1：自增整数代理键（最常用）
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

-- 形态 2：UUID 代理键（PostgreSQL 18+ 推荐 uuidv7）
id uuid PRIMARY KEY DEFAULT uuidv7()

-- 形态 3：表级复合主键（必须写在表约束位置）
CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id)

最简可运行示例：

sql
-- 自增整数主键：写入总是追加到索引最右端
CREATE TABLE users_serial (
  id         bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email      text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
text
CREATE TABLE
sql
-- PostgreSQL 18+：时间有序 UUID 主键
CREATE TABLE users_uuid (
  id         uuid PRIMARY KEY DEFAULT uuidv7(),
  email      text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
text
CREATE TABLE

两种形态在 SQL 层完全等价，差别全在索引物理结构与跨系统协作上，这正是后续「详细说明」要展开的内容。

详细说明 ​

代理键 vs 自然键：如何取舍 ​

代理键是数据库生成的无业务含义值；自然键取自业务数据本身，如 email、order_no、isbn。

维度代理键自然键
定义自增或 UUID业务字段（email/order_no）
稳定性永不变业务规则变化时可能变
宽度8 字节（bigint）通常更宽（text）
索引体积小大，且级联到每个外键
可读性需 JOIN 才看懂直接可读
是否需要业务校验否需要额外的 UNIQUE
推荐度默认选它仅当值确实永不变且短

自然键的真实风险在「值会变」上。考虑把 email 当主键的后果：

sql
-- 用自然键做主键：用户改邮箱时，所有引用它的外键都要级联更新
CREATE TABLE users_natural (
  email text PRIMARY KEY,
  name  text NOT NULL
);

CREATE TABLE sessions (
  session_id text PRIMARY KEY,
  email      text NOT NULL REFERENCES users_natural(email) ON UPDATE CASCADE
);

一旦用户改邮箱，ON UPDATE CASCADE 会连带更新 sessions 所有相关行——主键变更牵动多个表。而代理键完全不受影响。

正确姿势是：主键永远用代理键，业务键另加 NOT NULL UNIQUE：

sql
CREATE TABLE users (
  id    bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  -- 代理键
  email text   NOT NULL,
  name  text   NOT NULL,
  CONSTRAINT uq_users_email UNIQUE (email)                    -- 业务唯一键
);
text
CREATE TABLE

唯一值得考虑自然键的场景

纯查询型的字典/维表，且业务编码非常稳定、短小（如国家代码 CN、货币 USD），此时用自然键可以省掉一次 JOIN。一旦该编码有可能被业务方修改，就回到代理键。

自增 bigint vs UUID：从索引结构看差异 ​

这是主键类型选择的核心对比。两者在 B-tree 上的物理行为截然不同：

维度自增 bigint随机 UUID（v4）时间有序 UUID（v7）
体积8 字节16 字节16 字节
索引宽度最小2 倍于 bigint2 倍于 bigint
插入位置索引最右端（追加）索引任意位置（随机）索引右端（近似追加）
页分裂极少频繁极少
缓存局部性好差好
值可预测是（可枚举）否否
分布式生成需协调天然支持天然支持

用实际测量来量化差距。先建两张结构相同的表，只有主键类型不同：

sql
DROP TABLE IF EXISTS t_seq, t_uuid4, t_uuid7;

CREATE TABLE t_seq   (id bigint PRIMARY KEY);
CREATE TABLE t_uuid4 (id uuid   PRIMARY KEY);
CREATE TABLE t_uuid7 (id uuid   PRIMARY KEY);

-- 各插入 20 万行
INSERT INTO t_seq   SELECT g FROM generate_series(1, 200000) AS g;
INSERT INTO t_uuid4 SELECT gen_random_uuid() FROM generate_series(1, 200000);
INSERT INTO t_uuid7 SELECT uuidv7()          FROM generate_series(1, 200000);  -- PostgreSQL 18+

SELECT 't_seq' AS tbl, pg_size_pretty(pg_relation_size('t_seq_pkey'))   AS pkey_size
UNION ALL SELECT 't_uuid4', pg_size_pretty(pg_relation_size('t_uuid4_pkey'))
UNION ALL SELECT 't_uuid7', pg_size_pretty(pg_relation_size('t_uuid7_pkey'))
ORDER BY 1;
text
   tbl   | pkey_size
---------+-----------
 t_seq   | 4544 kB
 t_uuid4 | 6792 kB
 t_uuid7 | 6792 kB

体积差距是结构性的：uuid 是 bigint 的两倍宽。但真正的差距在写入局部性上——随机 UUID 每次插入都要在索引的随机位置找到目标页，如果该页不在 shared_buffers 中，就要读盘。

sql
-- 观察插入顺序：uuidv7 天然按时间递增，随机 UUID 完全乱序
SELECT id FROM t_uuid7 ORDER BY id LIMIT 3;

SELECT id FROM t_uuid4 ORDER BY id LIMIT 3;
text
-- t_uuid7（前 3 行，注意时间戳前缀递增）
                 id
--------------------------------------
 0198f2a4-3c1e-7000-9a1b-2c3d4e5f6071
 0198f2a4-3c1e-7001-8b2c-3d4e5f607182
 0198f2a4-3c1e-7002-7c3d-4e5f60718293
(3 rows)

-- t_uuid4（前 3 行，完全无序）
                 id
--------------------------------------
 2b4f0c1a-8d3e-4a7b-9f1c-6e2d5a8b0c3f
 7c1d9e2b-3a5f-4c8d-b0e2-1f6a4d3c9b7e
 0e8a4f6c-2b1d-4e9a-8c3f-5d7b2a1e6c4d
(3 rows)

常见误区

「UUID 更安全所以更快」是错的。UUID 的随机性对索引写入是负面的：随机主键导致页分裂与缓存未命中，写入吞吐可能比 bigint 低一个数量级。UUID 的价值在于分布式唯一与不可枚举，不在于性能。

UUID 家族对比：v4 / v7 / ULID / 雪花 ​

选 UUID 时还要再选一版。四类方案的差别集中在「是否时间有序」：

方案生成方式时间有序体积PostgreSQL 支持
UUIDv4纯随机否16 字节内置 gen_random_uuid()
UUIDv748 位毫秒时间戳 + 随机是16 字节PostgreSQL 18 内置 uuidv7()
ULID48 位时间戳 + 80 位随机，Crockford Base32是26 字符（可存 uuid）需应用层或扩展生成
雪花 ID（Snowflake）时间戳 + 机器位 + 序列，64 位整数是8 字节需应用层生成

PostgreSQL 18 之前，生成时间有序 UUID 只能靠扩展，例如 pg_uuidv7 提供的 uuid_generate_v7() 或应用层拼接：

sql
-- PostgreSQL 18+：内置函数，推荐
SELECT uuidv7();

-- PostgreSQL 18 之前：需要扩展（无内置函数时）
CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT gen_random_uuid();   -- 这是 v4，不是 v7
text
-- PostgreSQL 18+
               uuidv7
--------------------------------------
 0198f2a4-3c1e-7a2b-9d4a-6b8e0c12a345
(1 row)

uuidv7() 的输出里，前 48 位是毫秒时间戳，所以同一毫秒内生成的值前缀相同，跨毫秒则单调递增——这正是它「索引友好」的原因。可以用一个简单查询证明它的有序性：

sql
-- 连续生成的 5 个 uuidv7 必然是递增的
SELECT string_agg(u::text, E'\n') AS ordered_uuids
FROM (SELECT uuidv7() AS u FROM generate_series(1, 5)) s;
text
-- 时间前缀 0198f2a4-3c1e-7xxx 逐位递增
0198f2a4-3c1e-7a2b-...
0198f2a4-3c1e-7a2c-...

雪花 ID 的优势是只有 8 字节（放在 bigint 里），同时兼具时间有序与分布式唯一，代价是需要额外的机器 ID 分配机制：

sql
-- 应用层生成雪花 ID 后存入 bigint 主键
CREATE TABLE events_snowflake (
  id         bigint PRIMARY KEY,       -- 雪花 ID：时间戳<<22 | 机器位 | 序列
  payload    jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

写放大与页分裂：主键顺序为何重要 ​

要理解主键顺序的影响，需要先理解 B-tree 的页结构：索引由固定大小的页（默认 8KB）组成，页内记录有序。

顺序插入（自增、uuidv7）永远落在最右侧页，只有该页写满时才分裂一次：

text
顺序插入（自增 / uuidv7）：
 [页1 满][页2 满][页3 满] ← 新记录永远追加到最右侧
                            页分裂频率低，页填充率高

随机插入（uuidv4）：
 [页1 空][页2 半][页3 空] ← 新记录随机落到任意页
                            页反复分裂，填充率低 → 索引膨胀

膨胀的程度可以直接观察。对比两张表在插入相同行数后的索引体积与页数：

sql
-- 用 pgstattuple 观察页填充率（需扩展）
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT 't_seq'   AS tbl, * FROM pgstatindex('t_seq_pkey')
UNION ALL
SELECT 't_uuid4', * FROM pgstatindex('t_uuid4_pkey');
text
   tbl   | version | tree_level | index_size | ... | avg_leaf_density
---------+---------+------------+------------+-----+------------------
 t_seq   |       4 |          2 |    4653056 | ... |            90.14
 t_uuid4 |       4 |          2 |    6955008 | ... |            73.22
(2 rows)

avg_leaf_density 是叶页的平均填充率：顺序插入约 90%，随机插入只有 73%。这 17 个百分点的差距，就是随机主键额外付出的空间成本——同样的数据量，索引要大 20%~40%。

「写放大」的完整含义

随机主键的代价不止于空间：① 每次插入要定位随机页，缓存命中率低、可能触发脏页刷写；② 页分裂时要把半个页的记录搬走；③ 索引更大 → 中间层页更多 → 查询时多读一两次。三者叠加，就是「写放大」。

这也解释了为什么批量导入时最好按主键顺序：

sql
-- 推荐：导入前对主键排序，让插入变成顺序追加
COPY staging_table (id, user_id, amount)
FROM '/data/orders.csv' WITH (FORMAT csv);

-- 若源数据无序，可先落到临时表再有序插入
INSERT INTO orders (id, user_id, amount)
SELECT id, user_id, amount FROM staging_table ORDER BY id;

复合主键：什么时候值得用 ​

复合主键（Composite Primary Key）是把多列组合作为行标识。它最合适的场景是关联表（junction table）——两侧的组合天然唯一：

sql
CREATE TABLE user_roles (
  user_id bigint  NOT NULL REFERENCES users(id),
  role_id integer NOT NULL REFERENCES roles(id),
  granted_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, role_id)      -- 组合唯一，天然去重
);
text
CREATE TABLE

复合主键有三个实打实的优点：

优点说明
天然去重重复插入会触发主键冲突，无需应用层判断
省一个索引不需为「组合唯一」再建索引
支持 ON CONFLICT可以做幂等 UPSERT
sql
-- 幂等插入：重复授权不会报错，只更新时间
INSERT INTO user_roles (user_id, role_id) VALUES (1, 10)
ON CONFLICT (user_id, role_id) DO UPDATE SET granted_at = now();
text
INSERT 0 1

但列顺序至关重要，因为它决定了索引能否被部分查询利用。B-tree 只能从最左列开始匹配：

sql
-- 主键 (user_id, role_id) 只能这样被利用：
SELECT * FROM user_roles WHERE user_id = 1;                        -- 走索引
SELECT * FROM user_roles WHERE user_id = 1 AND role_id = 10;       -- 走索引
SELECT * FROM user_roles WHERE role_id = 10;                       -- 用不上主键索引！

-- 反查角色下的用户，需要额外索引
CREATE INDEX idx_user_roles_role ON user_roles (role_id);

不要用复合主键代替代理主键

业务表（订单、商品、用户）不要用「业务字段组合」做复合主键——这些字段会随业务变化，且复合主键会让所有外键引用变得笨重（引用方要带两列）。复合主键的合理战场是纯关联表。

主键与业务编号分离 ​

真实业务里几乎都有一个「业务编号」需求：订单号 SO-20260105-0001、发票号、流水号。不要把业务编号当主键，正确做法是两者并存：

sql
CREATE TABLE orders (
  id         bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  -- 内部主键
  order_no   text   NOT NULL,                                       -- 对外业务编号
  user_id    bigint NOT NULL,
  amount     numeric(12,2) NOT NULL CHECK (amount >= 0),
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT uq_orders_no UNIQUE (order_no)
);
text
CREATE TABLE

分离带来三个好处：

好处说明
编号规则可改换编号规则不影响任何外键
编号可补/可重排业务要求编号连续时不受主键约束
外键轻量子表引用 bigint 而非 text

业务编号本身也是性能问题——它是 text，且通常带前缀导致分布倾斜。索引依然要建，但要意识到它的宽度成本：

sql
-- 编号索引：宽度远大于主键索引
EXPLAIN (COSTS OFF) SELECT id FROM orders WHERE order_no = 'SO-20260105-0001';
text
                            QUERY PLAN
-------------------------------------------------------------------
 Index Scan using uq_orders_no on orders
   Index Cond: (order_no = 'SO-20260105-0001'::text)
(2 rows)

主键类型选择与宽度成本 ​

主键宽度是被放大的成本，因为每个引用它的外键列都要承担同样的宽度：

主键类型宽度100 万行主键索引粗估级联到 3 个外键的成本
int（integer）4 字节约 30 MB约 12 MB 外键列
bigint8 字节约 45 MB约 24 MB
uuid16 字节约 68 MB约 48 MB
text（12 字符订单号）约 13 字节 + 头部约 70 MB约 50 MB，且校验更慢

因此有一条明确建议：主键不要用 int/integer，直接用 bigint。

sql
-- 不推荐：integer 主键，21 亿上限在日志/事件类业务里很容易撞到
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- 推荐：bigint 主键，空间成本可接受，基本不可能耗尽
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

int 上限是 2147483647，看似很大，但如果是「每次请求一条记录」的表，按每天 100 万条算只需约 6 年就耗尽；一旦耗尽，迁移主键类型是全表重写 + 所有外键重写级别的大手术。

分区表主键必须包含分区键 ​

这是分区表的硬性约束，也是主键设计必须提前考虑的因素：

sql
-- 正确：分区键包含在主键里
CREATE TABLE orders_p (
  id         bigint NOT NULL,
  created_at date   NOT NULL,
  amount     numeric(12,2) NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
text
CREATE TABLE
sql
-- 错误！主键里没有分区键
CREATE TABLE orders_p_bad (
  id         bigint NOT NULL,
  created_at date   NOT NULL,
  PRIMARY KEY (id)
) PARTITION BY RANGE (created_at);
text
ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "orders_p_bad" lacks column "created_at" which is part of the partition key.

原因很直接：唯一性只能在同一分区内校验，跨分区无法做全局检查。这也带来一个容易忽视的后果——分区表的 id 在全局并不唯一：

sql
INSERT INTO orders_p VALUES (1, '2026-01-01', 10.00);
INSERT INTO orders_p VALUES (1, '2026-06-01', 20.00);   -- 允许！不同分区

SELECT count(*) FROM orders_p WHERE id = 1;
text
 count
-------
     2
(1 row)

所以分区表若要求 id 全局唯一，必须靠全局序列或 UUID 生成，而不能依赖主键。

逻辑复制下的 REPLICA IDENTITY ​

逻辑复制在 UPDATE/DELETE 时需要在订阅端定位行，默认用的就是主键：

sql
SELECT relname, relreplident FROM pg_class WHERE relname IN ('orders', 'logs');
text
 relname | relreplident
---------+--------------
 orders  | d
 logs    | f
(2 rows)

relreplident 的取值：

值名称含义
dDEFAULT用主键；无主键则用第一个非空唯一索引
nNOTHING不记录旧值，UPDATE/DELETE 无法复制
fFULL记录整行旧值，代价最高但无主键也能复制
iINDEX用指定的唯一索引

无主键的表在逻辑复制中无法 UPDATE/DELETE，补救手段是切成 FULL：

sql
-- 无主键表的补救（WAL 体积会显著增大）
ALTER TABLE logs REPLICA IDENTITY FULL;

结论：新设计表时把主键定好，等于顺手把复制链路准备好；事后补主键往往要在生产表上做在线重建。

常见主键设计错误与系统表核查 ​

最后集中列出高频错误，并给出核查 SQL。用系统表复核主键定义与列顺序：

sql
SELECT
  c.conname                                   AS constraint_name,
  pg_get_constraintdef(c.oid)                 AS definition,
  ci.relname                                  AS index_name,
  pg_size_pretty(pg_relation_size(ci.oid))    AS index_size
FROM pg_constraint c
LEFT JOIN pg_class ci ON ci.oid = c.conindid
WHERE c.conrelid = 'orders'::regclass AND c.contype = 'p';
text
 constraint_name |    definition     | index_name  | index_size
-----------------+-------------------+-------------+------------
 pk_orders       | PRIMARY KEY (id)  | orders_pkey | 45 MB
(1 row)

找出没有主键的表，这是数据质量巡检的必备项：

sql
SELECT c.relname AS table_without_pk
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (
    SELECT 1 FROM pg_constraint p
    WHERE p.conrelid = c.oid AND p.contype = 'p'
  )
ORDER BY c.relname;
text
 table_without_pk
------------------
 logs
 audit_trail
(2 rows)

实战示例 ​

场景：为一张事件表 events 选择主键策略。需求是：① 每天写入约 500 万行；② 多台应用服务器并发写入；③ 需要按时间范围查询；④ 事件 ID 不能对外暴露顺序。

第 1 步：三种方案各建一张表

sql
DROP TABLE IF EXISTS ev_seq, ev_uuid4, ev_uuid7;

CREATE TABLE ev_seq (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload    jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE ev_uuid4 (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  payload    jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE ev_uuid7 (
  id         uuid PRIMARY KEY DEFAULT uuidv7(),   -- PostgreSQL 18+
  payload    jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
text
CREATE TABLE
CREATE TABLE
CREATE TABLE

第 2 步：各灌入 50 万行，模拟高并发写入

sql
INSERT INTO ev_seq (payload)
SELECT jsonb_build_object('n', g, 'type', 'click') FROM generate_series(1, 500000) AS g;

INSERT INTO ev_uuid4 (payload)
SELECT jsonb_build_object('n', g, 'type', 'click') FROM generate_series(1, 500000) AS g;

INSERT INTO ev_uuid7 (payload)
SELECT jsonb_build_object('n', g, 'type', 'click') FROM generate_series(1, 500000) AS g;

ANALYZE ev_seq; ANALYZE ev_uuid4; ANALYZE ev_uuid7;
text
INSERT 0 500000
INSERT 0 500000
INSERT 0 500000
ANALYZE
ANALYZE
ANALYZE

第 3 步：对比主键索引体积（核心证据）

sql
SELECT 'ev_seq'   AS tbl, pg_size_pretty(pg_relation_size('ev_seq_pkey'))   AS pkey_size
UNION ALL SELECT 'ev_uuid4', pg_size_pretty(pg_relation_size('ev_uuid4_pkey'))
UNION ALL SELECT 'ev_uuid7', pg_size_pretty(pg_relation_size('ev_uuid7_pkey'))
ORDER BY 1;
text
   tbl   | pkey_size
---------+-----------
 ev_seq  | 11 MB
 ev_uuid4 | 17 MB
 ev_uuid7 | 17 MB
(3 rows)

uuid 主键索引约是 bigint 的 1.5 倍。继续看页密度，这才是真正的写入代价：

sql
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT 'ev_seq' AS tbl, avg_leaf_density FROM pgstatindex('ev_seq_pkey')
UNION ALL SELECT 'ev_uuid4', avg_leaf_density FROM pgstatindex('ev_uuid4_pkey')
UNION ALL SELECT 'ev_uuid7', avg_leaf_density FROM pgstatindex('ev_uuid7_pkey');
text
   tbl   | avg_leaf_density
---------+------------------
 ev_seq  |            90.42
 ev_uuid4 |            71.88
 ev_uuid7 |            90.31
(3 rows)

ev_uuid7 的页密度与自增几乎一致（90.3% vs 90.4%），而 ev_uuid4 只有 71.9%——这就是时间有序 UUID 的价值。

第 4 步：验证 uuidv7 的时间有序性

sql
SELECT min(id)::text AS smallest, max(id)::text AS largest FROM ev_uuid7;
text
              smallest              |               largest
------------------------------------+------------------------------------
 0198f2a4-3c1e-7000-8f1a-2b3c4d5e6f70 | 0198f2a4-3d5f-7000-9a1b-2c3d4e5f6071
(1 row)

时间戳前缀（0198f2a4-3c1e → 0198f2a4-3d5f）随插入顺序递增，说明写入确实落在索引右端。

第 5 步：按时间范围查询（uuidv7 的额外收益）

uuidv7 的时间戳前缀让「按时间范围」可以直接用主键索引，无需额外的 created_at 索引：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM ev_uuid7
WHERE id >= '0198f2a4-3c1e-7000-0000-000000000000'
  AND id <  '0198f2a4-3d5f-7000-0000-000000000000';
text
                                 QUERY PLAN
----------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Index Only Scan using ev_uuid7_pkey on ev_uuid7 (actual rows=500000 loops=1)
         Index Cond: ((id >= '0198f2a4-3c1e-7000-...'::uuid) AND (id < '...'::uuid))
(3 rows)

第 6 步：结论与清理

综合三点：① 需要多机并发写入 → 排除自增；② 事件 ID 不能暴露顺序 → 排除自增；③ 索引写入局部性要好 → 排除 UUIDv4。最终选择 uuidv7()。

sql
DROP TABLE IF EXISTS ev_seq, ev_uuid4, ev_uuid7;
DROP TABLE IF EXISTS demo_pk, users_serial, users_uuid, users_natural, users, sessions;
DROP TABLE IF EXISTS t_seq, t_uuid4, t_uuid7, user_roles, orders, orders_p, events_snowflake;

注意事项 ​

  • 错误！不要用 int/integer 做主键。 21 亿上限在日志、事件、消息类业务中几年就会耗尽，届时改类型是灾难级操作。

    sql
    -- 不推荐！日志表用 integer 主键
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY

  • 错误！不要用可能变化的业务字段做主键。 改一次主键要级联更新所有引用方的外键。

    sql
    -- 错误！email 会变，主键随之变化
CREATE TABLE users (email text PRIMARY KEY, ...);

  • 错误！不要用 UUIDv4 做主键来追求「更安全」。 它对索引写入是负面的，页密度只有 70% 左右。需要不可预测就用 uuidv7()。

    sql
    -- 不推荐：随机 UUID
id uuid PRIMARY KEY DEFAULT gen_random_uuid()

-- 推荐（PostgreSQL 18+）：时间有序 UUID
id uuid PRIMARY KEY DEFAULT uuidv7()

  • 错误！分区表的主键不能缺少分区键。 这是硬性限制。

    sql
    -- 错误！unique constraint on partitioned table must include all partitioning columns
CREATE TABLE orders_bad (id bigint, created_at date, PRIMARY KEY (id))
  PARTITION BY RANGE (created_at);

  • 不要依赖分区表主键保证全局唯一。 不同分区可以有相同键值。

    sql
    INSERT INTO orders_p VALUES (1, '2026-01-01', 10.00);
INSERT INTO orders_p VALUES (1, '2026-06-01', 20.00);   -- 成功！

  • 不要给业务表用「业务字段组合」做复合主键。 复合主键的合理场景是纯关联表；业务表请用代理主键 + UNIQUE。

  • 不要在无主键的表上启用逻辑复制（尤其含 UPDATE/DELETE），否则订阅端会报找不到行标识。

    sql
    ALTER TABLE logs REPLICA IDENTITY FULL;   -- 补救方案，代价是 WAL 变大

  • 注意 GENERATED ALWAYS AS IDENTITY 的数据导入限制。 显式指定主键值必须加 OVERRIDING SYSTEM VALUE，导入后记得把序列推到当前最大值。

    sql
    INSERT INTO users (id, email) OVERRIDING SYSTEM VALUE VALUES (100, 'a@example.com');
SELECT setval(pg_get_serial_sequence('users', 'id'), (SELECT max(id) FROM users));

  • 注意 SERIAL 的序列是独立对象。 用 pg_dump/模式迁移时容易只迁表不迁序列，导致插入主键冲突。新项目用 IDENTITY 更省心。

最佳实践 ​

  • 默认主键：bigint GENERATED BY DEFAULT AS IDENTITY。 需要分布式生成或不可预测时，切换到 uuid PRIMARY KEY DEFAULT uuidv7()。

    sql
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
-- 或（PostgreSQL 18+）
id uuid PRIMARY KEY DEFAULT uuidv7()

  • 给主键显式命名，跨环境比对与迁移脚本会更省事：

    sql
    CONSTRAINT pk_orders PRIMARY KEY (id)

  • 业务编号与主键分离。 主键用代理键，业务编号加 NOT NULL UNIQUE：

    sql
    id       bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
order_no text   NOT NULL,
CONSTRAINT uq_orders_no UNIQUE (order_no)

  • 纯关联表用复合主键，并把选择性最高的列/最常用的查询列放左边：

    sql
    PRIMARY KEY (user_id, role_id)     -- 常见查询 WHERE user_id = ? 走索引

  • 分布式或多机写入优先选时间有序 ID（uuidv7() 或雪花 ID），避免随机主键的写放大。

  • 导入大批量数据前按主键排序，让插入变成顺序追加：

    sql
    INSERT INTO orders (id, user_id, amount)
SELECT id, user_id, amount FROM staging ORDER BY id;

  • 定期用系统表巡检无主键表，把它们补上——这直接决定逻辑复制能否工作：

    sql
    SELECT c.relname FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname = 'public'
  AND NOT EXISTS (SELECT 1 FROM pg_constraint p WHERE p.conrelid = c.oid AND p.contype = 'p');

  • 设计分区表时把主键与分区键一起规划，并明确「全局唯一」由谁保证（全局序列/UUID，而非主键）。

  • 上线前用 pg_get_constraintdef() 复核主键列顺序，复合主键的顺序错误会导致索引完全用不上。

    sql
    SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint
WHERE conrelid = 'orders'::regclass AND contype = 'p';

  • 主键宽度要算总账，把外键列的宽度一并计入——主键每宽 8 字节，所有引用方都要跟着宽 8 字节。

下一节

继续学习：命名规范

参考链接 ​