PostgreSQL ビューとマテリアライズド ビューの仕組みとそれらが TimescaleDB 連続集計に与える影響 (2023)

大学を卒業してすぐ、私は起業を目指して、友人5人とキャンパス近くの家に引っ越しました。家は少し荒れていましたが、家賃は安く、人々は素晴らしかったです。

私はそこに数年間住んでいましたが、私たちは伝統を築き上げました。誰かが引っ越しするときは必ず、そのグループに 3 つの知恵を共有するようお願いするのです。ばかばかしいものもあれば、真剣なものもあれば、奥深いものもあり、当時は PostgreSQL やマテリアライズド ビューとほとんど関連していないようでしたが、私の心に残っているものが 1 つありました。私の友人のジェイソンは博士号を取得したばかりです。応用物理学の博士号を取得した彼は、自分が学んだ知恵は「自分の無知を無駄にしないこと」だと語った。一度何かを学ぶと、それが当然のことだと思ってしまうため、暗黙知を克服して単純だが重要な質問をすることが非常に困難になると彼は説明しました。

数か月前、私はここタイムスケールでエンジニアリング教育を管理する新しい役割を始めました。そして、より多くのことを教えるようになったので、ジェイソンのアドバイスはこれまで以上に意味のあるものになりました。教えるということは、無駄にされた無知を取り戻すことです。それは、何年もそのことに取り組んでいる場合でも、最初に何かを学んだときのことを常に思い出すことです。最初に学んだときは啓示のように感じられたことも、現場で活動を続けると普通に感じられます。

そのため、私が最も熱心に取り組んできたことは、教えるのが最も難しい場合があることがよくあります。連続集計は TimescaleDB の最も人気のある機能の 1 つであり、私が設計を手伝ったこともあり、私が最も誇りに思っている機能の 1 つです。

私たちは最近、あることに取り組んでいました連続集計の刷新そして、変更について話し合っているときに、連続集計について説明するたびに、PostgreSQL ビューとマテリアライズド ビューに関するすべてのコンテキストを頭の中で説明してきたことに気づきました。

私は幸運なことに、試行錯誤を通じてこれを学ぶことができました。私は、ビューとマテリアライズド ビューについて学習し、それらがどのように機能するのか、どのように機能しないのかをすべて理解する必要がある問題に直面しました。Timescale に入社したときに、その経験を設計に活かすことができましたが、誰もがそのような贅沢を持っているわけではありません。

ビューとマテリアライズド ビューの概要

また、ここですべてを説明するつもりはありませんが、次のようなものがあることを知っておいてください。会社テーブルと株式リアルタイムハイパーテーブルは次のように定義されます。

CREATE TABLE company (シンボルテキスト NOT NULL、名前テキスト NOT NULL);CREATE TABLEstocks_real_time (タイムゾーンのタイムスタンプ NOT NULL、シンボルテキスト NOT NULL、価格倍精度、day_volume 整数);CREATE INDEX ON Stocks_real_time (シンボル、時刻); SELECT create_hypertable('stocks_real_time', 'time');

それを設定したら、次のことができますデータをインポートする必要に応じて、残りの部分も一緒に進めてください。

PostgreSQL ビューとは何ですか?なぜそれらを使用する必要があるのでしょうか?

このデータセットを使って調査したいことの 1 つは、会社の名前を取得できることです。お気付きのとおり、名前列は会社テーブルに結合できます。株式リアルタイム上のテーブルシンボル列なので、次のようにクエリを実行できます。

CREATE VIEWstocks_company AS SELECT s.symbol、s.price、s.time、s.day_volume、c.name FROMstocks_real_time s INNER JOIN company c ON s.symbol = c.symbol;

ビューを作成したら、別のクエリでそれを参照できます。

SELECT シンボル、価格 FROMstocks_company WHERE 時刻 >= '2022-04-05' および時刻 <'2022-04-06';

しかし、実際に内部では何をしているのでしょうか?前に述べたように、ビューは保存されたクエリのエイリアスとして機能するため、PostgreSQL はビューを置き換えます。株式会社定義されたクエリを使用して、結果のクエリ全体を実行します。つまり、株式会社ビューは次と同じです。

SELECT シンボル、価格 FROM (SELECT s.symbol, s.price, s.time, s.day_volume, c.name FROMstocks_real_time s INNER JOIN company c ON s.symbol = c.symbol) sc WHERE time >= '2022- 04-05' および時刻 <'2022-04-06';

ビューを、定義したときと同じクエリで手動で置き換えました。

それらが同じであるとどうやって判断できるのでしょうか?の説明するこのコマンドは、PostgreSQL がクエリをどのように実行するかを示しており、これを使用して、ビューへのクエリと副選択でクエリを実行するだけのクエリが同じ出力を生成するかどうかを確認できます。

注意してください、私はそれを知っています説明する計画は最初は少し恐ろしいように思えるかもしれません。あまり詳しくなくてもわかるように作ってみました説明するこの投稿を理解するための計画など、読みたくない場合は読み飛ばしてください。

そして両方を実行すると:

EXPLAIN (ANALYZE ON、BUFFERS ON) SELECT シンボル、価格 FROMstocks_company WHERE time >= '2022-04-05' and time <'2022-04-06';--ANDEXPLAIN (ANALYZE ON、BUFFERS ON) SELECT シンボル、価格FROM (SELECT s.symbol, s.price, s.time, s.day_volume, c.name FROMstocks_real_time s INNER JOIN company c ON s.symbol = c.symbol) sc WHERE time >= '2022-04-05'時刻 <'2022-04-06';

どちらも同じクエリ プランを生成していることがわかります (タイミングはわずかに異なる可能性がありますが、繰り返し実行すると平準化されます)。

ハッシュ結合 (コスト = 3.68..16328.94 行 = 219252 幅 = 12) (実際の時間 = 0.110..274.764 行 = 437761 ループ = 1) ハッシュ条件: (s.symbol = c.symbol) バッファー: 共有ヒット = 3667 - > _hyper_5_2655_chunk で _hyper_5_2655_chunk_stocks_real_time_time_idx を使用したインデックス スキャン (コスト = 0.43..12488.79 行 = 438503 幅 = 12) (実際の時間 = 0.057..125.607 行 = 437761 ループ = 1) インデックス条件: (("time" >=) '2022- 04-05 00:00:00+00'::タイム ゾーン付きのタイムスタンプ) AND ("time" < '2022-04-06 00:00:00+00'::タイム ゾーン付きのタイムスタンプ)) バッファ: 共有ヒット=3666 -> ハッシュ (コスト = 2.00..2.00 行 = 100 幅 = 4) (実際の時間 = 0.034..0.035 行 = 100 ループ = 1) バケット: 1024 バッチ: 1 メモリ使用量: 12kB バッファー: 共有ヒット = 1 -> c 社の Seq Scan (コスト = 0.00..2.00 行 = 100 幅 = 4) (実際の時間 = 0.006..0.014 行 = 100 ループ = 1) バッファー: 共有ヒット = 1 計画: バッファー: 共有ヒット = 682計画時間: 1.807 ミリ秒 実行時間: 290.851 ミリ秒(15 行)

プランが加わります会社の関連チャンクに株式リアルタイムハイパーテーブルを作成し、インデックス スキャンを使用して適切な行をフェッチします。しかし、彼らが同じことをしていることを理解するために、ここで何が起こっているのかを正確に理解する必要はありません。

ビューは複雑さを隠す

の参加する私たちの見解では、エイリアスされたクエリは非常に単純です。つまり、エイリアスされたクエリは比較的単純ですが、ビューがより複雑になるにつれて、ユーザーがデータベースにクエリを実行するためのより簡単な方法が非常に役立つことが想像できます。全部書く必要はない結合彼ら自身。 (次のような特別なビューを使用することもできます)データへの安全なアクセスを許可するセキュリティ バリア ビューただし、ここでは説明しきれないほどです。)

残念ながら、複雑さを隠すことも問題になる可能性があります。たとえば、この例では次のことに気づいたかもしれませんし、気づいていないかもしれません。実際には必要ありません参加する！の参加する私たちに名前のコラムから会社テーブルですが、ここで選択しているのはシンボルそして価格からの列。株式リアルタイムテーブル！テーブルに対してクエリを直接実行すると、クエリを回避することで約 2 倍の速度が得られます。参加する:

_hyper_5_2655_chunk で _hyper_5_2655_chunk_stocks_real_time_time_idx を使用したインデックス スキャン (コスト = 0.43..12488.79 行 = 438503 幅 = 12) (実際の時間 = 0.021..72.770 行 = 437761 ループ = 1) インデックス条件: (("time" >= '202) 2-04- 05 00:00:00+00'::タイムゾーン付きのタイムスタンプ) AND ("time" < '2022-04-06 00:00:00+00'::タイムゾーン付きのタイムスタンプ)) バッファ: 共有ヒット=3666Planning : バッファ: 共有ヒット = 10計画時間: 0.243 ミリ秒実行時間: 140.775 ミリ秒

クエリを書き出していたら、必要ないことがわかったかもしれません。参加する（あるいは最初から書いたこともありません）。一方、ビューはその複雑さを隠します。したがって、作業は簡単になりますが、注意しないとパフォーマンスの落とし穴につながる可能性があります。

実際に選択するの名前列の場合、次のようにビューを本来の目的に沿って使用していると言えます。

SELECT 名前、価格、シンボル FROMstocks_company WHERE time >= '2022-04-05' AND time <'2022-04-06';

ビューに関するこのセクションを要約すると、次のようになります。

• ビューは、クエリのエイリアスをデータベースに保存する方法です。
• PostgreSQL は、ビュー名をビュー定義で使用するクエリに置き換えます。

ビューはユーザーの複雑さを軽減するのに適しているため、複雑な内容を書き出す必要がなくなります。結合ただし、過度に使用するとパフォーマンスの問題が発生する可能性があります。また、複雑さを隠すと潜在的なパフォーマンスの落とし穴を特定することが難しくなる可能性があるためです。

あなたが気付くことの一つは、ビューはユーザー インターフェイスを改善することはできますが、パフォーマンスが実際に向上することはありません、実際にクエリを実行するのではなく、エイリアスを付けるだけであるためです。クエリを実行するものが必要な場合は、マテリアライズド ビューが必要になります。

PostgreSQL マテリアライズド ビューとは何か、いつ使用するか

CREATE MATERIALIZED VIEWstocks_company_mat AS SELECT s.symbol、s.price、s.time、s.day_volume、c.name FROMstocks_real_time s INNER JOIN company c ON s.symbol = c.symbol;CREATE INDEX onstocks_company_mat (symbol, time DESC) );stocks_company_mat に INDEX を作成します (時刻 DESC);

また、マテリアライズド ビューにいくつかのインデックスを作成したことにも気づくでしょう (マテリアライズド ビューで作成したものと同じものです)株式リアルタイム）！これはマテリアライズド ビューの優れた点の 1 つです。マテリアライズド ビューは、内部ではクエリの結果を保存する単なるテーブルであるため、インデックスを作成できます (これについては後ほど説明します)。

これで走れるようになりました分析の説明少し異なるクエリで、このキャッシュがクエリにどれだけ役立つかを理解するために、両方のデータで「AAPL」の 4 日間のデータを取得しようとしています。

EXPLAIN (分析オン、バッファー オン) SELECT 名前、価格 FROMstocks_company_mat WHERE time >= '2022-04-05' AND time <'2022-04-09' AND シンボル = 'AAPL';stocks_company_mat のビットマップ ヒープ スキャン (コスト= 1494.93..56510.51 行 = 92196 幅 = 17) (実際の時間 = 11.796..46.336 行 = 95497 ループ = 1) 条件を再確認します: ((symbol = 'AAPL'::text) AND ("time" >= '2022- 04-05 00:00:00+00'::タイム ゾーン付きのタイムスタンプ) AND ("time" < '2022-04-09 00:00:00+00'::タイム ゾーン付きのタイムスタンプ)) ヒープ ブロック: 正確=14632 バッファ: 共有ヒット = 14969 -> Stocks_company_mat_symbol_time_idx のビットマップ インデックス スキャン (コスト = 0.00..1471.88 行 = 92196 幅 = 0) (実際の時間 = 9.456..9.456 行 = 95497 ループ = 1) インデックス条件: ((シンボル) = 'AAPL'::text) AND ("time" >= '2022-04-05 00:00:00+00'::タイムゾーン付きタイムスタンプ) AND ("time" < '2022-04-09 00: 00:00+00'::タイムゾーン付きタイムスタンプ)) バッファ: 共有ヒット=337計画: バッファ: 共有ヒット=5計画時間: 0.102 ミリ秒実行時間: 49.995 ミリ秒EXPLAIN (ANALYZE ON、BUFFERS ON) SELECT 名前、価格 FROMstocks_company WHERE time >= '2022-04-05' AND 時刻 <'2022-04-09' AND シンボル = 'AAPL';ネストされたループ (コスト=919.95..30791.92 行=96944 幅=19) (実際の時間=6.023..75.367)行=95497 ループ=1) バッファ: 共有ヒット=13215 -> c 社のシーケンススキャン (コスト=0.00..2.25 行=1 幅=15) (実際の時間=0.006..0.018 行=1 ループ=1) フィルタ: (シンボル = 'AAPL'::text) フィルターによって削除された行: 99 バッファ: 共有ヒット = 1 -> 追加 (コスト = 919.95..29820.23 行 = 96944 幅 = 12) (実際の時間 = 6.013..67.491 行 = 95497 ループ = 1) バッファー: 共有ヒット = 13214 -> _hyper_5_2655_chunk s_1 のビットマップ ヒープ スキャン (コスト = 919.95..11488.49 行 = 49688 幅 = 12) (実際の時間 = 6.013..22.334 行 = 49224 ループ = 1) 条件を再確認します: ((symbol = 'AAPL'::text) AND ("time" >= '2022-04-05 00:00:00+00'::タイムゾーン付きタイムスタンプ) AND ("time" < '2022-04 -09 00:00:00+00'::タイム ゾーン付きのタイムスタンプ)) ヒープ ブロック: 正確 = 6583 バッファ: 共有ヒット = 6895 (... スペースのために省略) 計画: バッファ: 共有ヒット = 30 計画時間: 0.465 ミリ秒実行時間時間: 78.932ミリ秒

これらの計画を見ると、それが思っているよりも役に立たないことがわかります。少しスピードアップしましたが、実際には、ほぼ同じ量の作業を行っています。どうすればわかりますか？そうですね、ほぼ同じ数の 8KB バッファをスキャンします (「基礎シリーズのレッスン 0詳細については、こちらを参照してください)、同じ数の行をスキャンします。

マテリアライズド ビューのパフォーマンスが実現しない場合

どうしてこれなの？さて、私たちの参加するクエリ内の行数は減らなかったので、マテリアライズド ビュー株式会社マット実際には、それと同じ数の行が含まれています。株式リアルタイムハイパーテーブル！

SELECT (SELECT count(*) FROMstocks_company_mat) as rows_mat、(SELECT count(*) FROMstocks_real_time) as rows_tab;行マット | rows_tab ----------+---------- 7375355 | 7375355

したがって、大きなメリットはありませんが、同じ数の行を再度保存する必要があります。したがって、使用するストレージの量という点では、かなりのコストがかかる割に、ほとんどメリットが得られていません。非常に高価な関数を実行したり、非常に複雑な処理を実行したりする場合、これは大きな利点となる可能性があります。参加するマテリアライズド ビューの定義では定義されていますが、実際はそうではないため、あまり節約にはなりません。

この例で問題となるのは、ここから事態はさらに悪化するということです。ビューまたは具体化されたビューで実行したいことの 1 つは、どこフィルタリングする句だけでなくシンボルでも会社では名前。 (会社の銘柄記号は覚えていないかもしれませんが、名前は覚えています。)名前列は結合した列なので、ビューとマテリアライズド ビューの両方でそのクエリを実行して、何が起こるかを見てみましょう。

EXPLAIN (分析オン、バッファー オン) SELECT 名前、stocks_company_mat からの価格 WHERE time >= '2022-04-05' and time <'2022-04-06' AND name = 'Apple' ;stocks_company_mat でstocks_company_mat_time_idx を使用してインデックス スキャン (コスト=0.43..57619.99 行=92196 幅=17) (実際の時間=0.022..605.268 行=95497 ループ=1) インデックス条件: (("時間" >= '2022-04-05 00:00:00+00) '::タイムゾーン付きのタイムスタンプ) AND ("time" < '2022-04-09 00:00:00+00'::タイムゾーン付きのタイムスタンプ)) フィルター: (name = 'Apple'::text) 行が削除されましたフィルタ別: 1655717 バッファ: 共有ヒット=112577計画: バッファ: 共有ヒット=3計画時間: 0.116 ミリ秒実行時間: 609.040 ミリ秒EXPLAIN (ANALYZE ON、BUFFERS ON) SELECT 名前、stocks_company からの価格 WHERE time >= '2022-04-05' およびtime <'2022-04-06' AND name = 'Apple' ;ネストされたループ (コスト=325.22..21879.02 行=8736 幅=19) (実際の時間=5.642..56.062 行=95497 ループ=1) バッファ: 共有hit=13215 -> c 社の Seq Scan (コスト = 0.00..2.25 行 = 1 幅 = 15) (実際の時間 = 0.007..0.018 行 = 1 ループ = 1) フィルター: (name = 'Apple'::text ) フィルタによって削除された行: 99 バッファ: 共有ヒット = 1 -> 追加 (コスト = 325.22..21540.78 行 = 33599 幅 = 12) (実際の時間 = 5.633..48.232 行 = 95497 ループ = 1) バッファ: 共有ヒット = 13214 -> _hyper_5_2655_chunk s_1 のビットマップ ヒープ スキャン (コスト = 325.22..9866.59 行 = 17537 幅 = 12) (実際の時間 = 5.631..21.713 行 = 49224 ループ = 1) 条件を再確認します: ((symbol = c.symbol) AND ("time" >= '2022-04-05 00:00:00+00'::タイムゾーン付きタイムスタンプ) AND ("time" < '2022-04-09 00:00:00+00'::タイムスタンプタイムゾーンあり)) ヒープブロック: 正確=6583 バッファ: 共有ヒット=6895…計画: バッファ: 共有ヒット=30 計画時間: 0.454 ミリ秒 実行時間: 59.558 ミリ秒

今回は、通常のビューでのクエリの方がはるかに優れています。ヒットするバッファがはるかに少なく、10 倍の速さで戻ります。これは、インデックスを作成したためです。(記号、時間 DESC)マテリアライズドビューの場合はそうではありません(名前、時刻DESC)したがって、完全なスキャンにフォールバックする必要があります。時間インデックスを作成し、一致しない行を削除します。

マテリアライズド ビューのパフォーマンスが良好な場合

結局のところ、行数を削減するこのような株式データに対する非常に一般的な一連のクエリが存在することがわかりました。○ペン-HうーんLうわーC損失クエリ (OHLC) は次のようになります。

CREATE VIEW ohlc_view AS SELECT time_bucket('15 min', time) バケット、シンボル、first(価格、時間)、max(価格)、min(価格)、last(価格、時間) FROMstocks_real_time WHERE time >= '2022- 04-05' および時刻 <'2022-04-06' GROUP BY time_bucket('15 分', 時間), シンボル;CREATE MATERIALIZED VIEW ohlc_mat AS SELECT time_bucket('15 分', 時間) バケット, シンボル, first(price) 、時間)、最大(価格)、最小(価格)、最後の(価格、時間) FROMstocks_real_time GROUP BY time_bucket('15分'、時間)、シンボル ;CREATE INDEX on ohlc_mat(symbol, Bucket);CREATE INDEX ON ohlc_mat （バケツ）;

EXPLAIN (ANALYZE ON、BUFFERS ON) SELECT バケット、シンボル、最初、最大、最小、最後 FROM ohlc_viewWHERE バケット >= '2022-04-05' AND バケット <'2022-04-06';GroupAggregate を最終処理します (コスト = 39098.81. .40698.81 行 = 40000 幅 = 44) (実際の時間 = 875.233..1000.171 行 = 3112 ループ = 1) グループ キー: (time_bucket('00:15:00'::interval, _hyper_5_2655_chunk."time")), _hyper_5_2655_chunk .symbol バッファ: 共有ヒット = 4133、一時読み取り = 2343 書き込み = 6433 -> ソート (コスト = 39098.81..39198.81 行 = 40000 幅 = 92) (実際の時間 = 875.212..906.810 行 = 5151 ループ = 1) ソートキー: (time_bucket('00:15:00'::interval, _hyper_5_2655_chunk."time")), _hyper_5_2655_chunk.symbol ソート方法: クイックソート メモリ: 1561kB バッファ: 共有ヒット = 4133、一時読み取り = 2343 書き込み = 6433 -> 収集(コスト=27814.70..36041.26 行=40000 幅=92) (実際の時間=491.920..902.094 行=5151 ループ=1) 計画されたワーカー: 1 起動されたワーカー: 1 バッファ: 共有ヒット = 4133、一時読み取り = 2343 書き込み = 6433 -> 部分 HashAggregate (コスト = 26814.70..31041.26 行 = 40000 幅 = 92) (実際の時間 = 526.663..730.168 行 = 2576 ループ = 2) グループ キー: time_bucket('00:15:00'::interval, _hyper_5_2655_chunk."time")、_hyper_5_2655_chunk.symbol 計画されたパーティション: 128 バッチ: 129 メモリ使用量: 1577kB ディスク使用量: 19592kB バッファ: 共有ヒット = 4133、一時読み取り = 2343 書き込み = 6433 ワーカー 0: バッチ: 129 メモリ使用量: 1 577kBディスク使用法: 14088kB -> 結果 (コスト=0.43..13907.47 行=257943 幅=28) (実際の時間=0.026..277.314 行=218880 ループ=2) バッファ: 共有ヒット=4060 -> _hyper_5_2655_chunk_stocks_real_time_time_idx を使用した並列インデックス スキャン_hyper_5_2655_chunk (コスト=0.43..10683.19 行=257943 幅=20) (実際の時間=0.025..176.330 行=218880 ループ=2) インデックス条件: (("時間" >= '2022-04-05 00:00:00) +00'::タイム ゾーン付きのタイムスタンプ) AND ("time" < '2022-04-06 00:00:00+00'::タイム ゾーン付きのタイムスタンプ)) バッファー: 共有ヒット=4060計画: バッファー: 共有ヒット= 10計画時間: 0.615 ミリ秒実行時間: 1003.425 ミリ秒

マテリアライズド ビュー:

EXPLAIN (分析オン、バッファー オン) SELECT バケット、シンボル、最初、最大、最小、最後 FROM ohlc_mat WHERE バケット >= '2022-04-05' AND バケット <'2022-04-06';ohlc_mat で ohlc_mat_bucket_idx を使用したインデックス スキャン(コスト = 0.29..96.21 行 = 3126 幅 = 43) (実際の時間 = 0.009..0.396 行 = 3112 ループ = 1) インデックス条件: ((バケット >= '2022-04-05 00:00:00+00) '::タイム ゾーン付きタイムスタンプ) AND (バケット < '2022-04-06 00:00:00+00'::タイム ゾーン付きタイムスタンプ)) バッファー: 共有ヒット = 35 計画: バッファー: 共有ヒット = 6 計画時間: 0.148ミリ秒実行時間: 0.545 ミリ秒

作成したマテリアライズド ビューがあり、特定の時点でクエリが設定されています。

しかし、時間が経ち、たとえば 15 分後にさらにデータを挿入すると、ビューは古くなってしまいます。だったタイムバケット15 分単位で実行されるため、バケットのセット全体が存在しません。

基本的に、マテリアライズド ビューの精度は、キャッシュしているクエリを最後に実行したときと同じになります。走らなければなりませんマテリアライズドビューを更新それらが最新であることを確認します。

一度走ったらマテリアライズドビューを更新最終的には、次のようにマテリアライズド ビューに新しいデータが表示されます。

問題は、リフレッシュビューの作成にはコストがかかる可能性があり、その理由を理解するには、ビューがどのように機能するのか、なぜ古くなってしまうのかについてもう少し理解する必要があります。そしてそれは高価になる可能性があります。

マテリアライズド ビューの仕組み (およびマテリアライズド ビューが古くなった理由)

マテリアライズド ビューがどのように古くなり、リフレッシュが何を行っているかを理解するには、マテリアライズド ビューが内部でどのように動作するかを少し理解するのに役立ちます。基本的に、マテリアライズド ビューを作成すると、テーブルが作成され、そこにクエリからのデータが設定されます。のためにohlc_food私たちが取り組んできたビューでは、以下と同等です。

CREATE TABLE ohlc_tab AS SELECT time_bucket('15 min', time) バケット、シンボル、first(価格、時間)、max(価格)、min(価格)、last(価格、時間) FROMstocks_real_time GROUP BY time_bucket('15 min '、時間)、シンボル;

基になるテーブルにデータを挿入するとどうなるでしょうか?

つまり、具体化されたビューohlc_food作成時に実行されたクエリの結果が保存されています。

Stocks_real_time VALUES (now()、'AAPL'、170.91、NULL) に挿入します。

通常のビュー (ohlc_view) の生データに対して直接クエリを実行するだけなので、最新の状態が維持されます。株式リアルタイム。そして、近くにデータを挿入するだけの場合は、今（）、そしてはるかに古いデータのみをクエリする場合、マテリアライズド ビューは問題がないように見えます。 1 ～ 2 か月前のクエリには変化がありませんが、より最近にクエリを実行しようとすると、データがありません。もっと新しいデータで最新の状態にしたい場合は、次を実行する必要があります。

マテリアル化されたビューを更新 ohlc_mat;

これを行うと、内部で実際に起こっていることは、テーブルから切り捨て (すべてのデータを削除) し、クエリを再度実行してテーブルに挿入することです。

を使用していた場合ohlc_tab上の表の場合、同等の操作は次のようになります。

TRUNCATE TABLE ohlc_tab;INSERT INTO ohlc_tab SELECT time_bucket('15 min', time) バケット、シンボル、first(価格、時間)、max(価格)、min(価格)、last(価格、時間) FROMstocks_real_time GROUP BY time_bucket( 「15 分」、時間)、記号;

(実行すると動作が少し異なります)マテリアライズドビューを更新とともに同時にオプションですが、基本的には常にデータ セット全体に対してクエリが実行されるため、詳細についてはこの投稿の範囲を超えています)。

データベースは、ビューと同様に、実行したクエリを保存します。リフレッシュ何をすべきかを知っているだけで、それは素晴らしいことですが、最も効率的ではありません。データの大部分は変更されませんでしたが、それでもデータセット全体を破棄し、クエリ全体を再実行しました。

PostgreSQL が扱う OLTP データなどを扱っていて、更新や削除がデータ セット全体にランダムに分散している場合には問題ないかもしれませんが、時系列データを扱っている場合にはかなり非効率に思えてきます。書き込みはほとんどが最新の期間に行われます。

要約すると、クエリからの出力が、計算するためにスキャンする必要がある行数よりもはるかに小さいため、マテリアライズド ビューが非常に役立つケースが見つかりました。私たちの場合、それは集合体でした。しかし、マテリアライズド ビューを使用すると、ビューのようにクエリ時にクエリ出力を再実行するのではなく、クエリの出力を保存しているため、データが古くなってしまうことにも気付きました。

マテリアライズド ビューを最新の状態にするには、次のことが必要であることがわかりました。リフレッシュただし、時系列のユースケースの場合、a) 最新の状態にするために頻繁に更新する必要があります (この場合、少なくとも約 15 分ごと)、b) 更新は非効率的です。すべてのデータを削除して再実体化し、おそらく数か月前に遡って、直前の 15 分間から新しい情報を取得します。そしてそれが、私たちが Timescale で連続集計を開発した主な理由の 1 つです。

連続集約がどのように機能するか、およびそれらが最高のビューとマテリアライズド ビューからどのようにインスピレーションを得たか

私たちは、これらのタイプの集計のビューとマテリアライズド ビューの両方でこれらの問題を認識し、時間バケット化された集計が非常に一般的なユース ケースであることを知っていたため、時系列データを扱う人々のニーズにはるかに適したものを開発したいと考えました。私たちは、ビューと具体化されたビューを開発する際に、両方から学ぶように努めました。このセクションでは、連続集計がどのように正確に機能するかを部分的に説明していきます。

基本的に、連続集計を作成するときは、マテリアライズド ビューを作成するときと非常によく似た処理を行っています。そのため、マテリアライズド ビューの作成には、インターフェイスのわずかに変更されたバージョンを使用します。

CREATE MATERIALIZED VIEW ohlc_cont WITH (timescaledb.continuous) AS SELECT time_bucket('15 min', time) バケット、シンボル、first(価格、時間)、max(価格)、min(価格)、last(価格、時間) FROMstocks_real_time GROUP BY time_bucket('15 分', 時間)、シンボル;

これを実行すると、マテリアライズド ビューの場合と非常に似た状況になります。ビューの作成時に存在していたデータはありますが、新しいデータが挿入されると、ビューは古くなります。

継続的な集計を最新の状態に保つには、スケジュールされた集計が必要です。

新しいデータのスケジュールされた集計

マテリアライズド ビューには 2 つの主な問題があり、スケジュールされた集計で解決したいと考えていました。

1. マテリアライズド ビューを最新の状態に保ちたい場合は、手動で更新する必要があります。
2. すべての古いデータに対して不必要にクエリを再実行することは望ましくありません。新しいデータに対してのみ実行する必要があります。

集計をスケジュールするには、継続的な集計ポリシーを作成する必要があります。

SELECT add_continuous_aggregate_policy('ohlc_cont'::regclass, start_offset=>NULL, end_offset=>'15 分'::間隔、schedule_interval=>'5 分'::間隔);

継続的集約ポリシーをスケジュールすると、ポリシーに従って自動的に実行されます。スケジュール間隔私たちが指定しました。この例では、5 分ごとに実行されます。実行すると、すでに実体化されたデータと新しい挿入が確認され、少なくとも 1 つの 15 分のバケットが終了したかどうかが確認されます。存在する場合、次の 15 分間の部分だけでクエリが実行され、結果が連続集計で具体化されます。

これは、ユーザーの介入なしに、連続集計に次の 15 分間のデータが自動的に含まれるようになったということを意味します。

そしてそれははるかに効率的でした。ランニングと違ってマテリアライズドビューを更新古いデータをすべて削除してそれに対して集計を再計算するのではなく、次の 15 分間の期間に対して集計クエリを実行し、それを具体化に追加しました。そして、時間が進むにつれて、これは連続する 15 分間（または、その時間に選択した任意の期間）ごとに発生し続ける可能性があります。タイムバケット連続集計定義では)、新しいデータが入力されてから実体化されます。

これについて注意すべき点の 1 つは、ここでは点線で表されているウォーターマークと呼ばれるものを保存することによって、どこまで実体化したかを追跡していることです。 (注意: この名前は、銀行小切手のウォーターマークではなく、洪水によって引き起こされる最高ウォーターマークにちなんで付けられています。) したがって、スケジュールされた集計が実行される前、ウォーターマークは、具体化したすべてのデータの直後にあります。

これは、次のバケットを見つけて、集計を実行する前にバケットがすべて揃っていることを確認するのに役立ちます。取得したら、透かしを移動します。

したがって、私たちの透かしは、これまで実現した最も遠い点を表しています。

ただし、連続集計はまだ完全には最新ではなく、同じクエリを実行したビューと同じ結果が得られないことに気づくかもしれません。なぜ？

1. スケジュールされた集計では、次のバケットにすべてのデータが含まれるときと、それを具体化するためにジョブが実行されるときとの間に、ある程度のギャップが生じます。
2. デフォルトでは、次のバケットがいっぱいになった場合にのみデータを実体化するため、現在挿入が行われている部分的なバケットが欠落しています。そのバケットの部分的な結果を取得したい場合があります (これは、より大きなバケットを使用している場合に特に当てはまります)。

これに対処するために、リアルタイム ビューを作成しました。

リアルタイムビュー

リアルタイム ビューは、マテリアライズド ビューと通常のビューの長所を組み合わせて、データのより最新のビューを提供します。これらは連続集計のデフォルトであるため、連続集計の作成方法を変更する必要はまったくありません。ただし、連続集計が内部でどのように機能するかについて、前の図でいくつかのことを省略していたことを認めます。

リアルタイムの連続集計には 2 つの部分があります。

1. あ実体化されたハイパーテーブル、すでに計算された集計が保存されます。
2. そして、リアルタイムビュー、これは、実体化されたハイパーテーブルと生のハイパーテーブル (まだ集約されていない領域にある) の両方をクエリし、結果を結合します。

したがって、連続集計のビュー定義を見ると、次のようになります。

CREATE VIEW ohlc_cont AS SELECT _materialized_hypertable_15.bucket、_materialized_hypertable_15.symbol、_materialized_hypertable_15.first、_materialized_hypertable_15.max、_materialized_hypertable_15.min、_materialized_hypertable_15.last FROM _timescaledb_internal._materialized_hypertable_15 WHER E _materialized_hypertable_15.bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(15)) 、'-infinity'::timestamp with timezone)UNION ALL SELECT time_bucket('00:15:00'::interval,stocks_real_time."time") AS バケット、stocks_real_time.symbol、first(stocks_real_time."time"、stocks_real_time .price) AS 最初、max(stocks_real_time.price) AS max、min(stocks_real_time.price) AS min、last(stocks_real_time."time",stocks_real_time.price) AS 最後 FROMstocks_real_time WHEREstocks_real_time."time" >= COALESCE( _timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(15))、'-infinity'::タイムゾーン付きタイムスタンプ) GROUP BY (time_bucket('00:15:00'::interval,stocks_real_time."time"))、stocks_real_time.symbol ;

2 つのクエリを組み合わせたものです。すべてを結合する1 つ目は、バケットがウォーターマークより下にある具体化されたハイパーテーブルからデータを直接選択するだけで、2 つ目は時間列がウォーターマークより上にある集計クエリを実行します。

マテリアライズド ビューと通常のビューの両方の長所を利用して、通常のビューよりもはるかに高速でありながら最新の状態のものを作成する方法がわかります。

すでに実体化されたデータをクエリするだけの場合ほどパフォーマンスは高くありません (ただし、必要に応じてそれを可能にするオプション）しかし、ほとんどのユーザーにとって、過去数か月または場合によっては数年分のデータがすでに実体化されている一方で、クエリが必要となるのは生データの最後の数分または数日だけであるため、それでも大幅な高速化が実現されます。

順序が乱れたデータの無効化

私がすべての図で大きな仮定を立てていることに気づいたかもしれません。私はそれを想定していました全て挿入のうちのは最新の期間に発生したものです。時系列ワークロードの場合、これは次のようになります。たいてい真実。ほとんどデータは時間順に来ます。しかし、ほとんどすべてのことは、とても異なるもの。特に時系列ワークロードの場合、非常に多くのデータが入ってくるため、データの 99% が時系列通りであっても、データの 1% は依然として大量です。

また、単純に挿入 (または更新または削除) を時間の経過とともに蓄積させた場合、集計の結果は大幅に間違ってしまいます。このキャッシュ無効化の問題は、コンピューティングにおいて非常に一般的な問題であり、非常に難しい問題です。 PostgreSQL マテリアライズド ビューは、古いデータをすべて削除し、毎回それを再実体化することでこの問題を解決しますが、それがいかに非効率であるかはすでに述べました。

PostgreSQL のようなデータベースでこの種の問題を解決しようとする多くの人が試みるもう 1 つの方法は、トリガーです。標準トリガーはすべての行に対して実行され、すべての行の集計を更新します。

しかし実際には、行ごとのトリガーをうまく機能させるのは難しく、それでも重大な問題が発生する可能性があります。書き込み増幅つまり、挿入する行ごとに複数回書き込む必要があります。

次回継続集計ジョブを実行するときは、2 つのことを実行する必要があります。次の 15 分間のデータの通常の集計を実行することと、無効化された各領域に対して集計を実行して、その期間にわたる適切な値を再計算することです。

これにより、連続的な集計が行われることに注意してください。最終的には一貫性のある順不同の変更の場合。ただし、リアルタイム ビューでは、最新のデータに対して連続集計の一貫性がより強くなります (内部でビューを使用するため)。

ログ テーブルに結合し、無効化された領域に対してリアルタイムで集計を再実行することで、より強力な一貫性のある集計を作成することもできますが、ユーザーと話し合い、ここでのほとんどのケースでは最終的な整合性で十分であると判断しました。結局のところ、このデータはすでに遅れて到着しています。本質的に、それを行うことによるパフォーマンスへの影響は、一貫性を保証する価値がないと考えています。いずれにせよ、ユーザーが必要に応じて、手動のrefresh_continuous_aggregatesプロシージャこれにより、実体化されたハイパーテーブル内のデータがすぐに更新されます。

データの保持

連続集計で最後に達成したいことは、次のような方法でした。保つ生データをドロップした後の集計データ。これは、PostgreSQL ビューとマテリアライズド ビューの両方では不可能です。ビューの場合、生データを直接処理するため、データをドロップすると集約できないからです。

マテリアライズド ビューの場合はもう少し複雑です。更新を実行するまでは古いデータが保持されますが、更新を実行すると、より最近の期間に追加した新しいデータを取得し、次に古いデータを取得します。データがドロップされます。

連続集計を使用すると、実装がはるかに簡単になります。すでに実体化したデータを変更するときに起動される無効化トリガーについては説明しました。ドロップ イベントを含め、特定の期間より古いイベントは無視されます。

また、データを削除する前に無効化を処理できるため、最も古いものを削除する直前から正しいデータを実体化することができます。データ保持を構成できます継続的集計ポリシーを正しく設定することにより、。

効果がありますか?

したがって、時系列データに適切に機能する一連の適切なトレードオフを試みるために、ビュー、マテリアライズド ビュー、トリガーのマッシュアップ全体を用意しました。そこで問題は、それは機能するのかということです。

それをテストするために、データとポリシーなしで上記の連続集計を再作成し、リフレッシュ_連続_集約この手順では、合計で約 1 か月分のデータが実体化され、リアルタイム ビューの確認には約 30 分かかります。

もし私達連続集計から全期間にわたる集計データをクエリします。、約かかります18ミリ秒よりもわずかに遅いです。マテリアライズド ビュー内の完全にマテリアライズされたデータの場合は 5 ～ 6 ミリ秒、しかし、それはまだです通常のビューにかかる 15 秒よりも 1,000 倍高速、そして私たちは得ますほとんど通常のビューの最新の利点について説明します。私はそのトレードオフにかなり満足しています。

TimescaleDB を初めて使用する場合で、これを試してみたい場合は、次のことをお勧めします。タイムスケールにサインアップする。これは、Timescale を始める最も簡単な方法です。 30 日間は完全に無料で、クレジット カードは必要なく、数秒でデータベースを起動できます。