[译文] PostgreSQL 自动生成的主键的 UUID、序列或标识列？

有时客户会问我关于自动生成主键的最佳选择。在本文中，我将探索这些选项并给出建议。

为什么要自动生成主键？

每个表都需要一个主键。在关系数据库中，能够识别单个表行很重要。如果您想知道为什么，请在 Internet 上搜索数以千计的问题，寻求帮助以从表中删除重复条目。

建议您选择一个主键，它不仅是唯一的，而且在表行的生命周期内永远不会改变。这是因为外键约束通常引用主键，更改在别处引用的主键会导致麻烦或不必要的工作。

现在，有时一个表有一个自然的主键，例如一个国家公民的社会安全号码。但通常情况下，没有这样的属性，您必须生成一个人工主键。有些人甚至争辩说，即使有自然主键，也应该使用人工主键，但我不会参与那场“圣战”。

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对UUID使用 PostgreSQL 数据类型。不要尝试将它们转换为字符串，否则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, ... );

登录后复制

DEFAULT只要INSERT语句没有显式插入该列，PostgreSQL 就会使用该值。

使用serial和bigserial伪类型

此方法是定义序列和设置DEFAULT子句的快捷方式，如上所述。使用此方法，您可以按如下方式定义表：

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

这相当于以下内容：`

sql">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’);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列。

使用标识列

这是使用序列的另一种方式，因为 PostgreSQL 在“幕后”使用序列来实现标识列。

sql">CREATE TABLE uses_identity (
   id bigint GENERATED ALWAYS AS IDENTITY
             PRIMARY 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 使用两倍的存储空间，这将使索引更大，从而导致更多写入并占用更多缓存。

基准bigint与uuid

我的同事 Kaarel不久前进行了一次小型性能测试，发现这uuid比bigint更大的联接要慢。

我决定对这两个表运行一个小型的仅插入基准测试：

`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 个准备INSERT语句的事务五分钟：

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

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

column1	bigint	uuid
每秒插入	107090	74947
每行索引增长	30.5 字节	41.7 字节

使用bigint明显胜出，但差异并不大。

结论

序列生成的数字和UUID都可用作自动生成的主键。

除非需要在单个数据库之外生成主键，否则请使用标识列，并确保所有主键列的类型为bigint。

原文地址：
https://www.modb.pro/db/98437
https://www.cybertec-sql>postgresql.com/en/uuid-serial-or-identity-columns-for-sql>postgresql-auto-generated-primary-keys/