如何在不影响现有用户的情况下更改列的数据类型 PostgreSQL

Source: Dev.to

想象一下，你正在构建一款约会应用。你已经达成了一个重要里程碑：一百万用户。恭喜！

在早期，你保持了实现的简洁。为了记录用户是否有孩子，你使用了一个名为 kids 的单列字符串，例如：

const updatePreferencesSchema = Joi.object({
  kids: Joi.string().valid(
    "Yes- they live with me",
    "Yes- they don't live with me",
    "no"
  ).optional()
});

后来，产品团队希望获取更丰富的数据：他们需要知道用户是否有孩子、这些孩子是否住在家里，以及用户对孩子的意愿。新的 schema 如下：

const updatePreferencesSchema = Joi.object({
  kids: Joi.object({
    hasKids: Joi.boolean().required(),
    liveAtHome: Joi.boolean().required(),
    wantsKids: Joi.string().valid("Yes", "No", "Not Decided")
  }).optional()
});

直接执行 ALTER TABLE 并部署新代码会导致仍然保存旧字符串值的现有行出错。应用在尝试从仍存 "no" 的行读取 user.kids.hasKids 时会抛出 TypeError。

为了解决 中间状态，你可以使用 扩展与收缩模式（Expand and Contract Pattern）。

第 1 阶段：扩展（安全桥）

添加一个新列来保存新格式的数据，同时保持旧列不变。

-- Step 1: Add the new column (defaulting to an empty object)
ALTER TABLE users ADD COLUMN kids_v2 JSONB DEFAULT '{}';

双写逻辑

更新应用程序，使其同时写入两个列。这确保新数据兼容仍然读取 kids 的旧代码，以及读取 kids_v2 的新代码。

const oldValue = req.body.kids; // 例如，“yes- they live with me.”

const updateData = {
  kids: oldValue,
  kids_v2: {
    hasKids: oldValue.toLowerCase().includes("yes"),
    liveAtHome: oldValue === "yes- they live with me",
    wantsKids: "Not Decided" // 新需求的默认值
  }
};

第2阶段：迁移（回填过去）

现在你有一百万行的 kids_v2 仅为 {}。一次性更新所有行会锁表，因此请分批处理数据。

安全批量更新脚本（Node.js）

const { Pool } = require('pg');
const pool = new Pool();

async function backfillKidsData() {
  const batchSize = 5000;
  let hasMore = true;

  while (hasMore) {
    const res = await pool.query(`
      UPDATE users
      SET kids_v2 = jsonb_build_object(
        'hasKids', CASE WHEN kids ILIKE 'yes%' THEN true ELSE false END,
        'liveAtHome', CASE WHEN kids = 'yes- they live with me' THEN true ELSE false END,
        'wantsKids', 'Not Decided'
      )
      WHERE id IN (
        SELECT id FROM users
        WHERE kids_v2 = '{}' AND kids IS NOT NULL
        LIMIT ${batchSize}
      )
      RETURNING id;
    `);

    if (res.rowCount === 0) hasMore = false;
    console.log(`Migrated ${res.rowCount} rows...`);

    // Give the DB and event loop a short breather
    await new Promise(r => setTimeout(r, 100));
  }
}

运行此脚本，直到它报告没有更多行需要更新为止。

第 3 阶段：合同（干净切割）

一旦每一行在 kids_v2 中都包含有效对象，你就实现了 schema parity。

1. 切换 API – 更新代码，仅从 kids_v2 读取。
2. 监控 – 在接下来的几天里查看日志，留意是否出现 “undefined property” 错误。
3. 删除 – 删除旧列并移除双写逻辑。

-- Final cleanup
ALTER TABLE users DROP COLUMN kids;

结论

Expand and Contract 模式让您能够在不导致停机或数据完整性问题的情况下演进列的数据类型。通过扩展模式、以批次安全迁移数据，然后收缩（清理），您可以避免会导致运行时错误的空隙。