PostgreSQL を使用したマテリアライズド ビュー戦略 (2023)

byJack Christensenon 2015 年 4 月 23 日

集計データ、概要データ、計算データを返すクエリは、アプリケーション開発で頻繁に使用されます。これらのクエリの速度が十分でない場合があります。 Memcached または Redis を使用してクエリ結果をキャッシュすることは、これらのパフォーマンスの問題を解決するための一般的なアプローチです。ただし、これらには独自の課題が伴います。外部ツールに手を伸ばす前に、PostgreSQL がクエリ結果をキャッシュするためにどのような技術を提供しているかを検討する価値があります。

簡略化されたアカウント システムのサンプル ドメインを使用して、さまざまなアプローチを検討します。アカウントには多くのトランザクションを含めることができます。トランザクションは事前に記録でき、事後的にのみ有効になります。例えば3 月 9 日に有効となる借方は、3 月 1 日に入力できます。必要な概要データは口座残高です。

作成する テーブル アカウント( 名前 可変長文字 主要な 鍵);作成する テーブル トランザクション( ID シリアル 主要な 鍵、 名前 可変長文字 ない ヌル 参考文献 アカウント の上 アップデート カスケード の上 消去 カスケード、 額 数値(9、2) ない ヌル、 事後 タイムスタンプ ない ヌル);作成する 索引 の上 トランザクション (名前);作成する 索引 の上 トランザクション (事後);

この例では、それぞれ平均 50 件のトランザクションを持つ 30,000 のアカウントを作成します。

すべてのサンプル コードとデータは次の場所で入手できます。ギットハブ。

最適化するクエリは、口座残高を見つけることです。まず、すべての口座の残高を検索するビューを作成します。 PostgreSQLビューは保存されたクエリです。作成されたビューからの選択は、元のクエリからの選択とまったく同じです。つまり、毎回クエリが再実行されます。

作成する ビュー 口座残高 として選択する 名前、 合体する( 和(額) フィルター (どこ 事後 <= 現在のタイムスタンプ）、 0 ) として バランスから アカウント 左 参加する トランザクション 使用して(名前)グループ による 名前;

これは、素晴らしい機能である集約フィルター句を使用していることに注意してください。PostgreSQL 9.4で導入されました。

ここで、負の残高を持つすべての行を選択するだけです。

選択する * から 口座残高 どこ バランス < 0;

OS と PostgreSQL キャッシュをウォームアップするために数回実行した後、このクエリには約 3850 ミリ秒かかります。

複数の解決策を検討していきます。名前空間を維持するために、アプローチごとに個別のスキーマを作成します。

作成する スキーマ マットビュー;作成する スキーマ 熱心な;作成する スキーマ 怠け者;

パフォーマンスを向上させる最も簡単な方法は、マテリアライズドビュー。マテリアライズド ビューは、テーブルに保存されたクエリのスナップショットです。

作成する 現実化した ビュー マットビュー。口座残高 として選択する 名前、 合体する( 和(額) フィルター (どこ 事後 <= 現在のタイムスタンプ）、 0 ) として バランスから アカウント 左 参加する トランザクション 使用して(名前)グループ による 名前;

マテリアライズド ビューは実際にはテーブルであるため、インデックスを作成できます。

作成する 索引 の上 マットビュー。口座残高 (名前);作成する 索引 の上 マットビュー。口座残高 (バランス);

各行から残高を取得するには、マテリアライズド ビューから選択するだけです。

選択する * から マットビュー。口座残高 どこ バランス < 0;

パフォーマンスへの影響は印象的です。マイナス残高のあるすべての口座を取得するのにかかる時間はわずか 13 ミリ秒となり、453 倍の速さになりました。残念ながら、これらの実体化ビューには 2 つの大きな制限があります。まず、それらはオンデマンドでのみ更新されます。次に、マテリアライズド ビュー全体を更新する必要があります。単一の古い行だけを更新する方法はありません。

-- すべての行を更新しますリフレッシュする 現実化した ビュー マットビュー。口座残高;

古いデータが許容される可能性がある場合、これらは優れたソリューションです。しかし、データが常に最新である必要がある場合、それは解決策ではありません。

次のアプローチは、行を無効にする変更が発生するたびに積極的に更新されるテーブルにクエリを具体化することです。それを行うことができますトリガー。トリガーは、挿入や更新などのイベントが発生したときに実行されるコードの一部です。

まず、実体化された行を保存するテーブルを作成します。

作成する テーブル 熱心な。口座残高( 名前 可変長文字 主要な 鍵 参考文献 アカウント の上 アップデート カスケード の上 消去 カスケード、 バランス 数値(9、2) ない ヌル デフォルト 0);作成する 索引 の上 熱心な。口座残高 (バランス);

今、私たちはあらゆる方法を考える必要があります。口座残高古くなってしまう可能性があります。

アカウントの挿入時に、口座残高新しい口座の残高がゼロになるように記録します。

作成する 関数 熱心な。アカウントの挿入() 戻り値 引き金 安全 定義する 言語 plpgsqlとして $$ 始める 入れる の中へ 熱心な。口座残高(名前) 価値観(新しい。名前); 戻る 新しい; 終わり;$$;作成する 引き金 アカウントの挿入 後 入れる の上 アカウント のために それぞれ 行 実行する 手順 熱心な。アカウントの挿入();

の構文関数の作成そしてトリガーを作成するかなり広範囲にわたっています。詳細については、ドキュメントを参照してください。しかし、要約した説明は次のとおりです。関数を作成します。熱心なアカウント挿入作成したユーザーの権限で実行されるトリガー関数として (セキュリティ定義者）。挿入トリガー関数内では、新しい新しいレコードを保持する変数です。

アカウントの外部キーは次のように宣言されているため、アカウントの更新と削除は自動的に処理されます。更新カスケード時 削除カスケード時。

トランザクションの挿入、更新、および削除にはすべて、アカウント残高が無効になるという共通点があります。したがって、最初のステップは、アカウント残高の更新関数を定義することです。

作成する 関数 熱心な。リフレッシュアカウント残高(_名前 可変長文字) 戻り値 空所 安全 定義する 言語 SQLとして $$ アップデート 熱心な。口座残高 セット バランス= ( 選択する 和(額) から トランザクション どこ 口座残高。名前=トランザクション。名前 そして 事後 <= 現在のタイムスタンプ ) どこ 名前=_名前;$$;

次に、を呼び出すトリガー関数を作成できます。リフレッシュアカウント残高トランザクションが挿入されるたびに。

作成する 関数 熱心な。トランザクション挿入() 戻り値 引き金 安全 定義する 言語 plpgsqlとして $$ 始める 実行する 熱心な。リフレッシュアカウント残高(新しい。名前); 戻る 新しい; 終わり;$$;作成する 引き金 熱心なトランザクション挿入 後 入れる の上 トランザクション のために それぞれ 行 実行する 手順 熱心な。トランザクション挿入();

実行するPL/pgSQL で結果を気にしないクエリを実行する方法です。

トランザクションの削除の場合、変数のみを取得します。古いの代わりに新しい行。古い行の前の値を保存します。

作成する 関数 熱心な。トランザクション削除() 戻り値 引き金 安全 定義する 言語 plpgsqlとして $$ 始める 実行する 熱心な。リフレッシュアカウント残高(古い。名前); 戻る 古い; 終わり;$$;作成する 引き金 熱心なトランザクション削除 後 消去 の上 トランザクション のために それぞれ 行 実行する 手順 熱心な。トランザクション削除();

トランザクションを更新する場合、トランザクションが属するアカウントが変更された可能性を考慮する必要があります。私たちが使用するのは、古いそして新しい行の値を調べて、どの口座残高が無効になっており更新する必要があるかを判断します。

作成する 関数 熱心な。トランザクション更新() 戻り値 引き金 安全 定義する 言語 plpgsqlとして $$ 始める もし 古い。名前!=新しい。名前 それから 実行する 熱心な。リフレッシュアカウント残高(古い。名前); 終わり もし; 実行する 熱心な。リフレッシュアカウント残高(新しい。名前); 戻る 新しい; 終わり;$$;作成する 引き金 熱心なトランザクション更新 後 アップデート の上 トランザクション のために それぞれ 行 実行する 手順 熱心な。トランザクション更新();

最後に、これをすべて設定したら、口座残高テーブル。

-- 残高行を作成します。入れる の中へ 熱心な。口座残高(名前)選択する 名前 から アカウント;-- 残高行を更新します。選択する 熱心な。リフレッシュアカウント残高(名前)から アカウント;

マイナスの口座残高をクエリするには、単にアカウント残高テーブル。

選択する * から 熱心な。口座残高 どこ バランス < 0;

これはマテリアライズド ビューと同様に非常に高速です (13 ミリ秒 / 453 倍高速)。ただし、トランザクションが変更されても新鮮さが保たれるという利点があります。残念ながら、この戦略は 1 つの重要な要件、つまり時間の経過による行の無効化を考慮していません。

以前の解決策は悪くありませんでした。ただ不完全でした。完全なソリューションでは、実体化された行が古くなった場合に遅延更新します。

熱心な具体化戦略と同様に、最初のステップは、具体化された行を格納するテーブルを作成することです。違いは、有効期限列を追加することです。

作成する テーブル 怠け者。account_balances_mat( 名前 可変長文字 主要な 鍵 参考文献 アカウント の上 アップデート カスケード の上 消去 カスケード、 バランス 数値(9、2) ない ヌル デフォルト 0、 有効期限 タイムスタンプ ない ヌル);作成する 索引 の上 怠け者。account_balances_mat (バランス);作成する 索引 の上 怠け者。account_balances_mat (有効期限);

最初の行を作成します。Lazy.account_balances_matと有効期限として-無限大ダーティとしてマークします。

入れる の中へ 怠け者。account_balances_mat(名前、 有効期限)選択する 名前、 「-無限大」から アカウント;

熱心な戦略で実体化された行を無効にする可能性がある同じデータ変更は、遅延戦略で処理する必要があります。違いは、トリガーは更新のみを行うことです。有効期限-- 実際にはデータを再計算しません。

熱心な戦略と同様に、アカウントの挿入時に、account_balances_mat新しい口座の残高がゼロになるように記録します。しかし、また、有効期限。トランザクションのないアカウントの残高は永久に有効であるため、特別な PostgreSQL 値が提供されます。無限大として有効期限。無限大は他のどの値よりも大きいものとして定義されます。

作成する 関数 怠け者。アカウントの挿入() 戻り値 引き金 安全 定義する 言語 plpgsqlとして $$ 始める 入れる の中へ 怠け者。account_balances_mat(名前、 有効期限) 価値観(新しい。名前、 「無限」); 戻る 新しい; 終わり;$$;作成する 引き金 Lazy_account_insert 後 入れる の上 アカウント のために それぞれ 行 実行する 手順 怠け者。アカウントの挿入();

以前と同様、アカウントの更新と削除は外部キー カスケードによって処理されます。

トランザクションの挿入については、有効期限もし事後トランザクションの額が現在よりも少ない有効期限。これは、更新が絶対に必要な場合にのみ行われることを意味します。アカウントがすでに古いとみなされる場合は、事後新しいレコードの書き込みの IO コストを回避します。

作成する 関数 怠け者。トランザクション挿入() 戻り値 引き金 安全 定義する 言語 plpgsqlとして $$ 始める アップデート 怠け者。account_balances_mat セット 有効期限=新しい。事後 どこ 名前=新しい。名前 そして 新しい。事後 < 有効期限; 戻る 新しい; 終わり;$$;作成する 引き金 Lazy_transaction_insert 後 入れる の上 トランザクション のために それぞれ 行 実行する 手順 怠け者。トランザクション挿入();

トランザクションが挿入されるときとは異なり、トランザクションが更新されるときは、新しいアカウントを計算することはできません。有効期限アカウントのトランザクションを読み取らずに。これにより、アカウント残高を無効にするだけのコストが安くなります。単純に設定していきます有効期限に-無限大、他のすべての値より小さいものとして定義される特別な値。これにより、行は古いものとみなされます。

作成する 関数 怠け者。トランザクション更新() 戻り値 引き金 安全 定義する 言語 plpgsqlとして $$ 始める アップデート アカウント セット 有効期限=「-無限大」 どこ 名前 で(古い。名前、 新しい。名前) そして 有効期限<>「-無限大」; 戻る 新しい; 終わり;$$;作成する 引き金 Lazy_transaction_update 後 アップデート の上 トランザクション のために それぞれ 行 実行する 手順 怠け者。トランザクション更新();

トランザクションの削除では、次の場合に行を無効にします。事後現在の値以下です有効期限。しかし、at が現在の後である場合、有効期限私たちは何もする必要はありません。

作成する 関数 怠け者。トランザクション削除() 戻り値 引き金 安全 定義する 言語 plpgsqlとして $$ 始める アップデート 怠け者。account_balances_mat セット 有効期限=「-無限大」 どこ 名前=古い。名前 そして 古い。事後 <= 有効期限; 戻る 古い; 終わり;$$;作成する 引き金 Lazy_transaction_delete 後 消去 の上 トランザクション のために それぞれ 行 実行する 手順 怠け者。トランザクション削除();

最後から 2 番目のステップは、実体化された行をリフレッシュする関数を定義することです。

作成する 関数 怠け者。リフレッシュアカウント残高(_名前 可変長文字) 戻り値 怠け者。account_balances_mat 安全 定義する 言語 SQLとして $$ と t として ( 選択する 合体する( 和(額) フィルター (どこ 事後 <= 現在のタイムスタンプ）、 0 ) として バランス、 合体する( 分(事後) フィルター (どこ 現在のタイムスタンプ < 事後）、 「無限」 ) として 有効期限 から トランザクション どこ 名前=_名前 ) アップデート 怠け者。account_balances_mat セット バランス = t。バランス、 有効期限 = t。有効期限 から t どこ 名前=_名前 戻る account_balances_mat。*;$$;

この関数は共通テーブル式検索する集約フィルタバランスそして有効期限単一の選択で。次に、結果を使用して更新しますアカウント残高_マット。

最後に、口座残高ビュー。クエリの上部では、新しい行を読み取ります。account_balances_mat。下部では、古い行を読み取り、更新します。

作成する ビュー 怠け者。口座残高 として選択する 名前、 バランスから 怠け者。account_balances_matどこ 現在のタイムスタンプ < 有効期限連合 全て選択する r。名前、 r。バランスから 怠け者。account_balances_mat 腹筋 クロス 参加する 怠け者。リフレッシュアカウント残高(腹筋。名前) rどこ 腹筋。有効期限 <= 現在のタイムスタンプ;

マイナス残高を持つすべての口座を取得するには、単に口座残高ビュー。

選択する * から 怠け者。口座残高 どこ バランス < 0;

初めてクエリを実行するときは、すべてのアカウントの残高をキャッシュするため、約 5900 ミリ秒かかります。後続の実行には約 16 ミリ秒しかかかりません (368 倍高速)。一般に、1 つのクエリで更新される行はほんの一部であるため、クエリの実行時間はそれほど変動するべきではありません。

PostgreSQL の組み込みマテリアライズド ビューは、古いデータが許容される場合に限り、最小限の作業で最高のパフォーマンス向上を実現します。 Eager マテリアライズド ビューは絶対的に最高の読み取りパフォーマンスを提供しますが、時間の経過によって行が古くならない場合にのみ新鮮さを保証できます。遅延マテリアライズド ビューは、熱心なマテリアライズド ビューとほぼ同じくらい優れた読み取りパフォーマンスを提供しますが、あらゆる状況下で鮮度を保証できます。

さらに考慮すべき点は、読み取りが多いワークロードと書き込みが多いワークロードです。ほとんどのシステムは読み取り負荷が高くなります。ただし、書き込み負荷が高い場合は、積極的なマテリアライズド ビューから離れて、遅延を重視することを検討する必要があります。その理由は、熱心なマテリアライズド ビューは書き込みのたびにリフレッシュ計算を行うのに対し、遅延マテリアライズド ビューは読み取り時にのみそのコストを支払うためです。

PostgreSQL マテリアライゼーション戦略により、パフォーマンスが数百倍以上向上します。 Memcachd や Redis でのキャッシュとは対照的に、PostgreSQL マテリアライゼーションは次のことを提供します。酸保証します。これにより、アプリケーション層で処理する必要がある一貫性の問題のカテゴリ全体が排除されます。また、部品が 1 つ減り、システム全体のインフラがシンプルになります。

パフォーマンスの向上とシステムの簡素化には、より高度な SQL をコストとして支払う価値があります。

• この投稿のコード例
• Enterprise Rails の第 12 章ではマテリアライズド ビューについて説明しています
• 2008 PGCon でのマテリアライズド ビューの講演
• Jonathan Gardner マテリアライズド ビューのメモ

(ヘッダー画像は Flickr ユーザー経由t_buchtele)