psql 是 PostgreSQL 的 SQL REPL，支援包含資料定義語言（DDL）在內的全部 SQL——因此你可以在主控台上對設計選擇取得即時回饋。

  • 資料庫綱要與應用程式、業務一同存活：新產品需要新資料表，既有關聯也要演進以支援新功能。和已部署的程式碼一樣，「在保持相容的前提下加功能」遠比寫第一版困難耗時。
  • 長期維護需要版本控制：生產環境的綱要版本化，以及綱要原始碼的版本化——用 SQL 檔管理綱要，這些自然就能達成。
  • 有些視覺化工具能連上既有資料庫，從 PostgreSQL 目錄（catalog）中的資料表與約束（主鍵、外鍵等）產生視覺化文件。本書聚焦於綱要本身而非其視覺呈現，因此本章提供的是可與應用程式碼一起納入版控的 SQL。

如何撰寫資料庫模型#

寫查詢時我們已學會把 SQL 存成獨立的 .sql 檔、用 psql 的參數語法（:variable、:'variable'、:"identifier"）；撰寫資料庫模型用同一套工具就夠了。

先給自己一個不干擾既有程式的實驗場：

create database sandbox;

若需要與既有 SQL 物件互動，改用 schema 比較好：

create schema sandbox;
set search_path to sandbox;

在 PostgreSQL 中，每個資料庫都是隔離環境：連線字串必須指定目標資料庫，而且一個資料庫無法存取另一個資料庫的物件（目錄各自獨立）。想在沙盒與應用模型之間做 join，就用 schema 而非獨立資料庫。

迭代綱要的簡單有效技巧：把綱要寫成一個帶明確交易控制的 SQL 腳本，結尾接上測試查詢與 rollback。PostgreSQL 連 DDL 都支援交易，所以每次執行完就自動還原，可以反覆修改、重跑，不必先清理上一輪的痕跡。

以下用一個新聞論壇應用的 MVP 來示範：文章由編輯維護的分類清單中選一個分類；使用者可閱讀文章並留言（MVP 不支援回覆留言）。我們想要一份綱要加上可玩的資料集：幾個分類、足量文章、每篇文章隨機數量的留言。

begin;

create schema if not exists sandbox;

create table sandbox.category
 (
    id   serial primary key,
    name text not null
 );

insert into sandbox.category(name)
     values ('sport'),('news'),('box office'),('music');

create table sandbox.article
 (
    id        bigserial primary key,
    category  integer references sandbox.category(id),
    title     text not null,
    content   text
 );

create table sandbox.comment
 (
    id        bigserial primary key,
    article   integer references sandbox.article(id),
    content   text
 );

insert into sandbox.article(category, title, content)
     select random(1, 4) as category,
            initcap(sandbox.lorem(5)) as title,
            sandbox.lorem(100) as content
       from generate_series(1, 1000) as t(x);

insert into sandbox.comment(article, content)
     select random(1, 1000) as article,
            sandbox.lorem(150) as content
       from generate_series(1, 50000) as t(x);

-- 結尾接測試查詢（節錄），最後 rollback
select category.name,
       count(distinct article.id) as articles,
       count(*) as comments
  from      sandbox.category
       left join sandbox.article on article.category = category.id
       left join sandbox.comment on comment.article = article.id
group by category.name
order by category.name;

rollback;

典型用法是在 psql 提示符下以 \i 執行整個腳本。

執行輸出節錄（\i schema.sql）
BEGIN
...
CREATE TABLE
INSERT 0 4
CREATE TABLE
CREATE TABLE
INSERT 0 1000
INSERT 0 50000

    name    │ articles │ comments
════════════╪══════════╪══════════
 box office │      322 │    16113
 music      │      169 │     8370
 news       │      340 │    17049
 sport      │      169 │     8468
(4 rows)

ROLLBACK

腳本另外還附了「每分類前三篇」「文章數與平均長度」「留言最多的前五篇」等測試查詢，原理相同。

產生隨機資料#

上面的腳本呼叫了 PostgreSQL 沒有內建的函式：random(int, int) 與 sandbox.lorem(int)。做法是先建一張 sandbox.lorem(word text) 資料表，用 regexp_split_to_table() 把幾段經典 Lorem Ipsum 文字拆成單字存入（過濾掉 null 與空字串），再定義：

create or replace function random(a int, b int)
  returns int
  volatile
  language sql
as $$
  select a + ((b-a) * random())::int;
$$;

create or replace function sandbox.lorem(len int)
  returns text
  volatile
  language sql
as $$
  with words(w) as (
       select word
         from sandbox.lorem
     order by random()
        limit len
  )
  select string_agg(w, ' ')
    from words;
$$;

把單字從原本的上下文抽離、再完全隨機聚合，就得到夠隨機的假文字內容。

這種 order by random() limit N 取隨機列的方法，在大資料表上效率很差。若真有大表取樣需求，請參考 Andrew Gierth（現任 PostgreSQL committer）的文章 selecting random rows from a table。

建模範例#

有了資料，就能針對 MVP 的已知使用者故事測試應用查詢——例如「列出每個分類最新的文章，並附上各文章最新的三則留言」。這時我們發現：先前的綱要漏了文章與留言的發佈時間。

因為一切都還是帶隨機資料的草稿，最簡單的做法是整個砍掉重來：

drop schema sandbox cascade;

下一版綱要為 article 與 comment 各加上 pubdate timestamptz 欄位，插入資料時用 random(now() - interval '3 months', now() + interval '1 months') 產生隨機時間戳，這需要再定義一個時間版的 random：

create or replace function random
 (
   a timestamptz,
   b timestamptz
 )
 returns timestamptz
 volatile
 language sql
as $$
   select a
          + random(0, extract(epoch from (b-a))::int)
            * interval '1 sec';
$$;

接著就能解 MVP 的第一個查詢，嚐嚐這份綱要能否落實業務規則。以下查詢列出每分類最新文章與其最新三則留言，是嵌套的 Top-N 查詢經典實作：

\set comments 3
\set articles 1

  select category.name as category,
         article.pubdate,
         title,
         jsonb_pretty(comments) as comments

    from sandbox.category
         /*
          * Classic implementation of a Top-N query
          * to fetch 3 most articles per category
          */
         left join lateral
         (
              select id,
                     title,
                     article.pubdate,
                     jsonb_agg(comment) as comments
                from sandbox.article
                     /*
                      * Classic implementation of a Top-N query
                      * to fetch 3 most recent comments per article
                      */
                     left join lateral
                     (
                          select comment.pubdate,
                                 substring(comment.content from 1 for 25) || '…'
                                 as content
                            from sandbox.comment
                           where comment.article = article.id
                        order by comment.pubdate desc
                           limit :comments
                     )
                     as comment
                     on true    -- required with a lateral join

               where category = category.id

            group by article.id
            order by article.pubdate desc
               limit :articles
         )
         as article
         on true -- required with a lateral join

order by category.name, article.pubdate desc;

一跑就會發現缺索引。索引細節後續有專章，這裡先直接補上：

create index on sandbox.article(pubdate);
create index on sandbox.comment(article);
create index on sandbox.comment(pubdate);
查詢結果節錄（每分類一篇文章＋三則最新留言的 JSONB 文件）
─[ RECORD 1 ]───────────────────────────────────────────────────
category │ box office
pubdate  │ 2017-09-30 07:06:49.681844+02
title    │ Tenetur Quis Consectetur Anim Voluptatem
comments │ [
         │      {
         │         "content": "adipisci minima ducimus r…",
         │         "pubdate": "2017-09-27T09:43:24.681844+02:00"
         │      },
         │      ...
         │ ]
═[ RECORD 2 ]═══════════════════════════════════════════════════
category │ music
...

查詢為了排版加了 jsonb_pretty() 與 substring()；嵌入應用程式碼時應移除這些額外處理。

  • 這個查詢在筆電上約 500–600ms；把 substring(comment.content from 1 for 25) || '…' 換回 comment.content 後降到約 150ms。搭配適當的快取策略（文章讀多寫少）即可用於生產環境。

這份草稿綱要是回應 MVP 的良好第一版：

  • 它遵循正規化規則（見後續章節）。
  • 主要使用情境能寫成單一查詢，即使查詢偏複雜，在上千篇文章、五萬則留言的樣本下仍跑得夠快。
  • 分類、文章、留言的編輯工作流程都容易實作。

草稿綱要就是一個 SQL 檔，因此容易納入版控、與同事共享，並部署到開發、整合與持續測試環境。若需要視覺化綱要，也有工具能連上 PostgreSQL 直接從線上綱要產生圖表。

Backward 物件關聯對映 正規化 Forward