postgresql-table-design

此技能為設計 PostgreSQL 資料庫結構提供了嚴謹的框架。專為軟體工程師、資料庫管理員及系統架構師所設計，旨在協助建立優先考慮效能、資料完整性及長期維護性的強大資料模型。遵循這些模式，使用者可以避免常見的陷阱，例如隱式轉換、不必要的索引膨脹或錯誤的資料類型選擇，確保 PostgreSQL 執行個體在高併發負載下保持穩定與可擴展性。

主要功能包括：

• 標準化的識別碼與主鍵選擇建議，針對特定情境區分 BIGINT GENERATED ALWAYS AS IDENTITY 與 UUID 的使用時機。
• 正規化 (3NF) 架構設計，並針對讀取效能需求提供審慎的去正規化方案。
• 資料類型最佳實踐，強調使用 TEXT 取代 VARCHAR(n)、TIMESTAMPTZ 取代 TIMESTAMP，以及使用 NUMERIC 處理精確貨幣運算。
• 進階索引管理，包括針對 JSONB、陣列與幾何資料的 GIN 與 GiST 索引應用。
• PostgreSQL 特有功能指導，例如約束管理、UNIQUE NULLS NOT DISTINCT 設定以及網域類型 (Domain types) 的使用。
• 關於處理資料列 churn、TOAST 儲存以及 MVCC 相關 Vacuum 機制的實務建議。

使用說明與實務提示：

• 除非有嚴格的儲存空間限制，否則應優先使用 BIGINT。
• 外鍵 (Foreign Key) 欄位必須手動建立索引，PostgreSQL 不會自動完成此步驟。
• 使用 CHECK 約束進行長度驗證，而非受限的 VARCHAR 長度，以保持架構彈性。
• 利用範圍類型 (daterange, numrange) 來簡化排程與版本控制中的重疊查詢邏輯。
• 進行全文檢索時，務必為 TSVECTOR 與 TSQUERY 指定語言參數，以確保索引效能與正確性。
• 請注意，由於事務回滾或併發操作，識別碼序列可能會出現間斷，這是預期的行為，無需刻意修復。
• 處理半結構化資料時，優先選用 JSONB 以利用 GIN 索引與高效的二進位儲存。
• 若架構需結合向量嵌入功能，請使用 pgvector 類型以支援向量相似度搜尋。