Skip to content

Why are there gaps in my Postgres id sequence?

大多数数据库表都有一个主键，这个主键是一个虚构的数字，而且这个数字通常是通过序列生成的。

🌐 Most database tables have a primary key that is a made-up number, and that number is usually made by a sequence.

你可能会惊讶地发现，序列ID中出现空缺是使用关系数据库时的一种正常现象。这是一个常见的情况，起初可能会让人感到困惑，但这些空缺出现有多种原因，有时这是完全正常的，并不意味着你的数据库有问题。

🌐 You might be surprised to know that gaps in sequence IDs are a normal aspect of working with relational databases in general. It's a common scenario that can seem puzzling at first, but there are several reasons why these gaps occur, and sometimes, this is typically expected and does not indicate a problem with your database.

同样重要的是要理解，序列保证的是唯一性，而不是连续性，这不应该意味着你的数据库会有任何数据完整性问题。

🌐 It's also important to understand the distinction that sequences guarantee uniqueness, but not consecutiveness, and this should not imply any issues relating to data integrity with your database.

如何查看你的序列名称 #

🌐 How to check the name of your sequence

如果你不知道序列的名字，它通常是根据一个标准命名规则生成的：table_name_id_seq，其中 table_name 是你的表名，id 是你的自增列的名字。

🌐 If you don't know the name of your sequence, it's often formed based on a standard naming convention: table_name_id_seq, where table_name is the name of your table and id is the name of your serial column.

序列中出现空缺的常见原因 #

🌐 Common reasons for gaps in sequences

  1. 回滚 造成数据缺口最常见的原因之一就是事务回滚。如果你发起了一个包含插入操作的事务，负责为新行生成ID的序列会增加。如果由于某种原因事务没有成功完成——可能是因为违反了约束，或者出于故意回滚的决定——插入操作会被撤销，但使用过的序列值不会被归还或重复使用。文档中也有这样的说明：

为了避免阻塞从同一个序列获取数字的并发事务，nextval 操作永远不会被回滚；也就是说，一旦取到值，它就被认为已经使用过，不会再次返回。即使周围的事务后来中止，或者调用的查询最终没有使用这个值，这条规则依然有效。例如，带有 ON CONFLICT 子句的 INSERT 会先计算要插入的元组，包括执行任何必要的 nextval 调用，然后才会检测是否有冲突会导致它遵循 ON CONFLICT 规则。这样的情况会在分配的值序列中留下未使用的“空洞”。

  1. 删除 从你的表中删除行也会在主键值的序列中产生空缺。虽然删除不会直接影响序列，但它们会导致表中的ID看起来不连续。
  2. 手动序列调整 另一种导致缺口的原因可能是手动干预，比如直接增加序列号或者将它设置为一个新值。
  3. Upserts 当执行 upsert 时，即使设置为在冲突时不做任何操作，它仍然可以增加序列

检查空隙 #

🌐 Checking for gaps

要检查 Postgres 表中 ID 序列的间隙，你可以使用一个 SQL 查询，把 ID 序列和生成的、覆盖相同范围的数字序列进行比较：

🌐 To check for gaps in the sequence of IDs in a Postgres table, you can use a SQL query that compares the sequence of IDs to a generated series of numbers that spans the same range:

1
SELECT
2
    s.id AS missing_id
3
FROM
4
    generate_series((SELECT MIN(id) FROM your_table), (SELECT MAX(id) FROM your_table)) s(id)
5
    LEFT JOIN your_table ON your_table.id = s.id
6
WHERE
7
    your_table.id IS NULL;

这个查询应该能帮你找出存在缺口的地方。

🌐 This query should help you pinpoint where gaps exist.

遇到错误并调整顺序 #

🌐 Encountering errors and adjusting sequences

在处理序列的操作中，你可能会遇到这样的错误信息：

🌐 In operations involving sequences, you might encounter an error message such as:

Failed to run sql query: duplicate key value violates unique constraint "{table}_pkey"

这个错误表明序列生成的ID和表里的实际ID之间存在差异。

🌐 This error suggests a discrepancy between the sequence-generated IDs and the actual IDs in the table.

为了解决这个问题，你可以通过以下方式检查你序列的当前值或表中最大的 ID：

🌐 To address this, you can check the current value of your sequence or the highest ID in your table with:

1
postgres=# SELECT max(id) FROM <table_name>;
2
3
postgres=# SELECT nextval('{table}_{column}_seq');

并将序列值重置为与最高 ID 加 1 相匹配： SELECT setval('{table}_{column}_seq', (SELECT max(id) FROM <table_name>) + 1);

或者，或者可以把序列调整到一个特定的新值： ALTER SEQUENCE '{table}_{column}_seq' RESTART WITH new_value;

🌐 Or, alternatively, adjust the sequence to a specific new value: ALTER SEQUENCE '{table}_{column}_seq' RESTART WITH new_value;

实现一个无间隙的ID序列 #

🌐 Implementing a gapless ID sequence

如果你的应用需要连续的 ID，而且你决定构建一个无间隙的序列，三思而后行，因为这样会让所有使用这个“序列”的事务变成串行执行，从而大幅降低数据修改的性能。不过，如果你的应用确实需要无间隙的 ID，那你可以用触发器：在插入成功后，使用数据库触发器根据自定义逻辑分配下一个可用的无间隙 ID。

🌐 If your application requires contiguous IDs and you decide to build a gapless sequence. Think twice about this, as it will serialize all transactions that use that “sequence” which will then deteriorate your data modification performance considerably. However, if your application truly requires gapless IDs, then you can use a Trigger: After a successful insert, use a database trigger to assign an ID based on a custom logic that finds the next available gapless ID.