資料庫與叢集#

PostgreSQL 屬於資料庫管理系統這一類程式。當這個程式正在執行時，我們稱它為 PostgreSQL 伺服器（server）或實例（instance）。

PostgreSQL 管理的資料存放在資料庫（database）中。單一 PostgreSQL 實例可同時服務多個資料庫，它們合稱為資料庫叢集（database cluster）。

要使用叢集，必須先初始化（建立）它。存放叢集所有相關檔案的目錄，通常依指向該目錄的環境變數命名為 PGDATA。

從預編譯套件安裝時，可能會在標準 PostgreSQL 機制之上加入自己的「抽象層」，明確設定各工具所需的所有參數。這種情況下資料庫伺服器以作業系統服務的形式執行，你可能從未直接接觸過 PGDATA 變數。但這個術語已經約定俗成，本書仍會沿用。

叢集初始化後，PGDATA 中會有三個一模一樣的資料庫：

• template0：用於從邏輯備份還原資料、或建立使用不同編碼的資料庫等情境；絕對不可修改。
• template1：作為使用者在叢集中建立其他所有資料庫時的範本。
• postgres：一般資料庫，可自行斟酌使用。

系統目錄#

所有叢集物件（表格、索引、資料型別、函式等）的中繼資料，都存放在屬於系統目錄（system catalog）的表格中。

• 每個資料庫都有自己一套描述該資料庫物件的表格（與視圖）。
• 少數系統目錄表格為整個叢集共用，不屬於任何特定資料庫（技術上使用一個 OID 為零的虛設資料庫），但可從所有資料庫存取。

系統目錄可用一般 SQL 查詢檢視，但所有修改都必須透過 DDL 指令進行。psql 客戶端也提供一整套顯示系統目錄內容的指令。

命名慣例如下：

• 所有系統目錄表格名稱以 pg_ 開頭，例如 pg_database
• 欄位名稱以三個字母的前綴開頭，通常對應表格名稱，例如 datname
• 所有系統目錄表格中，宣告為主鍵的欄位一律叫 oid（object identifier，物件識別碼），其型別同樣叫 oid，是 32 位元整數

綱要#

綱要（schema）是存放資料庫所有物件的命名空間。除了使用者綱要之外，PostgreSQL 還提供數個預定義綱要：

不同使用者的暫存表格其實建立在不同的 pg_temp_N 綱要中，但每個人都用 pg_temp 這個別名來指涉自己的物件。

每個綱要都局限於特定資料庫，而所有資料庫物件都歸屬於某個綱要。

存取物件時若未明確指定綱要，PostgreSQL 會從搜尋路徑（search path）中挑選第一個合適的綱要。搜尋路徑以 search_path 參數的值為基礎，並隱含地補上 pg_catalog 及（必要時）pg_temp。這表示不同綱要可以包含同名物件。

表空間#

資料庫與綱要決定的是物件的邏輯分佈；表空間（tablespace）則定義實體資料佈局。表空間實質上就是檔案系統中的一個目錄。

• 可以把資料分散到不同表空間：封存資料放慢速磁碟，頻繁更新的資料放快速磁碟
• 一個表空間可供不同資料庫使用，一個資料庫也可把資料存放在多個表空間中

邏輯結構與實體佈局彼此獨立——這正是表空間設計的核心價值。

每個資料庫都有所謂的預設表空間。除非指定其他位置，所有資料庫物件都建立在此；該資料庫的系統目錄物件也存放於此。

叢集初始化時會建立兩個表空間：

• pg_default：位於 PGDATA/base 目錄。除非明確選擇其他表空間，否則作為預設表空間使用。
• pg_global：位於 PGDATA/global 目錄。存放整個叢集共用的系統目錄物件。

建立自訂表空間時可指定任意目錄，PostgreSQL 會在 PGDATA/pg_tblspc 目錄中建立指向該位置的符號連結。

PostgreSQL 使用的所有路徑都相對於 PGDATA 目錄，因此你可以把整個目錄搬到別的位置（當然，前提是已經停掉伺服器）。

關聯#

表格與索引是最重要的資料庫物件。儘管兩者差異甚大，卻有一個共通點：它們都由列（row）構成。對表格而言這點顯而易見，但對 B-tree 節點同樣成立——節點裡裝的是被索引的值以及指向其他節點或表格列的參照。

其他一些物件也具備相同結構：

• 序列（sequence）：實質上是單列表格
• 物化視圖（materialized view）：可想成「記住」了對應查詢的表格
• 一般視圖：不儲存任何資料，但其他方面與表格非常相似

在 PostgreSQL 中，這些物件統稱為關聯（relation）。

檔案與分支#

與關聯相關的所有資訊，存放在數個不同的分支（fork）中，每個分支容納特定類型的資料。

分支一開始由單一檔案代表，檔名由數字 ID（oid）構成，並可加上對應分支類型的後綴。檔案會隨時間成長，當大小達到 1 GB 時就會建立該分支的另一個檔案（這些檔案有時稱為 segment），並在檔名結尾加上區段序號。

1 GB 的檔案大小限制是歷史因素，為了支援無法處理大檔案的各種檔案系統。建置 PostgreSQL 時可用 ./configure --with-segsize 修改此限制。

因此，單一關聯在磁碟上是由多個檔案代表的。即使是一張沒有索引的小表格，也至少會有三個檔案（依必要分支的數量）。

除 pg_global 外，每個表空間目錄都包含各資料庫的獨立子目錄；屬於同一表空間與資料庫的所有物件檔案都位於同一子目錄中。

必須把這點納入考量：單一目錄中檔案過多時，檔案系統可能無法妥善處理。

主分支#

主分支（main fork）代表實際資料：表格列或索引列。除了不含資料的視圖之外，任何關聯都有這個分支。

主分支的檔案以其數字 ID 命名，該 ID 存放在 pg_class 表格的 relfilenode 值中。

=> CREATE UNLOGGED TABLE t(
   a integer,
   b numeric,
   c text,
   d json
);
=> INSERT INTO t VALUES (1, 2.0, 'foo', '{}');
=> SELECT pg_relation_filepath('t');
 pg_relation_filepath
−−−−−−−−−−−−−−−−−−−−−−
 base/16384/16385
(1 row)

路徑的解讀方式：base 目錄對應 pg_default 表空間，下一層子目錄用於資料庫，我們要找的檔案就在那裡。

=> SELECT oid FROM pg_database WHERE datname = 'internals';
  oid
−−−−−−−
 16384
(1 row)

=> SELECT relfilenode FROM pg_class WHERE relname = 't';
 relfilenode
−−−−−−−−−−−−−
       16385
(1 row)

=> SELECT size
FROM pg_stat_file('/usr/local/pgsql/data/base/16384/16385');
 size
−−−−−−
 8192
(1 row)

初始化分支#

初始化分支（initialization fork）只存在於未記錄表格（以 UNLOGGED 子句建立）及其索引。這類物件與一般物件相同，差別在於對它們執行的任何動作都不會寫入預寫式日誌。

• 好處：這些操作快得多
• 代價：發生故障時無法還原一致的資料

因此 PostgreSQL 在復原期間會直接刪除這類物件的所有分支，並用初始化分支覆寫主分支，藉此建立一個空的虛設檔案——未記錄表格的資料在故障後必然全部遺失。

初始化分支的檔名與主分支相同，但帶 _init 後綴：

=> SELECT size
FROM pg_stat_file('/usr/local/pgsql/data/base/16384/16385_init');
 size
−−−−−−
    0
(1 row)

可用空間映射#

可用空間映射（free space map，FSM）追蹤頁面內的可用空間。它的量一直在變動：清理（vacuum）後增加，新列版本出現時減少。FSM 用來快速找出能容納新插入資料的頁面。

相關檔案帶 _fsm 後綴。一開始不會建立這類檔案，只有在必要時才出現；最簡單的取得方式是清理表格：

=> VACUUM t;
=> SELECT size
FROM pg_stat_file('/usr/local/pgsql/data/base/16384/16385_fsm');
 size
−−−−−−−
 24576
(1 row)

為加速搜尋，可用空間映射組織成樹狀結構，至少佔用三個頁面——這解釋了為何一張近乎空白的表格其 FSM 檔案仍有 24 kB。

表格與索引都有可用空間映射。但由於索引列無法插入任意頁面（例如 B-tree 由排序順序決定插入位置），PostgreSQL 對索引只追蹤那些已被完全清空、可在索引結構中重複使用的頁面。

可見性映射#

可見性映射（visibility map，VM）能快速判斷某個頁面是否需要清理或凍結。它為每個表格頁面提供兩個位元：

• 第一個位元：頁面只含最新列版本時設定。清理會跳過這類頁面（沒東西可清）。此外，當交易嘗試從這類頁面讀取列時，無須檢查可見性，因此可以使用唯索引掃描（index-only scan）。
• 第二個位元：頁面只含已凍結列版本時設定。本書用凍結映射（freeze map）一詞指稱分支的這個部分。

可見性映射檔案帶 _vm 後綴，通常是最小的：

=> SELECT size
FROM pg_stat_file('/usr/local/pgsql/data/base/16384/16385_vm');
 size
−−−−−−
 8192
(1 row)

可見性映射只提供給表格，索引沒有。

頁面#

為了方便 I/O，所有檔案在邏輯上都切分成頁面（page，或稱 block），頁面是可讀寫的最小資料單位。因此，許多 PostgreSQL 內部演算法都是針對頁面處理而調校的。

• 頁面大小通常為 8 kB
• 可在某種程度上調整（最大 32 kB），但只能在建置時設定（./configure --with-blocksize），而且幾乎沒人這麼做
• 一旦建置並啟動，實例只能處理同一大小的頁面；無法建立支援不同頁面大小的表空間

不論屬於哪個分支，所有檔案在伺服器端的處理方式大致相同：頁面先被移入緩衝快取（在那裡可被行程讀取與更新），再依需要刷回磁碟。

TOAST#

每一列都必須放進單一頁面——沒有辦法讓一列延續到下一頁。為了儲存長列，PostgreSQL 使用一種稱為 TOAST（The Oversized Attributes Storage Technique，超大屬性儲存技術）的特殊機制。

TOAST 蘊含幾種策略：

• 把長屬性值切成較小的「吐司片」，移入獨立的服務表格
• 壓縮長值，讓該列能放進頁面
• 兩者兼施：先壓縮，再切片搬移

若主表格含有潛在的長屬性，系統會立刻為它建立一張對應的 TOAST 表格（所有屬性共用一張）。例如表格若有 numeric 或 text 型別的欄位，即使該欄位永遠不會存放長值，TOAST 表格仍會被建立。

對索引而言，TOAST 機制只能提供壓縮，不支援把長屬性搬到獨立表格——這限制了可被索引的鍵大小（實際實作取決於特定的運算子類別）。

四種儲存策略#

預設策略依欄位的資料型別選定。最簡單的檢視方式是在 psql 執行 \d+，也可以直接查系統目錄：

=> SELECT attname, atttypid::regtype,
  CASE attstorage
    WHEN 'p' THEN 'plain'
    WHEN 'e' THEN 'external'
    WHEN 'm' THEN 'main'
    WHEN 'x' THEN 'extended'
  END AS storage
FROM pg_attribute
WHERE attrelid = 't'::regclass AND attnum > 0;
 attname | atttypid | storage
−−−−−−−−−+−−−−−−−−−−+−−−−−−−−−−
 a       | integer | plain
 b       | numeric | main
 c       | text     | extended
 d       | json     | extended
(4 rows)

演算法#

PostgreSQL 的目標是每頁至少容納四列。因此若列的大小超過頁面（扣除標頭）的四分之一——標準大小的頁面約為 2000 位元組——就必須對某些值套用 TOAST 機制。依下列流程進行，一旦列長度不再超過門檻就停止：

1. 首先走訪使用 external 與 extended 策略的屬性，從最長的開始。extended 屬性會被壓縮，若壓縮結果（單看自身、不計其他屬性）仍超過頁面的四分之一，就立刻搬到 TOAST 表格。external 屬性處理方式相同，只是跳過壓縮階段。
2. 若第一輪後列仍放不進頁面，則把剩餘使用 external 或 extended 策略的屬性逐一搬入 TOAST 表格。
3. 若仍無濟於事，嘗試壓縮使用 main 策略的屬性，同時把它們留在表格頁面中。
4. 若列還是不夠短，才把 main 屬性搬入 TOAST 表格。

門檻值為 2000 位元組，但可用 toast_tuple_target 儲存參數在表格層級重新定義。

有時修改某些欄位的預設策略會很有用：若事先就知道某欄位的資料無法壓縮（例如存放 JPEG 影像），可為該欄位設定 external 策略，避免徒勞的壓縮嘗試：

=> ALTER TABLE t ALTER COLUMN d SET STORAGE external;

TOAST 表格的內部結構#

TOAST 表格位於名為 pg_toast 的獨立綱要中，該綱要不在搜尋路徑內，所以 TOAST 表格通常是隱藏的。暫存表格則使用 pg_toast_temp_N 綱要，與 pg_temp_N 類似。

=> SELECT relnamespace::regnamespace, relname
FROM pg_class
WHERE oid = (
   SELECT reltoastrelid
   FROM pg_class WHERE relname = 't'
);
 relnamespace |    relname
−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−
 pg_toast     | pg_toast_16385
(1 row)

=> \d+ pg_toast.pg_toast_16385
TOAST table "pg_toast.pg_toast_16385"
   Column   | Type    | Storage
−−−−−−−−−−−−+−−−−−−−−−+−−−−−−−−−
 chunk_id   | oid     | plain
 chunk_seq | integer | plain
 chunk_data | bytea   | plain
Owning table: "public.t"
Indexes:
    "pg_toast_16385_index" PRIMARY KEY, btree (chunk_id, chunk_seq)
Access method: heap

被切片的分塊使用 plain 策略是合乎邏輯的——沒有第二層 TOAST。

除了 TOAST 表格本身，PostgreSQL 還會在同一綱要中建立對應索引，存取 TOAST 分塊一律透過這個索引。

有了 TOAST 表格，一張表格所使用的分支檔案最小數量會增加到八個：主表格三個、TOAST 表格三個、TOAST 索引兩個。

=> UPDATE t SET c = repeat('A',5000);
=> SELECT * FROM pg_toast.pg_toast_16385;
 chunk_id | chunk_seq | chunk_data
−−−−−−−−−−+−−−−−−−−−−−+−−−−−−−−−−−−
(0 rows)

=> UPDATE t SET c = (
  SELECT string_agg( chr(trunc(65+random()*26)::integer), '')
  FROM generate_series(1,5000)
)
RETURNING left(c,10) || '...' || right(c,10);
         ?column?
−−−−−−−−−−−−−−−−−−−−−−−−−
 YEYNNDTSZR...JPKYUGMLDX
(1 row)
UPDATE 1

=> SELECT chunk_id,
  chunk_seq,
  length(chunk_data),
  left(encode(chunk_data,'escape')::text, 10) || '...' ||
  right(encode(chunk_data,'escape')::text, 10)
FROM pg_toast.pg_toast_16385;
 chunk_id | chunk_seq | length |        ?column?
−−−−−−−−−−+−−−−−−−−−−−+−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−
    16390 |         0 |   1996 | YEYNNDTSZR...TXLNDZOXMY
    16390 |         1 |   1996 | EWEACUJGZD...GDBWMUWTJY
    16390 |         2 |   1008 | GSGDYSWTKF...JPKYUGMLDX
(3 rows)

存取與效能取捨#

存取長屬性時，PostgreSQL 會自動還原原始值並回傳給客戶端，對應用程式完全透明。

若查詢不涉及長屬性，TOAST 表格根本不會被讀取——這正是正式環境不該使用星號（SELECT *）的原因之一。

另外，若客戶端只查詢長值的前幾個分塊，PostgreSQL 只會讀取必要的分塊，即使該值已被壓縮。

資料壓縮與切片都很耗資源，還原原始值同樣如此。因此在 PostgreSQL 中存放笨重資料並不是好主意，特別是那些被頻繁使用、又不需要交易邏輯的資料（例如掃描的會計文件）。

較好的替代方案是把這類資料放在檔案系統中，資料庫裡只留對應的檔名——但如此一來，資料庫系統就無法保證資料一致性了。