PostgreSQL 规约（2024版）

By 冯若航（@Vonng） | 2023年11月27日

Module:

PGSQL

0x00背景

没有规矩，不成方圆。

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

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

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

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列中，会溢出为负数。
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 等。
```
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选项

默认使用 en_US.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

使用服务接入

生产数据库接入必须通过域名接入服务，严禁使用 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 作为主键，且表存在频繁插入冲突时，需要关注整型溢出的问题。

使用游标后必须及时关闭

重复查询使用准备语句

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

选择合适的事务隔离等级

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

r h 09判断结果存在性不要使用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替代。

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

针对单行记录的极高频并发写入（秒杀），应当使用咨询锁对记录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 秒一次，直到不再堆积为止。
从监控系统确认止血后，尝试停止杀连接，若重新出现堆积则立即恢复杀连接。立即分析根因并进行相应处理（升配，限流，加索引等）

Last modified 2024-03-01: update blog images (ba0fa2b)