# 我所理解的 PostgreSQL 02: 資料庫物件有哪些? 該在什麼情境下使用?

{%hackmd @moment89/tXJIcGqmSI6_fJQp2hnJUA %}

---

在 PostgreSQL 中，資料庫物件 (Database Objects) 泛指儲存在資料庫中的結構性元素，Database、Schema、User、Table ... 等所有東西都是資料庫物件，它們都有唯一的 object id。

這節旨在介紹常用的資料庫物件，希望閱讀後會知道資料庫有哪些工具可以利用!

:::success
💡在 PostgreSQL 中，物件名稱預設為大小寫不敏感，系統會自動將未加雙引號的名稱轉為小寫儲存。
也就是說，即使你使用駝峰命名（例如 `MyTable`），資料庫實際會以 `mytable` 儲存。
若想保留大小寫，必須使用雙引號括住物件名稱（如 `"MyTable"`），但這會導致查詢時也需精確使用大小寫與雙引號。
:::

## [Schema](https://www.postgresql.org/docs/current/ddl-schemas.html)

可以理解為資料庫版本的命名空間 (類比 C# 的 namespace)，用來分類與管理物件。

一個 Database 下可以有多個 Schema。
Schema 和 Database 不同的是，==Database 之間是不直接互通的，但 Database 下的 Schema 之間是直接互通的==。
也就是我對單一 Database 連線，只要是我有訪問權限的 Schema，我可以在一個連線中訪問這些不同的 Schema 下的物件。

==不同 Schema 下可以有相同名稱的資料庫物件==。
例如我可以在 A Schema 下有 table_abc，在 B Schema 也下有 table_abc，這並不會衝突，它們就是兩個定義於不同 Schema 下的獨立物件。

### public Schema 是什麼?

透過 pgAdmin 左側導覽列，會發現每個 Database 下的 Schema 清單中，一定有一個名為 public 的 Schema。

<center class="half">
<img src=https://hackmd.io/_uploads/BJKcH8Lwgg.png width="200"/>
</center>

在資料庫預設參數中，有一項 `search_path` 參數，預設值為 `"$user", public`。

<center class="half">
<img src=https://hackmd.io/_uploads/SJFdLL8Plg.png width="200"/>
</center>

這項參數的作用是定義「==當一個物件未提供 Schema 名稱時預設的查詢路徑。==」

而預設值 `"$user", public` 就表示，當我們查詢物件沒有指定 Schema 名稱，它會看看有沒有跟當前使用者名稱一樣的 Schema (`$user` 會自動取代為當前使用者的名稱)、在該 Schema 下查找物件，若沒有則在 public 下查找物件。

故在應用層面
1. 若在開發上有將 schema 和 user 名稱一致的習慣，而某一物件在每個 schema 下都存在，則開發時可以利用這個特性避免重複的 `SELECT * FROM {different schema name}.same_table_name;` 語句。
2. ==若有希望能在查詢時省略 schema name、提供給 Database 下所有使用者都能用的共用資料庫物件，可以考慮建立在 public Schema==。
例如時間轉換、單純數值換算等功能性的物件。
3. 安裝 Extension，大多數 Extension 的功能函式預設都會放在 public Schema 下。
舉例來說，新建一個 test 資料庫，原本該資料庫的 public Schema 下沒有任何 Function (左圖)；在隨意安裝了一個 Extension，會看到同時新增了很多 Function (右圖)。

<center class="half">
<img src=https://hackmd.io/_uploads/S11NvUUvee.png height="400"/>
<img src=https://hackmd.io/_uploads/rkUbOLLvle.png height="400"/>
</center>

### 什麼時候該建立 Schema?

Schema 的使用沒有絕對規則或最佳實踐，主要依據實際應用需求與團隊偏好進行規劃即可。

希望能切分不同功能模組的資料庫物件以便管理 → ✅可以依模組分配 Schema，例如人員組織功能一個 Schema、設備資訊功能一個 Schema。

希望能依資料新舊管理 → ✅可以有用於存放即時資料 Schema 搭配存放歷史資料 Schema，前後者的資料表結構一致、用使用者決定可拿取即時資料還是歷史資料。

甚至有一派別是不使用多個 Schema，而是使用多個 Database 來確保資料隔絕。

## [Table](https://www.postgresql.org/docs/current/ddl-basics.html)

這應該是每個人對資料庫的第一印象。

可以想像成 Excel 表格，有標頭也有資料，標頭通常是定義好的，資料列則是可變動的，可能會增加、減少。

:::spoiler 補充說明
資料表的實體資料會儲存在磁碟上的一種稱為 Heap 的儲存結構中，對應的檔案位於 PostgreSQL 的資料目錄中。可以透過系統目錄 (System Catalog) 查詢表格的 object ID，再對應至資料夾中的實體檔案。若檔案過大，系統可能會將其拆分為多個檔案。

舉例來說，可透過系統資訊表 pg_database 找到資料庫的 oid、透過 pg_class 找到資料表的 oid。
在 base 資料夾下，可看到該資料庫 oid 的資料夾。
在該資料庫 oid 資料夾下可看到該物件的實體檔案。
:::

建立資料表的基本語句就是 `CREATE TABLE`，如下圖，可以建立一個沒有任何欄位的空 Table。(當然，這種 Table 無法儲存資料，對其做查詢也查不出資料。)

<center class="half">
<img src=https://hackmd.io/_uploads/rJmG9IUvgx.png width="400"/>
<img src=https://hackmd.io/_uploads/SJA758LPeg.png width="400"/>
</center>

我們當然不希望建立一張什麼都沒有的表，需要定義這個表的子物件。

以下介紹較常用的子物件 (指需要依附於物件如 Table、View、Materialized View 之上的物件)。

### Column

可以理解為 Excel 表格的標頭，用於定義欄位的名稱的型別。

欄位型別的定義可以==限制插入表格的資料格式，避免不符合預期的資料寫入==，例如我可以限制 A 欄位只能塞入數字、B 欄位只能塞入長度20以下的字串、C 欄位可以為空、D 欄位要有預設值 ... 等等。

PostgreSQL 支援的型別很多，除了基本的字串、數字，也支援 `json`、`jsonb`、`array` 型別，原生支援的型別清單可參考[官方文檔](https://www.postgresql.org/docs/current/datatype.html)。此外也可以[自訂型別](https://www.postgresql.org/docs/current/sql-createtype.html)，例如枚舉型別。

### [Constraint](https://www.postgresql.org/docs/current/ddl-constraints.html)

雖然 Column 的型別提供了一定程度的資料約束，但若有需要實現更細緻的約束，可以應用 Constraint。

1. Primary Key
* 限制==某個欄位==或==某一群欄位組合==在整個資料表中必須是唯一值且不可為空。
這相當於 Unique 約束和欄位 not null 約束的疊加。
* 此約束會自動建立索引。
在 pgAdmin 左側導覽列看不到，但可在 System Catalog 提供的系統資訊表 pg_index 查到。

<center class="half">
<img src=https://hackmd.io/_uploads/rkgY1lPDxe.pngg width="400"/>
</center>

* 一張表只能有一個 Primary Key 約束。(關聯式資料庫的標準定義)
2. Foreign Key
* 限制==某個欄位==或==某一群欄位組合==並須存在於所參照 `REFERENCES` 的另一張表中，且該欄位或該群欄位組合必須是所參照的表的 Primary Key。
* 這項約束主要用於確保資料一致性，可以設定當所參考的資料被刪除、更新時的行為。(我認為這是 Foreign Key 的核心價值，如果是使用預設的 `NO ACTION`，對資料一致性的幫助有限，因此使用外鍵時建議搭配適當的 `ON DELETE` 或 `ON UPDATE` 行為設定，以發揮其完整效用。)
* 注意，這和 Primary Key、Unique 約束不同，不會自動建立索引。
3. Unique
* 限制==某個欄位==或==某一群欄位組合==在資料表中必須是唯一值，但空值的資料列不受限制。
* 此約束會自動建立索引。
在 pgAdmin 左側導覽列看不到，但可在 System Catalog 提供的系統資訊表查到。
4. Check
* 可以設定欄位必須符合某一個 boolean 條件式的判斷，例如 A 欄位必須大於 0、B 欄位必須大於 C 欄位等。

### [Trigger](https://www.postgresql.org/docs/current/sql-createtrigger.html)

設定在對資料表執行特定操作的==之前==或==之後==自動觸發執行指定的函式 (Trigger Function)。

Trigger 的觸發頻率可以設定為 `FOR EACH ROW` 或 `FOR EACH STATEMENT`。

* `FOR EACH ROW` 表示==每一筆受影響的資料列都會觸發一次 Trigger Function==。
例如: 設定在對 A Table 每 `INSERT` 一筆資料之前，觸發 Trigger Function 去 B Table 查 C 欄位資料值一併寫入 A Table。
* `FOR EACH STATEMENT` 表示==只要操作符合條件，不論此次操作影響多少筆資料，Trigger Function 只會執行一次==。通常用於記錄操作行為或統一處理。
例如: 設定當對 A 資料表執行 `UPDATE` 操作時，不論更新幾筆資料，只觸發一次 Trigger，在 B 表中紀錄此次異動行為。

### [Partition](https://www.postgresql.org/docs/current/ddl-partitioning.html)

資料表分區是指==將一個大型資料表依照某個條件 (稱為 Partition Key) 切分成多個子表 (Partition)==，在結構上這些 Partition 是附屬於主表 (Partitioned Table) 的子物件。

當對主資料表 (Partitioned Table) 執行 DML 操作 (如 `INSERT`、`UPDATE`、`DELETE`) 時，PostgreSQL 會根據 Partition Key 的值，將資料導向對應的 Partition 子表。

在累積大量資料的時候使用 Partition 可以加速查詢效率，特別是在條件過濾 (如 `WHERE time BETWEEN...` ) 明確時。

💡注意事項
1. 若一張 Partitioned Table 下的 Partition 數量過多，查詢規劃器在查詢規劃階段會面臨額外成本，反而造成效能瓶頸。
2. 在對 Partitioned Table 插入資料時，如果相應範圍的 Partition 不存在或者沒有配置 Default Partition，是會報錯的!
3. PostgreSQL 並未內建自動建立或清除 Partition 的機制 (例如根據時間自動建立每日分區)，需透過第三方套件如 pg_partman，或自行撰寫維護腳本來實現。

## [Index](https://www.postgresql.org/docs/current/indexes.html)

Index 可以建立在 Table 或 Materialized View 等資料結構上，為==某個欄位==或==某一群欄位組合==建立 index，用於加速查詢效率。

### Index 運作機制

一般來說，`WHERE` 條件在沒有 Index 的情況下，資料庫會將表中所有資料列依序載入記憶體，逐行比對是否符合查詢條件，這稱為「全表掃描 (Sequential Scan)」。

而 Index 則是一種==額外的資料結構，它獨立於資料表==。

該檔案紀錄了欲取得的資料位在 heap file 中的實體位置 block / index (可以理解為資料的住址，例如 A 資料位在 block 2 的 index 5)，故查詢時只要將指定 block 的資料載入到記憶體即可。

PostgreSQL 預設使用 B-tree 結構來建立 Index，會將目標欄位的值排序後儲存成一棵平衡搜尋樹，查詢時依據值大小決定是向左還是向右子節點繼續搜尋，最終導引至資料所在位置。

### Index 副作用

由於每個 Index 都是獨立儲存的檔案，因此會額外占用磁碟空間。

假設 Table 本身 1GB，除了 Primary Key 還另外建有一個 index，可能各占 0.5GB，那此資料表的 Table + Index 就佔了 2GB。

此外，每當資料表發生 `INSERT`、`UPDATE` 或 `DELETE` 操作時，系統也必須同步更新相關 Index 結構，故會增加寫入成本，若是資料寫入頻繁的應用可能需要評估是否可接受。

## [View](https://www.postgresql.org/docs/current/sql-createview.html)

==相當於把一段 SQL 包裝成捷徑==，每次查詢 View 系統都會重新執行該 SQL 查詢，並即時回傳結果。

若有需要重複使用、邏輯稍微複雜或具通用性的查詢語句，可以考慮定義為 View，提升可讀性與維護性。

## [Materialized View](https://www.postgresql.org/docs/current/sql-creatematerializedview.html)

Materialized View 類似於 View，但不同的是，它==會將查詢結果「實際儲存」為資料檔案==，因此不需每次查詢都重新執行 SQL，可提升查詢效能，且支援建立 Index。

建立 Materialized View 時，系統會立即執行查詢語句，並將結果儲存。日後若來源資料異動，需透過 `REFRESH MATERIALIZED VIEW` 指令主動更新內容。

| | Materialized View | View |
| -------- | -------- | -------- |
|優點|料存為實體表、可以建立索引。|資料即時，無需手動更新。|
|缺點|來源資料異動不會即時反映，需手動刷新。|每次查詢都需重新執行 SQL，效能依查詢複雜度及來源資料量而定。|
|適合情境|資料更新頻率低、重複查詢同一批資料的情況。|需要即時資料，或邏輯複雜但可即時執行的查詢。|

## [Function](https://www.postgresql.org/docs/current/sql-createfunction.html)

用來封裝查詢邏輯，有 input、inout、output 介面。

通常來說，Function 不建議對資料 `INSERT`、`UPDATE`、`DELETE` 的操作，而是用來==執行查詢與邏輯判斷，並回傳計算結果==。
:::success
畢竟使用 Function 的方式是 `SELECT` 它，`SELECT` 字面意義是查詢，如果執行查詢動作卻會異動資料，對 Function 使用者來說有點反直覺。
:::

要注意設定 Function 時須評估所使用的函式易變性類型，以便查詢優化器正確評估其執行時機與結果快取可能性，進而提升查詢效能。➡️ [函式易變性類型介紹](https://hackmd.io/@moment89/FunctionVolatilityCategories)

Function 可使用 SQL 語法或 Procedural Languages (通常是 PL/pgSQL) 編寫。

## [Stored Procedure](https://www.postgresql.org/docs/current/sql-createprocedure.html)

用來封裝查詢邏輯，差異在於 Procedure 支援執行異動資料的行為，但不支援直接回傳查詢結果 (沒有 `RETURN` 值)。

Procedure 通常是==執行會異動資料庫內容的操作==，例如定時將資料處理後存到另一張表，或者執行資料庫維護指令。

常見用途如
* 對多張資料表執行複雜異動流程。
* 排程作業中，定時處理資料。
* 執行資料清理、統計等維護操作。

Stored Procedure 可使用 SQL 語法或 Procedural Languages (通常是 PL/pgSQL) 編寫。

## [Trigger Function](https://www.postgresql.org/docs/current/plpgsql-trigger.html)

Table 上綁定的 Trigger 本身不能直接執行邏輯，需綁定一個 Trigger Function 作為 Callback Function。

適合用於==資料表發生異動瞬間進行自動邏輯處理==，如驗證、補值等。

Trigger Function 可使用 SQL 語法或 Procedural Languages (通常是 PL/pgSQL) 編寫。

撰寫語法與一般 Function 類似，但需配合特殊變數使用，以 PL/pgSQL 舉例:
* `NEW`: 代表新資料列（適用於 `INSERT`、`UPDATE`）
* `OLD`: 代表舊資料列（適用於 `UPDATE`、`DELETE`）
* `TG_*` 系統變數：一系列 TG 開頭的變數可用於得 Trigger、來源表相關資訊。

## 個人建議

以下分享我個人在選擇使用物件的大致思路。

* 希望在資料進入資料表的瞬間進行同步加工處理 ➡️ Trigger 與 Trigger Function
* 處理邏輯需要傳遞變數或處理流程較動態 ➡️ Function 或 Stored Procedure
* 查詢重複性高且來源資料量小 ➡️ View
* 查詢重複性高且來源資料量大、即時性要求低 ➡️ Materialized View

| 資料來源量級 | 處理邏輯複雜度 | 即時性要求 | 需要輸入參數 | 是否異動資料 | 建議選項 |
| -- | -- | -- | -- | -- | -- |
| 無限制 | 高 | 無限制 | 無限制 | ==是== | Stored Procedure |
| ==少== | ==低== | 高 | 否 | 否 | View |
| 中等以上 | 低 | ==低== | 否 | 否 | Materialized View |
| 無限制 | 高 | 高 | ==是== | 否 | Function |