Share
Embed
MySQL 8.0 から PostgreSQL 16 への移行と RLS 導入までの道のりと学び
このスライドは、BtoB SaaSを展開するスタートアップ企業が、MySQL 8.0からPostgreSQL 16への移行を行い、さらにRow Level Security (RLS)を導入するまでの道のりを詳述しています。
主な内容:
1. 移行の背景と必要性:
• テナントデータの分離を徹底するため、データベースレイヤでの制御が必要であったこと。
• スタートアップ特有のコスト制約の中で現実的な解決策を模索。
2. 移行プロセス:
• スキーマおよびデータの移行手順を具体的に紹介。
• AWS DMSや独自ツールを駆使した検証と移行の取り組み。
3. RLS導入の工夫:
• セッション変数を活用したRLSの設計。
• データ混在を防ぐためのアプローチと事前準備。
4. 技術的課題への対処:
• 移行時のテスト、動作確認、想定外の事象への対応。
• 「うるさい隣人問題」をAurora Serverless v2で解決した事例。
5. 振り返りと学び:
• 入念な準備と動作確認の重要性。
• アプリケーションとデータベースの二層でデータ混在を防ぐ意義。
対象読者:
データベース移行を検討しているエンジニアや、テナント分離やセキュリティに課題を抱えるSaaS事業者に特に有益な情報を提供しています。
このスライドは、実践的な知見を共有することで、他の開発者の参考となる内容になっています。
Yuichiro Yamashita
December 05, 2024
Transcript
48 slides
-
目次 • 自己紹介 • なぜ MySQL から PostgreSQL に移行したのか •
MySQL から PostgreSQL への移行方法 • Row Level Security の導入方法 • そしてその先へ... -
前提 (弊社・弊社サービス 概要) • 弊社概要 ◦ プレシリーズAのスタートアップ • 弊社サービス概要 ◦
BtoB SaaS ◦ 顧客の顧客の情報、顧客の戦略情報を扱うサービス ◦ データ分析機能あり • データ量 ◦ 2024年11月時点でテナント数は200超 ◦ 顧客の顧客の情報は1テナントあたり最大10万件程度 ▪ 但し今後最大1,000万件程度まで増加予定 -
前提 (当時の弊社サービス Flyle の技術スタック) フロントエンド (SPA) バックエンド Rest API で通信
認証トークンとして JWTを使用 Hibernateを使用して (= ORM経由で) データベース接続 Prepared Statement を一部使用 リードレプリカはなし データベース -
なぜ MySQL から PostgreSQL に移行したのか • 弊社提供サービス Flyle の特性 ◦
BtoB SaaS ◦ お客様が抱えるVoCや戦略情報を扱うドメイン ◦ 複数のアプリケーションがデータベースに接続する 👉 (要件) お客様のデータ混在はあってはならない 👉 (要件) アプリケーションを超えて透過的にデータ混在を防げる仕組みが必要 • 移行前から実施していたこと ◦ アプリケーション (Kotlin / Hibernate) で、SQL に対して テナントID を指定 ▪ 一応静的検査でチェックもしていた ◦ ❌ 但し一部の複雑なクエリは静的検査不能 ◦ ❌ JOINした子テーブルに対する検査はできていなかった 👉 (課題) あってはならない事態を防ぐための仕組みが脆弱だった -
なぜ MySQL から PostgreSQL に移行したのか 👉 (要件) お客様のデータ混在はあってはならない 👉
(要件) アプリケーションを超えて透過的にデータ混在を防げる仕組みが必要 👉 (課題) あってはならない事態を防ぐための仕組みが脆弱だった これらを解決するするには データベースレイヤーでデータの混在を防ぐ仕組みが必要だった。 👉 「テナント分離」が必要 -
テナント分離の主な手法 サイロモデル (全て分離) プールモデル1 (ストレージは物理分離) DB APサーバー テナント2 DB
APサーバー テナント1 DB テナント2 テナント1 APサーバー プールモデル2 (ストレージも共有) DB テナント2 テナント1 APサーバー DB データ混在 👍リスクゼロ 👎設定間違いによる混在リスクあり 👎混在しやすいが軽減策あり うるさい隣人問題 👍リスクゼロ 👍リスクゼロ 👎顕在化しやすいが対処法あり サーバー費用 👎とても高い 👎高い 👍安い 運用コスト・自動化 👎とても大変 👎特にマイグレーションが大変 👍楽 ❌スタートアップの弊社には 費用が高すぎて非現実的 ❌費用・運用コストがかかる割に 結局混在リスクがある ✅混在リスクを最小化する前提で 採用 -
テナント分離の主な手法 スキーマ分離 テーブル分離 APサーバー APサーバー Row Level Security APサーバー データ混在
👎制御ミスによる混在可能性あり 👎制御ミスによる混在可能性あり 👎制御ミスによる混在可能性あり マイグレーション 👎時間がかかる (テナント数200超) 👎時間がかかる (テナント数200超) 👍速い 性能 🤔劣化しづらいがうるさい隣人問題あり 🤔劣化しづらいがうるさい隣人問題あり 👎劣化しやすいがデータ量次第 障害調査 👎テナントごとに調査する時間がかかる 👎テナントごとに調査する時間がかかる 👍テナントを跨いで調査できる テナント1 スキーマ foo_table テナント2 スキーマ foo_table 共通スキーマ tenan1_foo_table tenan2_foo_table 共通スキーマ foo_table ❌スタートアップの弊社には 許容できない運用コストだった ❌スタートアップの弊社には 許容できない運用コストだった ✅混在リスクを最小化する前提で 採用 -
MySQL から PostgreSQL に移行するために必要なこと 1. 事前調査 2. スキーマの移行手順確立 3. データの移行手順確立
4. アプリケーションの移行 5. 移行手順書の作成 6. 移行実行 -
事前調査 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) -
事前調査でわかったこと 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 など) -
スキーマの移行手順確立 • 方針 AWS DMS でスキーマ移行するとスキーマを正確に把握しづらいため、 (また、見落としている AWS DMS の仕様で混乱する可能性を避けるため)
手動でスキーマの移行を実施する • 手順 1. MySQL Workbench でスキーマをエクスポート 2. 独自プログラムで PostgreSQL 用の スキーマ生成文を作成 3. レビューによって正しさを確認 -
データの移行手順確立 • 方針 AWS DMS でデータを移行・検証する (事前に実行時間を計測) 独自ツールでも検証してクロスチェックする (事前に実行時間を計測)
• 手順 1. AWS DMS で移行・検証処理を実行 2. 検証エラーがないかをチェック → 絵文字 / CHAR0 を含む行で意図しない検証エラーが出たので無視する必要があった → NOT NULL などの制約はデータ移行後に設定する必要があることがわかった 3. 独自のバリデーションツールでもチェックして、データ移行ミスがないことをクロスチェック → MySQL と PostgreSQL にそれぞれクエリを実行してデータ差分がないかを確認 -
アプリケーションの移行 • Criteria Builder / Native SQL の修正 ◦
特に DELETE 文の書き方を修正 ▪ DELETE foo FROM foo という書き方を DELETE FROM foo に変更 ▪ INNER JOIN をサブクエリに変更 ◦ 非互換関数の修正 ▪ JSON_CONTAINS / JSON_SEARCH / JSON_REMOVE などなど... • 落ちているテストの修正 • レビュー ◦ 人手によるレビュー ◦ GPTによる変換結果との比較によるレビュー • 動作確認 ◦ Flyle の全機能に対する手動テストを複数回実施 -
移行手順書の作成 • 前日のタスク (概要) ◦ 検証環境を用いた移行リハーサル ◦ PostgreSQL
インスタンスを作成 ◦ PostgreSQL インスタンスのデータベースとユーザーを作成 ◦ パラメータストアにDBユーザーとDBパスワードを保存 ◦ 上記のDBユーザーとDBパスワードで PostgreSQL にログインできることを確認 ◦ MySQL と PostgreSQL 共にローカルからポートフォワードできることを確認する ◦ AWS DMS を実行するために必要なリソースを作成 ※実際には詳細な各手順の想定時間を含む手順ベースの細かな手順書を作成しました。 -
移行手順書の作成 • 当日のタスク (概要) ◦ メンテインする / サービスURLに対して basic
認証を追加する ◦ 稼働しているサービスを停止 ◦ MySQL のスナップショットを作成 ◦ MySQL を Security Group から切り離す ◦ PostgreSQL にテーブルを作成する ◦ AWS DMS を使用してデータを移行 ◦ AWS DMS を使用してデータバリデーションを実行 ◦ 独自ツールによるバリデーションを実行 ◦ アプリケーションコードをデプロイ ◦ 性能を含む動作確認して問題がなければメンテアウト (問題があれば MySQL に切り戻し) -
(振り返り) 移行作業でよかったこと • プロセス全体に関して ◦ 1日メンテインして移行することでプロセス自体がシンプルになった ◦ 切り戻しができる状態で移行作業に望めたので安心して取り組めた
• データ移行に関して ◦ 事前にデータ移行を何度も試したので実際の移行もスムーズだった ◦ 複数の手法でデータ検証したことでデータ移行ミスがないことを確信できた • アプリケーションに関して ◦ 複数の手法によるレビューと動作確認によって品質に自信を持てた ◦ 弊社の主なテストがコントローラー層に対する結合テストだったため、 比較的少ない修正でテストを実行することができ、品質に自信を持てた -
(振り返り) 移行作業で起きた想定外だったこと • DMS が想定よりも3時間ほど長くかかった。(想定は2時間) ◦ 原因: 検証時よりもデータが増えたことが影響した →
ベクトルデータが一気に増えている時期だったためそれが要因であると認識 • 独自ツールによるバリデーションが想定よりも時間がかかった ◦ 原因: 同上 但し移行時間に余裕を持っていたため、メンテ時間の中で対応できた -
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 と一致している行のみアクセスできる。 -
Row Level Security を実現する主な方法 1. (不採用) ユーザーとロールを使用する方法 🤔 運用
: ユーザーとロールを沢山作成する必要があり煩雑だがユーザー単位でメトリクスを監視できる 👎 コネクション : コネクションプールの管理が煩雑になる可能性がある 👎 設計 : ユーザーとロールの2箇所で制御するので複雑になる可能性がある 2. (不採用) ユーザーを使用する方法 🤔 運用 : ユーザーを沢山作成する必要があり煩雑だがユーザー単位でメトリクスを監視できる 👎 コネクション : コネクションプールの管理が煩雑になる可能性がある 👍 設計 : ユーザーで RLS を実現すれば良いのでシンプル 3. (採用) セッション変数を使用する方法 🤔 運用 : ユーザーは1つで良いので管理が楽だがテナントごとのメトリクスは見れない 👍 コネクション : コネクションプールの管理はシンプル (これまで通り) 👍 設計 : セッション変数で RLS を実現すれば良いのでシンプル -
Row Level Security を設定する事前準備 • 全てのテーブルに tenant_id を追加する ◦ 外部キー制約も設定する
• 性能劣化が起きないように、全ての INDEX に tenant_id を追加する ◦ Row Level Security が有効なクエリは、 強制的に WHERE 句に tenant_id = ‘foo’ が追加されるイメージ -
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; -
Row Level Security を設定する • セッション変数の設定方法 local を使用してセッション変数を使用することで現在のトランザクションのみに適用する
SET local app.tenant_id = 'xxx'; SET local app.admin = 'true’; -
Row Level Security をアプリケーションから利用する Tomcat Servlet API Spring Boot HTTP
リクエスト リクエスト ごとに 別スレッド (バーチャルス レッド) 認証 @Controller (コントローラー) @Transactional (トランザクション境界) @Repository (DBアクセス) テナント情報を ThreadLocal に保存 トランザクション境界にて AOP を用いてセッション変数を設定 DBアクセス時にトランザクション未設定の場合自 動的にトランザクションを開始して セッション変数を設定 (AOPを使用) -
Row Level Security をアプリケーションから利用する • リクエストを跨いでスレッドが共有されることはない ◦ Spring Boot
で仮想スレッドを使用する場合、スレッドプールは使用しない (スレッドは使いまわされない) ◦ 認証レイヤーでテナントIDを ThreadLocal に設定し、以降は ThreadLocal の値を信用する • DBコネクションプールは使いまわされる ◦ SET app.tenant_id = 'xxx'; のように local なしでセッション変数を設定すると意図しないテナントIDがセッション変数に 使用される可能性がある。よって、必ず local を使用する。 • コネクションプールはトランザクション単位で使用する ◦ 同じリクエスト (スレッド) であってもトランザクションごとに別のコネクションが使われる (場合がある) よって、トランザクション境界でセッション変数を指定する • トランザクションを貼らないで実行されるクエリに対する対処 ◦ クエリ発行直前にトランザクション有無をチェックし、 トランザクションが未指定の場合はトランザクションの開始とセッション変数の設定を実施する • セッション変数の指定は最小限にする ◦ スレッド / トランザクションごとにセッション変数を設定したかを管理して複数回の設定を回避する • Kotlin coroutines は使用しない ◦ Kotlin coroutines は複数コルーチンが同一スレッドで動作するので ThreadLocal を使えない -
Spring Boot 別スレッド リクエスト内などで更に別スレッドを起動する場合 メインスレッド @Transactional (トランザクション境界) @Repository (DBアクセス)
スレッド起動時に メインスレッドのテナントIDを別スレッドに引き継ぐ -
リクエスト内などで更に別スレッドを起動する場合 別スレッド起動コードイメージ (Kotlin) ※ イメージを伝えるための最小コードです。 val tenantIdByThread = ThreadLocal<TenantIdOrAdmin>()
fun async(process: () -> Unit) { val currentTenantId = tenantIdByThread.get() Thread.ofVirtual().start { tenantIdByThread.set(currentTenantId) process() } } -
Row Level Security の導入フロー 1テーブルにRLSを導入する 問題ないか 1機能テーブル群にRLSを導入する 問題ないか
数回に分けて 全テーブルにRLSを導入 RLS導入に関する設計をする 問題なし 問題なし 問題あり 問題あり -
Row Level Security 導入漏れの検査 「テーブル一覧取得SQL」と「RLS の設定取得SQL」を用いて、 以下の観点でチェックすることでRLSの導入漏れがないかを検査します • RLS
が設定されていないテーブルが存在しないか • RLS の設定が想定通りか -
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 -
テナント分離の主な手法 サイロモデル (全て分離) プールモデル1 (ストレージは物理分離) DB APサーバー テナント2 DB
APサーバー テナント1 DB テナント2 テナント1 APサーバー プールモデル2 (ストレージも共有) DB テナント2 テナント1 APサーバー DB データ混在 👍リスクゼロ 👎設定間違いによる混在リスクあり 👎混在しやすいが軽減策あり うるさい隣人問題 👍リスクゼロ 👍リスクゼロ 👎顕在化しやすいが対処法あり サーバー費用 👎とても高い 👎高い 👍安い 運用コスト・自動化 👎とても大変 👎特にマイグレーションが大変 👍楽 🎉 Row Level Security の導入によって解決 ❌ 未解決 -
テナント分離の主な手法 スキーマ分離 テーブル分離 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 によって解決 🤔 未解決だが現時点では実際の問題になっていない -
うるさい隣人問題を解決したい 課題: Flyle は分析系のクエリが多く、その一部がCPUの1スレッドを占有してしまい、他のクエ リ性能も下げてしまう事象が発生していた。(いわゆるスロークエリ) スロークエリを検知したらSQLを改善するサイクルは回していたが、顧客の増加スピード に追いつかず、タイムアウトが頻発するようになった。 分析機能は Flyle の中心機能の1つであるため、今後もスロークエリは増加し続けてし
まうと想定。 キャッシュなどで対応できないスロークエリもあるため、まずは他のクエリに影響が出な いようにしたい。 -
うるさい隣人問題を解決したい 解決策: Aurora Serverless v2 というものがある 負荷が上がると1秒以内にスケールするらしい。本当か? → 社内で検証したら本当かもしれない。
少なくともスロークエリによる他のクエリ性能劣化が発生しなくなった。 コストは多少高くなるが、Flyleの場合、十分に許容範囲だった。 -
振り返り MySQL → PostgreSQL 移行 • 入念な準備と入念な動作確認は何よりも重要 ◦ リリース後にデータベース移行に関連するバグは1件もなかった
• メンテインして移行できたことで物事が簡単になった ◦ toC 向けサービスだったら難しかったと思う (そもそも RLS は不要かもだが) Row Level Security 導入 • アプリケーションとデータベースの2レイヤでデータ混在を防げて安心 ◦ 情報漏洩は会社に致命的な損害を与えるのでできる限りのことをしておきたい • アーキテクチャとの噛み合わせが良かったので導入できた ◦ コルーチンを多用するプログラムだったら導入は難しかったかもしれない ◦ Java 19 で入った仮想スレッドによってスレッドプールを使わないのも安心だった