MySQL 8.0 から PostgreSQL 16 への移行と RLS 導入までの道のりと学び

Slide 1

Slide 1 text

MySQL 8.0 から PostgreSQL 16 への移行と RLS 導入までの道のりと学び  baseballyama / 山下裕一朗 

Slide 2

Slide 2 text

目次  ● 自己紹介  ● なぜ MySQL から PostgreSQL に移行したのか  ● MySQL から PostgreSQL への移行方法  ● Row Level Security の導入方法  ● そしてその先へ... 

Slide 3

Slide 3 text

自己紹介 

Slide 4

Slide 4 text

自己紹介 (baseballyama)  - 株式会社フライル  - ソフトウェアエンジニア  - Svelteコアチームメンバー 

Slide 5

Slide 5 text

前提 (弊社・弊社サービス概要)  ● 弊社概要  ○ プレシリーズAのスタートアップ  ● 弊社サービス概要  ○ BtoB SaaS  ○ 顧客の顧客の情報、顧客の戦略情報を扱うサービス  ○ データ分析機能あり  ● データ量  ○ 2024年11月時点でテナント数は200超  ○ 顧客の顧客の情報は1テナントあたり最大10万件程度  ■ 但し今後最大1,000万件程度まで増加予定 

Slide 6

Slide 6 text

前提 (当時の弊社サービス Flyle の技術スタック)  フロントエンド  (SPA)  バックエンド  Rest API で通信     認証トークンとして   JWTを使用  Hibernateを使用して   (= ORM経由で)  データベース接続     Prepared Statement   を一部使用    リードレプリカはなし   データベース 

Slide 7

Slide 7 text

なぜ  MySQL から PostgreSQL に移行したのか 

Slide 8

Slide 8 text

なぜ MySQL から PostgreSQL に移行したのか  ● 弊社提供サービス Flyle の特性  ○ BtoB SaaS  ○ お客様が抱えるVoCや戦略情報を扱うドメイン   ○ 複数のアプリケーションがデータベースに接続する   👉 (要件) お客様のデータ混在はあってはならない   👉 (要件) アプリケーションを超えて透過的にデータ混在を防げる仕組みが必要   ● 移行前から実施していたこと  ○ アプリケーション (Kotlin / Hibernate) で、SQL に対してテナントID を指定   ■ 一応静的検査でチェックもしていた   ○ ❌ 但し一部の複雑なクエリは静的検査不能   ○ ❌ JOINした子テーブルに対する検査はできていなかった   👉 (課題) あってはならない事態を防ぐための仕組みが脆弱だった  

Slide 9

Slide 9 text

なぜ MySQL から PostgreSQL に移行したのか  👉 (要件) お客様のデータ混在はあってはならない   👉 (要件) アプリケーションを超えて透過的にデータ混在を防げる仕組みが必要   👉 (課題) あってはならない事態を防ぐための仕組みが脆弱だった     これらを解決するするには  データベースレイヤーでデータの混在を防ぐ仕組みが必要だった。  👉 「テナント分離」が必要  

Slide 10

Slide 10 text

テナント分離の主な手法  サイロモデル  (全て分離)  プールモデル1  (ストレージは物理分離)   DB  APサーバー  テナント2  DB  APサーバー  テナント1  DB  テナント2  テナント1  APサーバー  プールモデル2  (ストレージも共有)   DB  テナント2  テナント1  APサーバー  DB  データ混在  👍リスクゼロ  👎設定間違いによる混在リスクあり   👎混在しやすいが軽減策あり   うるさい隣人問題   👍リスクゼロ  👍リスクゼロ  👎顕在化しやすいが対処法あり   サーバー費用  👎とても高い  👎高い  👍安い  運用コスト・自動化   👎とても大変  👎特にマイグレーションが大変   👍楽  ❌スタートアップの弊社には   費用が高すぎて非現実的   ❌費用・運用コストがかかる割に   結局混在リスクがある   ✅混在リスクを最小化する前提で   採用 

Slide 11

Slide 11 text

テナント分離の主な手法  スキーマ分離  テーブル分離  APサーバー  APサーバー  Row Level Security  APサーバー  データ混在  👎制御ミスによる混在可能性あり   👎制御ミスによる混在可能性あり   👎制御ミスによる混在可能性あり   マイグレーション   👎時間がかかる (テナント数200超)   👎時間がかかる (テナント数200超)   👍速い  性能  🤔劣化しづらいがうるさい隣人問題あり   🤔劣化しづらいがうるさい隣人問題あり   👎劣化しやすいがデータ量次第   障害調査  👎テナントごとに調査する時間がかかる   👎テナントごとに調査する時間がかかる   👍テナントを跨いで調査できる   テナント1  スキーマ    foo_table  テナント2  スキーマ    foo_table  共通スキーマ    tenan1_foo_table  tenan2_foo_table  共通スキーマ    foo_table  ❌スタートアップの弊社には   許容できない運用コストだった   ❌スタートアップの弊社には   許容できない運用コストだった   ✅混在リスクを最小化する前提で   採用 

Slide 12

Slide 12 text

🎉  ということで  Row Level Security の導入を  決定しました  🎉 

Slide 13

Slide 13 text

導入にあたり解決する必要があること  1. どうやって MySQL から PostgreSQL に移行するか  2. データの混在をどう防ぐか  3. うるさい隣人問題をどう防ぐか 

Slide 14

Slide 14 text

MySQL から PostgreSQL への移行方法 

Slide 15

Slide 15 text

前提: 移行方針  Flyle は BtoB SaaS であり、土日のアクセスは極端に少ないことから、  土曜日にメンテインして移行することで移行プロセスを簡単にすることにした。  また、日曜日を予備日とすることにした。 

Slide 16

Slide 16 text

MySQL から PostgreSQL に移行するために必要なこと  1. 事前調査  2. スキーマの移行手順確立  3. データの移行手順確立  4. アプリケーションの移行  5. 移行手順書の作成  6. 移行実行 

Slide 17

Slide 17 text

事前調査  1. PostgreSQL に移行することによるデメリットは何か  なぜUber EngineeringはPostgresからMySQLに切り替えたのか (by Uber Engineering)    2. そもそも MySQL で RLS を実現できないのか  satori という会社がサービス提供していたが年間 $70,000 だったので深掘りせず     3. AWS Startup Loft Tokyo の Ask an Expert にて相談  AWS Database Migration Service (AWS DMS) を教えてもらった     4. 有識者の記事を収集 (以下は一例)  MySQLからPostgreSQLに移行する際のTips (by Soudai)  MySQL/Postgres におけるトランザクション分離レベルと発生するアノマリーを整理する (by mpyw) 

Slide 18

Slide 18 text

事前調査でわかったこと  1. PostgreSQL に移行することによるデメリットは何か  👉 現時点の Flyle にとっては RLSのメリット > MySQLのメリットと判断     2. そもそも MySQL で RLS を実現できないのか  👉 ほぼ無理と判断    3. AWS Startup Loft Tokyo の Ask an Expert にて相談  👉 AWS DMS を使用してデータの移行はできる     4. 有識者の記事を収集 (以下は一例)  👉 型の違いに注意 (timestamp 型の精度 / 符号なし整数 / TEXT型など)   👉 トランザクション分離レベルの違いに注意   👉 MySQL の COLLATE utf8mb4_bin は、 PostgreSQL では C に相当する   👉 関数に違いがある (JSON関数 / IF / CASE など)  

Slide 19

Slide 19 text

スキーマの移行手順確立  ● 方針  AWS DMS でスキーマ移行するとスキーマを正確に把握しづらいため、  (また、見落としている AWS DMS の仕様で混乱する可能性を避けるため)  手動でスキーマの移行を実施する  ● 手順  1. MySQL Workbench でスキーマをエクスポート  2. 独自プログラムで PostgreSQL 用のスキーマ生成文を作成  3. レビューによって正しさを確認 

Slide 20

Slide 20 text

データの移行手順確立  ● 方針  AWS DMS でデータを移行・検証する (事前に実行時間を計測)   独自ツールでも検証してクロスチェックする (事前に実行時間を計測)   ● 手順  1. AWS DMS で移行・検証処理を実行   2. 検証エラーがないかをチェック   　 → 絵文字 / CHAR0 を含む行で意図しない検証エラーが出たので無視する必要があった   　 → NOT NULL などの制約はデータ移行後に設定する必要があることがわかった   3. 独自のバリデーションツールでもチェックして、データ移行ミスがないことをクロスチェック   　 → MySQL と PostgreSQL にそれぞれクエリを実行してデータ差分がないかを確認  

Slide 21

Slide 21 text

アプリケーションの移行  ● Criteria Builder / Native SQL の修正   ○ 特に DELETE 文の書き方を修正   ■ DELETE foo FROM foo という書き方を DELETE FROM foo に変更  ■ INNER JOIN をサブクエリに変更   ○ 非互換関数の修正  ■ JSON_CONTAINS / JSON_SEARCH / JSON_REMOVE などなど...   ● 落ちているテストの修正   ● レビュー  ○ 人手によるレビュー  ○ GPTによる変換結果との比較によるレビュー   ● 動作確認  ○ Flyle の全機能に対する手動テストを複数回実施  

Slide 22

Slide 22 text

移行手順書の作成  ● 前日のタスク (概要)   ○ 検証環境を用いた移行リハーサル   ○ PostgreSQL インスタンスを作成   ○ PostgreSQL インスタンスのデータベースとユーザーを作成   ○ パラメータストアにDBユーザーとDBパスワードを保存   ○ 上記のDBユーザーとDBパスワードで PostgreSQL にログインできることを確認   ○ MySQL と PostgreSQL 共にローカルからポートフォワードできることを確認する   ○ AWS DMS を実行するために必要なリソースを作成     ※実際には詳細な各手順の想定時間を含む手順ベースの細かな手順書を作成しました。  

Slide 23

Slide 23 text

移行手順書の作成  ● 当日のタスク (概要)   ○ メンテインする / サービスURLに対して basic 認証を追加する   ○ 稼働しているサービスを停止   ○ MySQL のスナップショットを作成   ○ MySQL を Security Group から切り離す   ○ PostgreSQL にテーブルを作成する   ○ AWS DMS を使用してデータを移行   ○ AWS DMS を使用してデータバリデーションを実行   ○ 独自ツールによるバリデーションを実行   ○ アプリケーションコードをデプロイ   ○ 性能を含む動作確認して問題がなければメンテアウト (問題があれば MySQL に切り戻し)    

Slide 24

Slide 24 text

(振り返り) 移行作業でよかったこと  ● プロセス全体に関して  ○ 1日メンテインして移行することでプロセス自体がシンプルになった   ○ 切り戻しができる状態で移行作業に望めたので安心して取り組めた   ● データ移行に関して  ○ 事前にデータ移行を何度も試したので実際の移行もスムーズだった   ○ 複数の手法でデータ検証したことでデータ移行ミスがないことを確信できた   ● アプリケーションに関して  ○ 複数の手法によるレビューと動作確認によって品質に自信を持てた   ○ 弊社の主なテストがコントローラー層に対する結合テストだったため、   比較的少ない修正でテストを実行することができ、品質に自信を持てた  

Slide 25

Slide 25 text

(振り返り) 移行作業で起きた想定外だったこと  ● DMS が想定よりも3時間ほど長くかかった。(想定は2時間)  ○ 原因: 検証時よりもデータが増えたことが影響した   → ベクトルデータが一気に増えている時期だったためそれが要因であると認識   ● 独自ツールによるバリデーションが想定よりも時間がかかった  ○ 原因: 同上    但し移行時間に余裕を持っていたため、メンテ時間の中で対応できた 

Slide 26

Slide 26 text

🎉  無事に PostgreSQL に移行できました  🎉 

Slide 27

Slide 27 text

Row Level Security の導入方法 

Slide 28

Slide 28 text

Row Level Security を実現する主な方法  1. ユーザーとロールを使用する方法  CREATE POLICY account_managers ON accounts TO managers USING (manager = CURRENT_USER);  accounts テーブルは managers ロールを持ち、かつ accounts テーブルの manager カラムの値が現在のユーザー名と一致している行のみアクセスできる。  2. ユーザーを使用する方法  CREATE POLICY user_policy ON users USING (user_name = CURRENT_USER);  users テーブルは、user_name が現在のユーザー名と一致している行のみアクセスできる。  3. セッション変数を使用する方法  CREATE POLICY user_policy ON users USING (tenant_id = current_setting('app.tenant_id'));  users テーブルはtenant_id が現在のセッション変数 app.tenant_id と一致している行のみアクセスできる。 

Slide 29

Slide 29 text

Row Level Security を実現する主な方法  1. (不採用) ユーザーとロールを使用する方法   🤔 運用 : ユーザーとロールを沢山作成する必要があり煩雑だがユーザー単位でメトリクスを監視できる   👎 コネクション : コネクションプールの管理が煩雑になる可能性がある   👎 設計 : ユーザーとロールの2箇所で制御するので複雑になる可能性がある   2. (不採用) ユーザーを使用する方法   🤔 運用 : ユーザーを沢山作成する必要があり煩雑だがユーザー単位でメトリクスを監視できる   👎 コネクション : コネクションプールの管理が煩雑になる可能性がある   👍 設計 : ユーザーで RLS を実現すれば良いのでシンプル   3. (採用) セッション変数を使用する方法   🤔 運用 : ユーザーは1つで良いので管理が楽だがテナントごとのメトリクスは見れない   👍 コネクション : コネクションプールの管理はシンプル (これまで通り)   👍 設計 : セッション変数で RLS を実現すれば良いのでシンプル  

Slide 30

Slide 30 text

Row Level Security を設定する事前準備  ● 全てのテーブルに tenant_id を追加する  ○ 外部キー制約も設定する   ● 性能劣化が起きないように、全ての INDEX に tenant_id を追加する  ○ Row Level Security が有効なクエリは、   強制的に WHERE 句に tenant_id = ‘foo’ が追加されるイメージ    

Slide 31

Slide 31 text

Row Level Security を設定する  ● USING を用いて読み取り用の RLS を設定する   ● WITH CHECK を用いて書き込み用の RLS を設定する   ● テナント跨ぎの処理用に特別なセッション変数を用意する   ● FORCE ROW LEVEL SECURITY を設定しセッション変数が未設定の場合はエラーにする   ○ 設定漏れの場合にエラーにすることで設定漏れによるデータ混在を防ぐ     CREATE POLICY user_policy ON users  USING (tenant_id = current_setting('app.tenant_id') OR current_setting('app.admin') = 'true')  WITH CHECK (tenant_id = current_setting('app.tenant_id') OR current_setting('app.admin') = 'true');    ALTER TABLE users ENABLE ROW LEVEL SECURITY;  ALTER TABLE users FORCE ROW LEVEL SECURITY; 

Slide 32

Slide 32 text

Row Level Security を設定する  ● セッション変数の設定方法   local を使用してセッション変数を使用することで現在のトランザクションのみに適用する   SET local app.tenant_id = 'xxx';  SET local app.admin = 'true’;   

Slide 33

Slide 33 text

Row Level Security をアプリケーションから利用する  Tomcat  Servlet API  Spring Boot  HTTP  リクエスト  リクエスト  ごとに  別スレッド  (バーチャルスレッド)  認証  @Controller  (コントローラー)  @Transactional  (トランザクション境界)   @Repository  (DBアクセス)  テナント情報を ThreadLocal に保存   トランザクション境界にて   AOP を用いてセッション変数を設定   DBアクセス時にトランザクション未設定の場合自動的にトランザクションを開始して   セッション変数を設定 (AOPを使用)  

Slide 34

Slide 34 text

Row Level Security をアプリケーションから利用する  ● リクエストを跨いでスレッドが共有されることはない   ○ Spring Boot で仮想スレッドを使用する場合、スレッドプールは使用しない (スレッドは使いまわされない)  ○ 認証レイヤーでテナントIDを ThreadLocal に設定し、以降は ThreadLocal の値を信用する  ● DBコネクションプールは使いまわされる   ○ SET app.tenant_id = 'xxx'; のように local なしでセッション変数を設定すると意図しないテナントIDがセッション変数に使用される可能性がある。よって、必ず local を使用する。  ● コネクションプールはトランザクション単位で使用する   ○ 同じリクエスト (スレッド) であってもトランザクションごとに別のコネクションが使われる (場合がある)  よって、トランザクション境界でセッション変数を指定する  ● トランザクションを貼らないで実行されるクエリに対する対処   ○ クエリ発行直前にトランザクション有無をチェックし、  トランザクションが未指定の場合はトランザクションの開始とセッション変数の設定を実施する  ● セッション変数の指定は最小限にする   ○ スレッド / トランザクションごとにセッション変数を設定したかを管理して複数回の設定を回避する  ● Kotlin coroutines は使用しない   ○ Kotlin coroutines は複数コルーチンが同一スレッドで動作するので ThreadLocal を使えない 

Slide 35

Slide 35 text

Spring Boot  別スレッド  リクエスト内などで更に別スレッドを起動する場合  メインスレッド  @Transactional  (トランザクション境界)   @Repository  (DBアクセス)  スレッド起動時に  メインスレッドのテナントIDを別スレッドに引き継ぐ  

Slide 36

Slide 36 text

リクエスト内などで更に別スレッドを起動する場合  別スレッド起動コードイメージ (Kotlin)  ※ イメージを伝えるための最小コードです。   val tenantIdByThread = ThreadLocal()   fun async(process: () -> Unit) {   val currentTenantId = tenantIdByThread.get()  Thread.ofVirtual().start {  tenantIdByThread.set(currentTenantId)   process()  }  } 

Slide 37

Slide 37 text

Row Level Security の導入フロー  1テーブルにRLSを導入する   問題ないか  1機能テーブル群にRLSを導入する   問題ないか  数回に分けて  全テーブルにRLSを導入   RLS導入に関する設計をする   問題なし  問題なし  問題あり  問題あり 

Slide 38

Slide 38 text

Row Level Security 導入漏れの検査  「テーブル一覧取得SQL」と「RLS の設定取得SQL」を用いて、  以下の観点でチェックすることでRLSの導入漏れがないかを検査します    ● RLS が設定されていないテーブルが存在しないか  ● RLS の設定が想定通りか 

Slide 39

Slide 39 text

Row Level Security 導入漏れの検査  SELECT  c.relname AS table_name  FROM pg_class c  JOIN pg_namespace n ON n.oid = c.relnamespace   WHERE  c.relkind = 'r'  AND n.nspname = 'public';  SELECT  c.relname AS table_name,  p.policyname AS policy_name,  p.cmd AS command,  p.qual AS using_condition,   p.with_check AS with_check_condition,   c.relforcerowsecurity AS is_rls_forced  FROM pg_policies p  JOIN pg_class c ON p.tablename = c.relname   WHERE c.relrowsecurity = true;  テーブル一覧取得SQL   RLS の設定取得SQL  

Slide 40

Slide 40 text

Row Level Security 導入漏れの検査  テーブル一覧取得SQL実行結果   RLS の設定取得SQL実行結果  

Slide 41

Slide 41 text

🎉  無事に Row Level Security を  導入できました  🎉 

Slide 42

Slide 42 text

そしてその先へ...    (発表の本題ではないので軽く触れます) 

Slide 43

Slide 43 text

テナント分離の主な手法  サイロモデル  (全て分離)  プールモデル1  (ストレージは物理分離)   DB  APサーバー  テナント2  DB  APサーバー  テナント1  DB  テナント2  テナント1  APサーバー  プールモデル2  (ストレージも共有)   DB  テナント2  テナント1  APサーバー  DB  データ混在  👍リスクゼロ  👎設定間違いによる混在リスクあり   👎混在しやすいが軽減策あり   うるさい隣人問題   👍リスクゼロ  👍リスクゼロ  👎顕在化しやすいが対処法あり   サーバー費用  👎とても高い  👎高い  👍安い  運用コスト・自動化   👎とても大変  👎特にマイグレーションが大変   👍楽  🎉 Row Level Security の導入によって解決   ❌ 未解決 

Slide 44

Slide 44 text

テナント分離の主な手法  スキーマ分離  テーブル分離  APサーバー  APサーバー  Row Level Security  APサーバー  データ混在  👎制御ミスによる混在可能性あり   👎制御ミスによる混在可能性あり   👎制御ミスによる混在可能性あり   マイグレーション   👎時間がかかる (テナント数200超)   👎時間がかかる (テナント数200超)   👍速い  性能  🤔劣化しづらいがうるさい隣人問題あり   🤔劣化しづらいがうるさい隣人問題あり   👎劣化しやすいがデータ量次第   障害調査  👎テナントごとに調査する時間がかかる   👎テナントごとに調査する時間がかかる   👍テナントを跨いで調査できる   テナント1  スキーマ    foo_table  テナント2  スキーマ    foo_table  共通スキーマ    tenan1_foo_table  tenan2_foo_table  共通スキーマ    foo_table  🎉 FORCE ROW LEVEL SECURITY によって解決   🤔 未解決だが現時点では実際の問題になっていない  

Slide 45

Slide 45 text

うるさい隣人問題を解決したい  課題:  Flyle は分析系のクエリが多く、その一部がCPUの1スレッドを占有してしまい、他のクエリ性能も下げてしまう事象が発生していた。(いわゆるスロークエリ)  スロークエリを検知したらSQLを改善するサイクルは回していたが、顧客の増加スピードに追いつかず、タイムアウトが頻発するようになった。  分析機能は Flyle の中心機能の1つであるため、今後もスロークエリは増加し続けてしまうと想定。  キャッシュなどで対応できないスロークエリもあるため、まずは他のクエリに影響が出ないようにしたい。   

Slide 46

Slide 46 text

うるさい隣人問題を解決したい  解決策:  Aurora Serverless v2 というものがある  負荷が上がると1秒以内にスケールするらしい。本当か?  → 社内で検証したら本当かもしれない。  　少なくともスロークエリによる他のクエリ性能劣化が発生しなくなった。    コストは多少高くなるが、Flyleの場合、十分に許容範囲だった。 

Slide 47

Slide 47 text

結果 : うるさい隣人問題を解決したい  ● スロークエリが明らかに減った  ● スロークエリのスパイクがなくなった 

Slide 48

Slide 48 text

振り返り  MySQL → PostgreSQL 移行  ● 入念な準備と入念な動作確認は何よりも重要  ○ リリース後にデータベース移行に関連するバグは1件もなかった   ● メンテインして移行できたことで物事が簡単になった  ○ toC 向けサービスだったら難しかったと思う (そもそも RLS は不要かもだが)   Row Level Security 導入  ● アプリケーションとデータベースの2レイヤでデータ混在を防げて安心  ○ 情報漏洩は会社に致命的な損害を与えるのでできる限りのことをしておきたい   ● アーキテクチャとの噛み合わせが良かったので導入できた  ○ コルーチンを多用するプログラムだったら導入は難しかったかもしれない   ○ Java 19 で入った仮想スレッドによってスレッドプールを使わないのも安心だった  