Amazon Web Services ブログ
Amazon RDS for PostgreSQL で高速リフレッシュ機能を構築する
この記事は CDL によるゲスト投稿です。CDL は自社を次のように紹介しています。「CDL は英国を拠点とする主要なインシュアテック企業であり、Financial Times 誌の業界に影響を与えている高成長の英国企業 Future 100 のリストに掲載されています。保険と金融サービスの分野で強力な実績を残し、そのソリューションは英国で最も収益性の高い保険代理店を支えています。 CDL はシステム上で 700 万件を超える保険契約を取引し、Sainsbury’s Bank、Tesco Bank、Swinton Insurance、Moneysupermarket.com などをクライアントに抱えています」
この記事では、CDL が Amazon RDS for PostgreSQL のマテリアライズドビューログをどう使って高速リフレッシュ機能を開発したかについて説明します。変更を追跡するために構築したものを詳述し、完全リフレッシュに代わる代替手段を示します。これにより、必要な時間が数時間から数秒に短縮されました。また、高速リフレッシュを可能にするためのオープンソースソフトウェアをより広い PostgreSQL コミュニティと共有し、関連するインストールプロセスの概要を示します。
課題
CDL は毎日数百万のトランザクションを処理しています。当時はビジネスインテリジェンス (BI) プラットフォームを Oracle から PostgreSQL に移行することを検討していました。切り替えを実際に行うには、お客様が最新のビジネスインテリジェンスへ引き続きアクセスできるようにするため、リレーショナルデータベースでこの大量の変更を処理し、ほぼリアルタイムでリフレッシュする必要がありました。
PostgreSQL は完全リフレッシュ機能のみを備えています。けれども、Google のサービスレベルアグリーメントでは 15 分ごとにデータをリフレッシュする必要があります。そして CDL は大量の変更を処理していることから、完全リフレッシュプロセスではこのタイムスケール内でこの規模のマテリアライズドビュー (MV) を処理することは不可能でした。当社の最大の MV ログは 150 GB を超えており、完全リフレッシュプロセスでは構築するのに何時間もかかります。またお客様によっては、1 日あたりのビュー数が 150 回を超える方もいらっしゃいます。
当社は、BI ソリューションの MV を使用して、オンライントランザクション処理 (OLTP) レイヤーからのデータを論理的にデータマートに非正規化しています。それにより、クライアントがデータを使用してデータ分析を実行できるようにしています。したがって、高速にリフレッシュできることは、Oracle から PostgreSQL に切り替えたときの CDL の重要な前提条件でした。
高速リフレッシュと完全リフレッシュ
高速リフレッシュでは、基になるテーブルのマテリアライズドビューログを使用して変更を追跡し、最後のリフレッシュ以降の変更のみが MV に適用されます。対照的に、完全リフレッシュプロセスはすべてのデータをリフレッシュします。そのため、大規模なデータセットの場合は必然的に数時間かかる可能性があります。
次の図は、ソリューションで MV を使用する方法を示しています。Attunity Replicate を使用して、OLTP レイヤーから RDS インスタンスにデータを同期します。次に、OLTP ソーステーブルから RDS インスタンスに MV を構築し、リフレッシュスケジュールにより 15 分ごとにジョブをリフレッシュします。
PostgreSQL のプロセスの開発
高速リフレッシュプロセスは、MV プロセスの実行に必要な機能を含む独自のスキーマにインストールされるように設計されています。それには、3 つのデータディクショナリテーブルと 1 つのロールがあります。
次の図の MV ログ作成のワークフローは以下のとおりです。
- ソーステーブルに UUID 列を追加する
- ソーステーブルの MV ログを作成する
- MV ログテーブルにインデックスを追加する
- ソーステーブルにトリガーを作成する
- エントリを
pgmview_logs
テーブルに挿入する
次の図に示す MV 作成のワークフローは以下のとおりです。
- SQL ステートメントを分解する
- 複合 MV テーブルを抽出する
- MV SQL からベーステーブルを作成する
- ソーステーブルに
rowid
を追加する rowid
列にインデックスを追加するpgmviews
テーブルに挿入するpgmviews_details
テーブルに挿入する- MV 上で完全リフレッシュを行う
ユーザーが実行する主な機能は次の 4 つです。
- mv$createMaterializedViewlog – MV ログを作成する
- mv$createMaterializedView – MV を作成する
- mv$removeMaterializedViewLog – MV ログを削除する
- mv$removeMaterializedView – MV を削除する
ソースコード
GitHub リポジトリからソースコードをダウンロードします。次のコードを参照してください。
高速リフレッシュ機能のインストール
高速リフレッシュ機能のインストールは、MODULEOWNER
パラメータで指定されたデータベース内の独自のスキーマに常駐するように設計されています。
MV コードをインストールするには、次の手順を実行します。
- リポジトリをダウンロードしたフォルダに移動し、
module_set_variable.sh
ファイルを編集します。
これは、高速リフレッシュ機能をインストールする場所のすべての変数が格納される場所です。
高速リフレッシュ機能をインストールするのに、SOURCEUSERNAME
/SOURCEPASSWORD
およびMVUSERNAME
/MVPASSWORD
パラメータは必要ありません。そのようなパラメータはテストハーネスのセットアップに使用します。次のコードを参照してください。テストケースで使用するパラメータ設定の例として、データベース
testpoc
およびマスターユーザー名dbamin
を持つ RDS インスタンスpg-tonytest.citnv5igau2v.eu-west-1.rds.amazonaws.com
があります。
インストールパッケージは、スキーマtestpoc
の下に高速リフレッシュ機能をインストールします。
ソーススキーマtestpocsource
があります。これは、ソースデータテーブルがテストハーネスとtestpocmv
のために移動する場所です。後者は、MV が構築されるスキーマです。次のコードを参照してください。 - 実行するスクリプト
runCreateFastRefreshModule.sh
のアクセス許可を変更して、実行します。次のコードを参照してください。このコードの実行には数秒とかからないはずです。
- 完了したら、設定した場所にあるログファイルを確認します。
この記事では、ログファイルは/tmp
にあります。ステータスは下部にあります。次のコード例を参照してください。
データベースの関数を見る
パッケージをインストールしたデータベースに接続すると、MODULEOWNER
の下に関数が表示されるはずです。この記事では、関数は testpoc
の下にあります。
テストハーネスのインストール
次のユースケースは、モジュールビュー (MV) の作成方法を示しています。6 つのテーブルを作成し、いくつかのデータをテーブルに挿入します。以下のすべての手順は、create_test_harness.sh
スクリプトにあり、module_set_variables.sh
で設定された変数を使用します。
- テストデータ用ののスキーマを作成します。次のコードを参照してください。
- MV のスキーマを作成します。次のコードを参照してください。
- テストデータを
sourceusername
として作成します。次のコードを参照してください。
- MV ログを作成します。次のコードを参照してください。
- MV を作成します。次のコードを参照してください。
- MV からデータを選択します。次のコードを参照してください。
テストハーネスの更新
これは、高速リフレッシュプロセスがどのように機能するかを示す非常に単純なテストです。この記事では、ベーステーブル t1
の行を更新し、高速リフレッシュ後にそれが MV にどのように反映されるかを確認します。このテストスクリプトは update_mv_test.sh
です。
- 次のように、テーブル
t1
の行をコードUPDATE testpocsource.t1 set code='yo' where code='hello'
で更新します。 - MV をチェックしデータが変更されていないことを確認します (データがリフレッシュされていないため)。次のコードは、まだ設定が
hello
になっていることを示しています。
- MV で高速リフレッシュを実行して、もう一度確認してください。次のコードは、行が変更されたことを示しています。
t1
テーブルを更新し、ビューを再度リフレッシュして、行をhello
に戻します。次のコードを参照してください。
テストハーネスの取り外し
テストハーネスを削除するには、drop_test_harness.sh スクリプトを実行します。これにより、データベースからデータと MV スキーマが削除されます。次のコードを参照してください。
高速リフレッシュ機能を削除する
データベースから高速リフレッシュ機能を削除するには、dropFastRefreshModule.sh
スクリプトを実行します。これにより、Y
を入力してオブジェクトの削除の続行を確認するように求められます。次のコードを参照してください。
まとめ
この記事では、高速リフレッシュモジュールを確認し、MV を起動して実行する簡単な例を示しました。お客様がリアルタイムのビジネスインテリジェンスにアクセスできるようにするには、独自のソリューションを見つける必要がありました。PostgreSQL で MV ログを使用して高速リフレッシュ機能をどう開発するかをご紹介しましたが、楽しんでいただけましたでしょうか。コードは GitHub リポジトリからダウンロードできます。
このシリーズのパート 2 にご期待ください。パート 2 ではモジュールを詳述し、本番環境でこのソリューションを実行して得られた結果について検討します。
著者について
Tony Mullen は、CDL のプリンシパルデータエンジニアです。彼は主に Oracle でデータベースに 18 年以上携わってきた経験があり、近年では PostgreSQL に取り組んでいます。彼は主要なアプリケーションの Oracle オンプレミスから Amazon RDS PostgreSQL への移行を率いています。また PostgreSQL マンチェスターの交流会を共催し、マンチェスターに PostgreSQL コミュニティを構築することに強い関心を持っています。