Amazon Aurora DSQL の PL/pgSQL で関数とプロシージャを試してみた

Amazon Aurora DSQL の PL/pgSQL で関数とプロシージャを試してみた

Amazon Aurora DSQL で PL/pgSQL がサポートされたので、IF/ELSE・ループ・変数代入で3つのテーブルを更新するプロシージャを作りました。同じ100件の処理を CALL 1回と個別の SQL 文300文で流し、所要時間の短縮を確認できました。
2026.11.20

はじめに

2026年11月19日、Amazon Aurora DSQL で PL/pgSQL がサポートされました。

https://aws.amazon.com/about-aws/whats-new/2026/11/aurora-dsql-plpgsql-support/

PL/pgSQL を使うと、関数、プロシージャ、無名ブロックを Aurora DSQL の中で作成・実行できます。条件分岐、ループ、変数代入を SQL と組み合わせて記述でき、ロジックをデータベース上で処理することで、クライアントから実行する SQL の回数を減らせます。

この記事では、クラスターを作って関数とプロシージャを実行し、同じ処理を個別の SQL 文で流した場合と所要時間も比べます。

検証環境

  • Aurora DSQL のリージョン: ap-northeast-1(単一リージョンのクラスター)
  • 実行環境: ローカル PC(Linux 7.2.12、aarch64)
  • psql: 18.6
  • AWS CLI: 2.41.2

クラスターの作成と接続

Aurora DSQL のクラスターは次のコマンドで作成しました。

aws dsql create-cluster --region ap-northeast-1 --tags Name=devio-plpgsql-verify --no-deletion-protection-enabled

次のコマンドで、状態が ACTIVE になったことを確認しました。

aws dsql get-cluster --region ap-northeast-1 --identifier <クラスター識別子>

接続用に、環境変数を設定する dsql-env.sh を次の内容で作り、source で読み込んでから psql で接続しました。

export DSQL_ENDPOINT=<クラスター識別子>.dsql.ap-northeast-1.on.aws
export PGPASSWORD=$(aws dsql generate-db-connect-admin-auth-token --hostname "$DSQL_ENDPOINT" --region ap-northeast-1)
export PGSSLMODE=verify-full
export PGSSLROOTCERT=system
export PGHOST=$DSQL_ENDPOINT PGUSER=admin PGDATABASE=postgres
source dsql-env.sh
psql

関数、プロシージャ、無名ブロックの作成と実行

CREATE FUNCTION、CREATE PROCEDURE、CALL、DO を実行しました。

CREATE TABLE my_table (id integer PRIMARY KEY, value text);
CREATE TABLE
CREATE FUNCTION increment(val integer) RETURNS integer
LANGUAGE plpgsql AS $$
BEGIN
  RETURN val + 1;
END;
$$;
CREATE FUNCTION
SELECT increment(41);
 increment 
-----------
        42
(1 行)

CREATE PROCEDURE insert_row(p_id integer, p_value text)
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO my_table VALUES (p_id, p_value);
END;
$$;
CREATE PROCEDURE
CALL insert_row(1, 'hello');
CALL
DO $$
BEGIN
  INSERT INTO my_table VALUES (2, 'from do block');
END;
$$;
DO
SELECT * FROM my_table ORDER BY id;
 id |     value     
----+---------------
  1 | hello
  2 | from do block
(2 行)

複数テーブルを操作するプロシージャ

簡易な注文処理を模したサンプル DB として、商品(items)、在庫(stock)、注文履歴(order_log)を用意しました。複数のテーブルにまたがる処理をプロシージャで実行できるか試しました。

プロシージャの定義

次の SQL を実行しました。

テーブルとプロシージャの定義
CREATE TABLE items (item_id integer PRIMARY KEY, name text, price integer);
CREATE TABLE stock (item_id integer PRIMARY KEY, qty integer);
CREATE TABLE order_log (
  order_id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  batch text,
  item_id integer,
  qty integer,
  amount integer
);

INSERT INTO items VALUES (1, 'pen', 100), (2, 'notebook', 250), (3, 'bag', 1200);
INSERT INTO stock VALUES (1, 1000000), (2, 1000000), (3, 1000000);

CREATE PROCEDURE process_orders(p_batch text, p_count integer)
LANGUAGE plpgsql AS $$
DECLARE
  v_item   integer;
  v_qty    integer;
  v_price  integer;
  v_amount integer;
BEGIN
  FOR i IN 1..p_count LOOP
    v_item := (i % 3) + 1;
    v_qty  := (i % 12) + 1;

    SELECT price INTO v_price FROM items WHERE item_id = v_item;

    IF v_qty >= 10 THEN
      v_amount := v_price * v_qty * 9 / 10;
    ELSE
      v_amount := v_price * v_qty;
    END IF;

    UPDATE stock SET qty = qty - v_qty WHERE item_id = v_item;
    INSERT INTO order_log (batch, item_id, qty, amount)
      VALUES (p_batch, v_item, v_qty, v_amount);
  END LOOP;
END;
$$;

実行結果

5件を処理する CALL を実行し、その後の stock と order_log の中身を確認しました。

CALL process_orders('smoke', 5);
CALL
SELECT * FROM stock ORDER BY item_id;
 item_id |  qty   
---------+--------
       1 | 999996
       2 | 999993
       3 | 999991
(3 行)

SELECT batch, item_id, qty, amount FROM order_log WHERE batch = 'smoke' ORDER BY item_id, qty;
 batch | item_id | qty | amount 
-------+---------+-----+--------
 smoke |       1 |   4 |    400
 smoke |       2 |   2 |    500
 smoke |       2 |   5 |   1250
 smoke |       3 |   3 |   3600
 smoke |       3 |   6 |   7200
(5 行)

プロシージャと個別の SQL 文の所要時間

同じ100件の処理を CALL 1回と個別の SQL 文で流し、所要時間を比べました。

比較する3つの方法

  • CALL 1回(process_orders に100件を渡す)
  • 個別の SQL 文(300文)を psql -f で流す(文ごとに自動コミット)
  • 同じ300文を psql --single-transaction -f で流す(全体で1トランザクション)

CALL 1回の SQL は次のとおりです。

CALL process_orders('call-r1', 100);

個別の文は、1件あたり SELECT、UPDATE、INSERT の3文です。どの方法も接続は1回です。経過時間は、psql プロセスの開始前と終了後に date コマンドで取得した時刻の差です。

個別の SQL 文の方式では、SELECT の結果を後続の文へ渡していません。そのため IF の判定は生成スクリプトで行い、価格は INSERT ... SELECT で items から取得しています。生成スクリプトは次のとおりです。

個別の SQL 文の生成スクリプト 03-gen-statements.sh
#!/usr/bin/env bash
# 使い方: 03-gen-statements.sh <batch> <count>
# process_orders と同じ処理を、個別の SQL 文(1 回あたり 3 文)で標準出力へ書く。
# IF(数量 10 以上は 1 割引)はここで判定し、割引後の式を INSERT へ埋め込む。
set -euo pipefail
batch=$1; count=$2
for ((i = 1; i <= count; i++)); do
  item=$(( i % 3 + 1 ))
  qty=$(( i % 12 + 1 ))
  if (( qty >= 10 )); then expr="price * $qty * 9 / 10"; else expr="price * $qty"; fi
  echo "SELECT price FROM items WHERE item_id = $item;"
  echo "UPDATE stock SET qty = qty - $qty WHERE item_id = $item;"
  echo "INSERT INTO order_log (batch, item_id, qty, amount) SELECT '$batch', $item, $qty, $expr FROM items WHERE item_id = $item;"
done

次はラウンド1で実行したコマンドです。ラウンド2〜5は、バッチ名の r1 を r2〜r5 に変えて実行しました。

# 個別の SQL 文のファイル(300文)を生成
./03-gen-statements.sh "auto-r1" 100 > "/tmp/auto-r1.sql"
./03-gen-statements.sh "single-r1" 100 > "/tmp/single-r1.sql"

# CALL 1回
psql -q -o /dev/null -v ON_ERROR_STOP=1 -c "CALL process_orders('call-r1', 100)"

# 個別の SQL 文(文ごとに自動コミット)
psql -q -o /dev/null -v ON_ERROR_STOP=1 -f "/tmp/auto-r1.sql"

# 個別の SQL 文(全体で1トランザクション)
psql -q -o /dev/null -v ON_ERROR_STOP=1 --single-transaction -f "/tmp/single-r1.sql"

計測結果

単位は秒です。各ラウンドで各方法を1回ずつ測りました。

ラウンド CALL(1文) psql -f(300文、文ごとに自動コミット) --single-transaction(300文、全体で1トランザクション)
1 0.574 6.646 5.402
2 0.251 6.651 3.495
3 0.219 6.447 3.717
4 0.244 6.447 3.589
5 0.219 4.918 4.938
中央値 0.244 6.447 3.717

プロシージャ内の COMMIT と ROLLBACK

ユーザーガイドでは、プロシージャをトップレベルで呼び出した場合に、COMMIT と ROLLBACK を含められると説明されています。COMMIT の前、COMMIT と ROLLBACK の間、ROLLBACK の後にそれぞれ INSERT を置いたプロシージャを、トップレベルの CALL で実行しました。

CREATE PROCEDURE txn_demo()
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO order_log (batch, item_id, qty, amount) VALUES ('txn-1-before-commit', 1, 1, 100);
  COMMIT;
  INSERT INTO order_log (batch, item_id, qty, amount) VALUES ('txn-2-before-rollback', 1, 1, 100);
  ROLLBACK;
  INSERT INTO order_log (batch, item_id, qty, amount) VALUES ('txn-3-after', 1, 1, 100);
END;
$$;
CREATE PROCEDURE
CALL txn_demo();
CALL
SELECT batch FROM order_log WHERE batch LIKE 'txn-%' ORDER BY batch;
        batch        
---------------------
 txn-1-before-commit
 txn-3-after
(2 行)

ROLLBACK の直前に INSERT した txn-2 の行だけが取り消され、プロシージャ内の COMMIT と ROLLBACK の動作を確認できました。

未サポートの機能

未サポートの機能は、ユーザーガイドに記載されています。

https://docs.aws.amazon.com/aurora-dsql/latest/userguide/working-with-plpgsql.html

まとめ

Aurora DSQL の PL/pgSQL で、関数およびプロシージャを作成・実行できることを確認できました。

今回の100件分の処理の実測では、プロシージャ化による処理時間の短縮を確認できました。

Aurora DSQL は、1回の SQL 実行にかかるネットワークのコストが高くなりがちです。複数のステップやトランザクション処理を伴うアプリケーションの性能改善策の一つとして、今回のアップデートをお試しください。

この記事をシェアする

AWSのお困り事はクラスメソッドへ

関連記事