反正規化資料型別 • PostgreSQL 的藝術

麥可・史東布雷克（Michael Stonebraker）主導的 PostgreSQL 專案，核心理念一直是可擴充性（extensibility）。這個設計選擇的結果之一，是某些型別允許繞過關聯式約束：例如陣列（array）把多個值存在同一個屬性值裡。在標準 SQL 中陣列內容是完全不透明的，只能整個看待；PostgreSQL 的可擴充設計則讓 SQL 語言得以增添新能力——為反正規化型別打造專屬運算子，能定址到 array 或 json 屬性值內部的值，並與 SQL 完美整合。以下這些內建型別把 PostgreSQL 的處理能力帶到另一個層次。

陣列#

PostgreSQL 內建陣列支援（見 Arrays 與 Array Functions and Operators 文件章節），有趣之處在於能直接從 SQL 處理陣列元素，還包括 GIN 索引支援。

陣列可用來反正規化資料、省掉查找表（lookup table）。適用的經驗法則：你大多把陣列當整體使用，只是偶爾搜尋其中元素。更重的處理用陣列會比查找表複雜。經典的好用例是使用者自訂標籤（tags）。

範例載入了 20 萬筆美國地理定位推文到 tweet 表（欄位含 id、date、hour、uname、message、經緯度等）。原始匯入 schema 並不適合 PostgreSQL 的能力：date 與 hour 無故分開，不如合成一個 timestamptz；經緯度可以合成單一 point。因為我們關心訊息中的標籤，下面順便把所有標籤抽成 text 陣列：

begin;

create table hashtag
  (
    id        bigint primary key,
    date      timestamptz,
    uname     text,
    message   text,
    location  point,
    hashtags  text[]
  );

with matches as (
   select id,
          regexp_matches(message, '(#[^ ,]+)', 'g') as match
     from tweet
),
     hashtags as (
   select id,
          array_agg(match[1] order by match[1]) as hashtags
     from matches
group by id
)
insert into hashtag(id, date, uname, message, location, hashtags)
     select id,
            date + hour as date,
            uname,
            message,
            point(longitude, latitude),
            hashtags
       from      hashtags
            join tweet using(id);

commit;

regexp_matches() 搭配 g 旗標回傳每一個符合項（而非只有第一個），一列一個，再按推文 id 分組、以 array_agg 聚合成標籤陣列：

          id         │                    hashtags
═════════════════════╪═════════════════════════════════════════════════
  720553447402160128 │ {#CriminalMischief,#ocso,#orlpol}
  720553457015324672 │ {#txwx}
  720553466804989952 │ {#Philadelphia,#quiz}
  720553475923271680 │ {#Retail,#hiring!,#job}
  ...
(10 rows)

處理標籤前先建立專門的 GIN index，讓 PostgreSQL 索引陣列的內容（標籤本身），而不是把每個陣列當不透明值：

create index on hashtag using gin (hashtags);

資料集中 #job 是熱門標籤，數數它出現幾次，順便確認索引真的被用來搜尋陣列內部：

explain (analyze, verbose, costs off, buffers)
 select count(*)
  from hashtag
 where hashtags @> array['#job'];

查詢計畫：GIN 索引的 Bitmap Index Scan

                            QUERY PLAN
══════════════════════════════════════════════════════════════════════
 Aggregate (actual time=27.227..27.227 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=3715
   -> Bitmap Heap Scan on public.hashtag (actual time=13.023..23.453
… rows=17763 loops=1)
          Output: id, date, uname, message, location, hashtags
          Recheck Cond: (hashtag.hashtags @> '{#job}'::text[])
          Heap Blocks: exact=3707
          Buffers: shared hit=3715
          -> Bitmap Index Scan on hashtag_hashtags_idx (actual time=1
…1.030..11.030 rows=17763 loops=1)
                Index Cond: (hashtag.hashtags @> '{#job}'::text[])
                Buffers: shared hit=8
 Planning time: 0.596 ms
 Execution time: 27.313 ms
(13 rows)

上面是已知熱門標籤的情況。要從資料本身發現熱門標籤，就用 unnest()：

  select tag, count(*)
    from hashtag, unnest(hashtags) as t(tag)
group by tag
order by count desc
   limit 10;

     tag      │ count
══════════════╪═══════
 #Hiring      │ 37964
 #Jobs        │ 24776
 #CareerArc   │ 21845
 #Job         │ 21368
 #job         │ 17763
 #Retail      │  7867
 ...
(10 rows)

這個查詢必須掃過全表的標籤，當然不會用前面的索引。unnest() 是 PostgreSQL 處理陣列的必備函式：它把陣列內容當成另一個關聯來處理——而 SQL 本來就備齊了處理關聯的所有工具。

招聘（hiring）主題在這個資料集裡很大。接著搜尋 #Retail 部門的職缺，並看看他們宣稱在哪些地點招人：

  select name,
         substring(timezone, '/(.*)') as tz,
         count(*)
    from hashtag

         left join lateral
         (
             select *
               from geonames
           order by location <-> hashtag.location
              limit 1
         )
         as geoname
         on true

   where hashtags @> array['#Hiring', '#Retail']

group by name, tz
order by count desc
   limit 10;

這裡另外匯入了 geonames 資料集，left join lateral 搭配 order by location <-> hashtag.location limit 1 挑出離推文位置最近的地名。
where 子句只比對同時包含 #Hiring 與 #Retail 的標籤陣列。

                       name                       │     tz      │ count
══════════════════════════════════════════════════╪═════════════╪═══════
 San Jose City Hall                               │ Los_Angeles │    31
 Sleep Inn & Suites Intercontinental Airport East │ Chicago     │    19
 Los Angeles                                      │ Los_Angeles │    14
 Dallas City Hall Plaza                           │ Chicago     │    12
 New York City Hall                               │ New_York    │    11
 ...
(10 rows)

PostgreSQL 陣列很強大，GIN 索引也讓它有效率——但還沒有效率到能在大量查找的場景取代查找表。另外，某些陣列函式呈現二次方（quadratic）行為：用迴圈逐一處理陣列元素真的很低效，請學會改用 unnest() 並以 where 子句過濾。如果你發現自己常常這麼做，那很可能是「其實需要一張查找表」的訊號。

複合型別#

PostgreSQL 的資料表由已知型別的元組構成，而這個型別可以獨立於資料表來管理：

begin;

create type rate_t as
  (
    currency text,
    validity daterange,
    value    numeric
  );

create table rate of rate_t
  (
    exclude using gist (currency with =,
                        validity with &&)
  );

insert into rate(currency, validity, value)
     select currency, validity, rate
       from rates;

commit;

這個 rate 表的行為與前一章定義的 rates 表完全相同，查詢起來也一樣。複合型別在本書未涵蓋的進階場景才比較有建構價值：

預存程序（stored procedure）API 的管理
複合型別陣列的進階用例

XML#

SQL 標準包含 SQL/XML：預定義 XML 資料型別，加上建構子、多個常式與函式、XML 與 SQL 型別的映射，以支援在 SQL 資料庫中操作與儲存 XML。PostgreSQL 實作了 XML 型別（見 XML type 與 XML functions 文件章節）。

需要處理 XML 文件時，最佳選項可能是 XSLT 轉換語言——不意外地，有個 PostgreSQL 擴充讓你用這個語言寫預存程序：PL/XSLT。

延伸範例：PL/XSLT 的 striptags 函式

create extension plxslt;

CREATE OR REPLACE FUNCTION striptags(xml) RETURNS text
    LANGUAGE xslt
AS $$<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns="http://www.w3.org/1999/xhtml"
>

  <xsl:output method="text" omit-xml-declaration="yes"/>

  <xsl:template match="/">
    <xsl:apply-templates/>
  </xsl:template>

</xsl:stylesheet>
$$;

使用方式：

create table docs
  (
    id      serial primary key,
    content xml
  );

insert into docs(content)
     values ('<?xml version="1.0"?>
<html xmlns="http://www.w3.org/1999/xhtml">
<body>hello</body>
</html>');

select id, striptags(content)
  from docs;

結果如預期，回傳去掉標籤後的文字 hello。

XML 支援偶爾派得上用場，但它主要是為了標準相容而加入的，實務上不常見；PostgreSQL 的 XML 處理函式與 XML 索引都相當有限。

JSON#

PostgreSQL 內建 JSON 支援，處理函式與運算子非常齊全，索引支援也完整（見 JSON Types 與 JSON Functions and Operators 文件章節）。

PostgreSQL 早在 9.2 版就實作了非常簡單的 JSON 型別。當時社群急於為 JSON 使用者提供方案，步調比平常的謹慎快了些：json 型別骨子裡是 text，只驗證輸入格式是否為合法 JSON……跟 XML 一個樣。
後來社群意識到，在 text 型別之上實作大量 JSON 處理與進階搜尋既不容易也不合理，於是實作了二進位版本的 jsonb，配備完整的運算子與函式——有人主張 b 代表 better。

兩者的差異：

json 是文字型別，原樣儲存送進來的資料表示：空白、縮排、重複的鍵全部保留（除了格式驗證外不做任何處理）。
jsonb 是進階的二進位儲存格式，具備完整的處理、索引與搜尋能力；會把 JSON 預處理成內部格式——每個鍵只留一個值，也不受多餘空白或縮排影響。

你需要、也應該用的型別是 jsonb；json 這個早期草稿只為了向後相容而留著。

快速範例看差異：

create table js(id serial primary key, extra json);
insert into js(extra)
     values ('[1, 2, 3, 4]'),
            ('[2, 3, 5, 8]'),
            ('{"key": "value"}');

select * from js where extra @> '2';

ERROR: operator does not exist: json @> unknown
HINT: No operator matches the given name and argument type(s).
You might need to add explicit type casts.

json 只是文字，連包含（contains）運算子都沒有實作。把欄位改成 jsonb 再試：

alter table js alter column extra type jsonb;

select * from js where extra @> '2';

 id │    extra
════╪══════════════
  1 │ [1, 2, 3, 4]
  2 │ [2, 3, 5, 8]
(2 rows)

也可以搜尋「包含另一個 JSON 陣列」的 JSON 陣列——where extra @> '[2,4]' 就只找到 [1, 2, 3, 4] 那一列。

實務上最常見的兩種 JSON 用例：

應用程式要管理的一組文件剛好是 JSON 格式。
設計者對資料模型某部分需要哪些欄位還不確定，希望模型能非常容易擴充。

第一種情況，jsonb 大幅提升應用程式處理文件的能力，包括以文件內容搜尋與過濾。可參考文件的 jsonb Indexing 一節，其中 jsonb_path_ops 為 @> 運算子提供非常好的通用索引：

create index on js using gin (extra jsonb_path_ops);

第二種情況，是把 PostgreSQL 當 schemaless 服務用、讓異質文件同居一個關聯。這種取捨在模型設計與維護的角度聽起來誘人，但日常查詢與應用開發的代價很高：你永遠不確定 jsonb 欄位裡會有什麼，SQL 敘述得非常小心，不然很容易漏掉本想命中的資料列。

好的折衷：以傳統方式設計並管理一組靜態欄位，另加一個 jsonb 的 extra 欄位收留那些「還不確定、偶爾才用」的東西（例如除錯或特殊案例）。一旦應用程式碼開始在每個場景查詢 extra 欄位——因為重要資料只存在那裡——就該把 extra 內容的相關部分升格為正式的關聯屬性了。

Enum#

這個型別是為了讓 MySQL 遷移更容易才加進 PostgreSQL 的。正規的關聯式設計會改用參考表（reference table）加外鍵：

create table color(id serial primary key, name text);

create table cars
 (
   brand   text,
   model   text,
   color   integer references color(id)
 );

insert into color(name)
     values ('blue'), ('red'),
            ('gray'), ('black');

insert into cars(brand, model, color)
     select brand, model, color.id
      from (
             values('ferari', 'testarosa', 'red'),
                   ('aston martin', 'db2', 'blue'),
                   ('bentley', 'mulsanne', 'gray'),
                   ('ford', 'T', 'black')
           )
              as data(brand, model, color)
           join color on color.name = data.color;

color 表列出可選顏色，cars 表登記某品牌某車型在某顏色下的供應。同一件事也可以用 enum 型別：

create type color_t as enum('blue', 'red', 'gray', 'black');

drop table if exists cars;
create table cars
 (
   brand  text,
   model  text,
   color  color_t
 );

insert into cars(brand, model, color)
     values ('ferari', 'testarosa', 'red'),
            ('aston martin', 'db2', 'blue'),
            ('bentley', 'mulsanne', 'gray'),
            ('ford', 'T', 'black');

MySQL 沒有 create type ... as enum 敘述，每個使用 enum 的欄位都會得到自己的匿名資料型別——每欄一個獨立型別，需要全域一致狀態時就祝你好運了。在 PostgreSQL 裡用不用 enum 大多是品味問題：畢竟對小型參考表的 join，PostgreSQL 的 SQL 引擎支援得很好。