多年來，物化視圖一直是Postgres期待已久的功能。他們最終到達了Postgres 9.3，儘管當時很有限。在Postgres 9.3中，當刷新實例化視圖時，它將在刷新時在表上保持鎖定。如果您的工作量是非常繁忙的工作時間，則可以工作，但是如果您要為最終用戶提供動力，那麼這將是一個大問題。在Postgres 9.4中，我們看到了Postgres實現了同時刷新實例化視圖的功能。現在，我們已經完全烘焙了物化視圖的支持，但即使如此，我們仍然看到它們可能並不總是正確的方法。
什麼是視圖view？
對於那些不是資料庫專家的人，我們將做一點備份。要了解什麼是實體化視圖，我們首先來看一個標準視圖。視圖是已定義的查詢，您可以像表一樣對其進行查詢。當您具有通常用於某些標準報表/構建塊的複雜數據模型時，視圖特別有用。稍後我們將介紹一個實例化視圖。
視圖非常適合簡化複雜SQL的複製/粘貼。缺點是每次執行視圖時都會重新計算結果。對於大型數據集，這可能會導致掃描大量數據，使緩存無效，並且通常速度較慢。輸入實例化視圖
物化你的視圖
讓我們從一個可能包含大量原始數據的示例架構開始。在這種情況下，一個非常基本的網絡分析工具會記錄綜合瀏覽量，發生時間和用戶的會話ID。
CREATE TABLE pageviews ( id bigserial, page text, occurred_at timestamptz, session_id bigint );
基於這些原始數據，有很多不同的視圖可能非常普遍。而且，如果我們有一個實時儀錶板，我們將為它提供動力，因為它可能花費很長時間來查詢原始數據，因此很快變得不可行。相反，我們可以對物化視圖進行一些匯總：
CREATE MATERIALIZED VIEW rollups AS SELECT date_trunc('day') as day, page, count(*) as views FROM pageviews GROUP BY date_trunc('day'), page;
對於每天至少瀏覽一次的頁面，這將為我們每天提供1條記錄。
對於每天晚上批處理的事情，可以處理前一天的事情。但是對於面對客戶的事情，您可能不希望等到一天結束後再提供有關網頁瀏覽量如何進行分析的信息。當然，您可以定期刷新一次：
refresh materialized view rollups;
這種刷新方式的缺點是每次刷新時都會重新計算當天的總數，這實際上是在進行不必要的處理。
為了可擴展性增量匯總
另一種方法是使用upsert，它使我們能夠增量匯總數據而不必重新處理所有基礎數據。 Upsert本質上是創建或更新。為此，我們將創建一個表而不是物化視圖，然後在其上施加唯一約束：
CREATE TABLE ( day as timestamptz, page text, count as bigint, constraint unq_page_per_day unique (day, page) );
現在開始匯總，我們將執行以下操作：
INSERT INTO rollups SELECT date_trunc('day') as day, page, count(*) as views FROM pageviews GROUP BY date_trunc('day'), page;
這基本上與我們的物化視圖相同。但是由於我們的獨特限制，當遇到已經插入的記錄時，插入會出錯。為了完成這項工作，我們將調整查詢以完成兩件事。一項我們將只處理新記錄，另一項我們將使用upsert語法。
為了處理新記錄，我們將保留上次停止記錄的記錄，僅處理新記錄。我們在本文中概述了一組方便使用的函數/表。使用適當的函數和表格來跟蹤我們上次中斷的位置，現在我們將查詢更新為僅匯總自上次處理後的數據。然後，我們將其與upsert結合在一起。 upsert將嘗試插入當天/頁面的任何新記錄，如果已經看到這些值，則將增加它們：
INSERT INTO rollups SELECT day, page, count(*) as views FROM pageviews WHERE event_id > e GROUP BY day, page ON CONFLICT (day, page) DO UPDATE SET views = views + EXCLUDED.views;
物化視圖與匯總表哪個正確？
物化視圖是一種非常簡單直接的方法。它們的易用性使它們成為快速簡便的事情的理想選擇。但是，對於具有較大活動負載的較大數據集和資料庫，僅處理上一次匯總的凈新數據可以更有效地利用資源。哪種方法最合適取決於時間和系統資源。儘管如您所見，匯總方法僅需要一點點努力，並且可以進一步擴展。
原文：https://www.citusdata.com/blog/2018/10/31/materialized-views-vs-rollup-tables/
本文：http://jiagoushi.pro/node/928
討論：請加入知識星球或者微信圈子【首席架構師圈】