七月 8, 2024
摘要：在本教程中，您将学习如何在 PostgreSQL 中以最短的停机时间更改列。
目录
背景
假设您的表中有一个 id 列，该列是一个自增整型字段，在 PostgreSQL 中其值最高可达 21 亿左右。20 亿听起来可能很多，但对于商业应用来说，这并不总是足够的。如果您的某个表接近此限制，则必须在达到此限制之前执行某些操作，以免到时将无法再向表中添加新行。
当然，一个预防措施是在一开始就将此字段设为 bigint，这是一种 8 字节的整型，但发现问题时可能为时已晚。最直接的做法是运行一条 ALTER 命令：
ALTER TABLE large_table ALTER COLUMN id BIGINT;
问题没那么简单！由于 PostgreSQL 运行的 MVCC 模型，更改列通常会导致整个表的重写，只有少数例外。同时，它要获取表的排他锁，这意味着没有其他人可以访问它。
如果表非常大，这个表会有好几天时间都无法访问。但是，有些实时进程会依赖此表，这些进程虽然可以暂时暂停，但绝不可能暂停几天。
使用触发器复制列
幸运的是，触发器可以解决我们的问题。触发器是 PostgreSQL 的一项功能（以及许多其他关系数据库），它在某些事件上自动执行查询。它允许我们将 id 列复制到另一种不同数据类型的列中。
该过程将如下所示：
1. 创建一个新的 bigid 列，类型为 bigint：
ALTER TABLE large_table ADD COLUMN bigid BIGINT NULL;
2. 在表上添加一个触发器，以便在插入新行时更新 bigid。
CREATE OR REPLACE FUNCTION update_large_table_bigid() RETURNS TRIGGER AS $BODY$
BEGIN
NEW.bigid=NEW.id;
RETURN NEW;
END
$BODY$ LANGUAGE PLPGSQL;
CREATE TRIGGER large_table_bigid_update
BEFORE INSERT ON large_table
FOR EACH ROW EXECUTE PROCEDURE update_large_table_bigid();
3. 为现有行回填 bigid：
UPDATE large_table SET bigid = id WHERE bigid IS NULL;
步骤 3 与系统的 I/O 能力相关联，是整个迁移过程中最耗时的部分。您应该通过将其分解为小的子查询（例如，一次只更新一小段范围的 id）来优化它，这样它就不会一次锁定整个表，并且可以更高效地执行。这可能大约需要几天才能完成，如果你在截止日期前还有几周的时间，那你应该可以忍受它。
完成这些步骤后，bigid 列的值将与 id 完全相同。可以通过在事务中执行以下查询，来完成迁移：
BEGIN;
LOCK TABLE large_table IN SHARE ROW EXCLUSIVE MODE;
DROP TRIGGER large_table_bigid_update ON large_table;
ALTER SEQUENCE large_table_seq OWNED BY large_table.bigid;
ALTER TABLE large_table ALTER COLUMN bigid SET DEFAULT nextval(large_table_seq);
ALTER TABLE large_table DROP COLUMN id;
ALTER TABLE large_table RENAME COLUMN bigid TO id;
COMMIT;
要记住两件事：
- 我们需要将序列移过来，以让 bigid 可以自动递增
- 在事务开始时必须锁定表 large_table，以防止在迁移过程中插入新行
该事务应该不会超过几秒钟，与长达几天的访问中断相比，这已经有了很大的提升！
使其成为主键
您可能已经注意到上述过程中缺少某些内容：新的 id 列不是主键。由于此列可为 null，因此将其设为主键，涉及 NOT NULL 约束检查和添加唯一索引。这将再次锁定表，并导致更多的停机时间。
我们可以通过提前创建唯一索引，来缩短停机时间，并告诉 PostgreSQL 在创建主键约束时使用该索引：
CREATE UNIQUE INDEX CONCURRENTLY large_table_uniq ON large_table(id);
ALTER TABLE large_table ADD PRIMARY KEY USING INDEX large_table_uniq;
请注意 CONCURRENTLY 关键字：这允许在不获取表的排他锁的情况下构建索引。当然，添加主键仍然会锁定表，因为它需要强制执行 NOT NULL 约束，但应该会快得多，因为它可以跳过索引创建阶段。
另一种方法是，首先创建一个无效的 NOT NULL CHECK 约束，然后对其进行验证：
ALTER TABLE large_table ADD CONSTRAINT large_table_not_null CHECK (id IS NOT NULL) NOT VALID;
ALTER TABLE large_table VALIDATE CONSTRAINT large_table_not_null;
验证过程只会获取 SHARE UPDATE EXCLUSIVE 锁，这样，其他连接仍然可以执行读取和写入。结果是，该表将没有主键，但唯一索引和 NOT NULL 约束在功能上是等效的，我们在这里也不会出现任何访问中断。
处理依赖关系
到目前为止一切顺利，如果 large_table 是一个独立的表，这个计划就可以完美执行。假设您有一个表依赖于它，我们将该表称为 reftable，它有一个引用 large_table 的 id 列的外键约束。我们需要将引用列 large_table_id 和约束一起迁移。
与 large_table 表类似，我们启动了 large_table_id 列的复制过程。
1. 首先添加一个触发器：
CREATE OR REPLACE FUNCTION update_reftable_large_table_bigid() RETURNS TRIGGER AS $BODY$
BEGIN
NEW.large_table_bigid = NEW.large_table_id;
RETURN NEW;
END
$BODY$ LANGUAGE PLPGSQL;
CREATE TRIGGER reftable_large_table_bigid_update
BEFORE INSERT ON reftable
FOR EACH ROW EXECUTE PROCEDURE update_reftable_large_table_bigid();
2. 然后回填现有行：
UPDATE reftable SET large_table_bigid = large_table WHERE large_table_bigid IS NULL;
同样，可以按 id 范围拆分成多次更新，也许还可以考虑并行运行。
3. 并发添加索引：
CREATE INDEX CONCURRENTLY reftable_large_table_bigid ON reftable(large_table_bigid);
我们需要在此列上有一个外键约束，引用 large_table 表。简单地添加它将会锁定表，因此，我们退回到“创建无效约束后验证”的方法。
ALTER TABLE reftable ADD CONSTRAINT reftable_large_table_bigid_fk
FOREIGN KEY (large_table_bigid) REFERENCES large_table(bigid) NOT VALID;
ALTER TABLE reftable VALIDATE CONSTRAINT reftable_large_table_bigid_fk;
最后但并非不重要的一点是，在完成 large_table 迁移之前，执行下面的外键迁移操作。
BEGIN;
LOCK TABLE reftable IN SHARE ROW EXCLUSIVE MODE;
ALTER TABLE reftable DROP CONSTRAINT reftable_large_table_id_fk;
ALTER TABLE reftable RENAME CONSTRAINT reftable_large_table_bigid_fk TO reftable_large_table_id_fk;
DROP TRIGGER reftable_large_table_bigid_update ON reftable;
ALTER TABLE reftable DROP COLUMN large_table_id;
ALTER INDEX reftable_large_table_bigid RENAME TO reftable_large_table_id;
ALTER TABLE reftable RENAME COLUMN large_table_bigid TO large_table_id;
COMMIT;
结论
这样就全部完成了。借助触发器和 NOT VALID 约束，我们可以将迁移的停机时间，从几天减少到仅几小时。请记住，这里 99% 的时间是添加主键。如果您正在迁移非主键列，或者它可以直接省略掉，或者您不介意改用 NOT NULL CHECK 约束，则停机时间可以大大减少到几分钟甚至几秒钟。