问题

短版

  • postgresql – Postgres 11.4部署在RDS上.
  • 是否有built-in或简单的方法来分割表中的行以进行批处理更新?
  • 一旦您有一个桶方案,如何在SQL中运行循环来处理每个桶,为服务器暂停一下以吸气?
  • 甚至有必要批量工作,还是我担心没有好的理由?

详细版本:

我们一直在收集数据一段时间,并使用TIMESTAMPTZ字段.我犯了一个错误,我应该使用时间戳.我们所做的是收集来自不同位置的大量数据,然后在将数据推送到Postgres之前自己计算UTC.据我所知,timestamp和TIMESTAMPTZ数据同样是8字节,TIMESTAMPTZ给你的是神奇的(和隐形的)AT TIME ZONE转换.这意味着,数据不是不同的,Postgres如何处理不同的数据.在我们的情况下,这意味着我们将数据推送到Postgres,然后再次将其拉出本地数据.我们的服务器没有一个时区域,因为什么可以不同的方式在本地运行.

好的,这是背景,我现在有1000万行,我正在研究更新.结构修改看起来很简单:

 -- 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
 

我必须删除并重新创建一些视图,但这只是运行一些备份脚本的问题.

我的问题是如何在不拖动服务器的情况下有效地执行更新?我想象一次5K行批处理东西.为了简单起见,假设我们的所有服务器都设置为US / Centra.当我们最初作为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.
 

这似乎有效(?),排除了处理Daylight储蓄时间的明显疏漏.我可以添加一个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中的循环中运行我的代码.如果有人应该指出如何,我会很感激.如果有一个简单的built-in chunking功能,我很想知道这一点.

我没有想到如何处理将在修改中抓住的传入数据的明确问题,而不是锁定整个表.我可能能能够做到这一点.

  最佳答案

如果你能负担得起,不要分批执行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