修复Postgres序列生成的数字中的孔/间隙 [英] Fixing holes/gaps in numbers generated by Postgres sequence

问题描述

我有一个postgres数据库，该数据库广泛使用序列来生成表的主键。
在大量使用此数据库（即添加/更新/删除操作）之后，使用序列作为主键的列现在有很多空位/空白，并且序列值本身很高。

I have a postgres database that uses sequences extensively to generate primary keys of tables. After a lot of usage of this database i.e. Adding/Update/Delete operation the columns that uses sequences for primary keys now have a lot holes/gaps in them and the sequence value itself is very high.

我的问题是：有什么方法可以解决主键中的这些空白？

My question is: Are there any ways in which we can fix these gaps in Primary Keys? which should inturn bring down the max value of the number in that columns and then reset the sequence?

注意：很多这些列也被其他列引用为ForeignKeys 。

Note: A lot of these columns are also referenced by other columns as ForeignKeys.

推荐答案

可以解决此问题，但数据库执行起来很昂贵（尤其是IO），并且可以保证再次发生。我不会担心这个问题。如果您接近4B，请将主键和外键升级到 BIGSERIAL 和 BIGINT 。如果您即将接近2 ^ 64 ...那么...我想对您的应用程序有更多了解。 ：〜]

It is possible to solve this problem, but is expensive for the database to do (especially IO) and is guaranteed to reoccur. I would not worry about this problem. If you get close to 4B, upgrade your primary and foreign keys to BIGSERIAL and BIGINT. If you're getting close to 2^64... well... I'd be interested in hearing more about your application. :~]

这篇关于修复Postgres序列生成的数字中的孔/间隙的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！