探探PostgreSQL开发规约

0x00背景

没有规矩，不成方圆。PostgreSQL的功能非常强大，但是要把PostgreSQL用好，需要后端、运维、DBA的协力配合。本文针对PostgreSQL数据库原理与特性，整理了一份开发规范，希望可以减少大家在使用PostgreSQL数据库过程中遇到的困惑。 你好我也好，大家都好。

0x01 命名规范

无名，万物之始，有名，万物之母。【强制】通用命名规则

本规则适用于所有对象名，包括：库名、表名、表名、列名、函数名、视图名、序列号名、别名等。

对象名务必只使用小写字母，下划线，数字，但首字母必须为小写字母，常规表禁止以打头。

对象名长度不超过63个字符，命名统一采用。

禁止使用SQL保留字，使用获取保留关键字列表。

禁止出现美元符号，禁止使用中文，不要以开头。

提高用词品味，做到信达雅；不要使用拼音，不要使用生僻冷词，不要使用小众缩写。

【强制】库命名规则

库名最好与应用或服务保持一致，必须为具有高区分度的英文单词。

命名必须以开头，为具体业务线名称，如果是分片库必须以结尾。

多个部分使用连接。例如：，等，总共不超过三段。

【强制】角色命名规范

数据库有且仅有一个：，用于流复制的用户命名为。

生产用户命名使用作为前缀，具体功能作为后缀。

所有数据库默认有三个基础角色：，，，分别拥有所有表的只读，只写，函数的执行权限。

生产用户，ETL用户，个人用户通过继承相应的基础角色获取权限。

更为精细的权限控制使用独立的角色与用户，依业务而异。

【强制】模式命名规则

业务统一使用作为模式名，为业务定义的名称，必须设置为首位元素。

，，为保留模式名。

分片模式命名规则采用：。

无特殊理由不应在其他模式中创建对象。

【推荐】关系命名规则

关系命名以表意清晰为第一要义，不要使用含混的缩写，也不应过分冗长，遵循通用命名规则。

表名应当使用复数名词，与历史惯例保持一致，但应尽量避免带有不规则复数形式的单词。

视图以作为命名前缀，物化视图使用作为命名前缀，临时表以作为命名前缀。

继承或分区表应当以父表表名作为前缀，并以子表特性（规则，分片范围等）作为后缀。

【推荐】索引命名规则

创建索引时如有条件应当指定索引名称，并与PostgreSQL默认命名规则保持一致，避免重复执行时建立重复索引。

用于主键的索引以结尾，唯一索引以结尾，用于约束的索引以结尾，普通索引以结尾。

【推荐】函数命名规则

以,,,,打头，表示动作类型。

重要参数可以通过,的后缀在函数名中体现。

避免函数重载，同名函数尽量只保留一个。

禁止通过等整型进行重载，调用时可能产生歧义。

【推荐】字段命名规则

不得使用系统列保留字段名：,,,,,等。

主键列通常命名为，或以作为后缀。

创建时间通常命名为，修改时间通常命名为

布尔型字段建议使用，等作为前缀。

其余各字段名需与已有表命名惯例保持一致。

【推荐】变量命名规则

存储过程与函数中的变量使用命名参数，而非位置参数。

如果参数名与对象名出现冲突，在参数后添加，例如。

【推荐】注释规范

尽量为对象提供注释（），注释使用英文，言简意赅，一行为宜。

对象的模式或内容语义发生变更时，务必一并更新注释，与实际情况保持同步。

0x02 设计规范

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

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

【强制】容量规划

单表记录过亿，或超过10GB的量级，可以考虑开始进行分表。

单表容量超过1T，单库容量超过2T。需要考虑分片。

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

存储过程适用于封装事务，减少并发冲突，减少网络往返，减少返回数据量，执行少量自定义逻辑。

存储过程不适合进行复杂计算，不适合进行平凡/频繁的类型转换与包装。

【强制】存储计算分离

移除数据库中不必要的计算密集型逻辑，例如在数据库中使用SQL进行WGS84到其他坐标系的换算。

例外：与数据获取、筛选密切关联的计算逻辑允许在数据库中进行，如PostGIS中的几何关系判断。

【强制】主键与身份列

每个表都必须有身份列，原则上必须有主键，最低要求为拥有非空唯一约束。

身份列用于唯一标识表中的任一元组，逻辑复制与诸多三方工具有赖于此。

【强制】外键

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

【强制】慎用宽表

字段数目超过15个的表视作宽表，宽表应当考虑进行纵向拆分，通过相同的主键与主表相互引用。

因为MVCC机制，宽表的写放大现象比较明显，尽量减少对宽表的频繁更新。

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

有默认值的列必须添加子句指定默认值。

可以在默认值中使用函数，动态生成默认值（例如主键发号器）。

【强制】合理应对空值

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

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

唯一约束须由数据库保证，任何唯一列须有唯一约束。

约束是泛化的唯一约束，可以在低频更新场景下用于保证数据完整性。

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

注意SQL标准不提供无符号整型，超过但没超过的值需要升格存储。

不要存储超过的值到列中，会溢出为负数。

【强制】注意时区问题

使用或存储时间，避免使用UNIX Epoch或自定义的Epoch存储秒数。

使用时，采用GMT/UTC时间，0时区标准时。

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

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

【推荐】主键类型

主键通常使用整型，建议使用，允许使用不超过64字节的字符串。

主键允许使用自动生成，建议使用发号器函数。

【推荐】选择合适的类型

能使用专有类型的，不使用字符串。（数值，枚举，网络地址，货币，JSON，UUID等）

使用正确的数据类型，能显著提高数据存储，查询，索引，计算的效率，并提高可维护性。

【推荐】使用枚举类型

较稳定的，取值空间较小（十几个内）的字段应当使用枚举类型，不要使用整型与字符串表示。

使用枚举类型有性能、存储、可维护性上的优势。

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

PostgreSQL的文本类型包括,,,,。除外并无本质存储区别。

带有修饰符的类型会检查字符串长度，会导致微小的额外开销，对字符串长度有限制时应当使用，避免插入过长的脏数据。

避免使用，为了与SQL标准兼容，该类型存在不合直觉的行为表现，且并没有存储和性能优势。

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

常规数值字段使用。主键、容量拿不准的数值列使用。

无特殊理由不要用，性能与存储提升很小，会有很多额外的问题。

表示4字节浮点数，表示8字节浮点数

浮点数仅可用于末尾精度无所谓的场景，例如地理坐标，不要对浮点数使用等值判断。

精确数值类型使用。

货币数值类型使用。

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

签名单独占用一行（函数名与参数），返回值单启一行，语言为第一个标签。

一定要标注函数易变性等级：,,。

添加确定的属性标签，如：,,，注意版本兼容性。

CREATEORREPLACE FUNCTION

nspname.myfunc(arg1_TEXT, arg2_INTEGER)

RETURNS VOID

LANGUAGE SQL

STABLE

PARALLEL SAFE

ROWS1

RETURNSNULLONNULLINPUT

AS$function$

SELECT1;

$function$;【推荐】针对可演化性而设计

在设计表时，应当充分考虑未来的扩展需求，可以在建表时适当添加1~3个保留字段。

对于多变的非关键字段可以使用JSON类型。

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

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

【推荐】使用新版本

新版本有无成本的性能提升，稳定性提升，有更多新功能。

充分利用新特性，降低设计复杂度。

【推荐】慎用触发器

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

0x03 索引规范

Wer Ordnung hält, ist nur zu faul zum Suchen.—German proverb【强制】在线查询必须有配套索引

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

索引有代价，不允许创建不使用的索引。

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

被索引字段大小无法超过2KB（1/3的页容量），原则上禁止超过64个字符。

如有大字段索引需求，可以考虑对大字段取哈希，并建立函数索引。或使用其他类型的索引（GIN）。

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

如在可空列上有排序需求，需要在查询与索引中明确指定还是。

注意，排序的默认规则是，即空值会出现在排序的最前面，通常这不是期望行为。

索引的排序条件必须与查询匹配，如：

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

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

【推荐】利用函数索引

任何可以由同一行其他字段推断得出的冗余字段，可以使用函数索引替代。

对于经常使用表达式作为查询条件的语句，可以使用表达式或函数索引加速查询。

典型场景：建立大字段上的哈希函数索引，为需要左模糊查询的文本列建立reverse函数索引。

【推荐】利用部分索引

查询中查询条件固定的部分，可以使用部分索引，减小索引大小并提升查询效率。

查询中某待索引字段若只有有限几种取值，也可以建立几个相应的部分索引。

【推荐】利用范围索引

对于值与堆表的存储顺序线性相关的数据，如果通常的查询为范围查询，建议使用BRIN索引。

最典型场景如仅追加写入的时序数据，BRIN索引更为高效。