没有规矩，不成方圆。
PostgreSQL的功能非常强大，但是要把PostgreSQL用好，需要后端、运维、DBA的协力配合。
本文针对PostgreSQL数据库原理与特性，整理了一份开发规范，希望可以减少大家在使用PostgreSQL数据库过程中遇到的困惑。你好我也好，大家都好。
0x01命名规范无名，万物之始，有名，万物之母。
通用命名规则
本规则适用于所有对象名，包括：库名、表名、表名、列名、函数名、视图名、序列号名、别名等。
对象名务必只使用小写字母，下划线，数字，但首字母必须为小写字母，常规表禁止以_打头。
对象名长度不超过63个字符，命名统一采用snake_case。
禁止使用SQL保留字，使用selectpg_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设计规范Suumcuique
字符编码必须为UTF8
禁止使用其他任何字符编码。
容量规划
单表记录过亿，或超过10GB的量级，可以考虑开始进行分表。
单表容量超过1T，单库容量超过2T。需要考虑分片。
不要滥用存储过程
存储过程适用于封装事务，减少并发冲突，减少网络往返，减少返回数据量，执行少量自定义逻辑。
存储过程不适合进行复杂计算，不适合进行平凡/频繁的类型转换与包装。
存储计算分离
移除数据库中不必要的计算密集型逻辑，例如在数据库中使用SQL进行WGS84到其他坐标系的换算。
例外：与数据获取、筛选密切关联的计算逻辑允许在数据库中进行，如PostGIS中的几何关系判断。
主键与身份列
每个表都必须有身份列，原则上必须有主键，最低要求为拥有非空唯一约束。
身份列用于唯一标识表中的任一元组，逻辑复制与诸多三方工具有赖于此。
外键
不建议使用外键，建议在应用层解决。使用外键时，引用必须设置相应的动作：SETNULL,SETDEFAULT,CASCADE，慎用级联操作。
慎用宽表
字段数目超过15个的表视作宽表，宽表应当考虑进行纵向拆分，通过相同的主键与主表相互引用。
因为MVCC机制，宽表的写放大现象比较明显，尽量减少对宽表的频繁更新。
配置合适的默认值
有默认值的列必须添加DEFAULT子句指定默认值。
可以在默认值中使用函数，动态生成默认值（例如主键发号器）。
合理应对空值
字段语义上没有零值与空值区分的，不允许空值存在，须为列配置NOTNULL约束。
唯一约束通过数据库强制。
唯一约束须由数据库保证，任何唯一列须有唯一约束。
EXCLUDE约束是泛化的唯一约束，可以在低频更新场景下用于保证数据完整性。
注意整数溢出风险
注意SQL标准不提供无符号整型，超过INTMAX但没超过UINTMAX的值需要升格存储。
不要存储超过INT64MAX的值到BIGINT列中，会溢出为负数。
注意时区问题
使用TIMESTAMPTZ或TIMESTAMP存储时间，避免使用UNIXEpoch或自定义的Epoch存储秒数。
使用TIMESTAMPTZ时，采用GMT/UTC时间，0时区标准时。
及时清理过时函数
不再使用的，被替换的函数应当及时下线，避免与未来的函数发生冲突。
主键类型
主键通常使用整型，建议使用BIGINT，允许使用不超过64字节的字符串。
主键允许使用Serial自动生成，建议使用Defaultnext_id()发号器函数。
选择合适的类型
能使用专有类型的，不使用字符串。（数值，枚举，网络地址，货币，JSON，UUID等）
使用正确的数据类型，能显著提高数据存储，查询，索引，计算的效率，并提高可维护性。
使用枚举类型
较稳定的，取值空间较小（十几个内）的字段应当使用枚举类型，不要使用整型与字符串表示。
使用枚举类型有性能、存储、可维护性上的优势。
选择合适的文本类型
PostgreSQL的文本类型包括char,char(n),varchar,varchar(n),text。除char(n)外并无本质存储区别。
带有(n)修饰符的类型会检查字符串长度，会导致微小的额外开销，对字符串长度有限制时应当使用varchar(n)，避免插入过长的脏数据。
避免使用char(n)，为了与SQL标准兼容，该类型存在不合直觉的行为表现，且并没有存储和性能优势。
选择合适的数值类型
常规数值字段使用INTEGER。主键、容量拿不准的数值列使用BIGINT。
无特殊理由不要用SMALLINT，性能与存储提升很小，会有很多额外的问题。
REAL表示4字节浮点数，FLOAT表示8字节浮点数
浮点数仅可用于末尾精度无所谓的场景，例如地理坐标，不要对浮点数使用等值判断。
精确数值类型使用NUMERIC。
货币数值类型使用MONEY。
使用统一的函数创建语法
签名单独占用一行（函数名与参数），返回值单启一行，语言为第一个标签。
一定要标注函数易变性等级：IMMUTABLE,STABLE,VOLATILE。
添加确定的属性标签，如：RETURNSNULLONNULLINPUT,PARALLELSAFE,ROWS1，注意版本兼容性。
CREATEORREPLACEFUNCTIONnspname.myfunc(arg1_TEXT,arg2_INTEGER)RETURNSVOIDLANGUAGESQLSTABLEPARALLELSAFEROWS1RETURNSNULLONNULLINPUTASfunctionSELECT1;function;
针对可演化性而设计
在设计表时，应当充分考虑未来的扩展需求，可以在建表时适当添加1~3个保留字段。
对于多变的非关键字段可以使用JSON类型。
选择合理的规范化等级
允许适当降低规范化等级，减少多表连接以提高性能。
使用新版本
新版本有无成本的性能提升，稳定性提升，有更多新功能。
充分利用新特性，降低设计复杂度。
慎用触发器
触发器会提高系统的复杂度与维护成本，不鼓励使用。
0x03索引规范WerOrdnungha?lt,istnurzufaulzumSuchen.
—Germanproverb
在线查询必须有配套索引
所有在线查询必须针对其访问模式设计相应索引，除极个别小表外不允许全表扫描。
索引有代价，不允许创建不使用的索引。
禁止在大字段上建立索引
被索引字段大小无法超过2KB（1/3的页容量），原则上禁止超过64个字符。
如有大字段索引需求，可以考虑对大字段取哈希，并建立函数索引。或使用其他类型的索引（GIN）。
明确空值排序规则
如在可空列上有排序需求，需要在查询与索引中明确指定NULLSFIRST还是NULLSLAST。
注意，DESC排序的默认规则是NULLSFIRST，即空值会出现在排序的最前面，通常这不是期望行为。
索引的排序条件必须与查询匹配，如：createindexontbl(iddescnullslast);
利用GiST索引应对近邻查询问题
传统B树索引无法提供对KNN问题的良好支持，应当使用GiST索引。
利用函数索引
任何可以由同一行其他字段推断得出的冗余字段，可以使用函数索引替代。
对于经常使用表达式作为查询条件的语句，可以使用表达式或函数索引加速查询。
典型场景：建立大字段上的哈希函数索引，为需要左模糊查询的文本列建立reverse函数索引。
利用部分索引
查询中查询条件固定的部分，可以使用部分索引，减小索引大小并提升查询效率。
查询中某待索引字段若只有有限几种取值，也可以建立几个相应的部分索引。
利用范围索引
对于值与堆表的存储顺序线性相关的数据，如果通常的查询为范围查询，建议使用BRIN索引。
最典型场景如仅追加写入的时序数据，BRIN索引更为高效。