后greSQL：即将用完整数列中的ID

Question

我们正在用爪哇在后交后SQL之上构建一个Web应用程序。它相当大且成功，至少应该能够再运行几年。

不幸的是，我们（嗯，我）在设计过程的早期阶段犯了一个严重的错误：所有数据库ID都是整数，从一个共享序列中分发。

Java的max int是2^31-1，所以大约20亿。PostgreSQL的整数类型也是如此。该系统目前每天消耗大约10k个id，而且随着新用户的增加，这个速度还在加快。

总有一天，id会用完并溢出。

我们正在寻找解决这种情况的方法。让我们立即解决显而易见的问题：切换到Java的long和Postgres的bigint是一个干净的解决方案，但它是大量的工作。我们需要尽可能推迟它。

到目前为止，我们有一些想法:

• 不要对所有内容使用一个序列，为每个表指定自己的序列。
  • 优点：这给了我们长达N倍的时间，其中N是表的数量。
  • 缺点：我们喜欢每行都有一个唯一的ID。
  • 优点：我们一些最大的ID猪可以通过这种方式改变。
  • 缺点：工作量不大。
  • 优点：我们的一些最大的ID猪可以通过这种方式修复。
  • 缺点：我们失去了代码的简单性。
  • 优点：至少我们不会让情况变得更糟。
  • 缺点：与其余代码的接触面将很丑陋。

  在这些约束下，还有哪些方法会延迟ID耗尽？

Answer 1

自从问了这个问题，我找到了一个很好的方法来解决一半的问题——数据库方面。所以，对于后代来说，这是做到这一点的方法。

>

查找integer或integer[]类型的所有DB列。手动检查结果并删除类型的列，例如text[]。

SELECT *
FROM information_schema.columns cls
JOIN information_schema.tables tbl ON cls.table_name = tbl.table_name
WHERE
  cls.table_schema = '<my schema>'
  AND cls.data_type = 'integer' OR cls.data_type = 'ARRAY'
  AND tbl.table_type = 'BASE TABLE';

为每个列准备数据类型更改 DDL：

ALTER TABLE <one of the tables found> 
ALTER COLUMN <one of its integral columns> 
TYPE bigint;

这工作得很好，除了VIEW：他们不喜欢我改变他们的返回类型。我需要重新创建所有这些 - 序列将是

1. 删除所有视图
2. 更改列类型
3. 重新创建所有视图

如何仅导出/备份VIEWs？

Answer 2

切换到多头远不是一个干净的解决方案。如果你增长得太大，只有一个明智的选择：uuid（是的，PostgreSQL附带了uuid数据类型）。

在128位，它的大小为4个整数，但你不想在几年内完成整个应用程序，对吗？当你的规模变得太大，需要对数据进行分片时，UUIDs可以工作。那时你将不能拥有一个共享的序列，这就是UUIDs有意义的原因。

作为奖励，您甚至可以在每一行保留您的独特属性。

迁移并不难：在PostgreSQL中添加一个NULL列很便宜，因此您可以先添加一个列，然后分批进行在线迁移，一次更新几千条记录，这样就不会停机。

然后，您可以使用这两个外键测试相同的代码。Java有类似于实验室或科学家的东西吗？

会有很多工作要做吗？是的，但如果你有一个如此受欢迎的应用程序，这显然是一个好迹象。

我还希望你已经吸取了教训，对所有的表使用相同的序列。老实说——我真的看不到这有什么附加值。如果你想知道一个对象适合哪里，你也可以用不同的方式命名主键（例如room_id、reservation_id等）。