為應用程式建模時，第一步永遠應該是徹底的正規化。這一步花時間，但花得值得——它讓我們深入理解正在設計的系統。
- 達到 3NF、BCNF 甚至 4NF 之後，下一步自然是產生資料、撰寫查詢：一是工作流程導向的 CRUD 查詢（一次處理單筆記錄），二是綜觀全局的報表查詢（週報行銷分析、開票、升售建議等）。
- 完成後可能出現困難：完全正規化的綱要在應用層太笨重卻無實益，或高度正規化帶來經過量測且無法容忍的效能損失。
- 完全正規化的綱要往往表多、參照多，意味著大量外鍵約束與 join。不過 PostgreSQL 本來就是照 SQL 標準與正規化規則打造的，join 一般表現很好；多數操作採列級鎖（row-level locking），約束的成本在很多情況下不是阻礙。
若應用程式的某部分工作負載確實撐不起完全正規化的綱要，就該尋找取捨。**反正規化（denormalization）**就是放寬正規化規則，在資料品質與資料維護之間換取可接受的平衡。技巧的選擇取決於目標：可能犧牲資料維護以加速報表，也可能反過來。
過早最佳化#
如同 CSV 反模式所示，資料庫建模很容易掉進過早最佳化的陷阱。**只有在你為必要性建立了強力論證之後，才使用反正規化技巧。**所謂強力論證是指：
- 已用真實生產資料（或分佈相同、盡可能真實的資料集）在多種伺服器配置上為應用程式碼跑過基準測試。
- 已花時間改寫 SQL 查詢，讓它們通過驗收測試。
- 清楚知道查詢的時間預算與實際耗時——平均、中位數、95 與 99 百分位。
效能是一項功能，但多半不是業務最重要的功能；只有超過某個門檻，糟糕的效能才是殺手，必須處理。那時才反正規化——也就是決定讓資料品質承擔風險，以服務使用者與業務——而不是之前。
功能依賴的取捨#
反正規化的主要手法是打破功能依賴規則，把資料重複放在多處以免重複撈取。做得對的話，打破功能依賴等同於在資料庫裡實作快取。
怎麼知道做得對？正確的做法是應用程式碼內建快取失效機制，且失效多半自動化——批次執行或事件觸發。本書的〈Computing and Caching in SQL〉一節介紹過可用於反正規化的快取與失效機制。
用 PostgreSQL 反正規化#
在 PostgreSQL 中，反正規化可以是選用反正規化的資料型別來取代外部參照表，還有許多其他技巧（部分列在本章後段），有些在其他資料庫系統也很普遍，有些則是 PostgreSQL 獨有。實作任何反正規化技巧時，謹記三條規則：
- 為所有資料選定並文件化單一事實來源。反正規化引入分歧——同一份資料會有多個彼此有差異的副本，所有人與所有程式碼都必須清楚真相在哪裡管理。
- 一定要實作快取失效機制。當你必須重設快取、發佈已知正確的資料版本時，這應該只是執行一個眾所周知、有文件、有測試、有維護的程序。
- 檢查資料維護的並行行為。反正規化意味著更複雜的資料維護操作，對多數應用而言可能降低寫入擴展性。下一章〈資料操作與並行控制〉深入這個主題。
總結：反正規化是最佳化資料庫模型的技巧，而沒量測過的東西無從最佳化——先正規化、做基準測試，再談最佳化。
具體化視圖#
回到 f1db 資料庫，計算某賽季車手與車隊積分：
\set season 2017
select drivers.surname as driver,
constructors.name as constructor,
sum(points) as points
from results
join races using(raceid)
join drivers using(driverid)
join constructors using(constructorid)
where races.year = :season
group by grouping sets(drivers.surname, constructors.name)
having sum(points) > 150
order by drivers.surname is not null, points desc; driver │ constructor │ points
══════════╪═════════════╪════════
¤ │ Mercedes │ 357
¤ │ Ferrari │ 318
¤ │ Red Bull │ 184
Vettel │ ¤ │ 202
Hamilton │ ¤ │ 188
Bottas │ ¤ │ 169
(6 rows)（結果數字「不對」是因為計算當時賽季尚未結束；having 只是為了縮短書中版面。）
應用程式若經常要顯示這份資訊——例如主儀表板就是本季積分摘要——而資訊被讀取的頻率遠高於它變動的頻率，就適合做快取。在 PostgreSQL 裡最簡單的做法是具體化視圖（materialized view）。這次改為計算所有賽季並按賽季建索引：
begin;
create schema if not exists v;
create schema if not exists cache;
create view v.season_points as
select year as season, driver, constructor, points
from seasons
left join lateral
/*
* For each season, compute points by driver and by constructor.
*/
(
select drivers.surname as driver,
constructors.name as constructor,
sum(points) as points
from results
join races using(raceid)
join drivers using(driverid)
join constructors using(constructorid)
where races.year = seasons.year
group by grouping sets(drivers.surname, constructors.name)
order by drivers.surname is not null, points desc
)
as points
on true
order by year, driver is null, points desc;
create materialized view cache.season_points as
select * from v.season_points;
create index on cache.season_points(season);
commit;先建一個每次引用都重新計算的傳統視圖，再在其上建具體化視圖，有兩個好處：
- 用簡單的
except查詢就能檢查具體化視圖偏離權威版本多少。- 想在應用程式中停用快取時，只要換個關聯名稱就能拿到相同（且保證最新）的結果集。
快取要在每場比賽後失效，而失效機制就是一句：
refresh materialized view cache.season_points;重新計算期間，
cache.season_points連select都會被鎖住。定義夠簡單的具體化視圖可以refresh ... concurrently，避免鎖住並行讀者。
有了快取，應用程式取相同結果集的查詢變成：
select driver, constructor, points
from cache.season_points
where season = 2017
and points > 150;歷史表與稽核軌跡#
有些業務需要完整的變更歷史以供稽核。常見做法是主表維護即時資料（照既有規則建模），另外建一張歷史表保存列的舊版本或存檔。
歷史表本身不是主表的反正規化版本，而是完全另一個模型——光是主鍵就不同。可能需要反正規化的部分在於：
- 當參照對象會變、而歷史依定義不能變時，外鍵參照做不到。
- 主表綱要會演進，歷史表不該改寫已寫入的歷史。（若對歷史記錄的處理很輕量——主要是列出與比較——也可以主表與歷史表同步加欄位。）
在 PostgreSQL 中，傳統歷史表之外還有利用 JSONB 的替代方案：
create schema if not exists archive;
create type archive.action_t
as enum('insert', 'update', 'delete');
create table archive.older_versions
(
table_name text,
date timestamptz default now(),
action archive.action_t,
data jsonb
);用 row_to_json() 就能把任何表的資料塞進存檔表：
insert into archive.older_versions(table_name, action, data)
select 'hashtag', 'delete', row_to_json(hashtag)
from hashtag
where id = 720554371822432256
returning table_name, date, action, jsonb_pretty(data) as data;回傳結果範例
─[ RECORD 1 ]────────────────────────────────────────────────────────────────
table_name │ hashtag
date │ 2017-09-12 23:04:56.100749+02
action │ delete
data │ {
│ "id": 720554371822432256,
│ "date": "2016-04-14T10:08:00+02:00",
│ "uname": "Brand 1LIVESTEW",
│ "message": "#FB @ Atlanta, Georgia https://t.co/mUJdxaTbyC",
│ "hashtags": [
│ "#FB"
│ ],
│ "location": "(-84.3881,33.7489)"
│ }
INSERT 0 1- 若使用 hstore 擴充，還能靠其
-運算子計算版本間差異。 - 以 jsonb 或 hstore 記錄歷史，整個應用程式一張存檔表就夠；更重要的是能容納模型演進的生命週期，同一存檔可以放不同版本的物件。
- 代價是：jsonb 雖然強大，仍不如結構化模型加進階 SQL 引擎的完整威力——但歷史資料的處理需求通常比即時資料寬鬆得多。
以範圍表示有效期間#
歷史需求的一個變形：資料過了有效期之後仍要參與處理。做財務分析或會計時，外幣發票必須對上開票當時的有效匯率，而不是最新匯率。
create table rates
(
currency text,
validity daterange,
rate numeric,
exclude using gist (currency with =,
validity with &&)
);select currency, validity, rate
from rates
where currency = 'Euro'
and validity @> date '2017-05-18';拜排除約束之賜，應用程式必然只收到一列：
currency │ validity │ rate
══════════╪═════════════════════════╪══════════
Euro │ [2017-05-18,2017-05-19) │ 1.240740
(1 row)查詢計畫顯示走的是排除約束附帶的特殊 GiST 索引（Index Scan using rates_currency_validity_excl），速度有保障。
需要保存「只在一段期間內有效」的值時，考慮 PostgreSQL 的範圍型別（range types）搭配保證不重疊的排除約束——這是很強大的技巧。
預先計算的值#
若應用程式每次存取資料都在計算相同的衍生值，可以在 PostgreSQL 端預先算好：
- 若計算規則只用到同一個 tuple 的資訊，可設為欄位的預設值。
- 或用 before trigger 計算後直接存入表中的欄位。（trigger 在本書後面有解決此情境的實例。）
列舉型別#
可以用 ENUM 取代參照表。處理一小串固定項目時，正規化做法是用專屬表管理接受值的目錄，並在各處引用它；但當查詢用到的關聯數超過 join_collapse_limit 或 from_collapse_limit 時，PostgreSQL 最佳化器可能失手——此時用 ENUM 取代參照表可能有利。
單一屬性多值#
CSV 反模式展示了「文字＋分隔符」多值欄位的所有缺點。但在同一列管理一個屬性的多個值，確實能減少應用程式要管理的列數（正規化做法是側表加主鍵參照）。
- 借助 PostgreSQL 對陣列的搜尋與索引支援，有時把清單存成主表的陣列屬性更有效率——尤其當應用程式常要刪除項目連同其所有關聯資料時。
- 若需要多個屬性各含多值，可考慮複合型別的陣列；但這種模型勝過正規化綱要的情況很少，而且管理這種複雜度並不便宜。
稀疏矩陣模型#
當每列有大量選用屬性、其中多數從未使用時，可以把這些屬性反正規化成一個 JSONB 額外欄位，全部收在單一文件裡。只要這個 jsonb 屬性裡的值不被綱要其他地方引用，且應用程式只需要整份取用，jsonb 就是取代正規化綱要的好取捨。
分割#
分割（partitioning）是把列數過多的表拆成多張各存一部分列的表，有 list、range 等方式；PostgreSQL 10 起直接支援表分割。分割本身不算反正規化，但 PostgreSQL 實作上的限制使它值得列在本節。根據 PostgreSQL（10）文件：
- 沒有機制自動在所有分割上建立對應索引，必須逐一下指令；也因此無法建立橫跨所有分割的主鍵、唯一約束或排除約束，只能約束個別葉分割。
- 分割表不支援主鍵，因此不支援指向分割表的外鍵，分割表指向其他表的外鍵亦不支援。
- 對分割表使用
ON CONFLICT會報錯，因為唯一／排除約束只能建在個別分割上，無法跨整個分割階層強制唯一性。 - 造成列跨分割移動的
UPDATE會失敗（新值不滿足原分割的隱含約束）。 - 列觸發器必須定義在個別分割上，而非分割母表。
在 PostgreSQL 10 使用分割，等於因缺少涵蓋性主鍵而連 1NF 都達不到，也失去以外鍵參照該表的能力。分割任何表之前——如同任何反正規化技巧——請先做功課：確認正規化模型真的撐不住應用的工作負載。
其他反正規化工具#
hstore、ltree、intarray、pg_trgm 等 PostgreSQL 擴充提供另一組有趣的取捨，用於特定情境。例如 ltree 可實作巢狀分類目錄，並在目錄中精準引用文章。
謹慎反正規化#
前面說過，值得再說一次：只有在清楚自己在做什麼、且再三確認沒有其他方式能以要求的效能實作業務情境時，才反正規化。
- 首先，查詢最佳化技巧——主要是改寫查詢，直到 PostgreSQL 一看就知道怎麼執行最好——能帶你走很遠。把查詢從幾分鐘改到幾毫秒的生產案例屢見不鮮，尤其是 ORM 或其他天真工具產生的查詢。
- 其次，反正規化是操作取捨的最佳化技巧。再引一次羅布・派克在 Notes on Programming in C 的第一條規則：
規則一：你無法預知程式會把時間花在哪。瓶頸出現在意想不到的地方，所以在證明瓶頸就在那裡之前，別自作聰明塞進速度補丁。
這條規則對資料庫模型同樣適用——甚至更棘手，因為我們通常只量測查詢的執行時間，而沒有量測以下這些時間：
- 理解資料庫模型
- 理解如何用這個模型解新的業務情境
- 撰寫應用程式所需的 SQL 查詢
- 驗證資料品質
所以再說一次：只有在別無選擇時，才拿這些好性質去冒反正規化的風險。