# PostgreSQL 规约（2024版）

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

---

---------

## 0x00背景

> 没有规矩，不成方圆。

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

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

本文[第一版](/pg/pg-convention)主要针对 PostgreSQL 9.4 - PostgreSQL 10 版本 ，当前最新版本针对 PostgreSQL 15/16/17 进行更新与调整。

---------

## 0x01 命名规范

> 计算机科学只存在两个难题：缓存失效和 **命名**。

**通用命名规则**（Generic）

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

**集群命名规则** （Cluster）

* PostgreSQL 集群的命名将作为集群资源的命名空间，必须为有效的 DNS 域名，不包含任何点号与下划线。
* 集群名应当由小写字母开头，仅包含小写字母、数字、减号，符合正则表达式：`[a-z][a-z0-9-]*`。
* PostgreSQL 数据库集群命名通常以三段式结构：`pg-<biz>-<tld>`。数据库类型 / 业务名称 / 业务线或环境
* `biz` 为最能代表业务特征的英文词语，应当仅由小写字母与数字组成，不得包含连字符 `-`。
* 使用备份集群搭建某一个现有集群的延迟从库时，`biz` 名应当为 `<biz>delay`，例如 `pg-testdelay`。
* 分支一个现有集群时，可以在 `biz` 尾部添加数字：例如从 `pg-user1` 可以分支出 `pg-user2`，`pg-user3` 等
* 水平分片集群，`biz` 命名中应当包含 `shard`，并缀以分片号，例如 `pg-testshard1`，`pg-testshard2`，……
* `<tld>` 为顶层业务线，也可用于区分不同环境：例如 `-tt`，`-dev`，`-uat`，`-prod` 等。无此需要可以省略。

**服务命名规则**（Service）

* 每一套 PostgreSQL 集群会对外提供 2～6 种不等的服务，这些默认使用固定命名规则。
* 服务名以集群名作为前缀，服务类型作为后缀，例如 `pg-test-primary`，`pg-test-replica`。
* 读写服务统一以 `primary` 后缀命名，只读服务统一以 `replica` 后缀命名，这两个为必选服务。
* ETL拉取/个人用户查询以 `offline` 后缀命名，直连主库/ETL写入以 `default` 后缀命名，为选配服务。
* 同步读取服务以 `standby` 后缀命名，延迟从库服务以 `delayed` 后缀命名，少量核心库可提供此服务。

**实例命名规则**（Instance）

* 一套 PostgreSQL 集群由至少一个实例组成，每个实例都有集群内从零或一开始唯一分配的实例号。
* **实例名** 由集群名 + 实例号通过连字符 `-` 拼接而成，例如： `pg-test-1`，`pg-test-2`。
* 实例号一经分配不得修改，持续到实例下线销毁，不得重新分配使用。
* 实例名将作为监控系统数据的 `ins` 标签，附加到该实例的所有数据上。
* 如果是用主机/数据库 1:1 独占式部署，节点 Hostname 可以使用数据库实例名。

**数据库命名规则**（Database）

* 数据库库名应当与集群、应用保持一致，必须为具有高区分度的英文单词。
* 命名以 `<tld>_<biz>` 的形式构建，`<tld>` 为顶层业务线，也可用于区分不同环境，不用可以省略。
* `<biz>` 为具体业务名称，例如 `pg-test-tt` 集群可以使用库名 `tt_test` 或 `test`。这一点不强制，即允许创建不同于集群 `<biz>` 名称的其他数据库。
* 对于分片库，`<biz>` 部分必须以 `shard` 结尾，但 **不应当** 包含分片号，例如 `pg-testshard1`，`pg-testshard2` 都用 `testshard` 即可。
* 多个部分使用 `-` 连接。例如：`<biz>-chat-shard`，`<biz>-payment` 等，总共不超过三段。

**角色命名规范**（Role/User）

* 数据库超级用户 `dbsu` 有且仅有一个：`postgres`，用于流复制的用户命名为 `replicator`。
* 用于监控的用户统一命名为 `dbuser_monitor`，用于日常管理的超级用户为：`dbuser_dba`。
* 程序/服务使用的业务用户默认使用 `dbuser_<biz>` 作为用户名，例如 `dbuser_test`。来自不同服务的访问应当使用独立的业务用户区分访问。
* 个人用户申请的数据库用户同意使用 `dbp_<name>`，其中 `name` 为 LDAP 中的标准用户名。
* 默认权限组命名固定为： `dbrole_readonly`，`dbrole_readwrite`，`dbrole_admin`，`dbrole_offline`。

**模式命名规则**（Schema）

* 业务统一使用一个全局的 `<prefix>` 作为模式名，尽可能简短，默认设置为 `search_path` 首位元素。
* `<prefix>` 不得使用 `public`，`monitor` ，不得与任何 PostgreSQL 扩展使用的模式名冲突，例如： `timescaledb`，`citus`，`repack`，`graphql`，`net`，`cron`，…… 不宜使用特殊名称：`dba`，`trash`。
* 分片模式命名规则采用：`rel_<partition_total_num>_<partition_index>`。中间为总分片数，目前固定使用 8192 ，后缀是分片号，从0开始计数。如 `rel_8192_0`，…… ，`rel_8192_11`，等等。
* 创建额外的模式，或者使用 `<prefix>` 之外的模式名需要由研发解释其必要性。

**关系命名规则**（Relation）

* 关系命名以表意清晰为第一要义，不要使用含混的缩写，也不应过分冗长，遵循通用命名规则。
* 表名应当使用 **复数名词**，并与历史惯例保持一致，应尽量避免带有不规则复数形式的单词。
* 视图以 `v_` 作为命名前缀，物化视图使用 `mv_` 作为命名前缀，临时表以 `tmp_` 作为命名前缀。
* 继承或分区表应当以父表表名作为前缀，并以子表特性（规则，分片范围等）作为后缀。
* 时间范围分区使用起始区间作为命名后缀，首个分区如果无上界则由研发指定一个足够久远的时间点：年级分区：`tbl_2023`，月级分区 `tbl_202304`，天级分区 `tbl_20230405`，小时级分区 `tbl_2023040518` ，默认分区以 `_default` 结尾。
* 哈希分区命名以余数作为分区表名的后缀，列表分区由研发手工指定与列表项对应的合理分区表名。

**索引命名规则**（Index）

* 创建索引时，应当 **显式指定** 索引名称，并与PostgreSQL默认命名规则保持一致。
* 索引名称以表名作为前缀，主键索引以 `_pkey` 结尾，唯一索引以 `_key` 结尾，普通索引以 `_idx` 结尾，用于 `EXCLUDED` 约束的索引以 `_excl` 结尾。
* 使用条件索引/函数索引时，应当在索引名称中体现使用的函数与条件内容。例如 `tbl_md5_title_idx`，`tbl_ts_ge_2023_idx`，但不可超出长度限制。

**字段命名规则**（Attribute）

* 禁止使用系统列保留字段名：`oid`， `xmin`， `xmax`，`cmin`，`cmax`，`ctid` 。
* 主键列通常命名为 `id`，或以 `id` 作为后缀。
* 创建时间字段惯用名为 `created_time`，最后修改时间惯用名为 `updated_time`
* 布尔型字段建议使用 `is_`，`has_` 等作为前缀。
* 额外的灵活 JSONB 字段固定使用 `extra` 作为列名。
* 其余各字段名需与已有表命名惯例保持一致，任何打破惯例的字段命名都应当做出书面设计说明与解释。

**枚举项命名** （Enum）

* 枚举项默认应当使用 `camelCase`，但也允许其他风格。

**函数命名规则**（Function）

* 函数命名以动词起头： `select`，`insert`，`delete`，`update`，`upsert`，`create` ，……。
* 重要参数可以通过 `_by_ids`，`_by_user_ids` 的后缀在函数名中体现。
* 避免函数重载，同名函数尽量只保留一个。
* 禁止通过 `BIGINT/INTEGER/SMALLINT` 等整型进行函数签名重载，调用时可能产生歧义。
* 存储过程与函数中的变量使用命名参数，避免位置参数（`$1`，`$2`，…）。
* 如果参数名与对象名出现冲突，在参数前添加 `_`，例如 `_user_id`。

**注释规范**（Comment）

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

---------

## 0x02 设计规范

> Suum cuique

**建表注意事项**

* 建表 DDL 语句需要使用标准格式，SQL 关键词大写，其他小写。
* 在字段名/表名/别名中统一使用小写，尽量不要区分大小写。如果遇到大小写混用的情况，或者与 SQL 关键词冲突的名称，需要使用双引号扩起进行引用。
* 能使用专有类型的，不使用字符串。（数值，枚举，网络地址，货币，JSON，UUID等）：使用正确的数据类型，能显著提高数据存储，查询，索引，计算的效率，并提高可维护性。
* 优化列的布局，对齐类型可以有额外的性能/存储空间收益。
* 唯一约束须由数据库保证，任何唯一列须有对应的唯一约束。`EXCLUDE` 约束是泛化的唯一约束，可以在低频更新场景下用于保证数据完整性。

**分区表注意事项**

* 如果单表超过百TB量级，或者每月增量数据超过十几GB量级，可以考虑进行表分区。
* 分区的指导原则是，让每个分区的大小尽可能落在 1GB ～ 64GB 的舒适范围内。
* 有条件按照时间范围分区的表优先按时间范围分区，通常使用的粒度包括： decade，year，month，day，hour，应当至少提前三个月创建好未来所需的分区。
* 对于极端倾斜的数据分布，可以组合使用不同的时间粒度，例如： 1900 - 2000 一个大分区，2000 - 2020 按年分区，2020 后按月分区。使用时间分区时，表名使用分区下限界的值（无穷大则选用一个足够久远的值）。

**宽表注意事项**

* 宽表（例如有几十个字段的表）可以考虑进行纵向拆分，通过相同的主键与主表相互引用。
* 因为PostgreSQL MVCC机制，宽表的写放大现象更为明显，减少对宽表的频繁更新。
* 互联网场景中，允许适当降低规范化等级，减少多表连接以提高性能。

**主键注意事项**

* 每个表都 **必须** 有 **身份列**，原则上必须有主键，最低要求为拥有 **非空唯一约束**。
* 身份列用于唯一标识表中的任一元组，逻辑复制与诸多三方工具有赖于此。
* 主键如果包含多列，应当在建表DDL的字段列表之后，使用 `PRIMARY KEY(a,b,...)` 单列指定。
* 主键原则上建议使用整型，可以谨慎使用 `UUID` 与长度受限的文本类型，使用其他类型需要显式说明与评估。
* 主键通常使用单一整型列，原则上建议使用 `BIGINT`，审慎使用 `INTEGER`，不允许使用 `SMALLINT`。
* 主键应使用 `GENERATED ALWAYS AS IDENTITY` 生成唯一主键；`SERIAL`，`BIGSERIAL` 仅当需要兼容 PG 10 以下版本时允许使用。
* 主键可以使用 `UUID` 类型作为主键，建议用 UUID v1/v7；审慎使用 UUIDv4 作为主键，随机 UUID 的局部性较差且存在碰撞概率。
* 使用字符串列作为主键时，应当添加长度限制。通常使用 `VARCHAR(64)`，使用更长的字符串时应当进行说明与评估。
* `INSERT/UPDATE` 时原则上禁止修改主键列的值，`INSERT RETURNING` 可用于返回自动生成的主键值。

**外键注意事项**

* 定义外键时引用必须显式设置相应的动作：`SET NULL`， `SET DEFAULT`， `CASCADE`，慎用级联操作。
* 外键引用的列，需要为其他表/本表上的主键列。
* 互联网类业务，特别是分区表、水平分片库慎用外键，可以在应用层解决。

**空值/默认值注意事项**

* 字段语义上没有零值与空值区分的，不允许空值存在，须为列配置 `NOT NULL` 约束。
* 字段语义上带有默认值的，应当配置 `DEFAULT` 默认值。

**数值类型注意事项**

* 常规数值字段使用 `INTEGER` 。容量拿不准的数值列使用 `BIGINT`。
* 无特殊理由不要用 `SMALLINT` ，性能与存储提升甚小，但会有很多额外的问题。
* 注意SQL标准不提供无符号整型，超过 `INTMAX` 但没超过 `UINTMAX` 的值需要升格存储。不要存储超过 `INT64MAX` 的值到 `BIGINT` 列中，会溢出为负数，有此需求请使用 `uint` 扩展插件。
* `REAL` 表示 4 字节浮点数，`FLOAT` 表示 8 字节浮点数。浮点数仅可用于末尾精度无所谓的场景，例如地理坐标。**切记不要对浮点数使用等值判断，零值除外**。
* 精确数值类型使用 `NUMERIC`，如果可行，请用 `NUMERIC(p)` 与 `NUMERIC(p,s)` 设置有效数字位数以及小数部分的有效位数。例如摄氏气温（`37.0`）可以用 `NUMERIC(3,1)` 类型来存储3位有效数字与1位小数。
* 货币数值类型使用 `MONEY`。

**文本类型注意事项**

* PostgreSQL的文本类型包括 `char(n)`， `varchar(n)`， `text`。默认情况下，可以使用 `text` 类型 ，不限制字符串长度，但受字段最大长度1GB限制。
* 如果条件许可，优先使用 `varchar(n)` 类型来设置一个最大字符串长度，这会引入极微小的额外检查开销，但能规避一些脏数据与极端情况。
* 避免使用 `char(n)`，该类型为了与SQL标准兼容，存在不合直觉的行为表现（补齐空格与截断），且并没有存储和性能优势。

**时间类型注意事项**

* 时间只有两种存储方式：带时区的 `TIMESTAMPTZ`，不带时区的 `TIMESTAMP` 。
* 建议使用带时区的 `TIMESTAMPTZ`，如果使用 `TIMESTAMP` 存储，必须使用0时区标准时。
* 生成0时区时间请使用 `now() AT TIME ZONE 'UTC'` ，不能直接截断时区 `now()::TIMESTAMP`。
* 统一使用 ISO-8601 格式输入输出时间类型：`2006-01-02 15:04:05`，避免DMY与MDY问题。
* 中国区域用户可以统一使用 `Asia/Hong_Kong` +8 时区，因为上海时区缩写 `CST` 有歧义。

**枚举类型注意事项**

* 较稳定的，取值空间较小（几十到几百内）的字段应当使用枚举类型，不要使用整型与字符串表示。
* 枚举内部使用动态整型实现，相比整型有可读性优势，相比字符串有性能、存储、可维护性上的优势。
* 枚举项只能添加，无法删除，但是可以重命名现有枚举值。`ALTER TYPE <enum_name>` 用于修改枚举。

**UUID类型注意事项**

* 请注意，全随机的 UUIDv4 用作主键时局部性太差，尽可能考虑用 UUIDv1 / v7 代替。
* 一些 UUID 生成/处理函数需要额外的扩展插件，例如 `uuid-ossp` ，`pg_uuidv7`  等，有此需求请在配置时指明。

**JSON类型注意事项**

* 如无特殊原因，总是使用二进制存储的 `JSONB` 类型与相关函数，而非文本版本的 `JSON`。
* 请注意 JSON 中的原子类型与 PostgreSQL 对应类型的细微差别：与 JSON 字符串对应的 `text` 类型中不允许出现 `\u0000` 零字符，与 JSON 数值类型对应的 `numeric` 中不允许出现 `NaN` 与 `infinity`。布尔值只接受小写的 `true` 与 `false` 字面值。
* 请注意JSON标准中的 `null` 对象和 SQL 标准中的空值 `NULL`  并非同一个概念。

**数组类型注意事项**

* 当存储元素数量较少时，可以使用数组字段代替单独。
* 适合用于存储元素数量相对较少且变化不频繁的数据。如果数组中的元素数量非常大或经常变化，考虑使用单独的表来存储数据，并使用外键关联。
* 高维度的浮点数组，可以考虑使用 `pgvector` 扩展提供的专用数据类型。

**GIS类型注意事项**

* GIS 类型默认使用 `srid=4326` 参考坐标系。
* 经纬度坐标点应当使用 `Geography` 类型，使用此类型时默认无需显式指定参考系坐标 `4326`

**触发器注意事项**

* 触发器会提高数据库系统的复杂度与维护成本，原则上不鼓励使用。禁止使用规则系统，此类需求应当使用触发器替代。
* 触发器的典型场景是，在修改某一行后自动修改 `updated_time` 为当前时间戳，或者将表的增删改动作记录到另一张日志表中，或者维持两张表在业务上的一致性。
* 触发器中的操作是事务性的，意味着如果触发器或触发器中的操作失败，整个事务都会回滚，所以请充分测试并证明触发器的正确性。对于递归调用、执行复杂查询死锁，多个触发器执行顺序等情况需要特别关注。

**存储过程/函数注意事项**

* 函数/存储过程适用于封装事务，减少并发冲突，减少网络往返，减少返回数据量，执行 **少量** 自定义逻辑。
* 存储过程 **不适合** 进行复杂计算，不适合进行频繁/频繁的类型转换与包装。在关键高负载系统中，应当移除数据库中 **不必要** 的计算密集型逻辑，例如在数据库中使用SQL进行WGS84到其他坐标系的换算。与数据获取、筛选密切关联的计算逻辑可以使用函数/存储过程：例如PostGIS中的几何关系判断。
* 不再使用的，被替换的函数与存储过程应当及时移除下线，避免与未来的函数发生冲突。
* 使用统一的函数创建语法格式，签名单独占用一行（函数名与参数），返回值单启一行，语言为第一个标签。一定要标注函数易变性等级：`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$;
  ```

**使用合理的Locale选项**

* 默认使用 `C.UTF8`，没有特殊理由不得更改。
* 默认的 `collate` 规则必须为 `C` ，避免字符串索引问题。
* https://mp.weixin.qq.com/s/SEXcyRFmdXNI7rpPUB3Zew

**使用合理的字符编码与本地化配置**

* 必须使用 `UTF8` 字符编码，严格禁止使用其他任何字符编码。
* 必须使用 `C` 作为 `LC_COLLATE` 默认排序规则，有特殊需求必须在DDL/查询子句中显式指定来实现。
* 字符集 `LC_CTYPE` 默认使用 `en_US.UTF8`，一些扩展依赖字符集信息方可正常工作，如 `pg_trgm` 。

**索引相关注意事项**

- 所有在线查询必须针对其访问模式设计相应索引，除极小表外不允许全表扫描。
- 索引有代价，不允许创建不使用的索引，应当及时清理不再使用的索引。
- 建立联合索引时，应当将区分度，选择率高的列放在前面，例如 ID，时间戳等。
- GiST索引可用于解决近邻查询问题，传统B树索引无法提供对KNN问题的良好支持。
- 对于值与堆表的存储顺序线性相关的数据，如果查询为范围查询，建议使用BRIN索引。最典型场景如仅追加写入的时序数据，BRIN索引相比Btree更为高效。
- 针对 JSONB / 数组字段进行检索时，可以使用 GIN 索引加速查询。

**明确B树索引空值的顺序**

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

**禁止在大字段上建立索引**

- 被索引字段大小无法超过2KB（1/3的页容量），在文本类型上创建索引需要谨慎，被索引的文本应当使用带有长度约束的 `varchar(n)` 类型。
- 文本类型用作主键时，必须设置最大长度。原则上长度不应当超过 64 个字符，特殊情况需显式说明评估。
- 如有大字段索引需求，可以考虑对大字段取哈希，并建立函数索引。或使用其他类型的索引（GIN）。

**充分利用函数索引**

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

**充分利用部分索引**

* 查询中查询条件固定的部分，可以使用部分索引，减小索引大小并提升查询效率。
* 查询中某待索引字段若只有有限几种取值，也可以建立几个相应的部分索引。
* 如果部分索引中的列会被频繁更新，请关注这些索引的膨胀情况

---------

## 0x03 查询规范

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

**使用服务接入**

- 生产数据库接入必须通过域名接入[服务](https://pigsty.cc/docs/concept/svc)，严禁使用 IP 地址直连。
- 服务与接入使用 VIP，LVS/HAProxy 屏蔽集群实例成员的角色变化，主从切换无需应用重启。

**读写分离**

- 互联网业务场景：写请求必须走主库，通过 Primary 服务访问。
- 读请求原则上走从库，通过 Replica 服务访问。
- 例外情况：需要读己之写的一致性保证，且检测到显著的复制延迟时，只读请求可以访问主库；或向DBA申请提供 Standby 服务。

**快慢分离**

- 生产中1毫秒以内的查询称为快查询，生产中超过1秒的查询称为慢查询。
- 慢查询必须走离线从库 —— Offline 服务/实例，应当在执行时设置超时。
- 生产中的在线普通查询执行时长原则上应当控制在 1ms 内。
- 生产中的在线普通查询执行时长，超过10ms需修改技术方案，优化达标后再上线。
- 在线查询应当配置10ms 数量级或更快的 Timeout，避免堆积造成雪崩。
- 禁止从 Primary 上 ETL 数据，应当使用 Offline 服务从专用实例取数。

**使用连接池**

- 生产应用必须通过连接池访问数据库，通过 1:1 部署的 Pgbouncer 代理访问 PostgreSQL 数据库。Offline 服务，个人用户严禁直接使用连接池。
- Pgbouncer 连接池默认使用 Transaction Pooling 模式，一些会话级别的功能可能无法使用（比如Notify/Listen），需要特别注意。在此模式下，1.21 以前的 Pgbouncer 不支持使用 Prepared Statements。特殊场景可以使用 Session Pooling 或绕开连接池直接访问数据库，需要 DBA 审核特批。
- 使用连接池时禁止修改连接状态，包括修改连接参数，修改搜索路径，更换角色，更换数据库。万不得已修改后必须彻底销毁连接，将状态变更后的连接放回连接池会导致污染扩散。严禁使用 pg_dump 通过 Pgbouncer 转储数据。

**为查询语句配置主动超时**

- 应用应当为所有的语句配置主动超时，超时后主动取消请求，避免雪崩。（Go context）
- 周期性执行的语句，必须配置小于执行周期的超时 Timeout，避免雪崩。
- HAProxy 配置有 24 小时连接默认超时，用于滚动过期长连接。请不要在离线实例上运行执行时间超过1天的 SQL，有此需求由DBA特批调整。

**关注复制延迟**

- 应用必须意识到主从之间的同步延迟，并妥善处理好复制延迟超出合理范围的情况。
- 常规情况下，复制延迟在 100µs / 几十KB 的数量级，但是在极端情况下，从库可能会出现分钟/小时级的复制延迟，应用应当知晓这种现象，并有相应的降级方案 —— 选择从主库读取，稍后重试，或直接报错。

**重试失败的事务**

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

**掉线重连**

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

**在线服务应用代码禁止执行DDL**

* 生产应用严禁执行 DDL，不要在应用代码里搞个大新闻。
* 例外场景：为分区表创建新的时间分区，可由应用谨慎管理。
* 特殊例外：办公系统使用的数据库，例如 Gitlab / Jira/ Confluence 等可以授予应用 DDL 权限。

**SELECT语句显式指定列名**

* 避免使用 `SELECT *`，或在 `RETURNING` 子句中使用 `*`。请使用具体的字段列表，不要返回用不到的字段。当表结构发生变动时（例如，新值列），使用列通配符的查询很可能会发生列数不匹配的错误。
* 一些表的字段经过维护之后，顺序会发生变化，例如：将 INTEGER 主键 `id` 升级为 `BIGINT` 后，`id` 的列顺序会到最后一列。此问题只能在维护迁移时择机修复，研发应当克制调整列顺序的强迫症，并在 SELECT 语句中显式指定列的顺序
* 例外：当存储过程返回具体的表行类型时，允许使用通配符。

**禁止在线查询全表扫描**

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

**禁止在事务中长时间等待**

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

**使用 count 计数时的注意事项**

* `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` 等操作都会消耗序列号，该消耗不会随事务失败而回滚。
* 当使用整型 `INTEGER` 作为主键，且表存在频繁插入冲突时，需要关注整型溢出的问题。
* 不推荐使用序列号生成身份列，考虑使用 `GENERATED ALWAYS AS IDENTITY` 代替。

**使用游标后必须及时关闭**

 **重复查询使用准备语句**

- 重复的查询应当使用 **准备语句（Prepared Statement）**，消除数据库硬解析的CPU开销。低于 1.21 版本的 Pgbouncer 无法在事务池化模式中支持此功能，请特别注意。
- 准备语句会修改连接状态，请注意连接池对于准备语句的影响。

**选择合适的事务隔离等级**

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

**判断结果存在性不要使用count**

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

**使用RETURNING子句一次性取回修改后的结果**

* `RETURNING` 子句可以在 `INSERT`，`UPDATE`，`DELETE` 语句后使用，有效减少数据库交互次数。

**使用UPSERT简化逻辑**

* 当业务出现插入-失败-更新的操作序列时，考虑使用 `UPSERT` 替代。
* 但请注意，`UPSERT` 即使没有成功插入，也会消耗序列号。

**利用咨询锁应对热点并发**。

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

**优化IN操作符**

* 使用 `EXISTS` 子句代替 `IN` 操作符，性能更佳。
* 使用 `=ANY(ARRAY[1,2,3,4])` 代替 `IN (1,2,3,4)`，效果更佳。
* 控制参数列表的大小，原则上不要超过1万个，超过时可以考虑分批处理。

**不建议使用左模糊搜索**

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

**使用数组代替临时表**

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

---------

## 0x04 管理规范

**使用 Pigsty 搭建 PostgreSQL 集群与基础设施**

* 生产环境统一使用 Pigsty 主干版本，在 x86_64 机器， CentOS 7.9 / RockyLinux 8.8 操作系统上部署数据库。
* `pigsty.yml` 配置文件通常包含了高度敏感的重要机密信息，应当使用 git 进行版本化管理，并严格控制访问权限。
* `files/pki` 内生成的 CA 私钥与其他证书应当妥善保管，定期将备份至安全区域存储归档，并严格控制访问权限。
* 所有密码都不允许使用默认值，确保都已经修改为强度足够的新密码。
* 严格控制管理节点与配置代码仓库的的访问权限，仅限 DBA 登陆与访问。

**监控系统是必选项**

* 任何部署必须有一套监控系统，生产环境至少使用两套 Infra 节点以提供冗余。

**根据需求合理规划集群架构**

* 任何由DBA管理的生产数据库集群，必须带有至少一个在线从库，用于在线故障切换。
* 默认使用 `oltp` 模板，分析类数据库使用 `olap` 模板，财务库使用 `crit` 模板，小微虚拟机（四核内）使用 `tiny` 模板。
* 年增数据量超过1TB的业务，或者写入 TPS 超过3～5万的集群，可以考虑搭建水平分片集群。

**使用 Patroni 与 Etcd 配置集群高可用**

* 生产数据库集群使用 Patroni 作为高可用组件，使用 etcd 作为 DCS。
* `etcd` 使用专用虚拟机集群，3 ～ 5 个节点，严格打散分布在不同机柜上。
* 必须开启 Patroni Failsafe 模式，确保 etcd 故障时集群主库可以继续工作。

**使用 pgBackRest 与 MinIO 配置集群PITR**

* 生产数据库集群使用 pgBackRest 作为备份恢复/PITR方案，使用 MinIO 作为备份存储仓库。
* MinIO 使用多节点多盘集群，亦可使用 S3 / OSS / COS 服务代替，冷备份必须设置密码加密。
* 所有数据库集群每天进行一次本地全量备份，保留最近一周的备份与WAL，每隔一月存留一个全备。
* 出现 WAL 归档错误时，应当及时检查备份仓库并排查问题。

**核心业务数据库配置注意事项**

* 核心业务集群至少需要配置两个在线从库，其中一个为专用离线查询实例。
* 核心业务集群需要搭建一套延迟24小时的延迟从库集群，用于应急数据恢复。
* 核心业务集群通常采用异步提交，与钱有关则采用同步提交。

**财务数据库配置注意事项**

* 财务数据库集群需要至少两个在线从库，其中一个为专用同步 Standby 实例，并启用 Standby 服务接入。
* 与钱有关的库必须使用 RPO = 0 的 `crit` 模板，启用同步提交确保数据零丢失，视情况启用 Watchdog。
* 与钱有关的库必须强制打开数据校验和，视情况打开全量 DML 日志。

**使用合理的字符编码与本地化配置**

* 必须使用 `UTF8` 字符编码，严格禁止使用其他任何字符编码。
* 必须使用 `C` 作为 `LC_COLLATE` 默认排序规则，有特殊需求必须在DDL/查询子句中显式指定来实现。
* 字符集 `LC_CTYPE` 默认使用 `en_US.UTF8`，一些扩展依赖字符集信息方可正常工作，如 `pg_trgm` 。

**业务数据库管理注意事项**

* 同一个集群内允许创建多个不同的数据库，必须使用 Ansible 剧本新建业务数据库。
* 所有业务数据库都必须同步存在于 Pgbouncer 连接池中。

**业务用户管理注意事项**

* 不同的业务/服务必须使用不同的数据库用户，必须使用 Ansible 剧本新建业务用户。
* 所有生产业务用户都必须同步存在于 Pgbouncer 连接池的用户列表文件中。
* 个人用户应当设置默认有效期为 90 天的密码并定时更换。
* 个人用户只允许从跳板机访问有权限的集群 Offline 实例，或带有 `pg_offline_query` 的从库。

**扩展插件管理注意事项**

* 安装新扩展时，必须先在集群所有实例中使用 `yum/apt` 安装对应大版本的扩展插件二进制软件包。
* 启用扩展前，需要确认扩展是否需要加入 `shared_preload_libraries` ，如果需要应当安排滚动重启。
* 注意 `shared_preload_libraries` 优先级顺序， `citus`， `timescaledb`，`pgml` 通常要放在最前面。
* `pg_stat_statements` 与 `auto_explain` 是必选插件，必须在所有集群中启用。
* 安装扩展统一使用 `dbsu` 进行，在业务数据库中 `CREATE EXTENSION` 执行创建。

**数据库XID与年龄注意事项**

* 关注数据库与表的年龄，避免XID事务号用尽。使用超过 20% 应当关注，超过 50% 应当立即介入处理。
* 处理 XID 时，按年龄从大到小顺序挨个对表执行 `VACUUM FREEZE`。

**数据库表与索引膨胀注意事项**

* 关注表与索引的膨胀率，避免索引性能劣化，使用 `pg_repack` 在线处理表/索引膨胀问题。
* 一般情况下，膨胀率超过 50% 的索引与表可以考虑进行重整处理。
* 处理超过 100GB 的表膨胀时，应当特别注意，并挑选业务低谷时进行。

**数据库重启注意事项**

* 重启数据库前，执行 `CHECKPOINT` 两次，强制脏页刷盘，可加速重启过程。
* 重启数据库前，执行 `pg_ctl reload` 重载配置确认配置文件正常可用。
* 重启数据库可使用 `pg_ctl restart` 或 patronictl 同时重启整个集群。
* 严禁使用 `kill -9` 关闭任何数据库进程。

**复制延迟注意事项**

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

**新从库数据预热**

* 高负载业务集群添加新从库实例时，应当对新数据库实例进行预热，逐步调整并应用 HAProxy 实例权重，分梯度上量：4,8,16,32,64,100。可以使用 `pg_prewarm` 将热数据加载至内存。

**数据库发布流程**

- 线上数据库发布需要经过研发自测，主管审核，QA审核（可选），DBA审核几个评估阶段。
- 研发自测阶段，应当由研发确保变更在开发、预发环境执行正确无误。
  - 如果是新建表，应当给出记录数量级，数据日增量预估值，读写吞吐量级预估。
  - 如果是新建函数，应当给出平均执行时间与极端情况说明。
  - 如果是模式变更，必须梳理清楚所有上下游依赖。
  - 如果是数据变更，记录订正，必须给出回滚 SQL。
- 研发 Team Leader 需要对变更进行评估与审核，对变更内容负责。
- DBA对发布的形式与影响进行评估与审核，提出审核意见，打回或统一执行

**数据工单格式**

* 数据库变更通过平台进行，每个变更一个工单。
* 标题清晰：某某业务需在 `xx` 库执行 `yy` 动作。
* 目标明确：每个步骤需要在哪些实例上执行哪些操作，结果如何校验。
* 回滚方案：任何变更都需要提供回滚方案，新建也需要提供清理脚本。
* 任何变更都需要记录归档，有完善的审批记录，首先由研发上级TL Review 审批通过后由 DBA 审批。

**数据库变更发布注意事项**

* 使用统一的发布窗口，每天 16:00 统一收集当日变更依次执行；16:00点后TL确认的需求将顺延至第二天执行。19:00 后不允许数据库发布，紧急发布请TL做特殊说明，抄送CTO审批同意后执行。

* 数据库 DDL 变更 DML 变更统一使用管理员用户 `dbuser_dba` 远程执行，确保默认权限正常工作。
* 业务管理员在自行执行 DDL 时，**必须** 先 `SET ROLE dbrole_admin` 后再执行发布，确保默认权限。
* 任何变更都需要有回滚预案方可执行，极个别无法回滚的操作需要特别谨慎处理（例如枚举加值）
* 数据库变更使用 `psql` 命令行工具，连接到集群主库执行，使用 `\i` 执行脚本或 `\e` 手工分批执行。

**删除表注意事项**

* 生产数据表 `DROP` 应当首先重命名，冷却 1～3 天确认没有访问后再移除。
* 清理表时必须梳理所有依赖，包括直接间接依赖的对象：触发器，外键引用等。
* 待删除的临时表通常放置于 `trash` Schema 中，通过  `ALTER TABLE SET SCHEMA` 修改模式名。
* 高负载业务集群中，移除特别大的表 (> 100G) 时挑选业务低谷进行，避免抢占 I/O 。

**创建与删除索引注意事项**

* 必须使用 `CREATE INDEX CONCURRENTLY` 并发创建索引，使用 `DROP INDEX CONCURRENTLY` 并发移除索引。
* 重建索引时，总是先创建新索引，再移除旧索引，并修改新索引名与旧索引保持一致。
* 创建索引失败后，应当及时移除 `INVALID` 的索引，修改索引后，使用 `analyze` 重新收集表上的统计数据。
* 业务空闲时，可以启用并行索引创建，并设置 `maintenance_work_mem` 为更大的值加速索引创建。

**审慎地进行模式变更**

* 尽可能避免整表重写式的变更，1GB 以内的表允许全表重写，DBA 应当在变更时告知所有相关业务方。
* 向现有表中添加新列时，应当避免在默认值中使用 `VOLATILE` 的函数，避免全表重写。
* 变更列类型时，必要时应当重建所有依赖该类型的函数，视图，并 `ANALYZE` 刷新统计信息。

**控制数据写入的批次规模**

* 大批量写入操作应当切分为小批量进行，避免一次产生大量WAL或占用 I/O。
* 大批量 `UPDATE` 后，执行 `VACUUM` 回收死元组占用的空间。
* 执行 DDL 语句本质是对系统目录的修改，同样需要控制一个批次内的DDL语句数量。

**数据加载注意事项**

* 使用 `COPY` 加载数据，如有需要可以并行执行。
* 加载数据前可以临时关闭 `autovacuum`，按需禁用触发器，并在加载完后再建立约束与索引。
* 调大 `maintenance_work_mem`，增大 `max_wal_size`。
* 加载完成后执行 `vacuum verbose analyze table`。

**数据库迁移、大版本升级注意事项**

* 生产环境统一使用标准迁移搭建剧本逻辑，通过蓝绿部署实现不停机集群迁移、大版本升级等需求。
* 对于停机时间没有要求的集群，可以使用 `pg_dump | psql` 逻辑导出导入的方式停机升级。

**数据误删/误更新处理流程**

- 事故发生后，立即评估是否需要停机止血，评估影响规模，决定处理手段。
- 研发侧如有办法恢复，优先由研发自行通过 SQL 发布进行订正；否则使用 `pageinspect` 与 `pg_dirtyread` 从坏表中抢救数据。
- 若有延迟从库，从延时从库中抽取数据进行修复。首先确认误删时间点，推进延迟从库至该 XID 后抽取数据。
- 大面积误删误写，经与业务沟通同意后，执行原地 PITR 回滚至特定时间。

**数据腐坏处理流程**

- 确认从库数据是否可用于恢复，若从库数据无恙可先 Switchover 至从库。
- 临时关闭 `auto_vacuum` ，定位错误根因，替换故障磁盘并补充新从库。
- 若系统目录损坏，或使用 `pg_filedump` 从表二进制文件中恢复数据。
- 若 CLOG 损坏，使用 `dd` 生成仿造提交记录。

**数据库连接打满注意事项**

* 出现连接打满现象（雪崩）时，立即使用杀连接查询治标止损：`pg_cancel_backend` 或 `pg_terminate_backend`。
* 使用  `pg_terminate_backend` 中止所有普通后端进程，从每秒一次（`psql` `\watch 1`）开始。并从监控系统确认连接情况，如果继续堆积，则不断提高杀连接查询的执行频次，例如每 0.1 秒一次，直到不再堆积为止。
* 从监控系统确认止血后，尝试停止杀连接，若重新出现堆积则立即恢复杀连接。立即分析根因并进行相应处理（升配，限流，加索引等）