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. 使用 volatile 默认值、存储生成列或标识的 ADD COLUMN 会在 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. 列表中或子查询中包含 NULL 的 NOT IN 永远不会为 true。除了 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. 在 PostgreSQL 18 中，多列 B-tree 可以在没有前导列的情况下为查询提供服务（跳跃扫描），但仅当该列具有较少的不同值时。 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 并从 PUBLIC 撤销 EXECUTE。 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. 全文表达式索引必须使用双参数形式的 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 连接、批量处理大变更、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)