Closed1

PL/pgSQLで負荷テストを楽にする

ゆゆじゃんゆゆじゃん

モチベ

  • 10万人分の会員データを作りたい
  • 手作業はつらい
  • スクリプトでやりたい
  • INSERT文を10万行流すのはDBが遅くてやってらんない
  • てか10万行とか編集しようとするとSQLエディターが耐えれない

そんなあなたにPL/pgSQL

  • 変数、FOR LOOP、IFと通常のクエリを組み合わせて書く手続き言語
  • INSERT文をループ処理で発行できる
  • 1度実行すれば解釈と実行をまとめてDBでやってくれるのでトランザクション管理、クライアントとの通信などのオーバーヘッドが減る（たぶん）
  • 今回はPostgreSQL 17で実験したけど他のRDBMSでも同じことができる

例

テーブル定義

-- 会員テーブルを作成
CREATE TABLE member (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    date_of_birth DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

PL/pgSQL

--テーブルをクリアする
TRUNCATE member;
/
DO $$
DECLARE 
    i BIGINT;
    i_limit BIGINT :=10000;
BEGIN
    FOR i in 1..i_limit LOOP
        INSERT INTO member (id, name, email, date_of_birth) VALUES
        (i, 'John'|| i || 'Doe', 'john'||i||'@example.com', '1990-01-01'::DATE +(i||' month')::interval);
    END LOOP;
END;
$$

負荷を切り替えつつテスト

こんなクエリを投げてみる。

EXPLAIN ANALYZE
SELECT
    json_agg(member) 
FROM
    member 
GROUP BY
    member.id;

レコード数に応じてコストが変動するのが分かる。
ただ、テーブル全体をプライマリキーで集約しているのでデータ量が増えても性能の劣化は少ない。

レコード数 cost actual time
1 54.63 0.031
1000 71.10 4.697
10000 585.57 19.279
1000000 61029.68 1150.685

JOINを含むクエリだと、レコード数に応じて戦略が変化するのが観察できてより面白いはず。

注意点

  • タイムアウト注意

    • DBの性能次第だけど、SQLクライアントのタイムアウトの設定時間を伸ばしたほうが良い

  • データ量注意

    • テーブル定義によってはとんでもない量のデータをサクッと生成するのでDBの容量をかなり圧迫する可能性がある
    • 業務で使うなら周りに迷惑かからないように事前確認
このスクラップは2025/01/23にクローズされました