在 SQL 中運算與快取 • PostgreSQL 的藝術

有句流傳甚廣的話（Phil Karlton）：電腦科學只有兩個難題——快取失效（cache invalidation）與命名。（Martin Fowler 的 Two Hard Things 頁面追溯了這句話的來源。）

該來看看 SQL 如何面對快取問題了。建立一組快取值很容易，通常就是寫一條 SQL 查詢——PostgreSQL 執行的每條查詢都使用整個資料庫的快照。最簡單的快取做法就是 create table … as：

create table tweet.counters as
  select   count(*) filter(where action = 'rt')
         - count(*) filter(where action = 'de-rt')
         as rts,
           count(*) filter(where action = 'fav')
         - count(*) filter(where action = 'de-fav')
         as favs
    from tweet.activity
         join tweet.message using(messageid);

有了 tweet.counters 表，隨時能查 rts 與 favs——但要怎麼更新它？這正是前述的快取失效問題，本章結尾會給出答案。

檢視（Views）#

檢視（view）把伺服器端運算整合進關聯的定義裡：運算仍在查詢時動態發生，對客戶端透明。使用 view 完全沒有快取失效問題——因為根本沒有東西被快取。

create view tweet.message_with_counters
       as
  select messageid,
         message.userid,
         message.datetime,
         message.message,
           count(*) filter(where action = 'rt')
         - count(*) filter(where action = 'de-rt')
         as rts,
           count(*) filter(where action = 'fav')
         - count(*) filter(where action = 'de-fav')
         as favs,
         message.location,
         message.lang,
         message.url
    from tweet.activity
         join tweet.message using(messageid)
group by message.messageid, activity.messageid;

應用程式碼直接查 tweet.message_with_counters，處理起來就像最初正規化版本的關聯一樣——view 把「計數器怎麼算出來」的複雜度藏了起來：

  select messageid,
         rts,
         nickname
    from tweet.message_with_counters
         join tweet.users using(userid)
   where messageid between 1 and 6
order by messageid;

 messageid │  rts   │   nickname
═══════════╪════════╪══════════════
         1 │  20844 │ Duke Theseus
         2 │ 111345 │ Hippolyta
         3 │  11000 │ Duke Theseus
         5 │   3500 │ Duke Theseus
         6 │  15000 │ Egeus
(5 rows)

View 把運算細節從應用程式碼中抽象出來，讓應用的多個部分——報表、資料分析、使用者分析產品，甚至以不同程式語言寫成的後端——都用同一套轉推與收藏的計算方式，共享同一份真相（shared truth）。

不過 view 雖然封裝了運算細節，每次被查詢引用時仍會重新計算。

物化檢視（Materialized Views）#

要把資料庫快照固化成永久關聯供之後查詢，PostgreSQL 的物化檢視（materialized view）讓這件事很容易：

create schema if not exists twcache;

create materialized view twcache.message
    as select messageid, userid, datetime, message,
              rts, favs,
              location, lang, url
         from tweet.message_with_counters;

create unique index on twcache.message(messageid);

（完整選項見 PostgreSQL 文件的 CREATE MATERIALIZED VIEW。）

應用程式碼改查 twcache.message，就能直接拿到預先算好的 rts、favs 欄位。但物化檢視中的資訊是靜態的：只有下特定指令才會更新。我們等於在 SQL 裡實作了一個快取，於是也接手了快取失效問題——只要訊息上發生新的轉推或收藏，快取就錯了。

建好快取後再跑一輪基準測試（100 個 worker、各 100 次轉推、messageid 3），然後查快取：

  select messageid,
         rts,
         nickname,
         substring(message from E'[^\n]+') as first_line
    from twcache.message
         join tweet.users using(userid)
   where messageid = 3
order by messageid;

 messageid │ rts  │   nickname   │    first_line
═══════════╪══════╪══════════════╪══════════════════
         3 │ 1000 │ Duke Theseus │ Go, Philostrate,
(1 row)

物化檢視確實是快取——它完全不知道剛剛那一萬次轉推。

「計數已經漏掉一些動作」的情況，其實用普通表或 view 也會發生：每條 PostgreSQL 查詢都用資料庫快照，若計數查詢執行期間有 insert 提交到 tweet.activity，查詢結果也不會包含那些新列。物化檢視只是把快取的存活時間拉長，讓問題更明顯而已。

要讓快取失效並重新計算，PostgreSQL 提供：

refresh materialized view concurrently twcache.message;

這個指令讓你能實作快取失效政策（cache invalidation policy）：
若業務只分析到前一天的資料，每晚 refresh 一次就是你的政策。
像 Twitter 這種即時訊息場景，政策可能要求計數新鮮度在五分鐘以內——只要 refresh 跑得比五分鐘快，用 cron 之類的排程器每五分鐘執行一次即可。

refresh 之後再查一次快取，就得到預期的答案（rts = 11000）。