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

by Kouhei Aoyagi

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Copyright ©Unique Vision Company, All Rights Reserved.  4  自己紹介  経歴東京電機大学大学院数理学専攻修了沖ソフトウェアフューチャーコンサルティング魔法のiらんどユニークビジョン自己紹介ユニークビジョンでCTOやってますマネジメントと開発をやってますバックエンド開発とDB設計が得意です趣味散歩(浅草)、美術館(MOA、松本、青森)、とんかつ(丸五)

Slide 5

Slide 5 text

Copyright ©Unique Vision Company, All Rights Reserved.  5  DBに機能を寄せるとは  一般的なWebアプリケーション Webなどのアプリケーションは Ruby, Rust, Python等のプログラミング言語で作成されます。そこではDBからデータを取得し、計算して、 DBに保存し、戻り値を返します。この場合通常のプログラミングではプログラミング言語側で計算や処理を行ます。 DBではデータの取得と保存だけを行います。

Slide 6

Slide 6 text

Copyright ©Unique Vision Company, All Rights Reserved.  6  DBに機能を寄せるとは  ロジックをなるべくDBで処理するようにすること主要となるビジネスロジックを DBで行います。 Webサーバーの役割は以下のようになります ● フロントエンドからでデータをバリデーション ● DBに入力データを渡して、戻り値を受け取る ● フロントエンドに返すデータの構築 ● その他、DBではできない外部APIとの処理

Slide 7

Slide 7 text

Copyright ©Unique Vision Company, All Rights Reserved.  7  DBに機能を寄せるとは  何故DBに機能を寄せるのか？ ■　WebサーバーとDBとの通信の往復を減らす一般のWebアプリケーションの作りでは、何度も通信が往復します。状況によっては1回のDBアクセスして複数件取得した後、それぞれの行ごとに DBアクセスをするN+1問題を起こすこともあります。 ■　SQLを直接扱うことで、細かなパフォーマンスの調整ができる ORマップなどの組み合わせでは、最適な SQLを得るかどうかわかりません。 DBに機能を寄せることで必然的に SQLを書くことになり、こまやかなパフォーマンスチューニングが可能になります。

Slide 8

Slide 8 text

Copyright ©Unique Vision Company, All Rights Reserved.  8  DBに機能を寄せるとは  SQLだけでは難しい処理は手続き型言語しかしSQLだけではできることが限られます。そこで DBの中で利用できる手続き型言語を利用します。 SQLに主に以下の機能を追加します。 ● 代入 ● 判定 ● ループこのようなプログラムは DBの中に保存されるためストアードプロシージャーと呼ばれます。代入判定ループ

Slide 9

Slide 9 text

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についてお話します

Slide 10

Slide 10 text

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;

Slide 11

Slide 11 text

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;

Slide 12

Slide 12 text

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;

Slide 13

Slide 13 text

Copyright ©Unique Vision Company, All Rights Reserved.  13  PL/pgSQLとは  メリットとデメリットの整理メリット ● 手続き型言語を導入することにより、 SQLではできない複雑な処理が書ける ● アプリケーションサーバーとの、通信が少なくできる ● FUNCTIONの機能で疑似テーブルが作れるので共通化ができる。 VIEWよりも柔軟 ● PROCEDUREの機能で細かいトランザクション制御ができるデメリット ● 新しい言語の習得 →　慣れるにはある程度の時間が必要 ● ループの使いすぎに注意 →　集合を扱った方がパフォーマンス向上 ● エディターの補助が無い

Slide 14

Slide 14 text

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のコードを自動生成しています。

Slide 15

Slide 15 text

Slide 16

Slide 16 text

Copyright ©Unique Vision Company, All Rights Reserved.  16  具体例  導出属性の計算読み込みが多いシステムでは、あらかじめ計算した結果を保存する導出属性を使います。例えば、図書館のシステムでユーザー一覧を出した時に貸し出している本の数も一緒に出したいとします。このとき1人ずつ貸出のテーブルを参照する必要があります。さらに貸出数でソートする場合などは全員分計算する必要があります。貸出と返却用にPL/pgSQLを作り、その中で導出属性の計算をします。導出属性の詳細をしらなくても、この PL/pgSQLを呼べばいいので、バグが少なくなります。

Slide 17

Slide 17 text

Copyright ©Unique Vision Company, All Rights Reserved.  17  具体例  データを削除したら関連データを削除する例えばユーザーを削除した時に、関連するデータも削除したいです。ユーザーを削除する PL/pgSQLに関連するテーブルも削除するようにできます。これを呼び出せばユーザーを削除するプログラムが他のテーブルのことを意識しなくて済みます。テーブルの設定にON DELETE CASCADEを付けても可能ですが、利用されているデータを削除しないなど、細かい調整が必要な場合は PL/pgSQLの方が便利です。

Slide 18

Slide 18 text

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[] );

Slide 19

Slide 19 text

Copyright ©Unique Vision Company, All Rights Reserved.  19  具体例  非同期にキューからデータを取得する複数のスレッドからキューのようにデータを取ることがあります。このような場合同じレコードに集中してしまい、効率よくデータ取得できません。 PL/pgSQLではデータが取るのをトランザクションごとに調停して対象が取得できるので効率化できます。参考：PL/pgSQLでQueueに模したテーブルから非同期に取得する

Slide 20

Slide 20 text

Copyright ©Unique Vision Company, All Rights Reserved.  20  具体例  One　more　thing・・・ PL/pgSQLのようなストアードプロシージャーは昔のネットワークが遅い時代では有効と言われることもあります。先ほど紹介した例の中でも数回の DBアクセスを軽減するだけのものもあり、確かにそれだけならわざわざ PL/pgSQLを使う必要な無いかもしれません。では次に実際に業務で使われている例を紹介します。

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

Copyright ©Unique Vision Company, All Rights Reserved.  22  まとめ  パフォーマンスを求めて DBに機能を寄せる戦略 ● ユニークビジョンで扱うデータは複雑な要件が多いため、 DBで処理を完結させるのが良い。 ● 導出属性や関連項目などは、 PL/pgSQLにまとめておくと利用する側が細かい仕様を意識する必要がなくなる。 ● 自動生成やLanguage Serverなどを駆使して生産性を上げている

Slide 23

Slide 23 text

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倍も早いようなので、今後利用していくことになると思います。