マテリアライズド ビューから連続集計まで: リアルタイム分析による PostgreSQL の強化

ユーザーがリアルタイムのデータ分析にアクセスできるようにすることは、多くの最新アプリケーションの重要な機能です。お気に入りのSaaSプラットフォームを使用しているところを想像してください。おそらく、リアルタイムのデータと履歴の分析情報を表示する直感的なダッシュボードがあるでしょう。おそらく、プラットフォームを操作して、カスタマイズされたレポートを作成し、詳細な指標を調査し、数週間または数か月にわたる傾向を視覚化することができます。

ユーザーとしては、このプラットフォームが遅いことは決して望まないでしょう。これは、これらの製品を支えるデータベースが、複雑な分析クエリなど、大量のデータに対するクエリを高速に実行する必要があることを意味します。

その間PostgreSQL は現在開発者の間で最も愛されているデータベースです、大量のデータのクエリが高速であることでは知られていません。ただし、心配しないでください。Postgresのツールボックスには常にツールが含まれています。最も優れたものの 1 つはマテリアライズド ビューです。

PostgreSQL マテリアライズド ビューとは何ですか?

マテリアライズ技術に基づいて、PostgreSQL マテリアライズド ビューは一般的に実行されるクエリを事前計算し、結果をテーブルとして保存します。ビューが参照されるたびに基になるクエリを実行する標準の PostgreSQL ビューとは異なり、マテリアライズド ビューはソース クエリの結果をデータベースに保持します。これの優れた点は、データベースがクエリを実行するたびに実行する必要がないことです。結果はすでにディスク上でアクセス可能であり、クエリに対する応答がはるかに速く得られます。

これは、計算にリソースを大量に消費するクエリの応答を最適化する優れた方法です。たとえば、大量のデータ、集計、または複数の結合の処理が含まれる可能性のあるクエリです。

マテリアライズド ビューの操作は非常に簡単です。ビューを作成するには、 CREATE MATERIALIZED VIEWステートメントと選択したクエリを使用します。

マテリアライズド ビューが作成されたら、通常のPostgreSQLテーブルとしてクエリを実行できます。

 CREATE MATERIALIZED VIEW customer_orders AS SELECT customer_id, COUNT(*) as total_orders FROM orders GROUP BY customer_id;

 -- Query the materialized view SELECT * FROM customer_orders;

このマテリアライズド ビューは、更新するまですぐに古くなります。ベース テーブルに新しいデータを追加している (またはデータを更新または削除している) 場合でも、マテリアライズド ビューにはそれらの変更が自動的に含まれません。作成時のスナップショットです。マテリアライズド・ビューを更新するには、 REFRESH MATERIALIZED VIEWを実行する必要があります。

 REFRESH MATERIALIZED VIEW customer_orders;

この最後の点 (更新の処理方法) は、次のセクションで説明するように、マテリアライズド ビューのアキレス腱です。

リアルタイム分析についてはどうですか? PostgreSQL マテリアライズド ビューの制限事項

すでに述べたように、PostgreSQL マテリアライズド ビューは、頻繁に実行されるクエリ、特に大量のデータを対象とするクエリを高速化するための強力なツールです。ただし、マテリアライズド ビューには、理想的とは言えない側面が 1 つあります。マテリアライズド ビューを最新の状態に保つには、更新する必要があります。

この 1 つの問題により、次の 3 つの重要な制限が生じます。

更新は非効率的であり、計算コストが高くなります

マテリアライズド ビューを更新すると、データセット全体に対してクエリが再計算されます。内部的には、更新を実行すると、古い実体化されたデータが削除され、新しい再実体化されたデータに置き換えられます。実装する増分更新(変更されたデータのみが更新される場合) 集約プロセスははるかに効率的になりますが、一貫したリレーショナル データベース内で正しく実装するのは困難です。スクリプトを追加することで回避策が可能な場合もありますが、特に複雑なクエリの場合や遅れてデータが到着する場合には、簡単とは程遠いです。

更新は自動的には実行されません

前述したように、マテリアライズド ビューには最新のデータが自動的に組み込まれません。 REFRESH MATERIALIZED VIEWを実行して更新する必要があります。運用設定で手動更新を実行することは現実的ではありません。より現実的な設定は、更新を自動化することです。

残念ながら、マテリアライズド ビューには自動更新機能が組み込まれていないため、PostgreSQL でマテリアライズド ビューの自動更新スケジュールを作成するには、何らかのスケジューラが必要です。これは、拡張機能を使用してデータベース内で処理することも、cron などのスケジューラーを使用してデータベース外で処理することもできます。ただし、更新にはコストがかかり、時間がかかるため、なんとかなっています。ビューを十分な速度で更新できない状況に陥ることが非常に簡単です。

マテリアライズド ビューに最新の結果が表示されない

マテリアライズド ビューの静的な性質の結果、クエリを実行すると、最後の更新以降に追加または変更されたデータが失われます (更新がスケジュールに従って行われた場合でも)。スケジュール ウィンドウが 1 時間に設定されている場合、集計時間は最大 1 時間に、更新を実行する実際の時間を加えたものになります。しかし、今日の多くのアプリケーションは、データの継続的なストリームの取り込みを暗示しており、多くの場合、これらのアプリケーションは、ビューのクエリ時に正確な情報を取得していることを確認するために、ユーザーに最新の結果を提供する必要があります。

マテリアライズド ビューがこれらの制限によって制約されるのは残念です。新しいデータが頻繁に受信されるライブ データセットから SaaS プラットフォームを構築している場合、マテリアライズド ビューは完全に破棄する必要がありますか?

答えはいいえだ。 Timescale では、マテリアライズド ビューを効果的に強化して最新のアプリケーションにより適したものにするソリューション、つまり連続集計を構築しました。

継続的な集計への対応: リアルタイム分析のための自動更新を備えたマテリアライズド ビュー

マテリアライズド ビューが単なる静的なスナップショットではなく、動的かつ効率的に更新される世界を想像してみてください。他に何も心配することなく、求めているクエリ パフォーマンスの向上にアクセスできます。さて、Timescale の連続集計について説明したようです。

連続集計 (TimescaleDB 拡張機能を介してすべての PostgreSQL データベースで利用可能、Timescale プラットフォームを介して AWS で利用可能) は、効率的な自動更新機能とリアルタイム要素で強化されたマテリアライズド ビューです。見た目も操作感もマテリアライズド ビューとほぼ同じですが、次のことが可能です。

• 更新ポリシーによる自動更新
• より効率的な更新プロセス: 更新を実行すると、最後の更新以降に変更されたデータのみが処理されます。
• 最新の結果、マテリアライズド ビューを活用できるユースケースの拡大 (リアルタイム分析、ライブ ダッシュボード、レポートなど)

更新を自動化してリソース効率を高める

連続集計の作成は、マテリアライズド ビューの作成と非常に似ています (また、通常の PostgreSQL テーブルとしてクエリを実行することもできます)。

 CREATE MATERIALIZED VIEW hourly_sales WITH (timescaledb.continuous) AS SELECT time_bucket(INTERVAL '1 hour', sale_time) as hour, product_id, SUM(units_sold) as total_units_sold FROM sales_data GROUP BY hour, product_id;

ただし、マテリアライズド ビューとは異なり、リフレッシュ ポリシーの作成は簡単です。データベース内で更新間隔を簡単に定義できるため、継続的な集計が自動的かつ定期的に更新されます。

以下の例では、連続集計を 30 分ごとに更新する更新ポリシーを設定します。 end_offsetパラメータは更新されるデータの時間範囲を定義し、 schedule_interval連続集計が更新される頻度を設定します。

 -- Setting up a refresh policy SELECT add_continuous_aggregate_policy('hourly_sales', end_offset => INTERVAL '1 minute', schedule_interval => INTERVAL '30 minutes');

この更新ポリシーが開始されると、単純なマテリアライズド ビューを使用する場合よりもプロセスがはるかに効率的になります。 REFRESH MATERIALIZED VIEWの実行とは異なり、連続集計が更新されるとき、Timescale はすべての古いデータを削除し、それに対して集計を再計算しません。エンジンは、最新の更新期間 (例: 30 分) に対してクエリを実行し、それを追加するだけです。具現化へ。

同様に、この最後の期間中に実行されたUPDATEとDELETEが識別され、それらに含まれるチャンク (パーティション) が再計算されます。 (Timescale に基づいて構築された連続集計ハイパーテーブル、これは自動的にパーティション化された PostgreSQL テーブルです。これは大きな利点であり、データが変更されたときにエンジンがテーブル全体ではなく特定のパーティションのみを再計算できるようになります。)

リアルタイム分析の最新の結果を表示する

しかし、連続集計では、最新の結果を表示するという問題はどのように解決されるのでしょうか?最後の更新後に新しいデータが追加され、連続集計をクエリするとどうなりますか?

この機能を許可するために、次の機能を追加しましたリアルタイム集計機能連続集合体に。 リアルタイム集計が有効な場合で、連続集計をクエリすると、表示される結果は 2 つの部分を組み合わせたものになります。

• 基礎となるマテリアライズド ビュー内のマテリアライズド データ。最後の更新で更新されました。
• 最新の、まだ実体化されていない生データ。まだベース テーブル (正確にはハイパーテーブル) にのみ存在します。

この機能は、マテリアライズド ビューを静的スナップショットから動的エンティティに変換し、保存されたデータが単なる履歴の反映ではなく、基になるデータセットの最新の表現であることを保証します。

連続集計の使用: 例

これですべてがうまく聞こえるかもしれませんが、例を使用すると、さらにわかりやすくなるでしょう (できれば)。

交通機関やライドシェア会社が使用するプラットフォームを想像してみてください。このプラットフォームには、企業が自社のフリートのステータスの概要を確認できるダッシュボードが含まれています。これには、主要な指標の最新ステータスを示す表と、特定の日および週のコンテキスト内で指標がどのように推移しているかを示す 2 つの視覚化が含まれます。

このアプリケーションを強化するには、まず乗り物に関するデータが常に挿入されるハイパーテーブルを作成します。ハイパーテーブルは次のようになります。

 CREATE TABLE rides ( ride_id SERIAL PRIMARY KEY, vehicle_id INT, start_time TIMESTAMPTZ NOT NULL, end_time TIMESTAMPTZ NOT NULL, distance FLOAT NOT NULL, price_paid FLOAT NOT NULL ); SELECT create_hypertable('rides', 'start_time');

ハイパーテーブルは非常に高速でスケーラブルです。このテーブルは、数十億行があってもパフォーマンスを維持します。

ライブ概要を提供してテーブルを強化するには、連続集計を使用してデータを 30 分ごとにバケット化します。これにより、プロセスの高速性と応答性が維持されます。

 -- Create continuous aggregate for live overview CREATE MATERIALIZED VIEW live_dashboard WITH (timescaledb.continuous, timescaledb.materialized_only=false)) AS SELECT vehicle_id, time_bucket(INTERVAL '30 minute', start_time) as minute, COUNT(ride_id) as number_of_rides, AVG(price_paid) as average_price FROM rides GROUP BY vehicle_id, minute;

 -- Set up a refresh policy SELECT add_continuous_aggregate_policy('live_dashboard', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL '15 minute');

前のコードでは、 end_offsetパラメータにより、アグリゲートが最新のデータをすぐに更新しようとしないようにし、データ到着の遅れに対応するためのバッファ時間を確保しています。 end_offsetを10 minutesに設定すると、アグリゲートは少なくとも 10 分前のデータを更新し、データ流入のわずかな遅延によって更新を見逃すことがなくなります。実際の使用例では、データ パイプラインで観察される平均遅延に基づいてこの値を調整します。

日次ビューを提供する視覚化を強化するには、2 番目の連続集計を作成します。このグラフでは、データが時間単位で表示されているため、前のグラフのように分単位の粒度は必要ありません。

 -- Create continuous aggregate for daily overview CREATE MATERIALIZED VIEW hourly_metrics WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT vehicle_id, time_bucket(INTERVAL '1 hour', start_time) as hour, COUNT(ride_id) as number_of_rides, SUM(price_paid) as total_revenue FROM rides WHERE start_time > NOW() - INTERVAL '1 day' GROUP BY vehicle_id, hour;

 -- Define refresh policy SELECT add_continuous_aggregate_policy('hourly_metrics', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL `1 hour`);

最後に、週次ビューを提供するグラフを強化するために、もう 1 つの連続集計を作成し、今回はデータを日ごとに集計します。

 -- Create continuous aggregate to power chart with weekly overview CREATE MATERIALIZED VIEW daily_metrics WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT vehicle_id, time_bucket(INTERVAL '1 day', start_time) as day, COUNT(ride_id) as number_of_rides, SUM(price_paid) as total_revenue FROM rides WHERE start_time > NOW() - INTERVAL '1 week' GROUP BY vehicle_id, day;

 -- Define refresh policy SELECT add_continuous_aggregate_policy('daily_metrics', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL '1 day);

PS 連続集計を定義するエクスペリエンスをさらに効率的にするには、 タイムスケールに階層的連続集計が導入されましたTimescaleDB 2.9 では。連続集計に慣れたら、他の連続集計の上に連続集計の作成を開始できます。たとえば、前の例では、分単位の集計の上に時間単位の集計を定義することもできます。

結論

PostgreSQL がもともと大規模なライブ データセットを処理する必要があるアプリケーション向けに構築されたものではなかったとしても、ご想像のとおり、この種のワークロードは現在どこにでも存在します。ただし、PostgreSQL には、このタスクを支援する機能が付属しています。マテリアライズド ビューは、クエリ結果を事前に計算し、高速に取得できるようにディスクに保存できるため、最も強力です。

ただし、マテリアライズド ビューには 3 つの重要な制限があります。まず、リフレッシュのトリガーは計算効率が非常に悪いです。第 2 に、これらの自動更新の設定さえもシームレスなプロセスではありません。 3 番目に、マテリアライズド ビューには、最後の更新以降に追加または変更されたデータが除外されるため、最新の結果が表示されません。

これらの制限により、マテリアライズド ビューは多くの最新アプリケーションにとって非現実的なソリューションになります。これを解決するために、私たちは連続集計を構築しました。これらは PostgreSQL マテリアライズド ビューであり、更新ポリシーを簡単に定義できるため、更新が自動的に行われます。これらの更新も増分であるため、はるかに効率的です。最後に、連続集計を使用すると、実体化されたデータと、前回の更新以降に追加および変更された生データを結合できるため、確実に最新の結果のみを取得できます。

ハードウェア上で PostgreSQL を実行している場合は、次の方法で連続集計にアクセスできます。 TimescaleDB 拡張機能のインストール。 AWS を使用している場合は、Timescale プラットフォームをチェックしてください。最初の 30 日間は無料です。

カルロタ・ソトとマット・アリエが執筆。