我有一个表,它有一个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。
然而,根据这篇文章,当主键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分钟制作一个几乎包含所有2万个重复项目的批量插入
- 引用id为的表包含10bn以上的行,并在timescaldb上运行
- 如果我们使用bigint(8byte(而不是int(4byte(,我们将使用(10bn*(8-4(byte(ca。40GB未压缩的额外空间
- 我想,有了时间刻度数据库,这可能会被压缩到10GB的额外空间,用于id
在这种情况下,考虑到表的不频繁更新以及存储id的空间使用情况,选择选项4比选择选项1更好吗?
我认为正确的解决方案是另一个:将标识列的数据类型更改为bigint
。这样就不会用完序列值。
用一个简单的ALTER TABLE
执行该更改将重写它并在语句期间锁定它。有更复杂的方法可以用更少的停机时间来完成,例如这个答案。