PostgreSQL 主键

内容：定义方式、底层原理、索引、最佳实践、常见坑

一、什么是 PostgreSQL 主键？

主键 = 唯一标识一行数据的列（或列组合）

在 PostgreSQL 中，主键同时意味着：

唯一（UNIQUE）
非空（NOT NULL）
自动创建唯一索引（B-Tree）

PRIMARY KEY = UNIQUE + NOT NULL + UNIQUE INDEX

二、定义主键的几种方式（从推荐到不推荐）

✅ 1️⃣ 建表时定义（最推荐）

单字段主键

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    name TEXT NOT NULL
);

联合主键

CREATE TABLE user_roles (
    user_id BIGINT,
    role_id BIGINT,
    PRIMARY KEY (user_id, role_id)
);

非常适合 多对多关系表
自动防止重复关系

✅ 2️⃣ 使用 identity 自增主键（PostgreSQL 10+ 推荐）

CREATE TABLE articles (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title TEXT NOT NULL
);

或：

id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

两者区别（重要）：

类型	是否允许手动插入
ALWAYS	❌ 默认禁止
BY DEFAULT	✅ 允许

⚠️ 3️⃣ SERIAL（旧写法，不推荐新项目）

id SERIAL PRIMARY KEY

问题：

本质是 sequence + int
不符合 SQL 标准
可控性差

❌ 4️⃣ 后期用 UNIQUE + NOT NULL 代替（不等价）

UNIQUE(id) + NOT NULL

❌ 不等价
❌ 语义不同
❌ 工具 / ORM 识别不了主键

三、主键的底层原理（你必须知道）

1️⃣ 主键就是一个唯一索引

\d articles

你会看到：

"articles_pkey" PRIMARY KEY, btree (id)

2️⃣ 插入 / 更新时的行为

插入重复主键 -> ❌ 直接失败
更新主键 -> 等价于：
- DELETE + INSERT
- 成本很高

📌 主键字段一旦确定，绝不更新

四、主键 vs 唯一索引（重要区别）

对比	主键	UNIQUE
是否允许 NULL	❌	✅
一个表能有几个	1 个	多个
ORM 识别	✅	❌
语义	行标识	约束

👉 能用主键就不要用 UNIQUE 代替

五、主键字段类型如何选（最佳实践）

✅ 强烈推荐

BIGINT / UUID / ULID

1️⃣ BIGINT（最常用）

id BIGINT GENERATED ALWAYS AS IDENTITY

顺序插入
索引友好
COPY / JOIN 快

2️⃣ UUID（分布式）

id UUID PRIMARY KEY DEFAULT gen_random_uuid();

✅ 多节点安全
❌ 索引膨胀
❌ 写入慢

3️⃣ ULID（推荐替代 UUID）

有序
全局唯一
索引友好

六、主键与性能的关系（非常关键）

❗ 主键 = 聚集索引吗？

PostgreSQL 没有聚集索引概念（和 MySQL InnoDB 不同）

主键 ≠ 数据物理顺序
物理顺序由 插入 / VACUUM / CLUSTER 决定

可选优化（高级）

CLUSTER articles USING articles_pkey;

✅ 提升主键范围查询
❌ 非自动
❌ 写入后会逐渐失效

七、COPY / 导入数据与主键

1️⃣ identity + COPY

GENERATED ALWAYS -> 禁止手写 id

解决：

不导 id
或使用 \copy … OVERRIDING SYSTEM VALUE
或改为 BY DEFAULT

2️⃣ 导入后一定要修复序列

SELECT setval(
    pg_get_serial_sequence('articles', 'id'),
    (SELECT MAX(id) FROM articles)
);

八、主键设计最佳实践（生产总结）

✅ 每个表 必须有主键
✅ 主键 不可变
✅ 主键 短、简单、数值型优先
✅ 多对多表用联合主键
❌ 不要用业务字段当主键
❌ 不要更新主键

九、总结

PostgreSQL 主键 = 行的唯一身份

推荐：BIGINT identity

一次设计，永不修改