もろもログ

ちょっとした技術メモを忘れないうちに書いていく

PostgreSQLでBULKUPDATE

2021-06-01

大量のデータを登録したり更新したり

異なる条件の大量のUPDATE文を実行する方法。

機会はあまりなそうだけど一応メモ（楽観排他の確認で使用するくらい？）

テスト用テーブルを作成

--テーブル作成
CREATE TABLE bulk_test
(
    id integer NOT NULL, 
    name text , 
    type integer,
    delete_flg boolean,
    last_update timestamp without time zone,
	CONSTRAINT bulk_test_pkey PRIMARY KEY (id)
)

こちらを参考にテストデータを作成。

INSERT INTO  bulk_test(id, name,type, delete_flg, last_update)
SELECT
  i, 
  format('テスト名_%s', i), --文字列に連番として付与する
  (SELECT (random() * 10000)::INTEGER % 5  + i - i),--0～4のランダムな値を設定
  false,
  clock_timestamp()
FROM
  generate_series(1, 100000) AS i
;


-- 100000のデータを作成
id	name	type	delete_flg	last_update
1	テスト名_1	2	false	2021-06-01 09:11:53.597824
2	テスト名_2	0	false	2021-06-01 09:11:53.599751
-- 省略
99999	テスト名_99999	3	false	2021-06-01 09:11:54.868251
100000	テスト名_100000	4	false	2021-06-01 09:11:54.868256

何も考えずに1件ずつ更新

--楽観排他確認用に更新日付を更新する
UPDATE bulk_test SET last_update = clock_timestamp() WHERE id = 1 AND  last_update = '2021-06-01 09:11:53.597824';
UPDATE bulk_test SET last_update = clock_timestamp() WHERE id = 2 AND  last_update = '2021-06-01 09:11:53.599751';
-- 省略
UPDATE bulk_test SET last_update = clock_timestamp() WHERE id = 99999 AND  last_update = '2021-06-01 09:11:54.868251';
UPDATE bulk_test SET last_update = clock_timestamp() WHERE id = 100000 AND  last_update = '2021-06-01 09:11:54.868256';

100000の更新に2分9秒程度かかった。

WITH句を使ってBULKUPDATEっぽくする

--WITH句にid 1～100000の内容を展開する
WITH
data( id, last_update )  AS (
VALUES
(1, '2021-06-01 09:11:53.597824'),
(2, '2021-06-01 09:11:53.599751'),
--省略
(99999, '2021-06-01 09:11:54.868251'),
(100000, '2021-06-01 09:11:54.868256')
)
UPDATE bulk_test
SET last_update = clock_timestamp()
FROM (SELECT id, last_update FROM data) data_table--UPDATE対象にWITH句を指定し、条件に使用する
WHERE
 bulk_test.id = data_table.id
AND  bulk_test.last_update = cast(data_table.last_update as timestamp);

このSQLだと更新時間が2831ミリ秒でかなり短縮された。

ただ、WITH句だとメモリの使用量増えてしまうかも、、、

もう1つのBULKUPDATEっぽい方法

WITH句を使わずにサブクエリに配列で展開し更新する方法もある。

この方法でも2762ミリ秒と更新時間は短い。

ただWITH句の方が可読性がよさそう。

UPDATE bulk_test SET
last_update = clock_timestamp()
FROM
(SELECT
unnest(array[1,/** 省略 **/ 99999, 100000]) as id,
unnest(array['2021-06-01 09:11:53.597824',/** 省略 **/ '2021-06-01 09:11:54.868251', '2021-06-01 09:11:54.868256']) as last_update
) as data_table
WHERE
bulk_test.id = data_table.id
AND  bulk_test.last_update = cast(data_table.last_update as timestamp);
目次