多年來，物化視圖一直是Postgres期待已久的功能。他們最終到達了Postgres 9.3，盡管當時很有限。在Postgres 9.3中，當刷新實例化視圖時，它將在刷新時在表上保持鎖定。如果您的工作量是非常繁忙的工作時間，則可以工作，但是如果您要為最終用戶提供動力，那么這將是一個大問題。在Postgres 9.4中，我們看到了Postgres實現(xiàn)了同時刷新實例化視圖的功能。現(xiàn)在，我們已經(jīng)完全烘焙了物化視圖的支持，但即使如此，我們?nèi)匀豢吹剿鼈兛赡懿⒉豢偸钦_的方法。

什么是視圖view？

對于那些不是數(shù)據(jù)庫專家的人，我們將做一點備份。要了解什么是實體化視圖，我們首先來看一個標準視圖。視圖是已定義的查詢，您可以像表一樣對其進行查詢。當您具有通常用于某些標準報表/構(gòu)建塊的復(fù)雜數(shù)據(jù)模型時，視圖特別有用。稍后我們將介紹一個實例化視圖。

視圖非常適合簡化復(fù)雜SQL的復(fù)制/粘貼。缺點是每次執(zhí)行視圖時都會重新計算結(jié)果。對于大型數(shù)據(jù)集，這可能會導(dǎo)致掃描大量數(shù)據(jù)，使緩存無效，并且通常速度較慢。輸入實例化視圖

物化你的視圖

讓我們從一個可能包含大量原始數(shù)據(jù)的示例架構(gòu)開始。在這種情況下，一個非常基本的網(wǎng)絡(luò)分析工具會記錄綜合瀏覽量，發(fā)生時間和用戶的會話ID。

CREATE TABLE pageviews ( id bigserial, page text, occurred_at timestamptz, session_id bigint );

基于這些原始數(shù)據(jù)，有很多不同的視圖可能非常普遍。而且，如果我們有一個實時儀表板，我們將為它提供動力，因為它可能花費很長時間來查詢原始數(shù)據(jù)，因此很快變得不可行。相反，我們可以對物化視圖進行一些匯總：

CREATE MATERIALIZED VIEW rollups AS SELECT date_trunc('day') as day, page, count(*) as views FROM pageviews GROUP BY date_trunc('day'), page;

對于每天至少瀏覽一次的頁面，這將為我們每天提供1條記錄。

對于每天晚上批處理的事情，可以處理前一天的事情。但是對于面對客戶的事情，您可能不希望等到一天結(jié)束后再提供有關(guān)網(wǎng)頁瀏覽量如何進行分析的信息。當然，您可以定期刷新一次：

refresh materialized view rollups;

這種刷新方式的缺點是每次刷新時都會重新計算當天的總數(shù)，這實際上是在進行不必要的處理。

為了可擴展性增量匯總

另一種方法是使用upsert，它使我們能夠增量匯總數(shù)據(jù)而不必重新處理所有基礎(chǔ)數(shù)??據(jù)。 Upsert本質(zhì)上是創(chuàng)建或更新。為此，我們將創(chuàng)建一個表而不是物化視圖，然后在其上施加唯一約束：

CREATE TABLE ( day as timestamptz, page text, count as bigint, constraint unq_page_per_day unique (day, page) );

現(xiàn)在開始匯總，我們將執(zhí)行以下操作：

INSERT INTO rollups SELECT date_trunc('day') as day, page, count(*) as views FROM pageviews GROUP BY date_trunc('day'), page;

這基本上與我們的物化視圖相同。但是由于我們的獨特限制，當遇到已經(jīng)插入的記錄時，插入會出錯。為了完成這項工作，我們將調(diào)整查詢以完成兩件事。一項我們將只處理新記錄，另一項我們將使用upsert語法。

為了處理新記錄，我們將保留上次停止記錄的記錄，僅處理新記錄。我們在本文中概述了一組方便使用的函數(shù)/表。使用適當?shù)暮瘮?shù)和表格來跟蹤我們上次中斷的位置，現(xiàn)在我們將查詢更新為僅匯總自上次處理后的數(shù)據(jù)。然后，我們將其與upsert結(jié)合在一起。 upsert將嘗試插入當天/頁面的任何新記錄，如果已經(jīng)看到這些值，則將增加它們：

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;

物化視圖與匯總表哪個正確？

物化視圖是一種非常簡單直接的方法。它們的易用性使它們成為快速簡便的事情的理想選擇。但是，對于具有較大活動負載的較大數(shù)據(jù)集和數(shù)據(jù)庫，僅處理上一次匯總的凈新數(shù)據(jù)可以更有效地利用資源。哪種方法最合適取決于時間和系統(tǒng)資源。盡管如您所見，匯總方法僅需要一點點努力，并且可以進一步擴展。

原文：https://www.citusdata.com/blog/2018/10/31/materialized-views-vs-rollup-tables/

本文：http://jiagoushi.pro/node/928

討論：請加入知識星球或者微信圈子【首席架構(gòu)師圈】

總結(jié)

以上是生活随笔為你收集整理的基于物化视图优化_「PostgreSQL技巧」PostgreSQL中的物化视图与汇总表比较的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。