postgresql - 分块表以将 timestamptz 批量更新为 timestamp

Question

精简版

• 在 RDS 上部署了 Postgres 11.4。
• 是否有内置或直接的方法来拆分表中的行以进行批量更新？
• 一旦有了存储桶方案，如何在 SQL 中运行一个循环来处理每个存储桶，并稍稍暂停一下让服务器喘口气？
• 是否有必要对工作进行批处理，或者我是在无缘无故地担心？

详细版:

我们收集数据已有一段时间了，并使用 timestamptz 字段。我犯了一个错误，我应该使用时间戳。我们所做的是从不同的位置收集大量数据，然后在将数据推送到 Postgres 之前自己计算 UTC。据我了解，无论哪种方式，timestamp 和 timestamptz 数据都是相同的 8 个字节，timestamptz 为您提供的是神奇(且不可见)的 AT TIME ZONE 转换。意思是，数据没有不同，只是 Postgres 处理不同数据的方式不同。在我们的例子中，这意味着我们通过将数据作为 UTC 推送到 Postgres 然后再将其拉出到本地而搞砸了。我们服务器的数据没有一个时区，这就是为什么我们在内部将其设置为 UTC，就像 Postgres 一样。为了使报告更简单，分析表通常有一个用于 local_dts 和 utc_dts 的冗余列。这样，我们就可以运行报告，比较不同时区设施的“周一早上 8 点到 11 点”。不同的设施有不同的时区，因此我们使用“本地”值，即他们的本地的此类查询。但是如果我们需要一个统一的时间线，那么我们就使用UTC。简单地说:同一个表中的行可能来自不同时区的来源。

好的，这就是背景，我现在有数千万行要更新。结构修改看起来很简单:

-- Change the data type, this is instantaneous.
ALTER TABLE assembly
   ALTER COLUMN created_dts 
   SET DATA TYPE timestamp;
   
-- Reset the default, it's probably not necessary, but the ::timestamptz is misleading/confusing here otherwise.
ALTER TABLE assembly
   ALTER COLUMN created_dts 
   SET DEFAULT '-infinity'::timestamp

我将不得不删除并重新创建一些 View ，但这只是运行一些备份脚本的问题。

我的问题是如何在不拖累服务器的情况下有效地进行更新？我正在想象一次按 5K 行等进行批处理。为了简单起见，假设我们所有的服务器都设置为美国/中部。当我们最初以 UTC 格式推送数据时，它又被 Postgres 转换了，所以现在数据因我们服务器时间和 UTC 之间的偏移而偏移。 (我认为。)如果是这样，最简单的更新可能如下所示:

SET TIME ZONE 'UTC'; -- Tell Postgres we're in UTC to line up the data with the UTC clock it's set to.
UPDATE analytic_scan 
  SET created_dts = created_dts at time zone 'US/Central' -- Tell Postgres to convert the value back to where we started.

这似乎可行(？)，忽略了处理夏令时的明显遗漏。我可以添加一个 WHERE 子句来处理它，但这并没有改变我的问题。现在的问题是，我有这样的记录数:

analytic_productivity           728,708
analytic_scan                 4,296,273
analytic_sterilizer_load        136,926
analytic_sterilizer_loadinv     327,700
record_changes_log           17,949,132

所以，不是很大，但也不是什么都没有。有没有一种方法可以在 SQL 中对数据进行明智的切片，以便

• 每行更新一次
• 没有一行被更新超过一次
• 一次更新的行数不多

所有的表都有一个 UUID ID PK 字段，一对夫妇有一个生成的身份列，就像从这个报告表中截取的一样:

CREATE TABLE IF NOT EXISTS "data"."analytic_productivity" (
    "id" uuid NOT NULL DEFAULT NULL,
    "pg_con_id" integer GENERATED BY DEFAULT AS IDENTITY UNIQUE,
    "data_file_id" uuid NOT NULL DEFAULT NULL,
    "start_utc" timestamptz NOT NULL DEFAULT '-infinity',
    "start_local" timestamptz NOT NULL DEFAULT '-infinity',
    "end_utc" timestamptz NOT NULL DEFAULT '-infinity',
    "end_local" timestamptz NOT NULL DEFAULT '-infinity')

我的一个想法是使用 UUID::text 的子字符串或散列来制作较小的批处理:

select * from analytic_sterilizer_loadinv 
  where left(id::text,1) = 'a'

这看起来很慢而且很糟糕。散列似乎好一点:

select abs(hashtext(id::text))  % 64,
       count(*)
       
  from analytic_sterilizer_loadinv 

桶的大小不是那么均匀，但可能已经足够了，如果需要，我可以增加桶的数量。不幸的是，我不知道如何使用存储桶在 SQL 中循环运行我的代码。如果有人应该指出如何，我将不胜感激。而且，如果有一个简单的内置分块功能，我很想知道。

我还没有想清楚如何处理将被修改捕获的传入数据的明确问题，而不是锁定整个表。我也许能做到。

Answer 1

如果您负担得起，请不要分批执行 UPDATE，而是一次性执行。主要缺点是这会使表膨胀，之后您应该在表上运行 VACUUM (FULL)，这会导致停机。

我会编写客户端代码来批量更新，例如在 bash 中:

typeset -i part=0

# PostgreSQL client time zone
export PGTZ=UTC

while [ $part -lt 64 ]
do
    psql <<-EOF
        UPDATE data.analytic_productivity
        SET created_dts = created_dts at time zone 'US/Central'
        WHERE abs(hashtext(id::text)) % 64 = '$part'
EOF
    psql -c "VACUUM data.analytic_productivity"

    part=part+1
done