探探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索引更为高效。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180620G1PPXA00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

同媒体快讯

扫码关注云+社区

领取腾讯云代金券