PHP小码农

Appearance

PRIMARY KEY 主键 ​

主键（Primary Key）是关系模型里最重要的约束：它同时保证了行标识唯一与值非空，也是外键引用的默认目标。本节把主键的语法形式、背后自动创建的唯一 B-tree 索引、大表加主键的在线做法，以及分区表与逻辑复制对主键的硬性要求一次讲清。

前置知识

阅读本节前，建议先了解：SECURITY DEFINER / INVOKER

基础概念 ​

从约束的角度看，主键等价于两个约束的组合：

text
PRIMARY KEY (col)  ≡  UNIQUE (col) + NOT NULL

但它比「自己写 UNIQUE + NOT NULL」多做了一件事：PostgreSQL 会自动在背后创建一个唯一 B-tree 索引。这个索引不是可选项，而是主键实现的一部分——它既负责唯一性检查，也负责加速 WHERE id = ? 这类查找。

几个必须记住的事实：

事实说明
一个表最多一个主键语法层面直接禁止，第二个会报错
主键列自动 NOT NULL无需（也不允许）再写 NOT NULL
主键自动建唯一索引索引名默认 <表名>_pkey
主键可被外键引用这是它作为默认引用目标的原因
主键列可以为复合PRIMARY KEY (a, b)，只能写在表约束位置
主键不继承继承表（INHERITS）的父子主键互相独立

主键与其它约束在系统表里由 contype 字段区分：

contype含义
p主键（Primary Key）
u唯一（Unique）
n非空（Not Null，PostgreSQL 18 起才有独立条目）
c检查（Check）
f外键（Foreign Key）
x排他（Exclude）

主键与 UNIQUE 的唯一实质差别是「是否强制非空」和「能否有多个」，其余行为（自动建索引、可被引用、可延迟）二者一致。因此本节的很多结论同样适用于 UNIQUE 唯一约束。

语法与用法 ​

text
-- 列级写法（单列主键）
column_name data_type PRIMARY KEY [ index_parameters ]

-- 表级写法（单列或复合）
[ CONSTRAINT constraint_name ] PRIMARY KEY ( column_name [, ... ] ) index_parameters

-- index_parameters
[ INCLUDE ( column_name [, ... ] ) ]      -- 覆盖列（PostgreSQL 11+）
[ WITH ( storage_parameter = value [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

-- 已有表上增删
ALTER TABLE table_name ADD [ CONSTRAINT constraint_name ] PRIMARY KEY ( column_name [, ... ] )
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY USING INDEX index_name
ALTER TABLE table_name DROP CONSTRAINT constraint_name [ CASCADE ]

最简可运行示例：

sql
-- 列级写法：单列主键
CREATE TABLE demo (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text   NOT NULL
);
text
CREATE TABLE
sql
-- 表级写法：复合主键只能这样写
CREATE TABLE order_items (
  order_id   bigint  NOT NULL,
  line_no    integer NOT NULL,
  product_id bigint  NOT NULL,
  qty        integer NOT NULL CHECK (qty > 0),

  PRIMARY KEY (order_id, line_no)   -- 组合唯一，且两列都自动 NOT NULL
);

两种写法在单列时语义完全等价：

sql
-- 写法 A：列级
CREATE TABLE t_a (id bigint PRIMARY KEY);

-- 写法 B：表级（结果与 A 完全一致）
CREATE TABLE t_b (id bigint, PRIMARY KEY (id));

详细说明 ​

主键背后一定有一个唯一 B-tree 索引 ​

这是主键最容易被忽略的一点。建完主键后立刻看表结构，会发现多了一个索引：

sql
CREATE TABLE users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL,
  name  text NOT NULL
);
sql
\d users
text
                            Table "public.users"
 Column |  Type  | Collation | Nullable |           Default
--------+--------+-----------+----------+------------------------------
 id     | bigint |           | not null | generated always as identity
 email  | text   |           | not null |
 name   | text   |           | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

users_pkey 这个索引在 pg_class 里是真实存在的对象：

sql
-- 用 pg_index 证明：主键的索引就是这张表上的唯一索引
SELECT
  c.relname                        AS index_name,
  i.indisunique                    AS is_unique,
  i.indisprimary                   AS is_primary,
  pg_get_indexdef(i.indexrelid)    AS index_def
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'users'::regclass;
text
 index_name | is_unique | is_primary |          index_def
------------+-----------+------------+------------------------------
 users_pkey | t         | t          | CREATE UNIQUE INDEX users_pkey ON public.users USING btree (id)
(1 row)

is_unique = t 且 is_primary = t，索引定义是 CREATE UNIQUE INDEX ... USING btree。这解释了三个常见现象：

  • 主键能加速查询，因为本质就是一个 B-tree 索引（原理见 B-Tree 索引原理）。
  • INSERT 时主键要维护索引，不是「免费的」。
  • DROP CONSTRAINT 会连带删掉索引，不能只删约束留索引。

列级与表级：复合主键必须用表级 ​

单列主键两种写法等价，但多列主键只能用表级：

sql
-- 错误！列级写法无法表达组合
CREATE TABLE bad_pk (
  order_id bigint  PRIMARY KEY,
  line_no  integer PRIMARY KEY      -- 语法上合法，但语义是「两个独立主键」
);
text
ERROR:  multiple primary keys for table "bad_pk" are not allowed

注意这个错误同时揭示了另一条规则：一个表只能有一个主键。即使写成两个单列主键，也会被拒绝。

正确写法：

sql
CREATE TABLE order_items (
  order_id   bigint  NOT NULL,
  line_no    integer NOT NULL,
  product_id bigint  NOT NULL,
  qty        integer NOT NULL,

  CONSTRAINT pk_order_items PRIMARY KEY (order_id, line_no)
);

复合主键的语义是组合唯一，不是「各自唯一」：

sql
INSERT INTO order_items VALUES (1, 1, 100, 2);
INSERT INTO order_items VALUES (1, 2, 100, 2);  -- 允许：line_no 不同
text
INSERT 0 1
INSERT 0 1
sql
-- 错误！组合重复
INSERT INTO order_items VALUES (1, 1, 999, 5);
text
ERROR:  duplicate key value violates unique constraint "pk_order_items"
DETAIL:  Key (order_id, line_no)=(1, 1) already exists.

一个表只能有一个主键 ​

这条限制来自关系模型本身：主键是「行的唯一标识」，多一个就没有意义。想表达「另一组值也唯一」应该用 UNIQUE。

sql
-- 错误！表里已经有一个主键
CREATE TABLE dup_pk (
  id    bigint NOT NULL PRIMARY KEY,
  email text   NOT NULL,
  PRIMARY KEY (email)
);
text
ERROR:  multiple primary keys for table "dup_pk" are not allowed
sql
-- 错误！已有主键的表上再加一个
ALTER TABLE users ADD PRIMARY KEY (email);
text
ERROR:  multiple primary keys for table "users" are not allowed

正确做法是加唯一约束：

sql
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

主键命名 ​

不指定名字时，PostgreSQL 自动生成 <表名>_pkey：

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

SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'products'::regclass
ORDER BY contype;
text
   conname     | contype | definition
---------------+---------+------------
 products_pkey | p       | PRIMARY KEY (id)
(1 row)

显式命名在跨环境比对、ALTER TABLE ... DROP CONSTRAINT 脚本、备份恢复时都更省事：

sql
CREATE TABLE orders (
  id       bigint GENERATED ALWAYS AS IDENTITY,
  order_no text   NOT NULL,
  CONSTRAINT pk_orders PRIMARY KEY (id)     -- 自定义名字
);

还有一种常见的迁移场景是改名（不重建索引）：

sql
ALTER TABLE orders RENAME CONSTRAINT pk_orders TO pk_orders_main;

ALTER TABLE：加主键的代价与在线做法 ​

ADD PRIMARY KEY 是一个「重活」：它要扫描整表校验唯一性，还要建一个唯一索引。代价与表大小成正比，且在操作期间持有 ACCESS EXCLUSIVE 锁（关于锁级别详见 修改表结构（ALTER TABLE））。

小表直接加即可：

sql
ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY (id);

大表的标准做法是先在线建唯一索引，再用 USING INDEX 挂上去，把耗时最长的部分从持锁阶段剥离出去：

sql
-- 第 1 步：在线建唯一索引（不阻塞读写，耗时最长的一步）
CREATE UNIQUE INDEX CONCURRENTLY uq_orders_id ON orders (id);

-- 第 2 步：校验索引是否有效（CONCURRENTLY 失败时会留下 INVALID 索引）
SELECT indexrelid::regclass AS index_name, indisvalid, indisready
FROM pg_index
WHERE indrelid = 'orders'::regclass AND indexrelid = 'uq_orders_id'::regclass;
text
 index_name  | indisvalid | indisready
-------------+------------+------------
 uq_orders_id | t          | t
(1 row)
sql
-- 第 3 步：把已有索引提升为主键约束（很快，但仍持 ACCESS EXCLUSIVE 锁）
ALTER TABLE orders
  ADD CONSTRAINT pk_orders PRIMARY KEY USING INDEX uq_orders_id;

-- 说明：索引名不会自动改名，仍是 uq_orders_id

USING INDEX 有两个使用条件：

条件说明
索引必须是唯一索引否则报错 is not a unique index
索引不能是表达式/部分索引必须是普通列索引，不能带 WHERE
索引列不能可空需要先 SET NOT NULL，否则报错
sql
-- 错误！部分唯一索引不能被提升为主键
CREATE UNIQUE INDEX uq_tmp ON orders (id) WHERE status <> 'cancelled';
ALTER TABLE orders ADD CONSTRAINT pk_x PRIMARY KEY USING INDEX uq_tmp;
text
ERROR:  index "uq_tmp" is a partial index

ADD PRIMARY KEY 会默认把主键列设为 NOT NULL。如果列里已经有 NULL，加主键就直接失败：

sql
CREATE TABLE legacy (id bigint, name text);
INSERT INTO legacy (name) VALUES ('a'), ('b');   -- 两行 id 都是 NULL

ALTER TABLE legacy ADD PRIMARY KEY (id);
text
ERROR:  column "id" of relation "legacy" contains null values

处理流程是先补数据、再加约束：

sql
UPDATE legacy SET id = nextval('legacy_id_seq') WHERE id IS NULL;
ALTER TABLE legacy ALTER COLUMN id SET NOT NULL;
ALTER TABLE legacy ADD PRIMARY KEY (id);

删除主键与其索引是一步到位的：

sql
ALTER TABLE orders DROP CONSTRAINT pk_orders;

-- 等价写法（按索引名删除）
ALTER TABLE orders DROP CONSTRAINT uq_orders_id;

DROP CONSTRAINT 默认 RESTRICT，有外键引用时会失败，需要先处理引用方或显式 CASCADE：

sql
-- 危险！会连带删除引用它的外键约束
ALTER TABLE orders DROP CONSTRAINT pk_orders CASCADE;

怎么选主键：代理键 vs 自然键 ​

PostgreSQL 没有「聚簇索引」概念，但主键索引的物理顺序仍会显著影响写入与查询（细节见 B-Tree 索引原理）。

维度代理键（Surrogate Key）自然键（Natural Key）
定义无业务含义的自增/UUID 列有业务含义的列（email、order_no）
稳定性永不变业务规则变化时要改
宽度8 字节（bigint）通常更宽（text）
索引体积小大，级联到外键更明显
可读性需要 JOIN 才看懂直接可读
推荐默认选它仅当值确实永不变且短

如果确实要用自然键，请把它写成一个额外的 UNIQUE 约束，主键仍保留代理键：

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

单调性对插入的影响是一个容易被低估的点：

主键类型插入模式影响
自增 bigint始终追加到索引最右端最好，页分裂少
时间有序 UUID（uuidv7()）基本追加好，PostgreSQL 18 新增，见下
随机 UUID v4随机落在索引任意位置差，写放大 + 缓存命中率低
自然键（字符串）取决于分布视分布而定，长度也是成本

PostgreSQL 18 新增了 uuidv7()，生成按时间戳有序的 UUID，兼顾了「不可预测/分布式唯一」与「索引友好」：

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

SELECT id FROM events LIMIT 1;
text
                  id
--------------------------------------
 0198f2a4-3c1e-7f2b-9d4a-6b8e0c12a345
(1 row)

对比随机 UUID：

sql
-- 不推荐：随机 UUID 主键，写入页随机跳转
CREATE TABLE events_bad (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid()
);

uuidv7() 有时间戳前缀，新行总是落在索引右端，因此插入局部性好得多。

主键与自增的搭配 ​

PostgreSQL 提供三种自增方式，主键搭配推荐 GENERATED ... AS IDENTITY：

写法标准性序列归属推荐度
GENERATED ALWAYS AS IDENTITYSQL 标准表，自动清理推荐（严格）
GENERATED BY DEFAULT AS IDENTITYSQL 标准表，自动清理推荐（可显式插值）
serial / bigserialPostgreSQL 方言独立对象，易误删兼容老代码
sql
CREATE TABLE users (
  -- 推荐：BY DEFAULT 允许数据迁移时显式指定 id
  id         bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email      text   NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT uq_users_email UNIQUE (email)
);

ALWAYS 更严格，导入数据必须加 OVERRIDING SYSTEM VALUE：

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

-- 错误！ALWAYS 不允许显式赋值
INSERT INTO users_strict (id, email) VALUES (100, 'a@example.com');
text
ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
sql
-- 正确：迁移时显式覆盖
INSERT INTO users_strict (id, email) OVERRIDING SYSTEM VALUE VALUES (100, 'a@example.com');

-- 导入完成后把序列推到当前最大值，避免后续自增冲突
SELECT setval(pg_get_serial_sequence('users_strict', 'id'),
              (SELECT max(id) FROM users_strict));

分区表：分区键必须包含在主键中 ​

这是分区表最常见的设计约束。PostgreSQL 要求唯一约束（含主键）的列必须是分区键的超集，因为唯一性只能在同一分区内保证，跨分区无法全局校验。

sql
-- 正确：分区键就是主键
CREATE TABLE orders (
  id         bigint  NOT NULL,
  created_at date    NOT NULL,
  amount     numeric(12,2) NOT NULL,
  PRIMARY KEY (id, created_at)          -- created_at 是分区键，被包含
) PARTITION BY RANGE (created_at);
sql
-- 错误！主键里没有分区键
CREATE TABLE orders_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_bad" lacks column "created_at" which is part of the partition key.

因此「按时间分区 + 单列 id 主键」这两个需求不能同时满足。实际项目里的处理方式：

方案说明代价
主键含分区键PRIMARY KEY (id, created_at)外键引用变复杂，引用方要带上两列
用全局 ID 但不做数据库唯一约束应用层保证 id 唯一失去数据库兜底
不分区保留单列主键大表维护成本升高

还有个细节：分区表的 id 在全局并不唯一。上面的正确示例中，(1, '2026-01-01') 与 (1, '2026-06-01') 可以共存：

sql
INSERT INTO orders VALUES (1, '2026-01-01', 10.00);
INSERT INTO orders VALUES (1, '2026-06-01', 20.00);   -- 允许！不同分区
SELECT count(*) FROM orders WHERE id = 1;
text
 count
-------
     2
(1 row)

所以分区表若要求 id 全局唯一，必须额外用全局序列/UUID 生成来保证，而不能依赖主键。

主键与逻辑复制：REPLICA IDENTITY ​

逻辑复制（Logical Replication）在 UPDATE / DELETE 时，需要一种方式在订阅端定位到对应行。默认取的就是主键。

sql
-- 查看表的 REPLICA IDENTITY 设置
SELECT relname, relreplident
FROM pg_class
WHERE relname IN ('users', 'logs');
text
 relname | relreplident
---------+--------------
 users   | d
 logs    | f
(2 rows)

relreplident 取值：

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

没有主键的表，UPDATE / DELETE 在逻辑复制中会失败（订阅端报 cannot delete from table ... because it does not have a replica identity），需要显式改成 FULL：

sql
-- 无主键表的补救（代价：每行变更都要记录全部旧列，WAL 显著增大）
ALTER TABLE logs REPLICA IDENTITY FULL;

这条与本节的关系是：主键不只服务于查询，也是逻辑复制的前提。设计新表时把主键定好，复制链路会省掉很多麻烦。逻辑复制的完整配置见后续「高可用与复制」阶段。

用系统表查询主键定义 ​

pg_get_constraintdef() 直接把约束翻译成可读 SQL，是复核与比对环境的首选：

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

也可以用 information_schema（更标准，但不含 PostgreSQL 特有信息）：

sql
SELECT tc.constraint_name, tc.constraint_type, kcu.column_name, kcu.ordinal_position
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON kcu.constraint_name = tc.constraint_name
 AND kcu.constraint_schema = tc.constraint_schema
WHERE tc.table_name = 'orders' AND tc.constraint_type = 'PRIMARY KEY'
ORDER BY kcu.ordinal_position;

实战示例 ​

场景：把一张已有 200 万行、原本没有主键的 orders 表在线加上主键，并验证过程中的索引与约束。

第 1 步：复现现状

sql
-- 初始表：无主键、id 可空
CREATE TABLE orders (
  id         bigint,
  user_id    bigint       NOT NULL,
  order_no   text         NOT NULL,
  amount     numeric(12,2) NOT NULL,
  created_at timestamptz  NOT NULL DEFAULT now()
);

INSERT INTO orders (id, user_id, order_no, amount)
SELECT g, (g % 1000) + 1, 'SO-' || lpad(g::text, 10, '0'), (random() * 1000)::numeric(12,2)
FROM generate_series(1, 2000000) AS g;

-- 记录体积，便于对比
SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size;
text
 table_size
------------
 137 MB
(1 row)

第 2 步：在线建唯一索引（不影响读写）

sql
CREATE UNIQUE INDEX CONCURRENTLY uq_orders_id ON orders (id);
text
CREATE INDEX

CONCURRENTLY 不阻塞读写，但可能失败并留下无效索引，务必校验：

sql
SELECT indexrelid::regclass AS index_name, indisvalid, indisready
FROM pg_index
WHERE indrelid = 'orders'::regclass AND indexrelid = 'uq_orders_id'::regclass;
text
  index_name  | indisvalid | indisready
--------------+------------+------------
 uq_orders_id | t          | t
(1 row)

若 indisvalid = f，需要先删除再重建：

sql
-- 失败时的清理
DROP INDEX CONCURRENTLY uq_orders_id;

第 3 步：确认无 NULL，再加主键

sql
SELECT count(*) AS null_rows FROM orders WHERE id IS NULL;
text
 null_rows
-----------
         0
(1 row)
sql
ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY USING INDEX uq_orders_id;
text
ALTER TABLE

第 4 步：验证结果

sql
\d orders
text
                                    Table "public.orders"
   Column   |           Type           | Collation | Nullable |           Default
------------+--------------------------+-----------+----------+------------------------------
 id         | bigint                   |           | not null |
 user_id    | bigint                   |           | not null |
 order_no   | text                     |           | not null |
 amount     | numeric(12,2)            |           | not null |
 created_at | timestamp with time zone |           | not null | now()
Indexes:
    "uq_orders_id" PRIMARY KEY, btree (id)

注意两点：id 自动变成了 not null；索引名仍是 uq_orders_id（USING INDEX 不会改名）。

sql
-- 唯一性确实生效
INSERT INTO orders (id, user_id, order_no, amount) VALUES (1, 1, 'DUP', 1.00);
text
ERROR:  duplicate key value violates unique constraint "uq_orders_id"
DETAIL:  Key (id)=(1) already exists.

第 5 步：补一个外键列索引（主键索引不覆盖 user_id）

sql
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);

第 6 步：清理

sql
DROP TABLE orders;

注意事项 ​

  • 一个表只能有一个主键。 需要多组唯一性时，多余的写 UNIQUE。
sql
-- 错误！
ALTER TABLE users ADD PRIMARY KEY (email);
  • ADD PRIMARY KEY 会扫描整表并建索引，且持 ACCESS EXCLUSIVE 锁。 大表必须走「CREATE UNIQUE INDEX CONCURRENTLY → ADD ... USING INDEX」。
sql
-- 大表上不要这样直接执行：全程阻塞读写
ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY (id);
  • ADD PRIMARY KEY 会隐式 SET NOT NULL。 列中已有 NULL 时直接失败。
sql
-- 错误！column "id" of relation "legacy" contains null values
ALTER TABLE legacy ADD PRIMARY KEY (id);
  • DROP CONSTRAINT 会连带删掉背后的索引。 想保留索引需重建。
sql
-- 删掉主键后，users_pkey 索引也随之消失
ALTER TABLE users DROP CONSTRAINT users_pkey;
  • USING INDEX 不能复用部分索引或表达式索引。
sql
-- 错误！index "uq_tmp" is a partial index
ALTER TABLE orders ADD CONSTRAINT pk_x PRIMARY KEY USING INDEX uq_tmp;
  • 分区表的主键必须包含分区键。 这是硬性限制，不是建议。
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);
  • 分区表的「主键」不保证全局唯一。 不同分区可以有相同键值，需要全局唯一请用全局序列或 UUID 生成。
sql
INSERT INTO orders VALUES (1, '2026-01-01', 10.00);
INSERT INTO orders VALUES (1, '2026-06-01', 20.00);  -- 成功！
  • 无主键表在逻辑复制中无法 UPDATE / DELETE。 需要 REPLICA IDENTITY FULL，但 WAL 体积会明显增大。
sql
ALTER TABLE logs REPLICA IDENTITY FULL;
  • 随机 UUID 主键会带来写放大。 每次插入都落在索引的随机位置，缓存命中率低、页分裂多。
sql
-- 不推荐：随机 UUID
id uuid PRIMARY KEY DEFAULT gen_random_uuid()

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

  • 主键列类型不要用 text/varchar 拼接业务信息。 主键索引会级联到每个外键列，宽度成本会被放大数倍。

  • 继承表的主键不跨父子生效。 子表可以插入与父表重复的 id（详见 创建表（CREATE TABLE））。

  • GENERATED ALWAYS AS IDENTITY 的数据导入需要 OVERRIDING SYSTEM VALUE，导入后别忘了 setval。

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

最佳实践 ​

  • 默认使用 bigint GENERATED BY DEFAULT AS IDENTITY 作为代理主键。 不要用 serial（序列是独立对象，容易被误删/漏迁），也不要用 int（26 亿上限在日志类业务里很容易撞到）。
sql
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • 业务唯一键额外写成 NOT NULL UNIQUE，不要试图塞进主键。
sql
id    bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
email text   NOT NULL,
CONSTRAINT uq_users_email UNIQUE (email)
  • 给主键显式命名。 默认的 <表名>_pkey 在跨环境比对和多版本迁移脚本里容易产生歧义。
sql
CONSTRAINT pk_orders PRIMARY KEY (id, created_at)
  • 大表加主键一律用两步法，并把 lock_timeout 设好，避免 ALTER TABLE 排队堵住后续所有查询。
sql
CREATE UNIQUE INDEX CONCURRENTLY uq_orders_id ON orders (id);

SET lock_timeout = '3s';
ALTER TABLE orders ADD CONSTRAINT pk_orders PRIMARY KEY USING INDEX uq_orders_id;
RESET lock_timeout;
  • 建唯一索引后校验 indisvalid，CONCURRENTLY 失败会静默留下无效索引。
sql
SELECT indexrelid::regclass, indisvalid FROM pg_index
WHERE indrelid = 'orders'::regclass AND NOT indisvalid;
  • UUID 主键优先用 uuidv7()（PostgreSQL 18+），避免随机 UUID 的索引写放大。
sql
-- PostgreSQL 18+
id uuid PRIMARY KEY DEFAULT uuidv7()

  • 分区表设计时先把主键和分区键一起规划。 一旦确定「按时间分区」，就要接受主键必须包含时间列。

  • 新表务必有主键。 它同时决定了外键引用目标与逻辑复制的行标识能力，缺主键的表在复制链路里几乎是不可用的。

  • 上线前用 pg_get_constraintdef() 复核主键定义与列顺序。 复合主键的列顺序会直接影响索引可用性。

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

下一节

继续学习：FOREIGN KEY 外键

参考链接 ​