postgresql-docs

PostgreSQL 文件

來自 PostgreSQL 18 文件，模型容易出錯的規則：令人意外的預設值、鎖和並發陷阱、PostgreSQL 18 的變更以及文件推薦的模式。每一條規則都附有支持它的文件 URL。

涵蓋版本

參考資料遵循 PostgreSQL 18 文件（次要版本 18.6）。僅從 18 版開始成立的事實會標明「PostgreSQL 18」。請先檢查伺服器版本。在較舊的伺服器上，請將每條「PostgreSQL 18」規則視為不可用。

如何瀏覽

僅從下方表格中閱讀任務所需的參考資料。來源：SOURCES.md。

快速規則

1. 在 PostgreSQL 18 中，沒有關鍵字的產生欄位是 VIRTUAL（虛擬），在讀取時計算。對於統計數據或邏輯複寫，請寫 STORED。https://www.postgresql.org/docs/18/ddl-generated-columns.html https://www.postgresql.org/docs/18/sql-altertable.html
2. 使用識別欄位，而不是 serial。識別欄位是 NOT NULL 但不是唯一的：請加上 PRIMARY KEY。https://www.postgresql.org/docs/18/ddl-identity-columns.html
3. 自己為每個外鍵的引用欄位建立索引。PostgreSQL 只為被引用的那一方建立索引。https://www.postgresql.org/docs/18/ddl-constraints.html
4. 普通的 CREATE INDEX 在整個建置過程中會阻擋寫入。在線上的資料表上，請在交易區塊外使用 CREATE INDEX CONCURRENTLY，並在失敗時刪除它留下的 INVALID 索引。https://www.postgresql.org/docs/18/sql-createindex.html
5. 將大型資料表的限制條件加上時，先設為 NOT VALID，然後再 VALIDATE CONSTRAINT，這樣不會阻擋寫入。PostgreSQL 18 也接受對 NOT NULL 這麼做。https://www.postgresql.org/docs/18/sql-altertable.html
6. ADD COLUMN 如果使用 volatile 預設值、儲存產生欄位或識別欄位，會在 ACCESS EXCLUSIVE 鎖下重寫整個資料表。非 volatile 預設值則不會。https://www.postgresql.org/docs/18/sql-altertable.html
7. 在遷移工作階段中設定 lock_timeout，並按角色或工作階段設定 statement_timeout、lock_timeout 和 transaction_timeout，永遠不要全域設定。https://www.postgresql.org/docs/18/runtime-config-client.html
8. 使用 INSERT ... ON CONFLICT (cols) DO UPDATE 進行 Upsert，這在 READ COMMITTED 層級下總是能在並發情況下插入或更新。MERGE 在另一個工作階段插入相同鍵值時，可能會因唯一性違規而失敗。https://www.postgresql.org/docs/18/transaction-iso.html
9. 在 SQLSTATE 為 40001 和 40P01 時重試整個交易。PostgreSQL 不會自行重試。請檢查 SQLSTATE，而不是錯誤訊息。https://www.postgresql.org/docs/18/mvcc-serialization-failure-handling.html
10. 預設隔離層級是 READ COMMITTED：每個陳述式都看到一個新的快照。多行業務規則需要 SERIALIZABLE 或明確的鎖。https://www.postgresql.org/docs/18/transaction-iso.html https://www.postgresql.org/docs/18/applevel-consistency.html
11. 對於任務隊列，使用 SELECT ... FOR UPDATE SKIP LOCKED，以及當鍵值不變時使用 FOR NO KEY UPDATE。https://www.postgresql.org/docs/18/sql-select.html https://www.postgresql.org/docs/18/explicit-locking.html
12. NOT IN 如果列表或子查詢中有 NULL，則永遠不會為真。除了 count 之外的聚合函數，在零行資料上會回傳 NULL。https://www.postgresql.org/docs/18/functions-comparisons.html https://www.postgresql.org/docs/18/functions-aggregate.html
13. 分頁和 LIMIT 需要一個在唯一鍵上的 ORDER BY，否則不同執行的結果會不同。https://www.postgresql.org/docs/18/queries-limit.html
14. 將時間點儲存為 timestamptz。TIMESTAMP '2004-10-19 10:23:54+02' 是一個不帶時區的時間戳，時區偏移會被忽略。https://www.postgresql.org/docs/18/datatype-datetime.html
15. now() 是交易開始時間。使用 clock_timestamp() 取得實際時間。永遠不要使用字串 'now' 作為預設值。https://www.postgresql.org/docs/18/functions-datetime.html
16. 使用 jsonb，並用 GIN (col jsonb_path_ops) 為 @> 查詢建立索引。運算子必須應用於索引的表達式，索引才會被使用。https://www.postgresql.org/docs/18/datatype-json.html
17. PostgreSQL 18 新增了 uuidv7()，一個時間有序的 UUID，與 gen_random_uuid() 並存。https://www.postgresql.org/docs/18/functions-uuid.html
18. PostgreSQL 18 的 RETURNING 在 INSERT、UPDATE、DELETE 和 MERGE 中可以讀取 old.col 和 new.col。https://www.postgresql.org/docs/18/dml-returning.html
19. EXPLAIN ANALYZE 會執行陳述式：請將寫入操作包在 BEGIN ... ROLLBACK 中。在 PostgreSQL 18 中它會自動包含 BUFFERS。https://www.postgresql.org/docs/18/sql-explain.html https://www.postgresql.org/docs/18/using-explain.html
20. 多欄位 B-tree 在 PostgreSQL 18 中可以在沒有前置欄位的情況下服務查詢（跳躍掃描），但僅在該欄位有少量相異值時有效。https://www.postgresql.org/docs/18/indexes-multicolumn.html
21. 保持 autovacuum 開啟，並避免使用 VACUUM FULL。Autovacuum 永遠不會分析分割資料表或暫存資料表：請自己為它們執行 ANALYZE。https://www.postgresql.org/docs/18/routine-vacuuming.html
22. 列層級安全性在 ENABLE ROW LEVEL SECURITY 之前不生效，而且資料表擁有者會繞過它，除非啟用 FORCE ROW LEVEL SECURITY。https://www.postgresql.org/docs/18/ddl-rowsecurity.html
23. SECURITY DEFINER 函數需要設定 SET search_path = trusted_schema, pg_temp，並在同一交易中將 EXECUTE 從 PUBLIC 收回。https://www.postgresql.org/docs/18/sql-createfunction.html
24. 授予資料表權限不會延伸到其所使用的序列，包括 serial 欄位的序列。請單獨設定序列權限。https://www.postgresql.org/docs/18/sql-grant.html
25. 用戶端預設使用 sslmode=prefer，這不會驗證伺服器。請使用 sslmode=verify-full。https://www.postgresql.org/docs/18/libpq-ssl.html
26. 使用 format('%I', ident) 和 EXECUTE ... USING 來建立動態 SQL，永遠不要用字串串接。https://www.postgresql.org/docs/18/plpgsql-statements.html
27. 全文檢索表達式索引必須使用 2 個參數的 to_tsvector('english', col)，而查詢必須重複完全相同的格式。https://www.postgresql.org/docs/18/textsearch-tables.html

參考資料

參考文件	何時閱讀
schema-design.md	建立資料表：識別欄位與序列、產生欄位、限制條件、時間鍵、領域、綱要與 search_path、分割區設計
migrations-and-locks.md	撰寫遷移：ALTER TABLE 鎖層級與重寫、NOT VALID、並行索引建立、REINDEX、分割區連接與卸離
data-types.md	選擇數值、文字、bytea、枚舉、UUID、陣列或範圍型別，範圍上的排除限制條件
datetime.md	任何與時間戳、時區、區間、extract、date_trunc、to_char、to_timestamp 相關的事
json.md	儲存、查詢、索引或更新 JSONB、jsonpath、JSON_VALUE、JSON_TABLE、JSON 聚合函數
queries.md	SELECT 邏輯：NULL 語意、JOIN、LATERAL、分組、聚合函數、視窗函數、分頁、UNION、CTE
writes.md	INSERT ON CONFLICT、UPDATE 和 DELETE 的 JOIN、批次處理大量變更、RETURNING、MERGE、COPY、大量載入
indexes.md	選擇或審查索引：型別、欄位順序、跳躍掃描、部分索引、表達式索引、INCLUDE、pattern_ops、GIN、BRIN
concurrency.md	隔離層級、重試、列鎖與表鎖、死結、建議鎖、逾時、LISTEN 與 NOTIFY
performance.md	解讀 EXPLAIN、統計資料與延伸統計資料、VACUUM、記憶體與規劃器設定、平行查詢、pg_stat_statements
security.md	GRANT 與預設權限、角色、列層級安全性、SECURITY DEFINER、密碼、TLS 與 libpq 連線參數
plpgsql-triggers.md	撰寫函數、PL/pgSQL、動態 SQL、例外處理、觸發器、程序
text-search.md	字串函數、LIKE 與正規表示式、引號、使用 tsvector 的全文檢索、排序、ts_headline

升級時需記住的 PostgreSQL 18 變更

• initdb 預設啟用資料校驗和，pg_upgrade 需要匹配的設定。https://www.postgresql.org/docs/18/release-18.html
• 對分割資料表執行 VACUUM 和 ANALYZE 也會處理其分割區。只對父資料表操作時請寫 ONLY。https://www.postgresql.org/docs/18/release-18.html
• pg_upgrade 會保留規劃器統計資料，但不會保留延伸統計資料。https://www.postgresql.org/docs/18/release-18.html
• AFTER 觸發器以事件排入佇列時活躍的角色執行。https://www.postgresql.org/docs/18/release-18.html
• 非同步 I/O（io_method，預設為 worker），以及 effective_io_concurrency 預設值為 16。https://www.postgresql.org/docs/18/runtime-config-resource.html

舊模式

舊	當前
預設值中的 'now' 或 'today' 字面值	now(), CURRENT_DATE (https://www.postgresql.org/docs/18/datatype-datetime.html)
MD5 密碼，password 認證方法	scram-sha-256 (https://www.postgresql.org/docs/18/auth-password.html)