postgresql - 执行频繁插入时防止标识 id 列溢出
问题描述
我有一个表,它有一个 id 列,指定如下:
CREATE TABLE foo
(
id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
name text,
description text,
CONSTRAINT foo_pkey PRIMARY KEY (id)
)
我经常更新这张表(每 5 分钟一次),但我使用的是“ON CONFLICT DO NOTHING”
如本文所述,postgres 中的连载文章正在增加,即使我在冲突中添加什么也不做,预计 id 使用此设置会增加。
但是根据这篇文章,当主键 ID 超过限制时会发生什么情况?我确实需要担心计数器超出限制。
如何防止在这种情况下发生错误?
需要明确的是,我不需要值是无间隙的,也不需要使值按顺序排列,我只需要唯一性并且我希望能够使用尽可能小的数据类型(我想在某个时候切换到 smallint)
我可以看到的可能解决方案:
- 手动确保在执行实际插入之前不会插入重复项,就像在此解决方案中所做的那样:POSTGRES - 使用 ON CONFLICT DO NOTHING 防止串行增量。缺点:计算上我现在不是每次插入检查唯一性约束两次吗?
- 不知何故,postgres 使用无间隙的顺序值。缺点:似乎这不是 postgres 应该如何工作的意图
- 当它循环而不是抛出错误时,让 postgres 重新启动计数器:https : //www.postgresql.org/docs/9.5/sql-createsequence.html 说有一个循环选项听起来正是我需要的。
- 随它去,接受我需要使用 bigint
更新
至今:
- 可行的选择,但如前所述,存在 CPU 资源损失
- 基于这篇文章PostgreSQL 无间隙序列的坏主意
- 不可能,因为这会导致重复的 id
- 大多数情况下的最佳选择
对于我的特殊情况,我想进一步确定选项 1 是否可能不比选项 4 更好:
- 带有 id 的表有大约 20k 行
- 每 5 分钟进行一次包含几乎所有 20k 重复项的批量插入
- 引用具有 id 的表的表包含 100 亿多行并在 timescaledb 上运行
- 如果我们使用 bigint(8byte) 而不是 int(4byte) 我们使用 (10bn*(8-4)byte) ca。40GB 未压缩额外空间
- 我猜使用 timescaledb 这会被压缩到可能 10GB 用于 id 的额外空间
在这种情况下,考虑到对表的不频繁更新以及在这种情况下存储 id 的空间使用情况,在这种情况下选择选项 4 是否比选项 1 更好?
解决方案
我认为正确的解决方案是另一种:将标识列的数据类型更改为bigint
. 然后你不会用完序列值。
使用一个简单的执行该更改ALTER TABLE
将重写它并在语句期间锁定它。有更复杂的方法可以减少停机时间,例如看这个答案。