有时客户会问我自动生成主键的最佳选择。在本文中，我将探讨这些选项并给出建议。
为什么自动生成主键？
每个表都需要一个主键。在关系数据库中，能够识别单个表行非常重要。如果你想知道为什么，可以在互联网上搜索到成千上万的从表格中删除重复条目的问题。
建议您选择一个主键，该主键不仅是唯一的，而且在表行的生命周期内不会更改。这是因为外键约束通常引用主键，而更改主键会导致麻烦或不必要的工作。
例如一个表有一个自然主键，一个国家公民的社会保险号。但通常情况下，没有这样的属性，您必须生成一个人工主键。有些人甚至认为，即使有一个自然主键，你也应该使用一个人工主键，但我不会卷入那场“圣战”。
PostgreSQL中自动生成主键的技术
基本有两种方法：
用序列生成键
序列是一个数据库对象，它的唯一用途是生成唯一的数字。它使用一个递增的内部计数器来实现这一点。
序列对并发访问进行了高度优化，它们永远不会两次发出相同的数字。尽管如此，从多个并发SQL语句中访问序列可能会成为瓶颈，因此有一个CACHE
选项可以使序列同时向数据库会话发送多个值。
序列不遵循正常的事务规则：如果事务回滚，序列不会重置其计数器。这是为了良好的性能，并不构成问题。如果你正在寻找一种生成无间隙数字序列的方法，序列不是正确的选择，你将不得不求助于效率更低、更复杂的技术。
要从序列中提取下一个值，可以使用如下nextval
函数：
SELECT nextval('sequence_name');复制
生成UUID
UUID（通用唯一标识符）是使用有效保证唯一性算法生成的128位数字。有几种标准化的算法。在PostgreSQL中，有许多函数可以生成UUID：
uuid ossp
扩展提供了生成UUID的函数。注意，由于名称中有连字符，因此必须在扩展名上打双引号（CREATE EXTENSION "uuid-ossp";
）。从PostgreSQL v13开始，您可以使用核心函数
gen_random_uuid()
生成版本4（随机）uuid。
请注意，对于uuid，您应该始终使用PostgreSQL uuid
数据类型。不要试图将它们转换为字符串或numeric
——这样会浪费空间并降低性能。
定义自动生成的主键
有四种方法可以使用自动生成的值定义列：
使用DEFAULT子句
可以将此方法用于序列和UUID。以下是一些例子：
CREATE TABLE has_integer_pkey (id bigint DEFAULT nextval('integer_id_seq') PRIMARY KEY,...);CREATE TABLE has_uuid_pkey (id uuid DEFAULT gen_random_uuid() PRIMARY KEY,...);复制
每当INSERT
语句没有显式插入该列时，PostgreSQL就会使用DEFAULT
值。
使用serial和bigserial伪类型
此方法是上面的定义序列+设置DEFAULT
子句的快捷方式。使用此方法，您可以定义如下表：
CREATE TABLE uses_serial (id bigserial PRIMARY KEY,...);复制
这相当于：
CREATE TABLE uses_serial (id bigint PRIMARY KEY,...);CREATE SEQUENCE uses_serial_id_seqOWNED BY uses_serial.id;ALTER TABLE uses_serial ALTER idSET DEFAULT nextval('uses_serial_id_seq');复制
“OWNED BY
”子句在列和序列之间添加了一个依赖项，因此删除列会自动删除序列。
使用serial
将创建一个integer
列，而bigserial
将创建一个bigint
列。
使用标识列
这是使用序列的另一种方式，因为PostgreSQL使用“后台”序列来实现标识列。
CREATE TABLE uses_identity (id bigint GENERATED ALWAYS AS IDENTITYPRIMARY KEY,...);复制
还有“GENERATED BY DEFAULT AS IDENTITY
”，区别是如果您尝试显式地为列插入一个值（与DEFAULT
子句非常类似），则不会收到错误消息。
可以为标识列指定序列选项：
CREATE TABLE uses_identity (id bigint GENERATED ALWAYS AS IDENTITY(MINVALUE 0 START WITH 0 CACHE 20)PRIMARY KEY,...);复制
使用BEFORE INSERT触发器
这类似于DEFAULT
值，但它允许您无条件地用生成的值覆盖用户插入的值。触发器的最大缺点是对性能的影响。
我应该使用integer(serial)还是bigint(bigserial)作为自动生成的主键？
你应该总是使用bigint
。
的确，integer
只需要四个字节，而bigint
需要八个字节。但是：
如果您的表很小，确实
integer
就足够了，既然表很小，那么浪费的四个字节就无关紧要了。但并不是每一张你设计的小表都会一直小下去！如果您有一个大表，则可能会超过
integer
的最大值，即2147483647。请注意，如果表中包含的行少于这个值，也可能会发生这种情况：您可能会删除行，一些序列值可能会因回滚的事务而“丢失”。在活动数据库中将一个大表中的主键列从
integer
更改为bigint
非常复杂，所以您应该省去这种痛苦。
有了bigint
，你肯定永远不会超过9223372036854775807的最大值：即使你每秒插入10000行不停顿，也要将近3000万年的时间才能达到这个极限。
我应该为自动生成的主键使用bigserial还是标识列？
除非必须支持旧的PostgreSQL版本，否则应该使用标识列。
PostgreSQL v11中引入了标识列，与bigserial
相比，它有两个优点：
它符合SQL标准，而
bigserial
是PostgreSQL的专有语法。这将使您的代码更具可移植性。如果使用
GENERATED ALWAYS AS IDENTITY
，如果试图通过显式插入数字来覆盖生成的值，则会收到错误消息。这避免了一个常见问题，即手动输入的值稍后会与生成的值冲突，从而导致意外的应用程序错误。
因此，除非您必须支持PostgreSQL v10或更低版本，否则没有理由使用bigserial
。
我应该使用bigint还是uuid作为自动生成的主键？
我的建议是使用序列，除非您使用数据库切分或有其他原因以“分散”的方式（在单个数据库之外）生成主键
实际差异
bigint
的优势显而易见：
bigint
只使用8个字节，而uuid
使用16个字节从序列中获取值比计算UUID便宜
使用序列的一个缺点是它是单个数据库中的单个对象。因此，如果使用分片，即跨多个数据库分发数据，则不能使用序列。在这种情况下，UUID是一个明显的选择。（您可以使用INCREMENT
大于1和不同的START
值定义的序列，但添加其他分片时可能会出现问题。）
当然，如果主键的值不是由数据库自动生成的，而是在分布在多个应用服务器上的应用程序创建的，那么您也会更喜欢UUID。
假想差异
有人认为UUID更好，因为它们将写操作分布在主键索引的不同页面上。这应该会减少争用，并导致一个更平衡或更少碎片化的索引。第一个是正确的，但这实际上可能是一个缺点，因为它需要缓存整个索引以获得良好的性能。第二个肯定是错误的，因为B树索引总是平衡的。另外，PostgreSQL v11中的一个变化确保单调递增的值比随机插入更有效地填充索引（但随后的删除当然会导致碎片）。简言之，uuid
使用了两倍的存储空间，这将使索引更大，导致更多的写操作，并占用更多的缓存。这一事减小了它的这些优势。
Benchmark：bigint VS uuid
在更大的连接中，uuid
比bigint
慢。
我决定用这两个表运行一个仅限插入的benchmark测试：
CREATE UNLOGGED TABLE test_bigint (id bigint GENERATED ALWAYS AS IDENTITY (CACHE 200) PRIMARY KEY);CREATE UNLOGGED TABLE test_uuid (id uuid DEFAULT gen_random_uuid() PRIMARY KEY);复制
我在我的笔记本电脑（SSD，8核）上用一个pgbench
定制脚本执行benchmark测试，该脚本有6个并发客户端，在5分钟内重复运行1000条准备好的INSERT
语句的事务：
INSERT INTO test_bigint /* or test_uuid */ DEFAULT VALUES;复制
bigint | uuid | |
|---|---|---|
| 每秒插入数 | 107090 | 74947 |
| 单行索引增长 | 30.5 bytes | 41.7 bytes |
使用bigint
显然是成功的，但差别并不显著。
结论
序列生成的数字和UUID都可用作自动生成的主键。
除非需要在单个数据库之外生成主键，否则请使用标识列，并确保所有主键列都是bigint
类型。
