[解決済み] マテリアライズド・ビューを常に最新の状態に保つにはどうすればよいですか？

2023-04-03 21:46:55

質問

を呼び出す必要がありますね。 REFRESH MATERIALIZED VIEW を呼び出す必要がありますね？これについての議論がウェブ上であまり見つからないことに驚いています。

これを行うにはどうしたらいいでしょうか？

ここの回答の上半分が、私が求めているものだと思います。 https://stackoverflow.com/a/23963969/168143

これには何か危険があるのでしょうか？ビューの更新が失敗した場合、updateやinsertなどを実行したトランザクションはロールバックされるのでしょうか？(これは私が望んでいることです...私はそう思います)

どのように解決するのですか？

<ブロッククオート

を呼び出す必要がありますね。 REFRESH MATERIALIZED VIEW を呼び出す必要がありますね？

はい、PostgreSQL単体では自動的には呼び出されませんので、何らかの方法で行う必要があります。

<ブロッククオート

どのようにすればよいのでしょうか？

多くの方法があります。いくつか例を挙げる前に、以下のことを心に留めておいてください。 REFRESH MATERIALIZED VIEW コマンドは AccessExclusive モードでビューをブロックするので、これが動作している間は SELECT を実行することもできません。

とはいえ、バージョン9.4以降であれば、テーブルの上に渡すのは CONCURRENTLY オプションを指定することができます。

REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv;

これはExclusiveLockを取得し、ブロックしない。 SELECT クエリをブロックしませんが、オーバーヘッドが大きくなります (変更されたデータの量に依存します。変更された行が少ない場合は、より高速になる可能性があります)。それでも、2つの REFRESH コマンドを同時に実行することはできません。

手動でリフレッシュ

考慮すべきオプションのひとつです。特にデータのロードや一括更新の場合 (たとえば、大量の情報/データを長い時間かけてロードするシステム)、データを修正または処理するための操作を最後に行うのが一般的なので、単純に REFRESH 操作を含めることができます。

REFRESH操作のスケジューリング

最初の、そして広く使われているオプションは、リフレッシュを呼び出すために何らかのスケジュールシステムを使うことです。例えば、cronジョブでそのように設定することができます。

*/30 * * * * psql -d your_database -c "REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv"

そして、30分ごとにマテリアライズド・ビューがリフレッシュされます。

考察

このオプションは、特に CONCURRENTLY オプションと一緒に使うのがいいでしょう。ただし、データが常に100%最新でないことを受け入れられる場合に限ります。このオプションの有無にかかわらず CONCURRENTLY の有無にかかわらず REFRESH コマンドはクエリ全体を実行する必要があるので、内部のクエリを実行するのに必要な時間を考慮してから REFRESH .

トリガーを使ったリフレッシュ

もう一つの選択肢は REFRESH MATERIALIZED VIEW をトリガー関数で呼び出すことです。

CREATE OR REPLACE FUNCTION tg_refresh_my_mv()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY my_mv;
    RETURN NULL;
END;
$$;

そして、ビュー上の変更を伴うテーブルでは、そうします。

CREATE TRIGGER tg_refresh_my_mv AFTER INSERT OR UPDATE OR DELETE
ON table_name
FOR EACH STATEMENT EXECUTE PROCEDURE tg_refresh_my_mv();

考察

パフォーマンスと並行性に関して、いくつかの重大な落とし穴があります。

すべての INSERT/UPDATE/DELETE 操作は、クエリを実行しなければなりません (MV を検討している場合、これは遅くなる可能性があります)。
であっても CONCURRENTLY は、1つの REFRESH はまだ別のものをブロックしているので、関連するテーブルの INSERT/UPDATE/DELETE はすべてシリアライズされます。

私が良いアイデアとして考えられる唯一の状況は、変更が本当にまれである場合です。

LISTEN/NOTIFY を使用してリフレッシュする

前のオプションの問題は、それが同期であり、各操作で大きなオーバーヘッドを課すということです。これを改善するために、以前のようにトリガーを使用することができますが、そのトリガーは NOTIFY 操作 :

CREATE OR REPLACE FUNCTION tg_refresh_my_mv()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NOTIFY refresh_mv, 'my_mv';
    RETURN NULL;
END;
$$;

ということで、接続を維持するアプリケーションを作り、そのアプリケーションで LISTEN 操作を呼び出す必要性を識別するために REFRESH . これをテストするために使える素晴らしいプロジェクトのひとつが pgsidekick このプロジェクトでは、シェルスクリプトを使って LISTEN をスケジュールすることができます。 REFRESH としています。

pglisten --listen=refresh_mv --print0 | xargs -0 -n1 -I? psql -d your_database -c "REFRESH MATERIALIZED VIEW CONCURRENTLY ?;"

あるいは pglater (また pgsidekick を呼び出さないようにするためです。 REFRESH を非常に頻繁に使用します。例えば、次のようなトリガーを使って、それを REFRESH と表示されますが、1分（60秒）以内です。

CREATE OR REPLACE FUNCTION tg_refresh_my_mv()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NOTIFY refresh_mv, '60 REFRESH MATERIALIZED VIEW CONCURRENLTY my_mv';
    RETURN NULL;
END;
$$;

というわけで、これは REFRESH を 60 秒以内に呼び出したり、また NOTIFY を60秒以内に何度も繰り返すと REFRESH は一度だけ起動されます。

考察

cronオプションと同様に、このオプションも、多少古いデータでも我慢できる場合にのみ使用するとよいでしょう。 REFRESH が本当に必要なときだけ呼び出されるので、オーバーヘッドが少なく、また、必要なときに近いタイミングでデータが更新されるという利点があります。

OBS: 私はまだコードと例を実際に試していないので、もし誰かが間違いやタイプミスを見つけたり、試してみてうまくいったり（あるいはいかなかったり）したら、教えてください。