OLTPとレポーティングのスキーマ：非正規化するか集計テーブルを追加するか？

OLTPとレポーティングがスキーマに異なる要求をする理由

OLTP（オンライン・トランザクション処理）は、アプリが日々行う操作そのものです：小さな処理を大量に、速く安全に行う必要があります。注文を作成し、ステータスを更新し、支払いを追加し、メッセージを記録する。データベースは高速な挿入や更新、外部キーなどの厳密なルール、少数の行に触れる単純なクエリに最適化されています。

一方でレポーティングは別の仕事です。ダッシュボードやBIスタイルの画面は、多くの行を走査して集計したり、期間を比較したりします。「この顧客を見せて」というより、「週別、地域別、商品カテゴリ別の売上をフィルタ付きで見せて」という要求が多く、幅広い読み取り、集計、複数テーブルの結合、繰り返し計算が必要になります。

これがOLTPとレポーティングのスキーマ判断における核心的な緊張です：書き込みをクリーンで一貫させる構造（正規化されたテーブル、複数のリレーション）は、スケールすると分析を遅く、コスト高にすることが多いのです。

単一のスキーマで両方をまかなえることも初期段階ではあります。しかしデータが増えると、次のようなトレードオフが目に見えてきます：

• トランザクション画面は高速だが、ダッシュボードが毎月遅くなる。
• 「一つの簡単なチャート」が多くの結合を伴う複雑なクエリになる。
• 同じ指標が複数箇所で計算され、値が一致しなくなる。
• 新しいフィルタを追加すると危険なクエリ変更が必要になる。

そのため、多くのチームは次のいずれか（または複数）を採ります：よく使うスライスのために特定のフィールドを非正規化する、繰り返し使う合計のために集計テーブルを追加する、あるいはレポーティング用のビュー（場合によっては別スキーマ）を作ってOLTPの性能を守りつつ数値の一貫性を保つ、という選択です。

トランザクション画面とBI画面で何が変わるか

トランザクション画面とBI画面は同じビジネス事実を表示することがあっても、データベースに求める挙動は正反対です。この緊張がOLTPとレポーティングのスキーマ選択の中心にあります。

トランザクション画面では、ほとんどのリクエストが少数の行に触れます。ユーザーが注文を作成し、顧客を編集し、支払いを返金し、ステータスを変更します。データベースは小さな挿入と更新で忙しく、それぞれを速く安全に確定する必要があります。

BI画面は異なります。読み取りが圧倒的に多く、ダッシュボードの1つのビューが何週間分ものデータをスキャンして、グループ化し、ソートし、複数の方法でフィルタします。これらのクエリは幅が広く（多くの列）、複数のビジネス領域からデータを引っ張ることがあります。

クエリの変化

OLTPでは、正規化されたテーブルと明確なリレーションが味方です。データを一貫して保ち、重複を避け、1つの事実を1か所で更新します。

BIでは、結合がボトルネックになりがちです。ダッシュボードは日付、地域、商品カテゴリ、担当者など、人がフィルタで使うフィールドをあらかじめ含む幅の広いテーブルの方が動作しやすいことが多く、これにより読み取り時の結合作業が減り、クエリが単純になります。

違いを見分ける簡単な目安：

• トランザクション画面：小さな書き込み多数、速いポイント読み取り
• BI画面：リクエストは少ないが、グループ化やフィルタを伴う重い読み取り
• OLTPデータ：整合性を守るための正規化
• BIデータ：結合や走査を減らすために再構成されることが多い

同時実行性と鮮度

OLTPは更新時に高い同時実行性を必要とします。長時間走るレポーティングクエリは、特に大きな範囲をスキャンすると、これらの更新をブロックまたは遅延させる可能性があります。

鮮度に関する期待も変わります。あるダッシュボードはほぼリアルタイム（サポートのキューなど）である必要があり、別のものは1時間毎や日次で十分（財務、パフォーマンス）です。スケジュールで更新できるのであれば、集計テーブル、マテリアライズドビュー、または別のレポーティングスキーマを使う自由が生まれます。

AppMasterでこれらの画面を作る場合は、早い段階で計画することが役立ちます：トランザクションモデルはクリーンに保ち、レポート用のデータはダッシュボードのフィルタと集計に合わせて成形してください。

レポーティングのために調整が必要だと分かるシグナル

日常のトランザクションは速いのにダッシュボードが遅いと感じるなら、これはOLTPとレポーティングのスキーマ分離の典型的な兆候です。トランザクション画面は少数の行に素早く触れますが、BIスタイルの画面は多くの行を走査し、グループ化し、同じ計算を何度も繰り返します。

単純なサインはタイミングです：開発では問題なかったダッシュボードクエリが本番で遅くなる、あるいはピーク時にタイムアウトする。報告ワークロードは「スパイクする」DB CPUとして現れることがあり、アプリのトラフィック自体は変わらないのにCPUが高くなるなら、データベースが大きなテーブルの結合や集計に苦労している証拠です。

一般的なシグナル：

• ダッシュボードが1つの質問を答えるために多数のテーブルを結合する。
• 同じ計算（売上、アクティブユーザー、平均処理時間）が複数のチャートやページで繰り返される。
• 日/週/月ごとの同じ合計を人が頻繁に要求し、毎回重いクエリが走る。
• BIクエリが遅くなったりタイムアウトしたりしている間に、通常のユーザーがレコードを作成・編集している。
• OLTPのトラフィックや書き込み量は安定しているのにデータベースCPUが上がる。

実例：営業チームが「担当者別・月別のパフォーマンス」画面を開き、地域・商品・チャネルでフィルタする。フィルタを変えるたびに同じ合計を再計算する複数テーブルの結合クエリが再実行されるなら、毎回そのコストを支払っていることになります。

AppMasterのようなプラットフォームで内部ツールを作る場合、レポーティングページが応答性を保つために複雑なバックエンドロジックを必要とするのが見えるポイントが多くあります。そこで非正規化、集計テーブル、あるいは別のレポーティングビューが「あると良い」から「必要」へと変わることが多いです。

非正規化が適切なとき

非正規化は、レポーティングの要件が予測可能な場合に有効です。毎週同じ少数のダッシュボード質問が繰り返され、ほとんど変わらないなら、毎回複数のテーブルから答えを組み立てる代わりにデータを質問に合わせて成形する価値があります。

これはOLTPとレポーティングのスキーマ判断におけるよくある転換点です：トランザクション画面は更新に優しいクリーンなテーブルを必要とし、BI画面は結合や走査を減らすための高速な読み取りを必要とします。分析用途では、いくつかのフィールドをコピーする方が、毎回5つのテーブルを結合するよりも安価になることがあります。

非正規化は、速度とクエリの単純化が明確に得られ、かつ書き込み経路を安全に保てる場合に行ってください。重要なのは、複製されたフィールドを「ユーザーが編集できる別の場所」として扱わず、派生データとして扱うことです。唯一の真実のソースを保ち、すべてのコピーがコードや制御されたプロセスで更新されるようにします。

良い候補は：

• ダッシュボードで常に読まれるが滅多に編集されないフィールド（顧客名、商品カテゴリ）
• 繰り返し結合するのが高コストな関係（多対多、深いチェーン）
• 素早くフィルタやグループ化に必要なフィールド（地域、チーム、プラン階層）
• 信頼できるテーブルからコピーでき、検証が容易なもの（自由入力ではない）

所有権は重要です。誰か（あるいは定期的なジョブ）が複製の整合性を保つ責任を持ち、ソースが変更されたときのルールを明確にする必要があります。

例：営業ダッシュボードが担当者・地域で注文をグループ化する場合、毎回 Orders -> Customers -> Regions を結合する代わりに、注文作成時に order に region_id を保存できます。顧客が後で地域を移動したら、履歴注文は「作成時の地域を保持する」か「夜間にバックフィルする」かのルールを決めてください。ルールを選び、文書化し、遵守します。

AppMasterとPostgreSQLを使っているなら、Data Designerでこのような非正規化フィールドをモデル化しやすいですが、誰が書けるかを制限して一貫して更新することが重要です。

非正規化で避けるべき落とし穴

非正規化はBI画面を高速化できますが、「二つの真実」を生む危険もあります。最も一般的な失敗は、同じ事実を複数箇所に繰り返して保存し、どのフィールドが優先されるかを明確にしないことです。もし order_total と行アイテムの両方を保存するなら、order_total が計算されるのか、ユーザーが入力するのか、支払いプロバイダからコピーしているのか、という一つのルールが必要です。

また、頻繁に変わるフィールドを非正規化するのは落とし穴です。顧客ステータス、アカウント担当、商品カテゴリ、地域割り当てなどは時間とともに変わることが多いです。利便性のために多くのテーブルにコピーすると、変更ごとにクリーンアップ作業が発生し、更新漏れがダッシュボードの誤ったスライスとして表れます。

OLTPパスで非常に幅の広いテーブルにするのも注意が必要です。多くの非正規化列をトランザクション処理のコアテーブルに追加すると、書き込みが遅くなり、ロック時間が増え、単純な更新が重くなります。これはイベント、注文行、サポートメッセージのような高頻度テーブルで特に問題になります。

文書化は多くのチームが想定するより重要です。メンテナンス計画のない非正規化列は時限爆弾です：人々はレポートでそれを読み、信頼し、ワークフローの変更で更新が止まっても気づかなくなります。

実例：order に rep_name を追加したとします。担当者が名前を変更または再割り当てされると、先月の数字が2つの名前に分かれてしまうかもしれません。表示のために名前が必要なら、安定した rep_id を保存して報告ビューで名前を解決するか、rep_name_at_sale のようにスナップショットとして意図的に保存することを検討してください。

非正規化する前に以下を確認してください：

• 繰り返される値の真のソースを定義して文書化する。
• 可変テキストより安定したIDを優先する。
• 現在の状態を報告するのか、時点スナップショットが必要かを決める。
• 明確なメンテナンス機構（トリガー、ジョブ、ワークフローステップ）と所有者を追加する。
• ミスマッチを監視する（単純な照合クエリ）ことで早期にエラーを検出する。

AppMasterとPostgreSQLを使う場合、Business Processステップにメンテナンスを結びつけると、更新が「思い出したとき」ではなく一貫して行われます。

集計・サマリテーブルを追加すべきとき

集計テーブルは、BI画面が同じ合計を何度も必要とする場合に有効です：日次サインアップ、プラン別売上、アクティブユーザー、返金、クローズされたチケットなどのKPIです。

繰り返しが多いときが良いサインです。複数のダッシュボードカードがほぼ同じGROUP BYで同じクエリを実行しているなら、データベースは同じ作業を繰り返しています。これは行数が千なら問題ないかもしれませんが、1,000万になればつらくなります。OLTPとレポーティングのスキーマ議論では、インデックスをいじるのをやめて事前計算に移る瞬間がここです。

また、予測可能な速度が必要なときにも集計テーブルは有効です。チャートは「時々速い」ではなく「常に数秒でロード」するべきです。集計テーブルは高コストな走査を小さなルックアップに変えます。

典型的なトリガー：

• ダッシュボードが多くの画面やフィルタで同じGROUP BYを繰り返す。
• 日/週/月の時間バケットやTop-Nリストをよくクエリする。
• 基本テーブルが追加重視（イベント、トランザクション、ログ）である。
• ステークホルダーが既知のカットオフで安定したKPIを期待する（例：「深夜時点」）。

リフレッシュ戦略が判断のもう一つの半分です。必要な鮮度によって現実的な選択肢が変わります：

• 定期リフレッシュ（5分毎、時間毎、夜間）で予測可能な負荷にする。
• 重要なアクション後のイベントベース更新（新規注文、サブスクリプション変更）で準リアルタイムにする。
• ハイブリッド：定期バッチでバックフィルし、小さな増分更新を重ねる。

テーブルはシンプルに保ちましょう：粒度（例：日次×プラン）が明確で、列はチャートが直接読むメトリクス（合計や件数など）に限定します。AppMasterで作るなら、PostgreSQLに集計を保存し、Business Processでスケジュール更新するパターンがよく合います。

集計テーブルの設計手順

集計テーブルはOLTPとレポーティングの妥協です：トランザクションの生データは保持しつつ、一般的なダッシュボード質問に素早く答える小さなテーブルを追加します。

1) まず粒度（グレイン）を決める

1行が何を意味するかを決めてください。ここを間違えると後で説明が難しくなります。一般的な粒度は「日×顧客」「1注文ごと」「担当者×日」などです。

粒度をテストする簡単な方法：1行を一意に識別できるか？もし「場合による」が出てくるなら粒度があいまいです。

2) 生データではなく質問に合わせてテーブルを設計する

ダッシュボードが実際に表示している数値をいくつか選びます。必要なものだけを保存してください：合計や件数が一般的で、範囲が必要なら最小/最大を追加します。「ユニーク顧客」が必要なら正確なdistinctが必要か近似で良いかを決め、その選択を文書化します。

実務的な手順：

• 5〜10のダッシュボード質問を書き出す（例：「担当者別の日次売上」）
• ほとんどを1行で答えられる粒度を選ぶ
• 列は集計値のみ（sum, count, min, max, 必要なら distinct）にする
• フィルタに合わせたキーとインデックス（日付、agent_id、customer_id）を追加する
• 遅れて到着するデータ（返金、編集、キャンセル）をどう扱うか定義する

3) 信頼できるリフレッシュ方法を選ぶ

バッチリフレッシュ（夜間、時間毎）は最も分かりやすいです。増分リフレッシュは速いですが「何が変わったか」のロジックが必要です。トリガースタイルの更新は準リアルタイムにできますが、制御しないと書き込み性能にリスクを与えます。

AppMasterなら、定期ジョブでBusiness Processを実行し昨日と今日を再計算し、古い日付は固定するパターンがよく使われます。

4) 差戻しチェックを追加する

集計テーブルに依存する前に、生テーブルと比較する簡単なチェックを追加します：

• 指定レンジの合計が許容差内で一致するか
• 同じフィルタで件数が一致するか（注文、ユーザー、チケット）
• いくつかのエンティティをスポットチェック（担当者一人、顧客一人）する
• 欠損（欠けている日）や重複（同じキーが二重）を検出する

これらのチェックが失敗したら、他の指標を追加する前にロジックを修正してください。速いダッシュボードで間違っているのは、遅いダッシュボードより悪いです。

レポーティングビューとスキーマを分けることで解決する問題

OLTPテーブルをクリーンに保つ目的は主に正確性です。明確なルール、強い制約、悪いデータを作りにくい構造が欲しい。一方でレポーティング画面は、結合が少なく読みやすい名前の列、すぐに読めるメトリクスが欲しくなります。このミスマッチがあるため、コアテーブルを変えるのではなくレポーティング層を追加することがよく行われます。

レポーティングビュー（または別スキーマ）は翻訳レイヤーのように働きます。アプリは正規化されたテーブルに書き込み続け、BI画面は「月別」「地域別」「Top10商品」のような問いに答えるために設計されたオブジェクトを読むという形です。これによりトランザクションロジックを壊さずにOLTPとレポーティングの緊張を解決できます。

ビューとマテリアライズドコピーの違い

論理ビューはデータ量が中程度でクエリが予測可能な場合に優れています。真のソースを1つに保ち、ダッシュボードクエリのロジックの重複を減らします。

マテリアライズドコピー（マテリアライズドビュー、集計テーブル、複製テーブル）は、レポーティング負荷が重い場合、計算が高コストな場合、ピーク時間に安定したパフォーマンスが必要な場合に適しています。

選び方の簡単な指針：

• 可読性と定義の一貫性が主目的なら論理ビューを使う。
• ダッシュボードが遅い、またはコア書き込みと競合するならマテリアライズドコピーを使う。
• 境界と所有権を明確にしたければ別のレポーティングスキーマを使う。
• レポーティングが書き込み遅延に影響するならレプリカや別DBを使う。

レポーティングが書き込みと競合するとき

ダッシュボードが幅広いスキャンや大きな結合を実行すると、同じデータベース上でトランザクションがブロックまたは遅延することがあります。読み取りレプリカや別のレポーティングDBは書き込みパスを保護します。それでも定義を一貫させたい場合は、レポーティング側でビューを作れば定義の整合性は保てます。

例：サポートチームのダッシュボードが「SLAステータス別のオープンチケット」を数秒毎に表示し、OLTPはチケットを頻繁に更新する場合、ビュー（あるいは事前集計）をレプリカに置けばダッシュボードを速く保ちながらチケットの更新を遅くしません。AppMasterプロジェクトでは、このパターンによりトランザクションデータモデルをクリーンに保ちつつ、レポーティング向け要素を画面に提供できます。

現実的な例：営業パフォーマンスダッシュボードの構築

ビジネス側から「日次売上、日次返金、過去30日間のTop商品リスト」を示す営業ダッシュボードの要望が来たとします。トランザクションDBは正規化されており、orders、payments、refunds、line_items が別々のテーブルにあります。これは正確性と更新には良い設計ですが、ダッシュボードでは大量の行をスキャンして結合し、日別にグループ化する必要があります。

最初は慎重なクエリ、適切なインデックス、小さな調整で許容速度が得られることが多いです。しかしボリュームが増すと、OLTPとレポーティングのトレードオフを検討し始めることになります。

オプションA：フィルタを速くするための非正規化

ダッシュボードが主にフィルタリングやスライシングを行うなら、軽い非正規化で改善できます。例えば、order（またはline_item）行に安定したいくつかのフィールドをコピーしておけば、クエリが追加の結合なしにフィルタできます。

良い候補は購入時に滅多に変わらないフィールド（商品カテゴリ、販売地域など）です。正規化テーブルに真のソースを維持しつつ、BI用に「クエリ向けのコピー」を保存します。

オプションB：チャートとランキングのための日次集計テーブル

チャートやTopリストが多いなら、集計テーブルが勝ちます。daily_sales のような日次ファクトテーブルを作り、date、gross_revenue、refunds、net_revenue、orders_count といった列を持たせます。Top商品には date と product_id をキーにした daily_product_sales を作ります。

鮮度とコストが選択を左右します：

• ほぼリアルタイムが必要（毎分）：非正規化してライブクエリ、または非常に短い間隔で集計をリフレッシュする。
• 時間毎や夜毎で良い：集計はクエリ時間を劇的に短くする。
• 高トラフィックのダッシュボード：集計はOLTPテーブルの負荷を減らす。
• 複雑なビジネスルール（返金タイミング、分割支払い）：集計は結果を一貫させ、テストしやすくする。

AppMasterのようなツールでは、クリーンなトランザクションモデルと別の定期処理で集計テーブルを埋める設計がマッチします。

ダッシュボードが遅く・数値が間違う原因となる一般的なミス

最も一般的な失敗パターンは、OLTPの書き込みとBIの読み取りを同じテーブルで混ぜ、いくつかのインデックスを追加すれば解決すると仮定することです。ダッシュボードは多くの行をスキャンしてグループ化・ソートしますが、これは注文を保存したりチケットを更新したりする仕事とは異なります。1つのスキーマで両方を無理にさせると、トランザクションが遅くなるか、ダッシュボードがタイムアウトするかのどちらかになります。

もう一つの静かな問題は、一見「便利な」報告ビューが高コストな作業を隠してしまうことです。ビューはクエリを単純に見せますが、データベースは毎回結合や計算を実行します。数週間後、誰かが「ちょっと一つフィールドを追加して」と結合を増やし、ダッシュボードが一晩で遅くなることがあります。ビューは何が行われるかを隠すだけで、実際の作業量は変わりません。

集計テーブルは速度を解決しますが、新たなリスク（ドリフト）を生みます。集計をスケジュールで再構築するなら更新が遅れることがあります。増分更新ならジョブの失敗やバグで合計が数日間ずれることがあります。これがダッシュボードとトランザクション画面の数値が合わない原因になります。

指標定義の変更は最も混乱を招くことがあります。例えば「Revenue」が最初は支払済み請求で、その後「支払済−返金」になり、さらに「認識収益」に変わると、ロジックを上書きすると過去のチャートが変わり、誰もダッシュボードを信頼しなくなります。

これらの問題を防ぐ実用的なガードレール：

• 重いダッシュボードクエリは可能なら書き込み重視のトランザクション経路から分離する（たとえ別のレポートテーブルを作るだけでも）。
• ビューをコードとして扱う：変更をレビューし、パフォーマンスをテストし、結合内容を文書化する。
• 集計テーブルの鮮度チェック（最終更新時刻、行数、正気の範囲の合計）を追加し、壊れたらアラートを出す。
• 主要指標をバージョン管理し、過去の定義を歴史的レポートのために残す。

AppMaster上のPostgreSQLでBI画面を作るなら、これらのルールは特に重要です。早く反復できるのは良いことですが、数値が正しいことが前提でなければ意味がありません。

スキーマを変える前のクイックチェックリスト

テーブルに手を入れる前に、まずダッシュボードが実際に何をしているかを書き出してください。上位のダッシュボードクエリ（約10件を目安）を書き出し、各クエリがどのくらいの頻度で実行されるかをメモします：ページロードごと、毎分、あるいはフィルタをクリックした時のみ。1日に500回実行されるクエリは週に2回しか実行されないクエリとは別の対処が必要です。

次に算術の整合性をチェックします。どの指標が加算可能（合算して良い）で、どれが特別なロジックを必要とするかをマークします。売上、数量、通話総数は一般に加算可能です。コンバージョン率、平均注文額、ユニーク顧客はそうではありません。これをやるだけで「速いが間違った」ダッシュボードを防げます。

続いて、クエリタイプごとに設計を選びます。OLTPとレポーティングのスキーマ判断に1つの答えを無理に当てはめる必要はありません。アクセスパターンに合わせて選びます：

• 画面が少数のフィールドを速く必要とし、ルールが単純なら非正規化。
• 同じグルーピング（日別、担当者別、地域別）を繰り返すなら集計テーブル。
• ロジックが複雑、あるいはトランザクションと明確な境界が欲しいなら別の報告ビュー／スキーマ。

各指標にとって「十分な鮮度」が何かを決め、単純な検証ルールを設定します。例：「ダッシュボードの日次注文数はその日付のordersテーブルの件数と0.5%以内で一致すること」や「総売上は請求書のpostedステータスと照合すること」など。

最後に所有者を決めます。スキーマ変更を承認する人（または小さなグループ）と指標定義のオーナーを明確にします。AppMasterで作るなら、これらの定義をデータモデルやBusiness Processesと一緒に管理して、同じロジックが画面とレポートで使われるようにしてください。

次のステップ：方針を決めて安全に実装する

OLTPとレポーティングのスキーマ判断を大規模な設計変更ではなく、性能バグとして扱ってください。まずは計測から始めます。最も遅い2〜3個のダッシュボードクエリを見つけ、実行頻度を記録し、形状（大きな結合、時間フィルタ、Top N、繰り返し合計）を把握します。

ユーザーに見える問題を解決するための最小の変更を選んでください。もし1つの結合が遅さの原因なら、ターゲットを絞った非正規化や計算列で十分かもしれません。同じ合計が何度も計算されているなら、小さな集計テーブルで解決することが多いです。BI画面が成長してトランザクションと競合するなら、別のレポーティングビューやスキーマに移す投資を検討してください。

信頼性を保ちながら安全に実装する流れの例：

• ダッシュボードのゴール（時間範囲、グルーピング、更新の必要性）と受け入れ基準（例：2秒以内でロード）を定義する。
• 変更は一度に1つだけ（1つの非正規化列、1つの集計テーブル、または1つのレポーティングビュー）。
• 固定のテストウィンドウ（昨日、過去7日、前月）を使って集計をOLTPソースと照合する。
• 段階的にロールアウトし、本番で1週間は性能と正確さを観察する。
• 「クエリ時間」と「行数」のアラートを入れて、静かなドリフトを早期検出する。

AppMasterでこれらの画面を作るなら、OLTPエンティティ（トランザクション画面や編集に使うもの）とレポーティングエンティティ（読み取り最適化されたモデル）をきれいに分ける計画を立ててください。実際のフィルタや日付範囲を使ってウェブUIビルダでBI画面をプロトタイプし、ユーザーが実際にクリックするものに基づいてデータモデルを調整します。

1週間の実使用後に次を決めます。簡単な修正で十分ならそのまま反復を続けます。合計の計算が高コストなら、明確なリフレッシュ計画を持つ集計テーブルに投資します。レポーティングが重要かつ重い負荷になってきたら、書き込みを速く保つために別のストアへ移すことを検討します。