如何通过变更让 PostgreSQL 翻车

原文标题 How not to change PostgreSQL column type

原文地址 https://notso.boringsql.com/posts/how-not-to-change-postgresql-column-type/

在开发应用程序和维护其后台数据库集群的过程中，我们经常会遇到实践与理论、开发环境与生产环境之间的差异。其中一个典型的例子就是变更数据库中的列类型。

对于在 PostgreSQL（及其他符合 SQL 标准的系统）中变更列类型的常规操作，大多数人都认为有一个标准的方法，即：

ALTER TABLE table_name
ALTER COLUMN column_name
[SET DATA] TYPE new_data_type

这种方法在语义上无可挑剔，但在特定情况下，它可能带来一些意想不到的困扰。

问题

让我们来创建一个示例表，并展示一些你可能会遇到的特定行为。从 1000 万条数据开始说起 —— 虽然听起来很多，但在数据世界里，只是沧海一粟。

-- create very simple table
CREATE TABLE sample_table (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    label TEXT,
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW()
);

-- populate with 10m records
INSERT INTO sample_table (label)
SELECT 
  'hash: ' || md5(random()::text)
FROM generate_series(1, 7000000);

将 id 的数据类型 从 INT 变更为 BIGINT。

alter_type_demo=# ALTER TABLE sample_table ALTER COLUMN id TYPE bigint;
ALTER TABLE
Time: 21592.190 ms (00:21.592)

然后 …… 21秒后，数据变更完成。但请注意，这只是一个含有大约 600 MB 数据的小型数据表。如果你要处理的数据量是这个的 100 倍怎么办？让我们来看看背后发生了什么。

PostgreSQL 必须执行的操作

变更数据类型以及其他许多操作都不是简单的任务，PostgreSQL 引擎需要执行以下几项关键任务：

• 重写整个表是最直接的必要步骤。将一个列从 INT 类型变更为 BIGINT 类型，需要为每个数据行额外分配 4 字节。原有的表结构要求数据以固定的字节大小存储，因此系统会以最有效的方式进行存储。在我们的例子中，这意味着需要读取并重新写入所有的 1000 万数据行，以符合新的数据行大小要求。

• 锁处理。虽然在我们这个假设的示例中加锁似乎不是问题，但如果你在实际的生产环境中，同时处理数百甚至数千个并发查询时执行 ALTER 命令，你可能需要先等待所有这些查询释放它们的锁。

• 索引和约束的重建。如果变更的列有索引或约束，这些索引和约束需要被重建或重新验证，这会带来额外的开销。

• 事务处理和预写日志（WAL，Write-Ahead Log）。为了保证数据的持久性——即 ACID 原则中的「持久性」（ACID 中的 Durability），PostgreSQL 必须在 WAL 文件中记录每次变更。这样，即使数据库发生崩溃，系统也可以通过重放 WAL 文件来恢复自上一次检查点以来的所有修改。

如你所见，在进行看似常规的表格维护时，实际涉及的因素远比想象的多。被修改数据的大小、磁盘输入/输出和容量以及系统的总体拥堵状态，都是必须考虑的关键点。

然而，这些还只是问题的一部分。在进行严肃的生产环境部署时，还有更多需要考虑的方面：

• 物理和逻辑实时复制：这又增加了一层复杂性。对于只读副本来说，系统默认通过同步提交来保持数据库集群的数据一致性。这种配置确保了只有在所有备份副本都确认接收到修改后，事务才会被最终确认。但这也带来了新的挑战，因为系统的表现现在还依赖于网络的传输效率，包括可能的网络拥堵，以及备份节点的延迟和输入/输出性能。

• 恢复和备份是另一个需要考虑的重要方面。虽然常规备份的大小可能不会有太大变化，但在变更前的最后一次备份与下一次备份之间所发生的所有事项都必须被考虑进去，确保能够做到时间点的一致性。

• 异步复制或为逻辑复制预留插槽（reserved slot）可能不太常见，但也不是没有。产生大量变更（以及 WAL 文件）可能会让性能较差（或不频繁）的复制系统落后相当长的时间。虽然这种情况可以接受，但必须确保源系统有足够的磁盘空间来长时间保存 WAL 文件。

如你所见，变更列数据类型并不像看起来那么简单。目前的 CI/CD 实践使得软件开发人员很容易就能提交并在生产环境中部署数据库schema变更，但这往往导致他们在几分钟后就面临生产环境的突发事件。虽然使用预发布环境可能有所帮助，但由于负载水平或成本限制，这样的环境并不能完全模拟生产环境的情况。

因此，问题的核心（我要再次强调）在于数据修改的规模、系统的整体拥堵状况、输入/输出性能以及目标数据库表在应用程序设计中的重要性。

最终，这些因素共同决定了完成 schema 变更所需的总时间，以及你的业务可能面临的独特限制 —— 这些限制你的业务可能承担得起，也可能承担不起。解决这一问题的最简单方法是在流量较低的时段安排计划内的维护工作，并确保其顺利完成。

如何安全地变更 PostgreSQL 列类型

如果你面临需要重写数百 GB 甚至 TB 的数据，并且几乎不能承受任何停机时间的情况，该如何安全地变更列类型呢？以下是一些步骤和策略。

让我们从坏消息开始：您无法避免重写整个表，这将在重写过程中产生大量 WAL 文件。这是必然的，你必须计划好如何管理它。

好消息是，你可以将可能的停机时间分散到一个比数据处理本身更长的时间段。具体的需求和限制会根据各个业务的需要而有所不同，因此需要仔细规划。

完整的 schema 过程可以总结为以下几个步骤：

1. 在目标表中添加一个新的列，并确保其类型正确。确保该列可为空且没有默认值，以避免全表重写。（更正：自 PostgreSQL 11 起，实际上无需重写整个表即可实现这一功能 ）例如，如果需要增加 order_id 的 ID，你应添加一个名为 new_order_id 的新列。

2. 设置一个触发器，以便在新数据进入时更新这一新列，确保在 schema 变更期间所有新数据都能及时填充到新列中。

3. 设计一个批处理变更逻辑，从旧列逐步变更到新列。批量大小和执行时间应根据业务或环境的实际运营限制进行调整。

4. 变更旧值：根据你的数据量、输入/输出能力及其他约束，旧数据的变更可能需要几小时到几周不等。对于较短的变更，你可能只需要在终端会话中运行一个 SQL 或 PL/pgSQL 函数（可以考虑使用 tmux）；而对于更长时间的变更，则可能需要更复杂的方法。

5. 变更完成后，为新列创建相应的约束和索引，并注意任何可能的锁定问题，尤其是当新字段作为外键的一部分时。

此时，你已准备好进行实际的切换操作。一旦确认所有数据行都已正确变更到新列，就到了面对最为复杂部分的时候了。如果可能的话，尽量在一次事务处理中完成，或安排在较短的计划停机期间进行。

• 删除原有的旧列。这个操作通常只会让表短暂地锁定。

• 在删除旧列之后，将新列重命名。这一步基本上标志着 schema 变更过程的完成。

考虑重新启动依赖于已更改表的所有应用程序是一种很好的做法，因为某些工具（ORM......说的就是你）可能会缓存 OID，无法从容应对更改。

然而，问题并未完全解决。仅仅删除列，只是移除了引用，而数据本身还会物理存留在磁盘上。在这种情况下，你可能需要执行一个全表清理（VACUUM FULL），这个操作可能会锁住整个表并完全重写数据，这有可能违背了进行在线 schema 变更的初衷。这时就需要「膨胀克星：pg_repack 与 pg_squeeze」出马了 —— 了解和准备这些工具是非常必要的。

结论

在 PostgreSQL 中变更列类型的操作可能看起来简单 —— 仅需执行一个 ALTER TABLE 命令。然而，对于涉及的每一个人来说，理解这一操作的复杂性至关重要。无论你是提出变更请求的软件开发者、负责审查此变更的人，还是在这类更改部署到生产环境后负责解决相关问题的技术人员，深刻理解这个过程都非常关键。此外，了解这种具体的变更还可以帮助你更容易地对其他可能代价高昂的操作有所预见。