パフォーマンスを求めてDBに機能を寄せる戦略

Transcript

1. ユニークビジョン株式会社
 パフォーマンスを求めてDBに 機能を寄せる戦略
 CTO　青柳康平

2. Copyright ©Unique Vision Company, All Rights Reserved.
 2
 会社紹介
 会社 ユニークビジョン株式会社 渋谷区千駄ヶ谷。新宿高島屋の前、ゴルフ 5の上 2024年4月現在　社員69名、エンジニア34名 業務 SNSマーケティングの領域でキャンペーンの実施や SNSアカウントを管理するツールなどを提供 主にXのキャンペーンやLINEのキャンペーンがメイン スポンサー Rust.Tokyo2023　ゴールドスポンサー Vue Fes Japan 2023　プラチナスポンサー AtCoderユニークビジョンコンテスト (2023年5回) X 
 Marketing Partners Line販促・OMO部門の Technology Partner
3. Copyright ©Unique Vision Company, All Rights Reserved.
 3
 会社紹介
 「AtCoder 競技プログラマー就職企業人気ランキング 2023」 レーティング800～1599 １４位！ エンジニア、デザイナー、ディレクター募集中です！
4. Copyright ©Unique Vision Company, All Rights Reserved.
 4
 自己紹介
 経歴 東京電機大学大学院数理学専攻修了 沖ソフトウェア フューチャーコンサルティング 魔法のiらんど ユニークビジョン 自己紹介 ユニークビジョンでCTOやってます マネジメントと開発をやってます バックエンド開発とDB設計が得意です 趣味 散歩(浅草)、美術館(MOA、松本、青森)、とんかつ(丸五)
5. Copyright ©Unique Vision Company, All Rights Reserved.
 5
 DBに機能を寄せるとは
 一般的なWebアプリケーション Webなどのアプリケーションは Ruby, Rust, Python等のプログラミング言語で作成されます。 そこではDBからデータを取得し、計算して、 DBに保存し、戻り値を返します。 この場合通常のプログラミングではプログラミング言語側で計算や処理を行ます。 DBではデータの取得と保存だけを行います。
6. Copyright ©Unique Vision Company, All Rights Reserved.
 6
 DBに機能を寄せるとは
 ロジックをなるべくDBで処理するようにすること 主要となるビジネスロジックを DBで行います。 Webサーバーの役割は以下のようになります • フロントエンドからでデータをバリデーション • DBに入力データを渡して、戻り値を受け取る • フロントエンドに返すデータの構築 • その他、DBではできない外部APIとの処理
7. Copyright ©Unique Vision Company, All Rights Reserved.
 7
 DBに機能を寄せるとは
 何故DBに機能を寄せるのか？ ▪　WebサーバーとDBとの通信の往復を減らす 一般のWebアプリケーションの作りでは、何度も通信が往復します。 状況によっては1回のDBアクセスして複数件取得した後、それぞれの行ごとに DBアクセスをするN+1問題を起 こすこともあります。 ▪　SQLを直接扱うことで、細かなパフォーマンスの調整ができる ORマップなどの組み合わせでは、最適な SQLを得るかどうかわかりません。 DBに機能を寄せることで必然的に SQLを書くことになり、こまやかなパフォーマンスチューニングが可能になり ます。
8. Copyright ©Unique Vision Company, All Rights Reserved.
 8
 DBに機能を寄せるとは
 SQLだけでは難しい処理は手続き型言語 しかしSQLだけではできることが限られます。そこで DBの中で利用できる手続き型言語を利用します。 SQLに主に以下の機能を追加します。 • 代入 • 判定 • ループ このようなプログラムは DBの中に保存されるため ストアードプロシージャーと呼ばれます。 代入 判定 ループ
9. Copyright ©Unique Vision Company, All Rights Reserved.
 9
 PL/pgSQLとは
 PostgreSQLで利用できる手続き言語 • 標準インストール PL/pgSQL、PL/Tcl、PL/Perl、PL/Python • AWSのRDS PL/Rust、PL/v8 • コンパイルして追加 PL/Java、PL/Lua、PL/R、PL/sh, … すごく便利そうですが、ライブラリがコンパイル時に限定されます ここではPostgreSQLで利用できる手続き言語の紹介と主に PL/pgSQLについてお話します
10. Copyright ©Unique Vision Company, All Rights Reserved.
 10
 PL/pgSQLとは
 PL/pgSQLの特徴 他のプログラミング言語ベースに対して、 SQLとの親和性が高いです。 ほぼほぼSQLがそのまま書けて、代入、判定、ループと融合しています。 • 代入 w_value := p_value + 1; • 判定 IF p_uuid IS NOT NULL THEN END IF; • ループ FOR w_index IN 1..10 LOOP END LOOP; ▪　こんなループも書けます FOR w_row IN SELECT t1.uuid ,t1.name FROM users AS t1 LOOP IF w_row.name LIKE ‘%abc%’ THEN END IF END LOOP;
11. Copyright ©Unique Vision Company, All Rights Reserved.
 11
 PL/pgSQLとは
 2種類のユーザー定義処理 目的ごとに2種類のユーザー定義処理を作成できます。 • FUNCTION 主に値を返すことが目的です。特に複数行返すことでテーブルと同じ使い勝手になります • PROCEDURE 主にトランザクション制御が目的です。任意のタイミングでコミットすることができます。 SELECT t1.* FROM my_function() AS t1; IF w_name = ‘abc’ THEN COMMIT; END IF;
12. Copyright ©Unique Vision Company, All Rights Reserved.
 12
 PL/pgSQLとは
 任意の型の定義 また任意の型が定義できます。例えば 2つのテーブルを結合したような型を定義して FUNCTIONの戻り値に設 定できます。 DROP TYPE IF EXISTS type_project_get_select_users CASCADE; CREATE TYPE type_project_get_select_users AS ( uuid UUID, name TEXT company_name TEXT ); CREATE OR REPLACE FUNCTION project_get_select_users( ) RETURNS SETOF type_project_get_select_users AS $FUNCTION$ DECLARE BEGIN RETURN QUERY SELECT t1.uuid, t1.name, t2.name FROM users AS t1 INNER JOIN companies AS t2 ON (t1.company_uuid = t2.uuid); END; $FUNCTION$ LANGUAGE plpgsql;
13. Copyright ©Unique Vision Company, All Rights Reserved.
 13
 PL/pgSQLとは
 メリットとデメリットの整理 メリット • 手続き型言語を導入することにより、 SQLではできない複雑な処理が書ける • アプリケーションサーバーとの、通信が少なくできる • FUNCTIONの機能で疑似テーブルが作れるので共通化ができる。 VIEWよりも柔軟 • PROCEDUREの機能で細かいトランザクション制御ができる デメリット • 新しい言語の習得 →　慣れるにはある程度の時間が必要 • ループの使いすぎに注意 →　集合を扱った方がパフォーマンス向上 • エディターの補助が無い
14. Copyright ©Unique Vision Company, All Rights Reserved.
 14
 PL/pgSQLとは
 エディターの補助が無い フォーマッタがなければリンターもありません。 SQLだけならありますが、 PL/pgSQLとなると皆無です。そこで 2 つのツールを作成しました。 PostgreSQL Language ServerはVSCodeで使えるLanguage Serverになります。どこかで動いている PostgreSQLに渡して文法などをチェックします。 ERDToolはJSONで定義したERDからコードを生成するツールです。これでテーブル構造に近い PL/pgSQLの CRUDのコードを自動生成しています。
15. Copyright ©Unique Vision Company, All Rights Reserved.
 15
 具体例
 具体例 • 導出属性の計算 • データを削除したら、関連データも削除する • トップページで様々な種類のデータを取得する • 非同期にキューからデータを取得する
16. Copyright ©Unique Vision Company, All Rights Reserved.
 16
 具体例
 導出属性の計算 読み込みが多いシステムでは、 あらかじめ計算した結果を保存する導出属性 を使います。 例えば、図書館のシステムでユーザー一覧を出した時に貸し出している本の数も一緒に出したいとします。この とき1人ずつ貸出のテーブルを参照する必要があります。さらに貸出数でソートする場合などは全員分計算する 必要があります。 貸出と返却用にPL/pgSQLを作り、その中で導出属性の計算をします。 導出属性の詳細をしらなくても、この PL/pgSQLを呼べばいいので、バグが少なくなります。
17. Copyright ©Unique Vision Company, All Rights Reserved.
 17
 具体例
 データを削除したら関連データを削除する 例えばユーザーを削除した時に、関連するデータも削除したいです。 ユーザーを削除する PL/pgSQLに関連するテーブルも削除するようにできます。これを呼び出せばユーザーを 削除するプログラムが他のテーブルのことを意識しなくて済みます。 テーブルの設定にON DELETE CASCADEを付けても可能ですが、利用されているデータを削除しないなど、 細かい調整が必要な場合は PL/pgSQLの方が便利です。
18. Copyright ©Unique Vision Company, All Rights Reserved.
 18
 具体例
 トップページで様々な種類のデータを取得する トップページでは全体を俯瞰する情報を色々と表示したいので、複数のデータが必要になります。 PL/pgSQLでデータを返す時に、それぞれのデータを JSONにまとめることで違う種類のレコード扱うことができ ます。 参考：PL/pgSQLで違う種類のデータをまとめて取得する CREATE TYPE type_my_get_top_page AS ( user_json_array JSONB[] ,company_json_array JSONB[] ,book_json_array JSONB[] );
19. Copyright ©Unique Vision Company, All Rights Reserved.
 19
 具体例
 非同期にキューからデータを取得する 複数のスレッドからキューのようにデータを取ることがあります。このような場合同じレコードに集中してしまい、 効率よくデータ取得できません。 PL/pgSQLではデータが取るのをトランザクションごとに調停して対象が取得できるので効率化できます。 参考：PL/pgSQLでQueueに模したテーブルから非同期に取得する
20. Copyright ©Unique Vision Company, All Rights Reserved.
 20
 具体例
 One　more　thing・・・ PL/pgSQLのようなストアードプロシージャーは昔のネットワークが遅い時代では有効と言われることもありま す。 先ほど紹介した例の中でも数回の DBアクセスを軽減するだけのものもあり、確かにそれだけならわざわざ PL/pgSQLを使う必要な無いかもしれません。 では次に実際に業務で使われている例 を紹介します。
21. Copyright ©Unique Vision Company, All Rights Reserved.
 21
 具体例
 他のFUNCTIONをコール SELECT IF SELECT IF SELECT ELSE SELECT 他のFUNCTIONをコール IF INSERT ELSE UPDATE IF SELECT UPDATE INSERT SELECT IF 計算 RETURN IF RETURN FOR(SELECT) IF UPDATE ELSE UPDATE 実際の例　記事と一緒にメディアを登録する PL/pgSQL
22. Copyright ©Unique Vision Company, All Rights Reserved.
 22
 まとめ
 パフォーマンスを求めて DBに機能を寄せる戦略 • ユニークビジョンで扱うデータは複雑な要件が多いため、 DBで処理を完結させるのが良い。 • 導出属性や関連項目などは、 PL/pgSQLにまとめておくと利用する側が細かい仕様を意識する必要がな くなる。 • 自動生成やLanguage Serverなどを駆使して生産性を上げている
23. Copyright ©Unique Vision Company, All Rights Reserved.
 23
 おまけ
 手続き型言語間のパフォーマンス Build high-performance functions in Rust on Amazon RDS for PostgreSQL という資料でPL/Rust、PL/pgSQL、PL/v8のパフォーマンスを測定しています。 特定の条件下ではPL/Rustが4倍も早いようなので、今後利用していくことになると思います。
24. Copyright ©Unique Vision Company, All Rights Reserved.
 24
 ご清聴ありがとうございました