[译] PostgreSQL自动生成主键如何选择？

原文作者： Laurenz Albe
翻译：多米爸比
原文链接：https://www.cybertec-postgresql.com/en/uuid-serial-or-identity-columns-for-postgresql-auto-generated-primary-keys/

经常有客户向我咨询自动生成主键的最佳实践方法。本文我将探讨几种可选项，并给出推荐。

为什么需要自动生成主键？

每个表都需要一个主键。因为在关系型数据库中，识别每个数据表的行是很重要的。关于这一点如果还存疑，我们去网上搜索会发现有大量的人都在提问请求帮助他们解决如何删除重复数据的问题。

好的建议是我们不仅要对表选择一个唯一的主键，并且在其生命周期内不要改变主键值。这是因为其它表的外键通常会参考这张表的主键来进行约束，另外其他相关引用该主键值的地方也会出现问题。

有的时候，创建表时，可以从表字段中找到一个原生主键，例如身份证号码。但是通常情况下，是没有这样的字段，此时我们必须构造一个主键。也有部分人认为表中即使有原生字段可以作为主键使用，也应该独立构建，不应该依赖业务字段，但我不会加入这样争论中。

一、系统提供的自动生成主键方法

有两个基础的方法：

1.使用sequence序列生成主键

sequence是一个数据库对象，它的唯一目的就是生成不重复的数字。它是使用内部的递增计数器实现。

sequence极大的优化了并发访问，并且不会出现重复的数字。然而，当有很多并发的SQL语句访问同一个sequence时可能会成为瓶颈，因此，sequence提供了一个CACHE选项，它会在数据库会话期间一次性分配多个值。

sequence通常不会遵循事务的规则：如果一个事务进行回滚，sequence不会重置它的计数器。这也是为了性能所需，并且也不会构成问题。如果你需要生成一个无缝的数字序列，sequence不是一个正确的选择，此时你需要对序列值做低效的排序和更复杂的技术处理。

为了从一个sequence中获取下一个值，我们需要用到这样的函数：

SELECT nextval('sequence_name');

更多操作sequence的函数请参考文档。

2.生成UUIDs

一个UUID（universally unique identifier）是由算法生成的128-bit的数字,算法会保证唯一性。有几个标准化的算法。在PostgreSQL中，有两种方式生成UUIDs函数。

• uuid-ossp扩展提供了生成UUIDs的函数。注意，由于"-"在名称中，需要使用双引号名称来创建扩展( CREATE EXTENSION “uuidossp”; )
• 从PostgreSQL v13版本开始，可以使用内核函数 gen_random_uuid()来生成v4版本的UUIDs。

注意：对于UUIDs我们应该始终使用PostgreSQL的uuid数据类型。而不应该把uuid数据类型转换成string或numberic类型，否则我们将会浪费存储空间以及失去很好的性能。

二、自定义的自动生成主键方法

有四种方式可以使用自动生成值来定义字段：

1.使用DEFAULT从句

我们可以参考sequences和UUIDs的方法。下面是例子：

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值。

2.使用serial和bigserial伪类型

这种方法其实是使用sequence以及设置DEFAULT默认值的一个快捷方式，使用这种方法，我们可以定义表如下：

CREATE TABLE uses_serial (
 id bigserial PRIMARY KEY,
 ...
);

其实上面的方式与下面的创建方式是等价的：

CREATE TABLE uses_serial (
 id bigint PRIMARY KEY,
 ...
);

CREATE SEQUENCE uses_serial_id_seq
 OWNED BY uses_serial.id;

ALTER TABLE uses_serial ALTER id
 SET DEFAULT nextval('uses_serial_id_seq');

"OWNED BY"从句在表字段和序列间增加了一个依赖，因此在删除表字段时会自动删除序列。

使用serial类型会创建一个integer类型的字段，而使用bigserial类型会创建一个bigint类型的
字段。（使用smallserial类型会创建一个smallint类型的字段）

3.使用identity columns

这是使用sequence的另一种形式，因为PostgreSQL实际是使用sequence来实现identity columns

CREATE TABLE uses_identity (
 id bigint GENERATED ALWAYS AS IDENTITY
 PRIMARY KEY,
 ...
);

还有一种形式是"GENERATED BY DEFAULT AS IDENTITY"，也是一样的效果，除了当我们显式插入指定列的值时（类似一个DEFAULT语句）不会得到一个报错信息。

译者注：
GENERATED ALWAYS AS IDENTITY声明的字段，插入语句不能显式赋值，否则会报错。
GENERATED BY DEFAULT AS IDENTITY声明的字段，插入语句可以显式赋值，会使用赋值覆盖。

我们也可以对identity columns指定sequence选项：

CREATE TABLE uses_identity (
 id bigint GENERATED ALWAYS AS IDENTITY
 (MINVALUE 0 START WITH 0 CACHE 20)
 PRIMARY KEY,
 ...
);

4.使用BEFORE INSERT触发器

这个和DEFAULT默认值类似，但它允许我们强制覆盖用户插入的值。当然触发器最大的缺点是对性能有影响。

自动生成主键：integer(serial)和bigint(bigserial)我们该选择使用哪一种？

答案是我们应该使用bigint。

integer占4个字节，而bigint占8个字节。但是：

• 如果我们有一个小表，integer是足够的，浪费的4字节也无关紧要。然而并不是设计的每个表都是小表。
• 如果我们有一个大表，可能会超过integer的最大值2147483647。注意这也可能发生在包含较少行的表上：我们可能会删除行，sequence值可能会被回滚的事务消耗。

如今，在生产数据库中把一个大表的主键字段从integer转变成bigint类型而不使其消耗额外的停机时间是相当复杂的，此种经历的痛苦我们应该尽量避免。

使用bigint，可以肯定我们不会超过bigint的最大值9223372036854775807：即使我们以每秒10000行的速度不停的插入数据，我们至少需要三千万年才会达到上限。

自动生成主键：bigserial和identity column我们该选择使用哪一种？

答案是我们应该使用identity column，除非我们为了兼容旧版本的PostgreSQL。

identity column是从PostgreSQL v10开始引入的（原文写的是v11,应该是作者记错了），它对比bigserial有两个优点：

• identity column遵从SQL标准，bigserial属性是PostgreSQL里的语法。
• 如果我们使用GENERATED ALWAYS AS IDENTITY，如果我们在插入语句里显式赋值会得到系统的错误提示。这能让我们规避一个常见的坑：我们显式插入的值会与系统将来自动生成的值产生冲突，导致应用程序出现错误。

所以除非我们为了支持PostgreSQL v10以下的版本，否则我们没有理由继续使用bigserial。

自动生成主键：bigint和uuid我们该选择使用哪一种？

我的建议是使用sequence bigint，除非我们再数据库里使用了sharding分片或者有分布式的场景需求。

实际的区别

bigint具有明显的优点：

• bigint使用8字节存储，而uuid使用16字节存储。
• 从sequence取值的代价比计算获取UUID值要廉价的多。

使用sequence的一个缺点是它限制在单个数据库的单个对象中。所以如果你使用分片，把数据分布到多个数据库时不能使用sequence。在分片场景下，UUIDs才是合理的选择。（虽然我们也可以用sequence来解决：例如我们可以在定义的sequence时使用INCREMENT大于1以及不同的START步长值，但是这样我们增加分区的操作上问题会更多。）

当然，如果我们的主键不是数据库自动生成的，而是由不同应用服务里的应用程序创建时，我们也可以选择UUIDs。

臆想的区别

也有人争论使用UUIDs会更好些，因为可以将主键索引分散写入到不同的数据块。这被认为会减少写入竞争、写入更加均衡以及更少的碎片化索引。第一点是正确的，但这其实是一个缺点，实际我们需要缓存整个索引来提高性能。第二点完全是错误的，众所周知B-tree是总是保持平衡。同时PostgreSQL v11有一个新的变化：使用序列单调递增的填充索引比随机插入更加高效（但是后续的删除操作肯定是会引起碎片化的）。简而言之，上述优点要么是微不足道，要么是客户不存在的，事实上比均衡更加有影响的因素是uuid使用两倍的存储，这会让索引变得更大，写入也会变大，并且占用更多的缓存。

基准测试：bigint VS uuid

我的同事Kaarel不久前做了一个小的性能测试发现：大表join操作时uuid比bigint会慢一些。

因此我决定使用如下的两个表做少量的insert-only的测试：

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工具模拟6个并行的客户端，每个客户端执行1000个事务，对下面自定义的脚本持续运行5分钟。

INSERT INTO test_bigint /* or test_uuid */ DEFAULT VALUES;

自动生成主键性能对比 bigint VS uuid

很明显bigint性能更好，但是差异也并不是特别悬殊。

结论

sequence和UUIDs都可以用作自动生成主键。除非在单个数据库之外生成主键，否则请使用identity columns，并确保主键字段的类型为bigint。

我的相关文章

PostgreSQL新主键算法Nano ID介绍

保持联系

现组建了一个PG乐知乐享交流群，欢迎关注文章的小伙伴加微信进群吹牛唠嗑，交流技术。