在 PostgreSQL 中增量刷新物化視圖

是否可以在 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

定期添加新值graph或更新現有值。我想graph_avg每隔幾個小時刷新一次視圖，僅針對已更新的值。但是在 PostgreSQL 9.3 中，整個表都被刷新了。這是相當耗時的。下一個版本 9.4 允許CONCURRENT更新，但仍會刷新整個視圖。對於數以百萬計的行，這需要幾分鐘。

跟踪更新和新值並僅部分刷新視圖的好方法是什麼？

您始終可以實現自己的表作為“物化視圖”。這就是我們之前MATERIALIZED VIEW在 Postgres 9.3 中實現的方式。

您可以創建一個普通的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。要涵蓋這一點，您需要考慮整個表格。這一切都取決於確切的要求。

引用自：https://dba.stackexchange.com/questions/86779