在PostgreSQL中增量刷新实例化视图

CREATE TABLE graph (
   xaxis integer NOT NULL,
   value integer NOT NULL,
);

CREATE MATERIALIZED VIEW graph_avg AS 
SELECT xaxis, AVG(value)
FROM graph
GROUP BY xaxis

#1 楼

您始终可以实现自己的表，作为“实例化视图”。这就是我们在Postgres 9.3中实现MATERIALIZED VIEW之前所做的工作。

您可以创建一个简单的VIEW：

CREATE VIEW graph_avg_view AS 
SELECT xaxis, AVG(value) AS avg_val
FROM   graph
GROUP  BY xaxis;

并在一次或每次需要重新开始时实现结果：

CREATE TABLE graph_avg AS
SELECT * FROM graph_avg_view;

（或直接使用SELECT语句，而不创建VIEW。）
然后，根据未公开的用例详细信息，您可以手动更改DELETE / UPDATE / INSERT 。

一个基本的DML语句，其中包含用于表的数据修改CTE的原样：

假设没有其他人尝试同时写入graph_avg（读取没有问题）：

WITH del AS (
   DELETE FROM graph_avg t
   WHERE  NOT EXISTS (SELECT FROM graph_avg_view WHERE xaxis = t.xaxis)
   )
, upd AS (
   UPDATE graph_avg t
   SET    avg_val = v.avg_val
   FROM   graph_avg_view v
   WHERE  t.xaxis = v.xaxis
   AND    t.avg_val <> v.avg_val
-- AND    t.avg_val IS DISTINCT FROM v.avg_val  -- alt if avg_val can be NULL
   )
INSERT INTO graph_avg t  -- no target list, whole row
SELECT v.*
FROM   graph_avg_view v
WHERE  NOT EXISTS (SELECT FROM graph_avg WHERE xaxis = v.xaxis);

基本食谱


将带有默认timestamp的now()列添加到基表中。让我们称之为ts。如果有更新，请添加触发器以设置每次更改xaxis或value的每次更新的当前时间戳。



创建一个小表以记住最新快照的时间戳。让我们称它为mv：

CREATE TABLE mv (
   tbl text PRIMARY KEY
 , ts timestamp NOT NULL DEFAULT '-infinity'
); -- possibly more details

创建此局部多列索引：

CREATE INDEX graph_mv_latest ON graph (xaxis, value)
WHERE  ts >= '-infinity';

在查询中使用最后一个快照的时间戳作为谓词，以完美使用索引来刷新快照。
在事务结束时，删除索引并使用事务时间戳重新创建索引，以替换索引谓词中的时间戳（最初为'-infinity'），您也将其保存到表中。一次交易中的所有内容。
请注意，部分索引可以很好地覆盖INSERT和UPDATE操作，但不能覆盖DELETE。为此，您需要考虑整个表格。这完全取决于确切的要求。

#2 楼

并发更新（Postgres 9.4）

虽然您没有要求增量更新，但Postgres 9.4确实提供了新的并发更新功能。

引用文档…


在PostgreSQL 9.4之前，刷新物化视图意味着锁定整个表，因此防止任何查询它，如果刷新花费很长时间来获取排他锁（它等待查询）使用它来完成），反过来又阻止了后续查询。现在，可以使用CONCURRENTLY关键字来缓解这种情况：

 postgres=# REFRESH MATERIALIZED VIEW CONCURRENTLY mv_data;

但是，物化视图上将需要存在唯一索引。它没有锁定实例化视图，而是创建了它的临时更新版本，比较这两个版本，然后对实例化视图应用INSERT和DELETE以应用差异。这意味着查询在更新时仍然可以使用实例化视图。
与非并发形式不同，元组不会被冻结，由于前面提到的DELETE会留下无效的元组，因此它需要VACUUMing。 >

此并发更新仍在执行完整的全新查询（不是增量查询）。因此，CONCURRENTLY不会节省总的计算时间，而只是将您的物化视图在更新期间不可用的时间减至最少。