問題

短版

  • 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