时间戳

首页 > 解决方案 > 在 postgres 中执行大规模 JSONB 替换的最有效方法

postgresql - 在 postgres 中执行大规模 JSONB 替换的最有效方法

问题描述

我有将近 200 万条记录需要对标识符数组中的一个值进行简单的字符串替换，比如这个部分记录

    {
      "id": "a851375d-f6a0-4fba-ba69-c584901de73c",
      "source": "MARC",
      "title": "Voice Content and Usability [electronic resource] / So, Preston.",
      "identifiers": [
        {
          "value": "9781952616020",
          "identifierTypeId": "fcca2643-406a-482a-b760-7a7f8aec640e"
        },
        {
          "value": "(CaSebORM)9781098128852",
          "identifierTypeId": "7e591197-f335-4afb-bc6d-a6d76ca3bace"
        },
        {
          "value": "5429852",
          "identifierTypeId": "d5989db8-ddc3-4f06-a465-73e99ec262d3"
        },
        {
          "value": "ocm00000001saf9781098128852",
          "identifierTypeId": "7e591197-f335-4afb-bc6d-a6d76ca3bace"
        }
]
}

我需要从最终值中检测并删除“ocm0000000”，并且我正在寻找最有效的方法。当我试图简单地将 jsonb 转换为字符串时，请进行简单的字符串替换，例如

update my.table
set jsonb = REPLACE(jsonb::text, 'ocm0000000', '')::jsonb

它使 CPU 冲破了屋顶，并为我们的 RDS 集群占用了所有连接，从而干扰了其他操作，因此有必要终止该进程。

当有这么多行受到影响时，进行这种替换的最佳方法是什么？

标签： postgresqljsonb

解决方案

您当前的 UPDATE 将重写每一行，即使是不需要更改的行。您应该添加一个 WHERE 子句来防止这种情况。如果您想将更新分成多个批次，这一点尤其重要。

set jsonb = REPLACE(jsonb::text, 'ocm0000000', '')::jsonb
where jsonb::text like '%ocm0000000%'

完成此操作的最简单方法是声明一个维护窗口并直接执行，或者在非工作时间执行。对我来说，用 SSD 驱动器在小型笔记本电脑上更新 200 万份示例记录只用了不到 2 分钟。你说这只是部分，但我不知道你的真实记录会大多少。也许您可以克隆我们的服务器并使用它来测试需要多长时间。您还可以临时预置更多 IOPS 以使其运行得更快。

如果你想批量运行它，你可以添加一个 where 子句来限制将被更新的 ctid 的范围：

set jsonb = REPLACE(jsonb::text, 'ocm0000000', '')::jsonb 
where jsonb::text like '%ocm0000000%' 
and ctid between '(0,0)' and '(9999,99)'; --first ~80MB.

然后您将等待系统喘口气，然后重复 CTID 窗口向下滑动。重复直到整个桌子都被覆盖。您应该只需要直到 ctid 中的第一个数字与表中的当前页面一样大。然后在没有 ctid 条件的情况下再运行一次，以确保没有任何遗漏。

推荐阅读