PostgreSQL主键自动生成策略

Posted on 2022-06-14 in database, postgres • 4024 words • 9 minute read

为什么要使用主键？

关系型数据库中的每张表都有一个主键。主键能标识数据库表中特定的一行数据，主键的数据不能重复(UNIQUE）且不为空(NOT NULL)。

主键分为自然主键(Natural Primary Key)和代理主键(Artificial Primary Key)。当主键本身具有意义，如身份证号、学号，这个主键被称为自然主键；当主键无意义，仅作为标识符来区别一行数据，这个主键被称为代理主键。

现代数据库系统允许建表时不指定特定列为主键，但会隐式使用表中的隐藏列作为主键，性能也会有影响。而使用主键可以确保数据唯一，在这张表的生命周期中永远不会被改变，也不会引来什么麻烦。

主键自动生成有哪些方法？

1. 生成Sequence

Sequence是数据库中的对象(Object)，简单来说就是一个自增计数器。

Sequence针对高并发进行了优化，并发访问时也不会产生两次同样的数值。如担心并发过高，访问该Sequence时出现瓶颈，可以针对Sequence指定Cache，这样每次Sequence会预生成数值存储在内存中，拥有更快的读取。

事务在Sequence上无作用，即使事务产生回滚，Sequence不会重置计数，这样能保证性能，更不会带来问题。如希望Sequence值保持连续，那用Sequence就不是正确的选择，需要寻找效率更低且更复杂的技术来完成。

2. 生成UUID

UUID(universally unique identifier)是128位的数字，可通过几种标准化算法生成从而保证数字的独特性。PostgreSQL有一些生成UUID的办法：

使用uuid-ossp扩展
从PostgreSQL 13开始，可以使用gen_random_uuid()方法来生成version 4的UUID

应使用uuid数据类型来存储UUID，使用string或numeric既浪费空间，又降低性能。

如何定义主键自动生成？

建表时可通过4种方式定义自生成值

1. 使用DEFAULT

CREATE TABLE some_table (
  id BIGINT DEFAULT nextval('some_table_id_seq') PRIMARY KEY,
  ...
);

CREATE TABLE some_table (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
);

如果INSERT时没有明确插入该列，PostgreSQL就会插入DEFAULT指定的值。

2. 使用Serial和BigSerial伪类型

该方法简化了Sequence的创建：

CREATE TABLE some_table (
  id BIGSERIAL PRIMARY KEY,
  ...
);

等同于：

CREATE TABLE some_table (
  id BIGINT PRIMARY KEY,
  ...
);

CREATE SEQUENCE some_table_id_seq OWNED BY some_table.id;

ALTER TABLE some_table ALTER id SET DEFAULT nextval('some_table_id_seq');

使用 OWNED BY会将列id与some_table_id_seq进行绑定，如果drop掉列id，那么some_table_id_seq也同时会被drop掉。

Serial创建的列为integer类型，BigSerial创建的列为bigint类型。

3. 使用IDENTITY列

可以定义列为IDENTITY列，PostgreSQL会隐式创建一个Sequence绑定在该列上：

CREATE TABLE some_table (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
);

还可以通过该方式创建，区别是这种方式明确往该列插入值时不会报错：

CREATE TABLE some_table (
  id BIGINT GENERATED ALWAYS AS IDENTITY (MINVALUE 0 START WITH 0 CACHE 10) PRIMARY KEY,
);

4. 使用BEFORE INSERT触发器

和DEFAULT方式差不多，但会用生成的值无条件覆盖插入的值。性能影响很大。

用integer(serial) 还是 bigint(bigserial) 作为主键数据类型？

用bigint

integer占用4个字节，bigint占用8个字节，但：

如果表为小表，integer的确够了，但bigint多出来的4个字节也不会产生多大影响。
如果表为大表，那表中的数据有可能会超过integer的最大值2147483647。将活跃大表的主键从integer改成bigint的SQL语句很简单，但执行后该表不可用的时间会相当长。

使用bigint，肯定不会超过最大值9223372036854775807，即使每秒插入10000行，也需要3000万年才能超过最大值。

用bigserial 还是 IDENTITY列作为ID自动生成主键？

用IDENTITY列，如果要兼容老版本PostgreSQL选bigserial

PostgreSQL 11引入了IDENTITY列，相比BIGSERIAL有两个优势：

IDENTITY列属于SQL标准，BIGSERIAL是PostgreSQL专有语法，进行数据库迁移时更有优势。
如使用GENERATED ALWAYS AS IDENTITY，在INSERT时明确指定了一个值，PostgreSQL会抛出错误。这样能避免手动输入的值和之后自生成的值发生冲突。

除非使用的是PostgreSQL 10或更低版本，否则不推荐使用bigserial。

用Sequence(bigint) 还是 UUID作为ID自动生成主键？

除非数据库做Sharding，或需要外部应用生成主键，有原因的话选UUID，否则选Sequence(bigint)

真实差异

bigint占用8个字节，UUID占用16个字节
从Sequence中获取bigint值的开销比计算UUID要小

Sequence能确保单个数据库的任意访问时得到的值唯一。数据库如果使用Sharding，Sequence并不是一个很好的选择，UUID更好。Sequence可以用INCREMENT让自增大于1，这样数据库每个Shard中的Sequence值能保证唯一，但增减Shard数量时又会有新问题。

如果主键的值并不是通过数据库生成，而是通过外部应用（如分布式系统应用）来生成，选择UUID也是没问题的。

假想差异

很多人坚信UUID作为主键更好，因为UUID能将写操作分散到主键索引的不同页上。看上去能减少索引争用，能有更平衡的索引或不那么稀疏的索引页。虽然第一个结论是对的，但更像是缺点，因为要加载更多的索引到内存中，有更多的缓存未命中，想要获得更好的性能就需要更多的内存，而且写入更多索引时产生的WAL更多，从而又需要更多的CPU时间、磁盘操作、网络通信。第二个结论是错的，B树索引总是平衡的，除非删除某个特定索引页上的数据，否则不会产生稀疏索引页。

使用Sequence作为索引时，所有的新数字是紧密相连的，几乎只会在接近尾部的索引页上进行操作，而在PostgreSQL 11版本中的一个Commit，进一步保证在进行INSERT操作时单调增加的值比随机生成的值更有效地填充索引。

简而言之，UUID与Sequence相比的优势很少，或者没有，UUID需要存储比Sequence要多，索引更大，导致更多的磁盘操作，占用更多的内存。

安全问题

有人担心Sequence不安全，会泄露业务信息，因为可以通过观察自增生成的值从而预估业务增量。

如有这方面的担忧，请使用UUID。

说了那么多UUID的不好，还推荐UUID？

是，但不完全是

通过往生成UUID的算法中引入一些确定性，可以避开上面的一些问题。不要盲目使用Version 4 UUID，2ndQuadrant写了关于Sequential UUID Generator的文章，可以使用sequential-uuids extension提供的两个方法：

uuid_sequence_nextval(sequence, block_size, block_count)
uuid_time_nextval(interval_length, block_count)

两个方法都使用了相同的思路，在UUID初始固定空间使用非随机数据，其余部分使用随机数据。uuid_sequence_nextval使用一个序列来生成非随机数据，而uuid_time_nextval使用当前时间戳。两个方法都可配置，如果使用1 byte作为固定空间，即16位，64K数值，剩余部分还能保证相当高的随机性。

    c32f              7dc9-8c8b-44ff-a507-1f52585131ed
|----------|    |------------------------------------------|
  sequence                       randomness
   1 byte                         7 bytes
   16 bits                        112 bits

使用完这64K数据之后，生成器会回到第一个值上：

prefix := (nextval('s') / block_size) % block_count

简单来说，就是将索引想象成一个环，数据均匀在环上移动并插入，这个环写入一定层时又会新开一个索引页继续写，这样能减少内存中停留的索引页，提高缓存命中率，WAL更小，磁盘有更好的吞吐。

有没有除Sequence和UUID的简易方案？

有的，试试ULID

ULID能与UUID兼容，能存储到PostgreSQL中的UUID类型中，前48位为时间戳，其余部分提供随机。

    01D45VGTV6             48329YZFE7HYVGWC
|----------------|    |------------------------|
       time                   randomness
      48bits                    80bits

ULID使用了Base32，看上去和使用Base16的UUID不太一样。

ULID和上面说的所有UUID生成器不同的地方是，前面时间位是顺序递增的，从不循环。优点非常明显：所有使用生成器创建的ULID都能很好的进行排序。缺点也很明显：随机位少，更容易发生碰撞。

如果使用一个固定前缀呢？

你是说Stripe ID？

Stripe ID附加了一个ID前缀，如ch_，其中的一些数字为时间戳，其余数据提供随机：

ch_3JN1LPC0uUk3QxXy1qs3CN76

前缀能让人了解ID上下文，比如ch_ 为 Charge ID的前缀，cus_ 为 Customer ID的前缀。无论是开发、测试、Debug、追踪生产问题都十分简单，查询数据时有迹可循。

存储bigserial或UUID数据时空间效率十分不错，但将一个字符串前缀放到前面会影响这一点，因为数据需要存储为VARCHAR或TEXT。但有个办法：在数据进入数据库表前拿掉前缀，从数据库表中提取时追加前缀。

总结一下？

确定的：

主键应有足够的空间，bigint(bigserial) 和 uuid都不错，integer(serial) 不推荐。
考虑性能，使用bigint(bigserial)、sequential-uuid或ulid都不错。
bigint(bigserial) 能保证在一个数据库上生成ID不会遇到严重问题，很多情况下不会立即进行数据库大规模扩展，但如果业务发展迅速，请尽快具备分布式生成ID的能力，并尽快进行迁移。
隐晦的ID提供了足够的安全，能防止枚举碰撞攻击，并难以推断存在的数据量和数据产生的速度。

可能的：

对单调递增生成的数据排序等同于按创建时间排序，如果不在一些地方上有强迫症，应该没什么问题。
使用前缀可以轻松区分ID类型，并且很容易制作很多有用的内部工具。

不好的：

Version 4 UUID的好处是拥有广泛的支持，不是绝对的好。更聪明的UUID生成器或ULID这样的替代方案有很多。
避免发明不需要的技术。我在现在的工作中自研了相当聪明的工具使用Base62将UUID压缩，并且作为主键存储为VARCHAR，与UUID查询兼容，但与UUID存储不兼容，无论是认知还是操作上都耗掉了我非常多的精力和时间，收益只有当时自鸣得意的小胜利。

Timbo Site

write something