# PostgreSQL开发规约（2018版）

LLMS 索引： [llms.txt](/llms.txt)

---

> [微信公众号原文](https://mp.weixin.qq.com/s/W1hwbl3qmjC4Dcmadc8uSg)

## 0x00背景  

> 没有规矩，不成方圆。

PostgreSQL的功能非常强大，但是要把PostgreSQL用好，需要后端、运维、DBA的协力配合。  

本文针对PostgreSQL数据库原理与特性，整理了一份开发规范，希望可以减少大家在使用PostgreSQL数据库过程中遇到的困惑。你好我也好，大家都好。  

## 0x01 命名规范

> 无名，万物之始，有名，万物之母。

【强制】 **通用命名规则**

* 本规则适用于所有对象名，包括：库名、表名、表名、列名、函数名、视图名、序列号名、别名等。
* 对象名务必只使用小写字母，下划线，数字，但首字母必须为小写字母，常规表禁止以 `_` 打头。
* 对象名长度不超过63个字符，命名统一采用 `snake_case`。
* 禁止使用SQL保留字，使用 `select pg_get_keywords();` 获取保留关键字列表。
* 禁止出现美元符号，禁止使用中文，不要以 `pg` 开头。
* 提高用词品味，做到信达雅；不要使用拼音，不要使用生僻冷词，不要使用小众缩写。

【强制】 **库命名规则**

* 库名最好与应用或服务保持一致，必须为具有高区分度的英文单词。
* 命名必须以 `<biz>-` 开头，`<biz>` 为具体业务线名称，如果是分片库必须以 `-shard` 结尾。
* 多个部分使用 `-` 连接。例如：`<biz>-chat-shard`，`<biz>-payment` 等，总共不超过三段。

【强制】 **角色命名规范**

* 数据库 `su` 有且仅有一个：`postgres`，用于流复制的用户命名为 `replication`。
* 生产用户命名使用 `<biz>-` 作为前缀，具体功能作为后缀。
* 所有数据库默认有三个基础角色： `<biz>-read`，`<biz>-write`，`<biz>-usage`，分别拥有所有表的只读，只写，函数的执行权限。
* 生产用户，ETL用户，个人用户通过继承相应的基础角色获取权限。
* 更为精细的权限控制使用独立的角色与用户，依业务而异。

【强制】 **模式命名规则**

* 业务统一使用 `<*>` 作为模式名，`<*>` 为业务定义的名称，必须设置为 `search_path` 首位元素。
* `dba`，`monitor`，`trash` 为保留模式名。
* 分片模式命名规则采用：`rel_<partition_total_num>_<partition_index>`。
* 无特殊理由不应在其他模式中创建对象。

【推荐】 **关系命名规则**

* 关系命名以表意清晰为第一要义，不要使用含混的缩写，也不应过分冗长，遵循通用命名规则。
* 表名应当使用复数名词，与历史惯例保持一致，但应尽量避免带有不规则复数形式的单词。
* 视图以 `v_` 作为命名前缀，物化视图使用 `mv_` 作为命名前缀，临时表以 `tmp_` 作为命名前缀。
* 继承或分区表应当以父表表名作为前缀，并以子表特性（规则，分片范围等）作为后缀。

【推荐】 **索引命名规则**

* 创建索引时如 **有条件** 应当指定索引名称，并与PostgreSQL默认命名规则保持一致，避免重复执行时建立重复索引。
* 用于主键的索引以 `_pkey` 结尾，唯一索引以 `_key` 结尾，用于 `EXCLUDED` 约束的索引以 `_excl` 结尾，普通索引以 `_idx` 结尾。

【推荐】 **函数命名规则**

* 以 `select`,`insert`,`delete`,`update`,`upsert` 打头，表示动作类型。
* 重要参数可以通过 `_by_ids`, `_by_user_ids` 的后缀在函数名中体现。
* 避免函数重载，同名函数尽量只保留一个。
* 禁止通过 `BIGINT/INTEGER/SMALLINT` 等整型进行重载，调用时可能产生歧义。

【推荐】 **字段命名规则**

* 不得使用系统列保留字段名：`oid`, `xmin`, `xmax`,`cmin`, `cmax`, `ctid` 等。
* 主键列通常命名为 `id`，或以 `id` 作为后缀。
* 创建时间通常命名为 `created_time`，修改时间通常命名为 `updated_time`
* 布尔型字段建议使用 `is_`，`has_` 等作为前缀。
* 其余各字段名需与已有表命名惯例保持一致。

【推荐】 **变量命名规则**

* 存储过程与函数中的变量使用命名参数，而非位置参数。
* 如果参数名与对象名出现冲突，在参数后添加 `_`，例如 `user_id_`。

【推荐】 **注释规范**

* 尽量为对象提供注释（`COMMENT`），注释使用英文，言简意赅，一行为宜。
* 对象的模式或内容语义发生变更时，务必一并更新注释，与实际情况保持同步。

## 0x02 设计规范

> Suum cuique

【强制】 **字符编码必须为UTF8**

* 禁止使用其他任何字符编码。

【强制】 **容量规划**

- 单表记录过亿，或超过10GB的量级，可以考虑开始进行分表。
- 单表容量超过1T，单库容量超过2T。需要考虑分片。

【强制】 **不要滥用存储过程**

* 存储过程适用于封装事务，减少并发冲突，减少网络往返，减少返回数据量，执行 **少量** 自定义逻辑。
* 存储过程 **不适合** 进行复杂计算，不适合进行平凡/频繁的类型转换与包装。

【强制】 **存储计算分离**

* 移除数据库中 **不必要** 的计算密集型逻辑，例如在数据库中使用SQL进行WGS84到其他坐标系的换算。
* 例外：与数据获取、筛选密切关联的计算逻辑允许在数据库中进行，如PostGIS中的几何关系判断。

【强制】 **主键与身份列**

* 每个表都必须有 **身份列**，原则上必须有主键，最低要求为拥有 **非空唯一约束**。
* 身份列用于唯一标识表中的任一元组，逻辑复制与诸多三方工具有赖于此。

【强制】 **外键**

* 不建议使用外键，建议在应用层解决。使用外键时，引用必须设置相应的动作：`SET NULL`, `SET DEFAULT`, `CASCADE`，慎用级联操作。

【强制】 **慎用宽表**

* 字段数目超过15个的表视作宽表，宽表应当考虑进行纵向拆分，通过相同的主键与主表相互引用。
* 因为MVCC机制，宽表的写放大现象比较明显，尽量减少对宽表的频繁更新。

【强制】 **配置合适的默认值**

* 有默认值的列必须添加 `DEFAULT` 子句指定默认值。
* 可以在默认值中使用函数，动态生成默认值（例如主键发号器）。

【强制】 **合理应对空值**

- 字段语义上没有零值与空值区分的，不允许空值存在，须为列配置 `NOT NULL` 约束。

【强制】 **唯一约束通过数据库强制**。

* 唯一约束须由数据库保证，任何唯一列须有唯一约束。
* `EXCLUDE` 约束是泛化的唯一约束，可以在低频更新场景下用于保证数据完整性。

【强制】 **注意整数溢出风险**

* 注意SQL标准不提供无符号整型，超过 `INTMAX` 但没超过 `UINTMAX` 的值需要升格存储。
* 不要存储超过 `INT64MAX` 的值到 `BIGINT` 列中，会溢出为负数。

【强制】 **统一时区**

* 使用 `TIMESTAMP` 存储时间，采用 `utc` 时区。
* 统一使用ISO-8601格式输入输出时间类型：`2006-01-02 15:04:05`，避免DMY与MDY问题。
* 使用 `TIMESTAMPTZ` 时，采用GMT/UTC时间，0时区标准时。

【强制】 **及时清理过时函数**

- 不再使用的，被替换的函数应当及时下线，避免与未来的函数发生冲突。

【推荐】 **主键类型**

- 主键通常使用整型，建议使用 `BIGINT`，允许使用不超过64字节的字符串。
- 主键允许使用 `Serial` 自动生成，建议使用 `Default next_id()` 发号器函数。

【推荐】 **选择合适的类型**

* 能使用专有类型的，不使用字符串。（数值，枚举，网络地址，货币，JSON，UUID等）
* 使用正确的数据类型，能显著提高数据存储，查询，索引，计算的效率，并提高可维护性。

【推荐】 **使用枚举类型**

* 较稳定的，取值空间较小（十几个内）的字段应当使用枚举类型，不要使用整型与字符串表示。
* 使用枚举类型有性能、存储、可维护性上的优势。

【推荐】 **选择合适的文本类型**

* PostgreSQL的文本类型包括 `char(n)`, `varchar(n)`, `text`。
* 通常建议使用 `varchar` 或 `text`，带有 `(n)` 修饰符的类型会检查字符串长度，会导致微小的额外开销，对字符串长度有限制时应当使用 `varchar(n)`，避免插入过长的脏数据。
* 避免使用 `char(n)`，为了与SQL标准兼容，该类型存在不合直觉的行为表现（补齐空格与截断），且并没有存储和性能优势。

【推荐】 **选择合适的数值类型**

* 常规数值字段使用 `INTEGER`。主键、容量拿不准的数值列使用 `BIGINT`。
* 无特殊理由不要用 `SMALLINT`，性能与存储提升很小，会有很多额外的问题。
* `REAL` 表示4字节浮点数，`FLOAT` 表示8字节浮点数
* 浮点数仅可用于末尾精度无所谓的场景，例如地理坐标，不要对浮点数使用等值判断。
* 精确数值类型使用 `NUMERIC`，注意精度和小数位数设置。
* 货币数值类型使用 `MONEY`。

【推荐】 **使用统一的函数创建语法**

- 签名单独占用一行（函数名与参数），返回值单启一行，语言为第一个标签。
- 一定要标注函数易变性等级：`IMMUTABLE`, `STABLE`, `VOLATILE`。
- 添加确定的属性标签，如：`RETURNS NULL ON NULL INPUT`,`PARALLEL SAFE`,`ROWS 1`，注意版本兼容性。

```sql
CREATE OR REPLACE FUNCTION
  nspname.myfunc(arg1_ TEXT, arg2_ INTEGER)
  RETURNS VOID
LANGUAGE SQL
STABLE
PARALLEL SAFE
ROWS 1
RETURNS NULL ON NULL INPUT
AS $function$
SELECT 1;
$function$;
```

【推荐】 **针对可演化性而设计**

* 在设计表时，应当充分考虑未来的扩展需求，可以在建表时适当添加1~3个保留字段。
* 对于多变的非关键字段可以使用JSON类型。

【推荐】 **选择合理的规范化等级**

- 允许适当降低规范化等级，减少多表连接以提高性能。

【推荐】 **使用新版本**

- 新版本有无成本的性能提升，稳定性提升，有更多新功能。
- 充分利用新特性，降低设计复杂度。

【推荐】 **慎用触发器**

* 触发器会提高系统的复杂度与维护成本，不鼓励使用。

## 0x03 索引规范

> Wer Ordnung hält, ist nur zu faul zum Suchen.  
>

【强制】 **在线查询必须有配套索引**

- 所有在线查询必须针对其访问模式设计相应索引，除极个别小表外不允许全表扫描。
- 索引有代价，不允许创建不使用的索引。

【强制】 **禁止在大字段上建立索引**

- 被索引字段大小无法超过2KB（1/3的页容量），原则上禁止超过64个字符。
- 如有大字段索引需求，可以考虑对大字段取哈希，并建立函数索引。或使用其他类型的索引（GIN）。

【强制】 **明确空值排序规则**

* 如在可空列上有排序需求，需要在查询与索引中明确指定 `NULLS FIRST` 还是 `NULLS LAST`。
* 注意，`DESC` 排序的默认规则是 `NULLS FIRST`，即空值会出现在排序的最前面，通常这不是期望行为。
* 索引的排序条件必须与查询匹配，如：`create index on tbl (id desc nulls last);`

【强制】 **利用GiST索引应对近邻查询问题**

- 传统B树索引无法提供对KNN问题的良好支持，应当使用GiST索引。

【推荐】 **利用函数索引**

* 任何可以由同一行其他字段推断得出的冗余字段，可以使用函数索引替代。
* 对于经常使用表达式作为查询条件的语句，可以使用表达式或函数索引加速查询。
* 典型场景：建立大字段上的哈希函数索引，为需要左模糊查询的文本列建立reverse函数索引。

【推荐】 **利用部分索引**

* 查询中查询条件固定的部分，可以使用部分索引，减小索引大小并提升查询效率。
* 查询中某待索引字段若只有有限几种取值，也可以建立几个相应的部分索引。

【推荐】 **利用范围索引**

* 对于值与堆表的存储顺序线性相关的数据，如果通常的查询为范围查询，建议使用BRIN索引。
* 最典型场景如仅追加写入的时序数据，BRIN索引更为高效。

【推荐】 **关注联合索引的区分度**

* 区分度高的列放在前面

## 0x04 查询规范

> The limits of my language mean the limits of my world.  
>
> —Ludwig Wittgenstein

【强制】 **读写分离**

- 原则上写请求走主库，读请求走从库。
- 例外：需要读己之写的一致性保证，且检测到显著的复制延迟。

【强制】 **快慢分离**

- 生产中1毫秒以内的查询称为快查询，生产中超过1秒的查询称为慢查询。
- 慢查询必须走离线从库，必须设置相应的超时。
- 生产中的在线普通查询执行时长，原则上应当控制在1ms内。
- 生产中的在线普通查询执行时长，超过10ms需修改技术方案，优化达标后再上线。
- 在线查询应当配置10ms数量级或更快的超时，避免堆积造成雪崩。
- Master与Slave角色不允许大批量拉取数据，数仓ETL程序应当从Offline从库拉取数据

【强制】 **主动超时**

- 为所有的语句配置主动超时，超时后主动取消请求，避免雪崩。
- 周期性执行的语句，必须配置小于执行周期的超时。

【强制】 **关注复制延迟**

- 应用必须意识到主从之间的同步延迟，并妥善处理好复制延迟超出合理范围的情况
- 平时在0.1ms的延迟，在极端情况下可能达到十几分钟甚至小时量级。应用可以选择从主库读取，稍后再度，或报错。

【强制】 **使用连接池**

- 应用必须通过连接池访问数据库，连接6432端口的pgbouncer而不是5432的postgres。
- 注意使用连接池与直连数据库的区别，一些功能可能无法使用（比如Notify/Listen），也可能存在连接污染的问题。

【强制】 **禁止修改连接状态**

- 使用公共连接池时禁止修改连接状态，包括修改连接参数，修改搜索路径，更换角色，更换数据库。
- 万不得已修改后必须彻底销毁连接，将状态变更后的连接放回连接池会导致污染扩散。

【强制】 **重试失败的事务**

- **查询** 可能因为并发争用，管理员命令等原因被杀死，应用需要意识到这一点并在必要时重试。
- 应用在数据库大量报错时可以触发断路器熔断，避免雪崩。但要注意区分错误的类型与性质。

【强制】 **掉线重连**

* **连接** 可能因为各种原因被中止，应用 **必须** 有掉线重连机制。
* 可以使用 `SELECT 1` 作为心跳包查询，检测连接的有消息，并定期保活。

【强制】 **在线服务应用代码禁止执行DDL**

* 不要在应用代码里搞大新闻。

【强制】 **显式指定列名**

* 避免使用 `SELECT *`，或在 `RETURNING` 子句中使用 `*`。请使用具体的字段列表，不要返回用不到的字段。当表结构发生变动时（例如，新值列），使用列通配符的查询很可能会发生列数不匹配的错误。
* 例外：当存储过程返回具体的表行类型时，允许使用通配符。

【强制】 **禁止在线查询全表扫描**

- 例外情况：常量极小表，极低频操作，表/返回结果集很小（百条记录/百KB内）。
- 在首层过滤条件上使用诸如 `!=`, `<>` 的否定式操作符会导致全表扫描，必须避免。

【强制】 **禁止在事务中长时间等待**

* 开启事务后必须尽快提交或回滚，超过10分钟的 `IDEL IN Transaction` 将被强制杀死。
* 应用应当开启AutoCommit，避免 `BEGIN` 之后没有配对的 `ROLLBACK` 或 `COMMIT`。
* 尽量使用标准库提供的事务基础设施，不到万不得已不要手动控制事务。

【强制】 **使用游标后必须及时关闭**

【强制】 **科学计数**

* `count(*)` 是 **统计行数** 的标准语法，与空值无关。
* `count(col)` 统计的是 `col` 列中的 **非空记录数**。该列中的NULL值不会被计入。
* `count(distinct col)` 对 `col` 列除重计数，同样忽视空值，即只统计非空不同值的个数。
* `count((col1, col2))` 对多列计数，即使待计数的列全为空也会被计数，`(NULL,NULL)` 有效。
* `a(distinct (col1, col2))` 对多列除重计数，即使待计数列全为空也会被计数，`(NULL,NULL)` 有效。

【强制】 **注意聚合函数的空值问题**

* 除了 `count` 之外的所有聚合函数都会忽略空值输入，因此当输入值全部为空时，结果是 `NULL`。但 `count(col)` 在这种情况下会返回0，是一个例外。
* 如果聚集函数返回空并不是期望的结果，使用 `coalesce` 来设置缺省值。

【强制】**谨慎处理空值**

- 明确区分零值与空值，空值使用 `IS NULL` 进行等值判断，零值使用常规的 `=` 运算符进行等值判断。
- 空值作为函数输入参数时应当带有类型修饰符，否则对于有重载的函数将无法识别使用何者。
- 注意空值比较逻辑：任何涉及到空值比较运算结果都是 `unknown`，需要注意 `unknown` 参与布尔运算的逻辑：
  - `and`：`TRUE or UNKNOWN` 会因为逻辑短路返回 `TRUE`。
  - `or`：`FALSE and UNKNOWN` 会因为逻辑短路返回 `FALSE`
  - 其他情况只要运算对象出现 `UNKNOWN`，结果都是 `UNKNOWN`
- 空值与 **任何值** 的逻辑判断，其结果都为空值，例如 `NULL=NULL` 返回结果是 `NULL` 而不是 `TRUE/FALSE`。
- 涉及空值与非空值的等值比较，请使用 `IS DISTINCT FROM` 进行比较，保证比较结果非空。
- 空值与聚合函数：聚合函数当输入值 **全部** 为NULL时，返回结果为NULL。

【强制】 **注意序列号空缺**

* 当使用 `Serial` 类型时，`INSERT`，`UPSERT` 等操作都会消耗序列号，该消耗不会随事务失败而回滚。
* 当使用整型作为主键，且表存在频繁插入冲突时，需要关注整型溢出的问题。

【推荐】  **重复查询使用准备语句**

- 重复的查询应当使用 **准备语句（Prepared Statement）**，消除数据库硬解析的CPU开销。
- 准备语句会修改连接状态，请注意连接池对于准备语句的影响。

【推荐】 **选择合适的事务隔离等级**

- 默认隔离等级为 **读已提交**，适合大多数简单读写事务，普通事务选择满足需求的最低隔离等级。
- 需要事务级一致性快照的写事务，请使用 **可重复读** 隔离等级。
- 对正确性有严格要求的写入事务请使用 **可序列化** 隔离等级。
- 在RR与SR隔离等级出现并发冲突时，应当视错误类型进行积极的重试。

【推荐】 **判断结果存在性不要使用count**

* 使用 `SELECT 1 FROM tbl WHERE xxx LIMIT 1` 判断是否存满足条件的列，要比Count快。
* 可以使用 `select exists(select * FROM app.sjqq where xxx limit 1)` 将存在性结果转换为布尔值。

【推荐】 **使用RETURNING子句**

* 如果用户需要在插入数据和，删除数据前，或者修改数据后马上拿到插入或被删除或修改后的数据，建议使用 `RETURNING` 子句，减少数据库交互次数。

【推荐】 **使用UPSERT简化逻辑**

* 当业务出现插入-失败-更新的操作序列时，考虑使用 `UPSERT` 替代。

【推荐】 **利用咨询锁应对热点并发**。

* 针对单行记录的极高频并发写入（秒杀），应当使用咨询锁对记录ID进行锁定。
* 如果能在应用层次解决高并发争用，就不要放在数据库层面进行。

【推荐】**优化IN操作符**

* 使用 `EXISTS` 子句代替 `IN` 操作符，效果更佳。
* 使用 `=ANY(ARRAY[1,2,3,4])` 代替 `IN (1,2,3,4)`，效果更佳。

【推荐】 **不建议使用左模糊搜索**

* 左模糊搜索 `WHERE col LIKE '%xxx'` 无法充分利用B树索引，如有需要，可用 `reverse` 表达式函数索引。

【推荐】 **使用数组代替临时表**

* 考虑使用数组替代临时表，例如在获取一系列ID的对应记录时。`=ANY(ARRAY[1,2,3])` 要比临时表JOIN好。

## 0x05 发布规范

【强制】 **发布形式**

* 目前以邮件形式提交发布，发送邮件至dba@p1.com 归档并安排提交。
* 标题清晰：xx项目需在xx库执行xx动作。
* 目标明确：每个步骤需要在哪些实例上执行哪些操作，结果如何校验。
* 回滚方案：任何变更都需要提供回滚方案，新建也需要提供清理脚本。

【强制】**发布评估**

- 线上数据库发布需要经过研发自测，主管审核，（可选QA审核），DBA审核几个评估阶段。
- 自测阶段应当确保变更在开发、预发环境执行正确无误。
  - 如果是新建表，应当给出记录数量级，数据日增量预估值，读写量级预估。
  - 如果是新建函数，应当给出压测报告，至少需要给出平均执行时间。
  - 如果是模式迁移，必须梳理清楚所有上下游依赖。
- Team Leader需要对变更进行评估与审核，对变更内容负责。
- DBA对发布的形式与影响进行评估与审核。

【强制】 **发布窗口**

* 19:00 后不允许数据库发布，紧急发布请TL做特殊说明，抄送CTO。
* 16:00点后确认的需求将顺延至第二天执行。（以TL确认时间为准）

## 0x06 管理规范

【强制】 **关注备份**

* 每日全量备份，段文件持续归档

【强制】 **关注年龄**

* 关注数据库与表的年龄，避免事物ID回卷。

【强制】 **关注老化与膨胀**

* 关注表与索引的膨胀率，避免性能劣化。

【强制】 **关注复制延迟**

* 监控复制延迟，使用复制槽时更必须十分留意。

【强制】 **遵循最小权限原则**

【强制】**并发地创建与删除索引**

* 对于生产表，必须使用 `CREATE INDEX CONCURRENTLY` 并发创建索引。

【强制】 **新从库数据预热**

* 使用 `pg_prewarm`，或逐渐接入流量。

【强制】 **审慎地进行模式变更**

* 添加新列时必须使用不带默认值的语法，避免全表重写
* 变更类型时，必要时应当重建所有依赖该类型的函数。

【推荐】 **切分大批量操作**

* 大批量写入操作应当切分为小批量进行，避免一次产生大量WAL。

【推荐】 **加速数据加载**

* 关闭 `autovacuum`，使用 `COPY` 加载数据。
* 事后建立约束与索引。
* 调大 `maintenance_work_mem`，增大 `max_wal_size`。
* 完成后执行 `vacuum verbose analyze table`。