現代のアプリケーションのためのデータベース設計パターン

Blog

データベース設計は、アプリケーションが育つほど重みを増すのに、正面から学ばれることの少ないスキルです。プロジェクトの初期にきちんと設計されたスキーマは、その後の数多くの機能開発を、大がかりな書き直しなしに支えます。逆に、雑な設計はどの新機能も苦しくし、どのクエリも遅くし、すべてのデプロイを危うくします。データ層についての決定はアプリケーションのあらゆる層に跳ね返り、あとから変えるには高い代償を伴います。

本記事では、現代のアプリケーションにとって重要なパターンを扱います。学術的な演習ではなく、チームが本番のデータベースを速く、保守可能に、そして安全に変更していくための実践的な戦略です。ストレージエンジンの選択、スキーマ設計の原理、クエリの最適化、CQRS やイベントソーシングといったアーキテクチャ上のパターン、そしてチームとデータが成長してもデータベースを滑らかに動かし続けるための運用上の作法を順に見ていきます。

データベースの選択：リレーショナル対 NoSQL

最初に下す、そして最も重い決定が、どの種類のデータベースを使うかです。よい知らせは、かつての宗派的な論争がほぼ終わったことです。今日、純粋にリレーショナルだけ、あるいは NoSQL だけというチームはまれです。現実的なやり方は、ワークロードごとに適した道具を選び、必要なら複数のストレージエンジンを併存させることです。

PostgreSQL や SQLite のようなリレーショナルデータベースは、データに明確な関係があり、参照整合性が大切で、エンティティをまたいで自在に問い合わせる必要がある場面で力を発揮します。請求システム、在庫管理ツール、あるいはトランザクションが完全に成立するか、まったく成立しないかのどちらかでなければならないアプリケーションを作っているなら、ACID 保証が要ります。リレーショナルデータベースは、二十年を超える実地の検証を経たそれらの保証を与えてくれます。

MongoDB のようなドキュメントデータベースは、データが階層的で、アクセスパターンが前もって分かっていて、一貫性の保証を書き込みスループットやスキーマの柔軟性と引き換えにできる場合に、より適しています。コンテンツ管理システム、イベントログのパイプライン、そしてデータの形が頻繁に変わるアプリケーションで真価を発揮します。

主データベースを選ぶための、実践的な判断の枠組みを挙げます。

  • 既定では PostgreSQL を使うこと。ユースケースの 95% をうまく扱い、ドキュメント型データのための JSON カラムを備え、優れたインデックス機能と成熟したエコシステムを持ちます。ここから始め、特別な理由があるときだけ離れてください。
  • アプリケーションがデバイス上、WASM 経由のブラウザ内、あるいは単一サーバーのツールとして組み込みデータベースを必要とするときは、SQLite に手を伸ばしてください。設定不要で、読み取りが非常に速く、近年の拡張機能によって驚くほどの能力を備えています。
  • 深くネストしたドキュメントを常にひとまとまりとして読み書きし、一貫性の要求が結果整合の読み取りを許せるほど緩いなら、MongoDB や Firestore を検討してください。
  • 本番での複数データベースの罠を避けてください。二つのデータベースを走らせることは、運用の複雑さを倍にします。主データベースではそのワークロードを扱えないと測定できたときにだけ、二つ目のストレージエンジンを足してください。

本番のコードベースでよく見かける最初の後悔は、リレーショナルなデータのために NoSQL データベースを選んでしまうことです。エンティティが互いを参照し、結合が必要なら、欲しいのはリレーショナルデータベースです。アプリケーションのオブジェクトとリレーショナルなテーブルの間のインピーダンスミスマッチは確かに存在しますが、本来リレーショナルなモデルと、そのために設計されていないドキュメントストアとの間のミスマッチに比べれば、はるかに小さいものです。

正規化、非正規化、そして本当の中間地点

正規化は誰もが学校で習います。第一、第二、第三正規形――この段階は、きれいで、重複がなく、更新時の異常が起きないスキーマを約束します。しかし本番の現実はもっと微妙です。完全に正規化されたスキーマは、たった一つの画面を描くために十数個のテーブルを結合するクエリを生みがちで、それは遅く、複雑です。完全に非正規化されたスキーマは書き込みを速く、読み取りを単純にしますが、一貫性の問題を持ち込み、どの書き込みも誤りを招きやすくします。

実践的な中間地点は、整合性のために正規化し、性能のために非正規化する——ただしそれを意識的に行い、理由を記録することです。データの本当の関係を捉えた正規化スキーマから始めてください。そして実際のクエリパターンを測定したあとで、性能上の利点が追加の複雑さに見合う箇所にだけ、非正規化したフィールドや集計テーブルを足します。

-- Start normalized
CREATE TABLE orders (
  id        UUID PRIMARY KEY,
  user_id   UUID NOT NULL REFERENCES users(id),
  status    TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id         UUID PRIMARY KEY,
  order_id   UUID NOT NULL REFERENCES orders(id),
  product_id UUID NOT NULL REFERENCES products(id),
  quantity   INT NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL
);

-- Denormalize only when measured: add total to orders
ALTER TABLE orders ADD COLUMN total NUMERIC(10,2);

-- Keep it consistent with a trigger or application-level logic
CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE orders SET total = (
    SELECT SUM(quantity * unit_price)
    FROM order_items WHERE order_id = NEW.order_id
  ) WHERE id = NEW.order_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_total_trigger
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW EXECUTE FUNCTION update_order_total();

規則は単純です。重要なクエリを測定するまで、決して非正規化しないこと。測定前の非正規化は、実在する問題を解いている証拠もないまま、重複データの複雑さだけを丸ごと持ち込みます。非正規化するときは、決定を記録し、一貫性を確かめるテストを足し、ずれを見つけて直せる突き合わせ処理を用意してください。重複データはいつか必ずずれます。ずれの検知を計画に入れるのは悲観ではなく、エンジニアリングの成熟です。

本当に効くインデックス戦略

インデックスは、どんなデータベース利用者にも使える、最も効果の大きい性能改善です。よく配置された一本のインデックスが、数百万行の逐次走査をわずかなページ読み取りに変えることがあります。しかしインデックスは無料ではありません。どのインデックスも書き込みに負荷を足し、ストレージを消費し、一つのクエリに対して候補が多すぎればプランナを迷わせることさえあります。

本番で一貫してよい結果を生む戦略は、三つの原理に従います。第一に、外部キーにインデックスを張ること。別のテーブルを参照するカラムは、既定でインデックス対象にすべきです。結合の性能は結合の両側でのインデックス探索に依存しており、外部キーのインデックス忘れは、リレーショナルデータベースで最もよくある重大な性能上の見落としです。

第二に、カラムではなくクエリパターンにインデックスを張ること。最も遅いクエリの WHERE 句と ORDER BY 句を見て、そのパターンに正確に一致する複合インデックスを作ってください。(status, created_at) の複合インデックスは created_at だけで絞り込むクエリの役には立ちませんが、(created_at, status) の複合インデックスは、created_at が十分に選択的であれば両方のクエリに効きます。複合インデックスにおけるカラムの順序は、決定的に重要です。

-- Instead of separate indexes
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created ON orders(created_at);

-- Create composite indexes that match real query patterns
-- Query: SELECT * FROM orders WHERE status = 'active' ORDER BY created_at DESC;
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- Query: SELECT * FROM orders WHERE user_id = $1 AND status = 'active';
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

第三に、前後を測定すること。PostgreSQL の pg_stat_user_indexes ビューは、どのインデックスが実際に使われ、どれが遊んでいるかを示します。ワークロードを流し、インデックス利用統計を確認し、一度も走査されないインデックスは削除してください。使われないインデックスは無害ではありません。あらゆる書き込みを遅くし、有用なデータページを保持できたはずのキャッシュメモリを食いつぶします。

部分インデックスは、あまり使われていない道具です。行の一部分だけを繰り返し問い合わせるのなら（有効な注文、未処理のイベント、削除されていないユーザー）、その行だけを覆う部分インデックスを作ってください。全体インデックスのごく一部の大きさで済み、走査もずっと速くなります。

-- Partial index: only index active orders
CREATE INDEX idx_orders_active ON orders(created_at DESC)
WHERE status = 'active';

-- This index is tiny compared to a full index and serves the query perfectly

リポジトリパターンとデータアクセスの抽象化

リポジトリパターンは、ドメインのロジックとデータアクセスのコードの間を仲立ちします。コレクションのようなインターフェースを提供して集約の取得と保存を担い、下層のストレージ機構の詳細を隠します。実際には、アプリケーションのコードは userRepository.findById(id) や orderRepository.save(order) のようなメソッドを呼ぶだけで、そのデータが PostgreSQL から来たのか、キャッシュ層から来たのか、外部 API から来たのかを知りません。

このパターンの値打ちは、データベースを替える必要が出たときや、キャッシュ層を入れるときにはっきりします。コントローラ、サービス、ユーティリティのあちこちにクエリが散らばっているチームは、MongoDB から PostgreSQL へ移るときに数百のファイルの書き直しに直面します。リポジトリを使っているチームは、少数の実装ファイルを差し替えるだけで済みます。インターフェースは変わらないからです。

ただし、リポジトリパターンとリレーショナルデータベースの間にはよく知られた緊張があります。リポジトリのインターフェースがあまりに一般的だと（findAll、findById、save、delete）、リレーショナルデータベースが備える豊かな問い合わせ能力を表現できません。そこでチームは専門的なクエリメソッドをリポジトリに足していき、抽象は少しずつ漏れていきます。解決は、リレーショナルデータベースのためのリポジトリが、キーバリューストアのためのリポジトリより多くのメソッドを持つことを受け入れる、というものです。findActiveByRole、searchByName、countByStatus を持つユーザーリポジトリは、抽象の失敗ではなく、下層エンジンの能力に対して正直であるということです。

問い合わせ能力を隠すリポジトリは、抽象ではなく足かせです。目的はドメインをストレージの詳細から切り離すことであって、すべてのデータベースを最小公倍数へ切り詰めることではありません。

CQRS とイベントソーシング：踏み込んだパターンに移るとき

CQRS（コマンドクエリ責務分離）は、データを変える経路とデータを読む経路を分けます。最も簡素な形では、CQRS のアーキテクチャは同じデータベースを使いながら、書き込みと読み取りで別のモデルを用います。書き込みモデルは不変条件を強制し、イベントを生みます。読み取りモデルはそのイベントを消費し、特定のクエリに最適化した非正規化のビューを組み立てます。この分離により、両側を独立に拡張し、最適化できます。

イベントソーシングはさらに進みます。エンティティの現在の状態を保存する代わりに、それを変えたすべてのイベントを保存します。現在の状態は、それらのイベントを再生して求めます。これにより、完全な監査証跡、任意の時点の状態を再構成する能力、そして下流の利用者にとって自然なイベント源が手に入ります。引き換えになるのは、相当量の運用上の複雑さです。イベントストアの基盤、投影の管理、書き込みモデルと読み取りモデルの間の結果整合、そして状態ではなくイベントで考えるという認知的な負荷です。

パターンについての正直な評価はこうです。ほとんどのアプリケーションは CQRS もイベントソーシングも必要としません。これらは、より単純なアーキテクチャでは満たせない特定の要件があるときにだけ正当化される複雑さを足します。CQRS を検討するのは、読み取りと書き込みのワークロードが根本的に異なる性質を持つ場合です。複雑な読み取り投影を伴う高い書き込みスループット、あるいは操作ごとに異なる一貫性の要求があるときです。イベントソーシングを検討するのは、法や製品の要件から改変不能な監査証跡が必要な場合、あるいはすべての状態変化が再構成可能かつ分析可能であるべき場合です。

これらのパターンを採るなら、まず CQRS だけで始め、監査証跡の要件が明確な場合にだけイベントソーシングを足してください。PostgreSQL で別の読み取りモデルを保守することによる CQRS の実装は、十分に手に負えます。その上にイベントストアを足すのは、複雑さの大きな飛躍であり、時間と予算を伴う意識的な決定として行うべきものです。

マイグレーション、コネクションプール、そして運用の健全さ

データベース設計の運用面こそ、よいパターンが滑らかに進化するアプリケーションと、カラムの型を変えるのに本番サーバーで事故を起こすアプリケーションとを分けるところです。三つの作法が、データベース変更をうまく扱えるチームと、そこで苦しむチームを一貫して分けています。

第一に、どのスキーマ変更も、バージョン管理に入れた可逆のマイグレーションであるべきです。Flyway、Liquibase、Alembic のような道具は、マイグレーションを順に適用し、どれが適用済みかを追跡します。マイグレーションのファイルはコードです。レビューされ、テストされ、アプリケーションコードと同じパイプラインでデプロイされます。個々のマイグレーションは小さく、一点に絞るべきです。一つのファイルでカラムを足し、データを埋め、テーブル名を変えるマイグレーションは危険です。独立に巻き戻せる手順へ分けてください。

第二に、コネクションプーリングは任意ではありません。データベース接続を開くのは高くつきます。TCP ハンドシェイク、SSL のネゴシエーション、認証が含まれるからです。コネクションプールは、スレッドが借りては返す持続的な接続の集合を保ちます。プールの大きさは重要です。少なすぎればリクエストが待ち、多すぎればデータベースがコンテキスト切り替えに時間を費やします。PostgreSQL のよい出発点は pool_size = 2 * CPU コア数 で、そこからクエリの待ち時間と接続待ち時間を見ながら調整してください。

第三に、マイグレーションを CI/CD パイプラインに注意深く組み込むこと。安全なやり方は、新しいアプリケーションコードをデプロイする前にマイグレーションを適用することです。こうすれば、新しいコードは期待どおりのスキーマを見つけ、古いコードも新しいスキーマとまだ両立します（先に適用された変更に互換性があるからです）。つまり、どのマイグレーションも現行のコードと両立しなければなりません。現行コードが参照しているカラムを消さないこと、移行期間を置かずにテーブル名を変えないことです。

  • 拡張：古いコードがまだ動いている間に、新しいカラムやテーブルを足す。
  • 移行：データを埋め、書き込みを新しい構造へ移す。
  • 縮約：古いコードがもう動いていないことを確かめたうえで、古いカラムやテーブルを削除する。

無停止のスキーマ変更のためのこの拡張・移行・縮約のパターンが効くのは、実行中のコードが扱えない状態にデータベースを決して置かないからです。規律は要ります（一つのデプロイ周期の間、古いコード経路を生かしておく必要があります）が、データベース絡みのデプロイ失敗で最もよくある原因を取り除いてくれます。

SQL か ORM か：正しい釣り合いを見つける

生の SQL を書くか、オブジェクトリレーショナルマッパーを使うかという論争は、ソフトウェア開発で最も息の長い議論の一つです。どちらの側にも正当な論点があり、正しい答えはプロジェクトの文脈次第です。

Prisma、TypeORM、SQLAlchemy のような ORM は、アプリケーションのオブジェクトとデータベースのテーブルの間の自動マッピング、マイグレーション管理、そして自分の言語のままでのクエリ組み立てを提供します。定型コードの丸ごと一区画を消し去り、立ち上がりを速くします。代償は、ORM が SQL を抽象してしまうことです。何かが起きたとき（遅いクエリ、予想外の結合、ロックの昇格）、ORM の振る舞いと下層の SQL の両方を理解して調べなければなりません。ORM は複雑なアクセスパターンに対して最適でないクエリを生むことがあり、N+1 問題は ORM を使うどのチームにもつきまといます。

生の SQL は、データベースサーバーで実行されるものを完全に制御できます。自分のスキーマとエンジンの能力に合わせて、思いどおりのクエリを書けます。代償は、自動マッピングを失い、マイグレーションを自分で管理しなければならず、コードベースのあちこちに散らばった SQL 文字列はテストしづらく、リファクタリングはさらに難しい、ということです。

現実的な中間は、単純な CRUD 操作の 80% には ORM を使い、性能の詰めや複雑な集計が要る 20% では生の SQL に降りることです。よい ORM は、生のクエリを実行して結果を型付きオブジェクトに戻す手段を備えています。単純な操作には ORM のクエリビルダを使い、複雑なものには生の SQL を書き、どちらも実際のデータベースで現実的なデータ量を使って試してください。

PostgreSQL は、能力、信頼性、エコシステムの釣り合いがどのデータベースより優れているため、現代のアプリケーションにとって既定の選択です。SQLite は組み込みとローカルファーストの領域を占めています。MySQL はレガシーと WordPress のエコシステムに広く残っています。もっとも、どのデータベースを選ぶかは、それをうまく使うために当てるパターンほど重要ではありません。スキーマ設計、インデックス、マイグレーション管理、運用の作法のほうが、ロゴよりもはるかに強く成否を決めます。

どのデータベースを選ぶかは、それをうまく使うために当てるパターンほど重要ではありません。スキーマ設計、インデックス、そして運用の作法が、ロゴよりもはるかに強く成否を決めます。

データベース設計は一度きりの作業ではありません。測り、調整し、学び続ける営みです。本記事のパターンは出発点として役に立ちますが、本当の熟達は、自分のデータベースが実際のワークロードでどう振る舞うかを観察し、的を絞った意識的な変更を重ねることから生まれます。単純に始めてください。すべてを測ってください。そして、ためらわずにマイグレーションファイルへ手を伸ばしてください。

See what your own repository can account for.

Thirty minutes on a repository you choose, including the part the record cannot attribute.
Book a demoSee the product