这篇文章试图提炼一些关于 PostgreSQL 中的视图和物化视图是什么、它们擅长什么、它们的不足之处以及我们如何从它们中学到的一些经验教训，以使连续聚合成为令人难以置信的时间序列数据分析工具.这篇文章也来自我们正在进行的 PostgreSQL 和 TimescaleDB YouTube 系列的基础，所以，请那些第一次遇到这个的人，不要浪费你的无知
视图和物化视图入门
为了了解 PostgreSQL 视图、物化视图和 TimescaleDB 连续聚合，我们将需要一些数据来演示这些概念并更好地理解它们中的每一个最有用的地方。我使用了我们的入门教程中的数据，因此，如果您愿意，可以继续进行操作（WHERE不过，您可能需要更改子句中的某些日期）。我们的教程处理财务数据，但其中许多见解非常适用。
另外，我不会在这里介绍整个事情，但你应该知道我们有一个company表和一个stocks_real_time超表，定义如下：
CREATE TABLE company (
symbol text NOT NULL,
name text NOT NULL
);
CREATE TABLE stocks_real_time (
time timestamp with time zone NOT NULL,
symbol text NOT NULL,
price double precision,
day_volume integer
);
CREATE INDEX ON stocks_real_time (symbol, time);
SELECT create_hypertable('stocks_real_time', 'time');
设置完成后，您可以导入数据，如果您愿意，您应该能够跟随其余部分。
什么是 PostgreSQL 视图？我为什么要使用它们？
我们可能想用这个数据集探索的一件事是能够获得我们公司的名称。您会注意到该name列仅存在于company表中，它可以连接到该列stocks_real_time上的表，symbol因此我们可以像这样查询：
CREATE VIEW stocks_company AS
SELECT s.symbol, s.price, s.time, s.day_volume, c.name
FROM stocks_real_time s
INNER JOIN company c ON s.symbol = c.symbol;
创建视图后，我可以在另一个查询中引用它：
SELECT symbol, price
FROM stocks_company
WHERE time >= '2022-04-05' and time <'2022-04-06';
但这实际上在做什么呢？正如我之前提到的，视图充当存储查询的别名，因此 PostgreSQL 用stocks_company定义它的查询替换视图并运行完整的结果查询。这意味着对stocks_company视图的查询与以下内容相同：
SELECT symbol, price
FROM (
SELECT s.symbol, s.price, s.time, s.day_volume, c.name
FROM stocks_real_time s
INNER JOIN company c ON s.symbol = c.symbol) sc
WHERE time >= '2022-04-05' and time <'2022-04-06';
我们已经用我们定义它的相同查询手动替换了视图。
我们怎么能说它们是一样的呢？该EXPLAIN命令告诉我们 PostgreSQL 如何执行查询，我们可以使用它来查看对视图的查询和仅在子选择中运行查询的查询是否产生相同的输出。
请注意，我知道EXPLAIN计划最初看起来有点吓人。我试图做到这一点，所以你不需要了解很多关于EXPLAIN计划或类似的东西来理解这篇文章，所以如果你不想阅读它们，请随意跳过它们。
如果我们同时运行：
EXPLAIN (ANALYZE ON, BUFFERS ON)
SELECT symbol, price
FROM stocks_company
WHERE time >= '2022-04-05' and time <'2022-04-06';
--AND
EXPLAIN (ANALYZE ON, BUFFERS ON)
SELECT symbol, price
FROM (
SELECT s.symbol, s.price, s.time, s.day_volume, c.name
FROM stocks_real_time s
INNER JOIN company c ON s.symbol = c.symbol) sc
WHERE time >= '2022-04-05' and time <'2022-04-06';
我们可以看到它们都产生了相同的查询计划（尽管时间可能略有不同，但它们会在重复运行时达到平衡）。
Hash Join (cost=3.68..16328.94 rows=219252 width=12) (actual time=0.110..274.764 rows=437761 loops=1)
Hash Cond: (s.symbol = c.symbol)
Buffers: shared hit=3667
-> Index Scan using _hyper_5_2655_chunk_stocks_real_time_time_idx on _hyper_5_2655_chunk s (cost=0.43..12488.79 rows=438503 width=12) (actual time=0.057..125.607 rows=437761 loops=1)
Index Cond: (("time" >= '2022-04-05 00:00:00+00'::timestamp with time zone) AND ("time" < '2022-04-06 00:00:00+00'::timestamp with time zone))
Buffers: shared hit=3666
-> Hash (cost=2.00..2.00 rows=100 width=4) (actual time=0.034..0.035 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
Buffers: shared hit=1
-> Seq Scan on company c (cost=0.00..2.00 rows=100 width=4) (actual time=0.006..0.014 rows=100 loops=1)
Buffers: shared hit=1
Planning:
Buffers: shared hit=682
Planning Time: 1.807 ms
Execution Time: 290.851 ms
(15 rows)
该计划将 连接company到超表的相关块stocks_real_time并使用索引扫描来获取正确的行。但是你并不需要确切地了解这里发生的事情来了解他们正在做同样的事情。✨编者注：如果您想了解有关 EXPLAIN 的更多信息，我建议您查看几周前我的同事 Feike Steenbergen 举办的Explaining Explain 会议。太棒了！
视图隐藏了复杂性
在JOIN我们的视图中非常简单，这意味着别名查询相对简单，但是您可以想象，随着视图变得越来越复杂，为用户提供一种更简单的方式来查询数据库会很有帮助，他们在哪里不必JOINs自己写。（您还可以使用安全屏障视图等特殊视图来安全地授予对数据的访问权限，但这超出了我们在此所能涵盖的范围！）。
不幸的是，隐藏复杂性也可能是一个问题。例如，您可能已经或可能没有注意到在我们的示例中我们实际上并不需要JOIN! 我们从表中获取列，JOIN但我们只选择和列，它们来自表！如果我们直接在表上运行查询，它可以通过避免：namecompanysymbolpricestocks_real_timeJOIN
Index Scan using _hyper_5_2655_chunk_stocks_real_time_time_idx on _hyper_5_2655_chunk (cost=0.43..12488.79 rows=438503 width=12) (actual time=0.021..72.770 rows=437761 loops=1)
Index Cond: (("time" >= '2022-04-05 00:00:00+00'::timestamp with time zone) AND ("time" < '2022-04-06 00:00:00+00'::timestamp with time zone))
Buffers: shared hit=3666
Planning:
Buffers: shared hit=10
Planning Time: 0.243 ms
Execution Time: 140.775 ms
如果我写出了查询，我可能已经看到我不需要JOIN（或者从一开始就没有写过）。而视图隐藏了这种复杂性。所以他们可以让事情变得更容易，但如果我们不小心，这可能会导致性能缺陷。
如果我们实际上SELECT是name列，那么我们可以说我们更多地使用视图来实现它的含义，如下所示：
SELECT name, price, symbol
FROM stocks_company
WHERE time >= '2022-04-05' AND time <'2022-04-06';
因此，总结本节的观点：
- 视图是一种在数据库中存储查询别名的方法。
- PostgreSQL 会将视图名称替换为您在视图定义中使用的查询。
视图可以很好地降低用户的复杂性，因此它们不必写出 complex JOINs，但如果过度使用它们也会导致性能问题，因为隐藏复杂性会使识别潜在的性能缺陷变得更加困难。
您会注意到的一件事是视图可以改进用户界面，但它们不会真正提高性能，因为它们实际上并不运行查询，它们只是给它起别名。如果你想要运行查询的东西，你需要一个物化视图。
什么是 PostgreSQL 物化视图以及何时使用它们
当我创建物化视图时，它实际上运行查询并存储结果。本质上，这意味着物化视图充当查询的缓存。缓存是提高各种计算系统性能的常用方法。我们可能会问的问题是：在这里会有帮助吗？因此，让我们尝试一下，看看效果如何。创建物化视图非常简单，我只需在MATERIALIZED创建视图命令中添加关键字即可：
CREATE MATERIALIZED VIEW stocks_company_mat AS
SELECT s.symbol, s.price, s.time, s.day_volume, c.name
FROM stocks_real_time s INNER JOIN company c ON s.symbol = c.symbol;
CREATE INDEX on stocks_company_mat (symbol, time DESC);
CREATE INDEX on stocks_company_mat (time DESC);
您还会注意到我在物化视图上创建了一些索引（与我在 上创建的索引相同stocks_real_time）！这是关于物化视图的一个很酷的事情，您可以在它们上创建索引，因为在底层它们只是存储查询结果的表（我们将在稍后解释）。
现在我可以运行EXPLAIN ANALYZE一个稍微不同的查询，我试图在这四天中获取“AAPL”的数据，以了解这种缓存对我们的查询有多大帮助：
EXPLAIN (ANALYZE ON, BUFFERS ON) SELECT name, price FROM stocks_company_mat WHERE time >= '2022-04-05' AND time <'2022-04-09' AND symbol = 'AAPL';
Bitmap Heap Scan on stocks_company_mat (cost=1494.93..56510.51 rows=92196 width=17) (actual time=11.796..46.336 rows=95497 loops=1)
Recheck Cond: ((symbol = 'AAPL'::text) AND ("time" >= '2022-04-05 00:00:00+00'::timestamp with time zone) AND ("time" < '2022-04-09 00:00:00+00'::timestamp with time zone))
Heap Blocks: exact=14632
Buffers: shared hit=14969
-> Bitmap Index Scan on stocks_company_mat_symbol_time_idx (cost=0.00..1471.88 rows=92196 width=0) (actual time=9.456..9.456 rows=95497 loops=1)
Index Cond: ((symbol = 'AAPL'::text) AND ("time" >= '2022-04-05 00:00:00+00'::timestamp with time zone) AND ("time" < '2022-04-09 00:00:00+00'::timestamp with time zone))
Buffers: shared hit=337
Planning:
Buffers: shared hit=5
Planning Time: 0.102 ms
Execution Time: 49.995 ms
EXPLAIN (ANALYZE ON, BUFFERS ON) SELECT name, price FROM stocks_company WHERE time >= '2022-04-05' AND time <'2022-04-09' AND symbol = 'AAPL';
Nested Loop (cost=919.95..30791.92 rows=96944 width=19) (actual time=6.023..75.367 rows=95497 loops=1)
Buffers: shared hit=13215
-> Seq Scan on company c (cost=0.00..2.25 rows=1 width=15) (actual time=0.006..0.018 rows=1 loops=1)
Filter: (symbol = 'AAPL'::text)
Rows Removed by Filter: 99
Buffers: shared hit=1
-> Append (cost=919.95..29820.23 rows=96944 width=12) (actual time=6.013..67.491 rows=95497 loops=1)
Buffers: shared hit=13214
-> Bitmap Heap Scan on _hyper_5_2655_chunk s_1 (cost=919.95..11488.49 rows=49688 width=12) (actual time=6.013..22.334 rows=49224 loops=1)
Recheck Cond: ((symbol = 'AAPL'::text) AND ("time" >= '2022-04-05 00:00:00+00'::timestamp with time zone) AND ("time" < '2022-04-09 00:00:00+00'::timestamp with time zone))
Heap Blocks: exact=6583
Buffers: shared hit=6895
(... elided for space)
Planning:
Buffers: shared hit=30
Planning Time: 0.465 ms
Execution Time: 78.932 ms
看看这些计划，我们可以看到它的帮助比人们想象的要少！它加快了一点，但实际上，他们正在做几乎相同数量的工作！我怎么知道？好吧，它们扫描大约相同数量的 8KB 缓冲区（请参阅基础系列的第 0 课以了解更多信息），并且它们扫描相同数量的行。
当物化视图性能没有实现时
为什么是这样？好吧，我们JOIN并没有减少查询中的行数，所以物化视图stocks_company_mat实际上与超表有相同的行数stocks_real_time！
SELECT
(SELECT count(*) FROM stocks_company_mat) as rows_mat,
(SELECT count(*) FROM stocks_real_time) as rows_tab;
rows_mat | rows_tab
----------+----------
7375355 | 7375355
所以，不是一个巨大的好处，我们必须重新存储相同数量的行。因此，在我们必须使用多少存储空间方面，我们以相当大的成本获得的收益微乎其微。现在，如果我们在物化视图定义中运行一个非常昂贵的函数或做一个非常复杂的函数，这可能是一个很大的好处JOIN，但我们不是，所以这并没有为我们节省太多。
关于我们的例子的问题是，它只会从这里变得更糟。我们可能想要对我们的视图或物化视图做的一件事是能够使用一个WHERE子句来过滤不仅针对symbol公司而且针对公司name。（也许我不记得一家公司的股票代码，但我记得它的名字。）请记住，该name列是我们加入的列，所以让我们在视图和物化视图上运行该查询，看看会发生什么：
EXPLAIN (ANALYZE ON, BUFFERS ON) SELECT name, price from stocks_company_mat WHERE time >= '2022-04-05' and time <'2022-04-06' AND name = 'Apple' ;
Index Scan using stocks_company_mat_time_idx on stocks_company_mat (cost=0.43..57619.99 rows=92196 width=17) (actual time=0.022..605.268 rows=95497 loops=1)
Index Cond: (("time" >= '2022-04-05 00:00:00+00'::timestamp with time zone) AND ("time" < '2022-04-09 00:00:00+00'::timestamp with time zone))
Filter: (name = 'Apple'::text)
Rows Removed by Filter: 1655717
Buffers: shared hit=112577
Planning:
Buffers: shared hit=3
Planning Time: 0.116 ms
Execution Time: 609.040 ms
EXPLAIN (ANALYZE ON, BUFFERS ON) SELECT name, price from stocks_company WHERE time >= '2022-04-05' and time <'2022-04-06' AND name = 'Apple' ;
Nested Loop (cost=325.22..21879.02 rows=8736 width=19) (actual time=5.642..56.062 rows=95497 loops=1)
Buffers: shared hit=13215
-> Seq Scan on company c (cost=0.00..2.25 rows=1 width=15) (actual time=0.007..0.018 rows=1 loops=1)
Filter: (name = 'Apple'::text)
Rows Removed by Filter: 99
Buffers: shared hit=1
-> Append (cost=325.22..21540.78 rows=33599 width=12) (actual time=5.633..48.232 rows=95497 loops=1)
Buffers: shared hit=13214
-> Bitmap Heap Scan on _hyper_5_2655_chunk s_1 (cost=325.22..9866.59 rows=17537 width=12) (actual time=5.631..21.713 rows=49224 loops=1)
Recheck Cond: ((symbol = c.symbol) AND ("time" >= '2022-04-05 00:00:00+00'::timestamp with time zone) AND ("time" < '2022-04-09 00:00:00+00'::timestamp with time zone))
Heap Blocks: exact=6583
Buffers: shared hit=6895
…
Planning:
Buffers: shared hit=30
Planning Time: 0.454 ms
Execution Time: 59.558 ms
这一次，我对常规视图的查询要好得多！它击中的缓冲区少得多，返回速度快 10 倍！这是因为我们为物化视图创建了索引 on(symbol, time DESC)而不是 on (name, time DESC)，所以它必须回退到扫描完整time索引并删除不匹配的行。
然而，普通视图可以在超表上使用更具选择性(symbol, time DESC)的索引，stocks_real_time因为它正在执行对JOIN表的company连接，并且它在symbol列上连接，这意味着它仍然可以使用更具选择性的索引。我们通过执行JOIN和缓存结果来“增强”物化视图，但是我们还需要在连接列上创建一个索引。所以我们了解到，这个查询不是物化视图的理想选择，因为它不是一个疯狂的复杂耗时JOIN并且不会减少行数。但是，如果我们想要运行一个可以减少行数的查询，那么这将是物化视图的绝佳候选者。
当物化视图表现良好时
事实证明，有一组非常常见的关于股票数据的查询确实减少了行数，它们被称为O pen – H igh- L ow- C丢失查询 (OHLC)，它们看起来像这个：
CREATE VIEW ohlc_view AS
SELECT time_bucket('15 min', time) bucket, symbol, first(time, price), max(price), min(price), last(time, price)
FROM stocks_real_time
WHERE time >= '2022-04-05' and time <'2022-04-06'
GROUP BY time_bucket('15 min', time), symbol;
CREATE MATERIALIZED VIEW ohlc_mat AS
SELECT time_bucket('15 min', time) bucket, symbol, first(time, price), max(price), min(price), last(time, price)
FROM stocks_real_time
GROUP BY time_bucket('15 min', time), symbol ;
CREATE INDEX on ohlc_mat(symbol, bucket);
CREATE INDEX ON ohlc_mat(bucket);
在这里，我将很多行聚合在一起，所以我最终在我的物化视图中存储的内容要少得多。（视图不存储任何行，它只是查询的别名。）我仍然创建了一些索引来帮助加快查找速度，但它们也小得多，因为此查询的输出中的行数要少得多. 所以现在，如果我从普通视图和物化视图中进行选择，我会看到一个巨大的加速！普通视图：
EXPLAIN (ANALYZE ON, BUFFERS ON)
SELECT bucket, symbol, first, max, min, last
FROM ohlc_view
WHERE bucket >= '2022-04-05' AND bucket <'2022-04-06';
Finalize GroupAggregate (cost=39098.81..40698.81 rows=40000 width=44) (actual time=875.233..1000.171 rows=3112 loops=1)
Group Key: (time_bucket('00:15:00'::interval, _hyper_5_2655_chunk."time")), _hyper_5_2655_chunk.symbol
Buffers: shared hit=4133, temp read=2343 written=6433
-> Sort (cost=39098.81..39198.81 rows=40000 width=92) (actual time=875.212..906.810 rows=5151 loops=1)
Sort Key: (time_bucket('00:15:00'::interval, _hyper_5_2655_chunk."time")), _hyper_5_2655_chunk.symbol
Sort Method: quicksort Memory: 1561kB
Buffers: shared hit=4133, temp read=2343 written=6433
-> Gather (cost=27814.70..36041.26 rows=40000 width=92) (actual time=491.920..902.094 rows=5151 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=4133, temp read=2343 written=6433
-> Partial HashAggregate (cost=26814.70..31041.26 rows=40000 width=92) (actual time=526.663..730.168 rows=2576 loops=2)
Group Key: time_bucket('00:15:00'::interval, _hyper_5_2655_chunk."time"), _hyper_5_2655_chunk.symbol
Planned Partitions: 128 Batches: 129 Memory Usage: 1577kB Disk Usage: 19592kB
Buffers: shared hit=4133, temp read=2343 written=6433
Worker 0: Batches: 129 Memory Usage: 1577kB Disk Usage: 14088kB
-> Result (cost=0.43..13907.47 rows=257943 width=28) (actual time=0.026..277.314 rows=218880 loops=2)
Buffers: shared hit=4060
-> Parallel Index Scan using _hyper_5_2655_chunk_stocks_real_time_time_idx on _hyper_5_2655_chunk (cost=0.43..10683.19 rows=257943 width=20) (actual time=0.025..176.330 rows=218880 loops=2)
Index Cond: (("time" >= '2022-04-05 00:00:00+00'::timestamp with time zone) AND ("time" < '2022-04-06 00:00:00+00'::timestamp with time zone))
Buffers: shared hit=4060
Planning:
Buffers: shared hit=10
Planning Time: 0.615 ms
Execution Time: 1003.425 ms
物化视图：
EXPLAIN (ANALYZE ON, BUFFERS ON)
SELECT bucket, symbol, first, max, min, last
FROM ohlc_mat
WHERE bucket >= '2022-04-05' AND bucket <'2022-04-06';
Index Scan using ohlc_mat_bucket_idx on ohlc_mat (cost=0.29..96.21 rows=3126 width=43) (actual time=0.009..0.396 rows=3112 loops=1)
Index Cond: ((bucket >= '2022-04-05 00:00:00+00'::timestamp with time zone) AND (bucket < '2022-04-06 00:00:00+00'::timestamp with time zone))
Buffers: shared hit=35
Planning:
Buffers: shared hit=6
Planning Time: 0.148 ms
Execution Time: 0.545 ms
嗯，这有帮助！在物化案例中，我们命中的缓冲区少得多，扫描的行也少得多，而且我们不需要执行GROUP BY和聚合，这会删除排序等。所有这些都意味着我们大大加快了查询速度！但是，物化视图不是彩虹和蝴蝶。因为我们没有涵盖他们的一个大问题，所以他们已经过时了！因此，如果您考虑像我们的股票表这样的表，它是一个典型的时间序列用例，这意味着它看起来像这样：
我们创建了一个物化视图，并在某个时间用我们的查询填充了它。
但是，随着时间的流逝，例如，15 分钟后，我们插入了更多数据，视图已经过时了！我们time_bucket以 15 分钟为增量，所以有一整套我们没有的桶！
从本质上讲，物化视图仅与它们上次运行它们正在缓存的查询一样准确。您需要运行REFRESH MATERIALIZED VIEW 以确保它们是最新的。
运行REFRESH MATERIALIZED VIEW后，我们将在物化视图中得到新数据，如下所示：
问题是，REFRESH视图可能很昂贵，并且要理解为什么我们应该更多地了解它们的工作原理以及它们为什么会过时。这可能很昂贵。
物化视图是如何工作的（以及为什么它们会过时）
要了解物化视图是如何过时的以及刷新正在做什么，有助于了解它们在后台是如何工作的。本质上，当您创建物化视图时，您正在创建一个表并使用来自查询的数据填充它。对于ohlc_mat我们一直在使用的视图，它相当于：
CREATE TABLE ohlc_tab AS
SELECT time_bucket('15 min', time) bucket, symbol, first(time, price), max(price), min(price), last(time, price)
FROM stocks_real_time
GROUP BY time_bucket('15 min', time), symbol;
Now, what happens when I insert data into the underlying table?
INSERT INTO stocks_real_time VALUES (now(), 'AAPL', 170.91, NULL);
因此，我们的物化视图ohlc_mat在我们创建它时存储了查询运行的结果。
常规视图 ( ohlc_view) 将保持最新，因为它只是直接在stocks_real_time. 如果我们只在 附近插入数据now()，并且只查询更旧的数据，那么物化视图看起来就可以了。与一两个月前相比，我们的查询没有任何变化，但如果我们尝试查询更近的时间，我们将没有任何数据。如果我们希望它更新最新数据，我们需要运行：
REFRESH MATERIALIZED VIEW ohlc_mat;
当我们这样做时，实际上发生的事情是我们从表中截断（删除所有数据），然后再次运行查询并将其插入到表中。
如果我们使用ohlc_tab上面的表格，等效的操作将类似于：
TRUNCATE TABLE ohlc_tab;
INSERT INTO ohlc_tab
SELECT time_bucket('15 min', time) bucket, symbol, first(time, price), max(price), min(price), last(time, price)
FROM stocks_real_time
GROUP BY time_bucket('15 min', time), symbol;
（当您REFRESH MATERIALIZED VIEW使用该CONCURRENTLY选项运行时，它的工作方式略有不同，但从根本上说，它总是在整个数据集上运行查询，详细信息超出了本文的范围。）
数据库，很像视图，存储我们运行的查询，这样当我们运行时REFRESH它就知道要做什么，这很好，但不是最有效的。即使大部分数据没有变化，我们还是扔掉了整个数据集，重新运行整个查询。
虽然在您使用 OLTP 数据时可能没问题，例如 PostgreSQL 使用的 OLTP 数据，并且您的更新/删除随机分布在您的数据集周围，但当您使用时间序列数据时，它开始显得非常低效，写入大多是在最近的时期。
总而言之，我们发现了一个实例化视图确实对我们有帮助的情况，因为查询的输出远小于我们必须扫描以计算它的行数。在我们的例子中，它是一个聚合。但我们也注意到，当我们使用物化视图时，数据会过时，因为我们正在存储查询的输出，而不是像使用视图那样在查询时重新运行它。
为了使物化视图保持最新，我们了解到我们需要REFRESH它，但对于时间序列用例，a）您必须经常刷新它（在我们的例子中，大约每 15 分钟左右至少) 使其保持最新，并且 b) 刷新效率低下，因为我们必须删除并重新实现所有数据，可能要追溯到几个月前，才能从前 15 分钟获取新信息。这也是我们在 Timescale 开发连续聚合的主要原因之一。
连续聚合如何工作以及它们如何受到最佳视图和物化视图的启发
对于这些类型的聚合，我们看到了视图和物化视图的这些问题，并希望开发出更好地满足具有时间序列数据的人们的需求的东西，因为我们知道时间分段聚合是一个非常常见的用例。在开发它们时，我们试图从视图和物化视图中学习。在本节中，我们将逐步了解连续聚合是如何工作的。
从根本上说，当我们创建一个连续聚合时，我们所做的事情与我们创建一个物化视图时发生的事情非常相似。这就是为什么我们使用稍微修改过的界面版本来创建物化视图的原因：
CREATE MATERIALIZED VIEW ohlc_cont
WITH (timescaledb.continuous) AS
SELECT time_bucket('15 min', time) bucket, symbol, first(time, price), max(price), min(price), last(time, price)
FROM stocks_real_time
GROUP BY time_bucket('15 min', time), symbol;
一旦我们这样做了，我们最终会遇到与物化视图非常相似的情况。我们有创建视图时周围的数据，但随着新数据的插入，视图将过时。
为了使连续聚合保持最新，我们需要计划聚合。
新数据的预定聚合
我们看到了希望通过计划聚合解决的物化视图的两个主要问题：
- 当我们希望它保持最新时，我们必须手动刷新物化视图。
- 我们不想不必要地对所有旧数据重新运行查询；我们应该只在新数据上运行它。
要安排聚合，我们需要创建一个连续聚合策略：
SELECT add_continuous_aggregate_policy('ohlc_cont'::regclass, start_offset=>NULL, end_offset=>'15 mins'::interval, schedule_interval=>'5 mins'::interval);
一旦我们安排了连续聚合策略，它将根据schedule_interval我们指定的自动运行。在我们的例子中，它每五分钟运行一次。当它运行时，它会查看我们已经物化的数据和新插入的数据，并查看我们是否已经完成了至少一个 15 分钟的存储桶。如果我们有，它将在接下来的 15 分钟部分运行查询，并将结果具体化到我们的连续聚合中。
这意味着连续聚合现在会自动获取下一个 15 分钟期间的数据，而无需用户干预。
而且效率更高。与 running 不同REFRESH MATERIALIZED VIEW，我们没有删除所有旧数据并针对它重新计算聚合，我们只是针对接下来的 15 分钟时间段运行聚合查询并将其添加到我们的具体化中。随着时间的推移，随着每个连续的 15 分钟时间段（或我们time_bucket在连续聚合定义中选择的任何时间段）填充新数据然后具体化，这可能会继续发生。
关于这一点需要注意的一点是，我们通过存储我们称之为水印的东西来跟踪我们已经实现的位置，这里用虚线表示。（注意：它以洪水引起的高水印命名，而不是银行支票上的水印。）所以在计划的聚合运行之前，水印就在我们已经物化的所有数据之后：
这有助于我们找到下一个存储桶，并确保在我们运行聚合之前它就在那里。一旦我们有了，我们移动水印：
所以我们的水印代表了我们迄今为止实现的最远点。
但是，您可能会注意到我们的连续聚合仍然不是完全最新的，并且不会为我们提供与运行相同查询的视图相同的结果。为什么？
- 计划的聚合在下一个存储桶拥有所有数据的时间和作业运行以实现它的时间之间会有一些差距。
- 默认情况下，我们仅在下一个存储桶已满时才具体化数据，因此我们缺少现在正在发生插入的部分存储桶。我们可能希望获得该桶的部分结果（当我们使用更大的桶时尤其如此）。
为了解决这个问题，我们制作了实时视图。
实时视图
实时视图结合了最好的物化视图和普通视图，为我们提供了更新的数据视图。它们是连续聚合的默认设置，因此我根本不需要更改创建连续聚合的方式。但是，我承认我在上一张图片中省略了一些关于连续聚合如何在幕后工作的内容。
实时连续聚合有两部分：
- 一个物化的超表，我们已经计算过的聚合被存储在其中。
- 还有一个实时视图，它同时查询物化超表和原始超表（在尚未聚合的区域中）并将结果组合在一起。
所以，如果你看一下连续聚合的视图定义，它看起来像这样：
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
WHERE _materialized_hypertable_15.bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(15)), '-infinity'::timestamp with time zone)
UNION ALL
SELECT time_bucket('00:15:00'::interval, stocks_real_time."time") AS bucket,
stocks_real_time.symbol,
first(stocks_real_time."time", stocks_real_time.price) AS first,
max(stocks_real_time.price) AS max,
min(stocks_real_time.price) AS min,
last(stocks_real_time."time", stocks_real_time.price) AS last
FROM stocks_real_time
WHERE stocks_real_time."time" >= COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(15)), '-infinity'::timestamp with time zone)
GROUP BY (time_bucket('00:15:00'::interval, stocks_real_time."time")), stocks_real_time.symbol;
这是两个与 a 放在一起的查询UNION ALL，第一个直接从物化超表中选择数据，我们的存储桶低于水印，第二个运行聚合查询，我们的时间列高于水印。
因此，您可以看到它如何利用物化视图和普通视图的优点来创建比普通视图快得多但仍然是最新的东西！
它不会像查询已经物化的数据那样高效（尽管如果您愿意，我们确实可以选择允许您这样做），但对于大多数用户来说，过去几个月甚至几年的数据已经物化了而只需要查询最后几分钟或几天的原始数据，这仍然会产生巨大的加速！
乱序数据失效
你可能已经注意到我在所有图表中都做了一个很大的假设。我假设我们所有的插入都发生在最近的时间段内。对于时间序列工作负载，这主要是正确的。大多数数据按时间顺序排列。但是，大多数和所有都是非常不同的东西。特别是对于时间序列工作负载，我们有如此多的数据进入，即使 99% 的数据是按时间顺序排列的，1% 的数据仍然很多！
如果我们只是让插入（或更新或删除）随着时间的推移而累积，那么聚合的结果将会有很大的错误。这个缓存失效问题是计算中很常见的问题，也是非常难的问题！PostgreSQL 物化视图通过删除所有旧数据并每次重新物化它来解决这个问题，但我们已经说过这是多么低效。
许多人试图在像 PostgreSQL 这样的数据库中解决这类问题的另一种方法是触发器。标准触发器将为每一行运行并更新每一行的聚合。
但在实践中，很难让每行触发器工作得很好，而且它仍然会导致显着的写入放大，这意味着我们必须为我们插入的每一行写入多次。
事实上，对于我们在原始超表上的每个连续聚合，我们需要为每一行至少写一次。它还会将我们可以使用的聚合限制为可以通过触发器修改的聚合，这些聚合比我们想要的要少。因此，我们创建了一种特殊的触发器，它跟踪语句中所有行的最小和最大修改时间，并将修改的时间范围写入日志表。我们称之为失效日志。
下一次连续聚合作业运行时，它必须做两件事：运行接下来 15 分钟数据的正常聚合，并在每个无效区域上运行聚合以重新计算该时间段内的正确值。
请注意，这使得我们的连续聚合最终对无序修改保持一致。但是，实时视图使连续聚合对最近的数据更加一致（因为它们使用后台视图）。
虽然我们可以通过加入我们的日志表并为无效区域实时重新运行聚合来制作更高度一致的聚合，但我们与用户交谈并认为最终一致性对于这里的大多数情况来说已经足够好了。毕竟，这些数据已经迟到了。本质上，我们认为这样做的性能影响不值得一致性保证。无论如何，如果用户愿意，他们可以通过运行手动refresh_continuous_aggregates过程来触发修改区域的连续聚合的手动刷新，这会立即更新物化超表中的数据。
数据保留
我们希望通过连续聚合完成的最后一件事是在删除原始数据后保留聚合数据的方法。这对于 PostgreSQL 视图和物化视图都是不可能的，因为对于视图，它们直接处理原始数据——如果你放弃它，它们就不能聚合它。
对于物化视图，它有点复杂：在您运行刷新之前，它们可以拥有旧数据，但是，一旦您运行刷新，要获取您在最近时间段添加的新数据，然后是旧数据数据被丢弃。
使用连续聚合，实现要简单得多。我们提到了当我们修改已经实现的数据时触发的失效触发器。我们只是忽略任何早于某个时间范围的事件，包括 drop 事件。
我们还可以在删除数据之前处理任何失效，以便您可以在删除最旧的数据之前实现正确的数据。您可以通过正确设置连续聚合策略来配置数据保留。
它有效吗？
因此，我们已经获得了视图、物化视图和触发器的整个混搭，以便尝试做出一组对时间序列数据有效的良好权衡。所以问题是：它有效吗？
为了测试这一点，我在没有数据和策略的情况下从上面重新创建了我们的连续聚合并运行了该refresh_continuous_aggregate程序，以便将大约一个月的数据在聚合中具体化，其中大约需要 30 分钟通过实时看法。
如果我们从连续聚合中查询整个周期的聚合数据，大约需要18 毫秒，这比物化视图中完全物化数据的 5-6 毫秒稍慢，但仍然比15 秒从普通视图中获取，我们从中获得了普通视图的大部分最新优势。我会对这种权衡感到非常满意。
如果您是 TimescaleDB 的新手并想尝试一下，我邀请您注册 Timescale Cloud。这是开始使用 Timescale 的最简单方法。它在 30 天内 100% 免费，无需信用卡，您将能够在几秒钟内启动一个包含演示数据的数据库（运行 Almond Milk 演示）。您可以在 Timescale Cloud 演示数据库中轻松托管 PostgreSQL 表和 TimescaleDB 超表，创建视图、物化视图和连续聚合，并探索它们之间在性能和开发人员体验方面的差异。
评论 (0)