PostgreSQL：整数列的ID即将用尽

Question

问题

我们正在使用Java和PostgreSQL构建一个Web应用程序。它相当大且成功，并且应该能够继续运行至少几年。

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

Java的最大int值是2^31-1，约为20亿。对于PostgreSQL的整数类型，情况也是如此。该系统目前每天会使用约10k个ID，并且随着我们获得新用户，速度正在上升。

有一天，ID将耗尽并溢出。

问题

我们正在寻找解决方案。让我们首先排除的那个显而易见的解决方案：切换到Java的long和Postgres的bigint是一个干净的解决方案，但它需要大量工作。我们需要尽可能地推迟它。

到目前为止，我们想到的一些解决方案：

• 不要为所有内容使用一个序列，而是为每个表分配自己的序列。
  • 优点：这样可以使我们获得多达N倍的时间，其中N是表的数量。
  • 缺点：我们喜欢每行都有唯一ID的事实。
• 停止为某些表使用序列ID。例如，具有客户事件的表实际上不需要ID：customer，timestamp是一个完全有效的主键。
  • 优点：我们最大的ID消耗量可以通过此方式改变。
  • 缺点：需要做出相当大的努力。
• 停止浪费IDs用于空记录。这在某些子表中发生，例如客户联系信息。始终存在该记录可以使代码更简单，但这意味着许多客户存储了一个空的联系信息记录。
  • 优点：我们最大的ID消耗量可以通过此方式修复。
  • 缺点：我们失去了代码的简洁性。
• 每个新表都必须使用long/bigint和新序列。
  • 优点：至少我们不会让它变得更糟。
  • 缺点：与其余代码的接触面将很丑陋。

在这些限制条件下，还有哪些方法可以推迟ID的耗尽？

- Matej

---

是的，这个问题很痛苦。只有两种解决方法：1）使用bigint并进行重构（或）2）不要对所有东西使用序列。我更倾向于重构（其他方法，在某个点上你会用尽所有数字）。仅在我们知道该应用程序在数字用尽之前将被关闭的情况下，才会选择选项二。 - kosa

“这是很多工作。我们需要尽可能地推迟它。”如果你推迟它，到你开始处理时它将变成两倍的工作量。咬紧牙关，现在就去做吧。 - JP Moresmau

我很不想说这个，但是既然你在寻求意见和选择，你很可能会被关闭。虽然我不会这么做，但肯定会有人这样做。 - Paul Tomblin

@PaulTomblin 没有意见，只有选择。这里没有主观成分：我列出的所有选项和我希望的选项都对我们的问题有可衡量和可量化的影响。 - Matej

我们没有足够的信息来真正回答你的问题：我们不知道为什么你“需要尽可能地推迟它”；我们不知道如果你进行重构（无疑是正确的长期方法），你会牺牲什么；我们不知道你的代码库有多大，有多少人需要在此上工作，你可以使用哪些工具，你的测试覆盖率如何等等。任何建议都将基于不完整的信息。 - Andy Turner

看起来很难想出一个比从“整数”重构代码到“长整数”更省力的解决方案。此外，你在设计上似乎又犯了几个错误（例如，在没有联系信息的情况下为什么要存储空记录或者为什么要在所有表格之间共享相同的序列？）。 - Bruno

Answer 1

转换为long远非一个干净的解决方案。如果您的应用程序变得过大，只有一种明智的选择：UUIDs（是的，PostgreSQL带有uuid数据类型）。

它的大小为128位，相当于4个整数，但是您不想在未来几年内重新编写整个应用程序，对吧？当您的数据增长到需要分片时，UUIDs将起作用。那时您将无法拥有共享序列，这就是UUIDs有意义的原因。

作为奖励，您甚至可以在每行上保留其唯一属性。

---

迁移并不难：在PostgreSQL中添加一个带有NULL的列是很便宜的，所以你可以先添加一个列，然后分批进行在线迁移，每次更新几千条记录，这样就不会有停机时间。

然后你可以使用两个外键测试相同的代码。Java是否有类似于laboratory或scientist的东西？

这会是大量的工作吗？是的，但这显然是一个好兆头，如果你有一个如此受欢迎的应用程序。

我也希望你已经从为所有表使用相同的序列中得到了教训。老实说 - 我真的看不出其中的附加价值。如果你想知道一个对象属于哪个表，你也可以用不同的主键命名它们（例如room_id、reservation_id等）。

- hruske

Answer 2

自从提出这个问题后，我找到了一个不错的方法来解决其中一半的问题 - 数据库端。因此，为了后代，这是解决方法。

1. Find all the DB columns of type integer or integer[]. Check the results manually and remove columns of type, for example, 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';
   

2. Prepare a data type change DDL for each of those columns:

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

3. This works beautifully, except for the VIEWs: they don't like me changing their return types. I need to re-create all of them - the sequence will be

   1. Drop all the views.
   2. Alter column types.
   3. Re-create all the views.
4. Stop the application, run the upgrade script from step 3, fix slow queries by running VACUUM and ANALYZE on all the tables.
5. Run tests and fix issues in the source code - for example, bigint[] cannot be cast to integer[].

如何仅导出/备份视图？

- Matej