PostgreSQL SQL 开发规范试行

太过爱你忘了你带给我的痛 2022-11-17 10:14 296阅读 0赞

https://mp.weixin.qq.com/s/GVFLwC9ruwlnxolYAxMQCw

PostgreSQL 不少的单位已经开始部署了，对于外包的开发，甲方也需要有相关的规范给出，虽然比MYSQL 的要求要少了不少，但该注意的还是要注意。

下面是我们的初步的规范，有兴趣的同学可以交流，我们也希望得到建议改善我们的规范。

1 Postgresql 数据库设计中数据库名为小写，多个单词可以通过下划线来分割，一个数据库名建议不超过20-30个字符。

2 Postgresql 数据库设计中表名为小写，多个单词可以使用\_下划线来进行分割，一个表名字尽量控制在20个字符以内。

3 Postgresql 在使用中可以使用存储过程，函数， 临时表。由于数据库将部署运维自动化工具，修改表结构等相关操作将导致触发器失效，不能使用触发器。使用存储过程中请尽量分割事务，对于频繁调用的存储过程，请进行测试将允许时间控制在合适的时间，建议不超过2-3秒钟，核心业务不能使用存储过程。

事务中需要有捕捉错误的代码，在存储过程出错后，进行及时反馈错误信息。函数不能与具体业务表有关，只能进行简单计算。

4 Postgresql 临时表属于进程，当进程终止后，临时表会被释放，如需要全局临时表可以使用实体表代替。清理时请使用 truncate table 的方式清理，减少wal log的产生。

5 Postgresql 在表设计中请使用utf8进行字符编码设计，所有数据库应统一相关编码，避免在后续数据库备份，移库，等出现一些不必要的麻烦。

6 Postgresql 在表设计中，不能使用外键，相关表关系在应用层定义。

7 Postgresql 在设计表中，主键设计控制，20字符以内，超过字符主键需要提出申请并说明原因，唯一索引一个表中最多只有一个,复合索引字段限制在3个及以内，另PG 支持include 索引，可以考虑使用include 的方式加速查询。

8 Postgresql 在表设计中，不建议使用经常进行， 行修改的模式，如一秒修改同一行N次，我们建议通过缓存，或REDIS的手段进行缓冲后，将状态变化完成的数据在刷入数据库，降低死锁，BLOCKED 的风险，提高程序的稳定性和性能。

9 Postgresql 在索引使用中可以进行选择：

1 普通方式的查询可以使用普通方式的索引进行处理

2 范围节点类型的查询，数据线性相关性很好，建议使用BRIN 索引进行处理查询。

3 模糊查询 LIKE %XXX% , 可以通过gin gist 方式索引处理，查询字符要超过3个字以上，字段大小小于10字符以下，表行数预估在1000万行以下。

4 字段中字符数量超过20不可以建立索引

10 Postgresql 处理中字段中含有英文的数据，需要采用CITEXT 的字段类型，如确认字段没有大小写混写，则采用varchar ,text字段类型

11 Postgresql 预估的大表采用分区表的方式，在数据量预估超过亿万级别的时，考虑通过分区表的方式进行相关的数据的分割。

12 Postgresql 在使用中对于JOIN 的表的数量控制在3个及3个以内，如必须增加JOIN的表的数量，需要和DB 组商议，表设计中需要注意关联表之间的设计关系，关联字段类型的数据类型必须一致，建议字段名也应一致。

13 Postgresql 在字段设计中，如字段的长度不固定，使用text的字段类型进行设计，能确认字段的长度，使用varchar字符类型， char类型禁用， text和varchar的性能，在Postgresql中没有差别

14 Postgresql 对于单库的数据表的数量控制在300个表内

15 Postgresql 在事务中，将读事务放在前方，写事务放在后方

具体可以与DB 组商议。

16 Postgresql 支持灵活的半结构化数据存储方式，如有中小批量的半结构化数据进行存储可以在Postgresql本身解决，通过hstore ,或者 jsonb 方式处理使用索引，部分提取数据，提高半结构化数据提取的性能。

18 Ptgresql 视图可以使用，请在使用视图中，注意相关视图的注释，和引用表的热度，不能频繁通过VIEW 来解决查询问题。

20 Postgresql 在千万行级别的表中少使用count(\*) 来进行相关的数据处理，必须有条件或走索引扫描计算。

21 Postgersql 在应用设计中，删除行操作，不能频繁删除行，设计成逻辑删除数据行的方式，避免在业务高峰期引擎死锁，或者blocked 的问题。

22 JAVA在操作PG的时候，和MYSQL的区别在于如果JAVA承接的数据类型是INT(MYSQL)，是可以用JAVA的STRING类型承接，但是POSTGRESQL（INT）类型JAVA是不能用STRING类型承接，必须用整型INT，同时反过来也是一样，所以开发者们请注意，POSTGRESQL是一个严谨性的数据库，在使用pg数据库的时候进行变量的给予和提取，JAVA的变量对应的一定要是相对应数据库的数据类型，和MYSQL的兼容行比较，POSTGRESQL是需要更认真的对待的。

23 POSTGRESQL 表操作中，表必须有别名，操作SQL不能带有子查询

24 创建表必须带有表注释，和列注释，主键命名应有前缀并在整体开发中统一命名，系统中严禁出现触发器和DBLINK，使用序列也应有统一的命名规则，索引的命名也要有相关的命名规则

25 操作SQL应用where 条件代替having字句，避免大量IN 的条件的元素控制在10个以内，JOIN 操作必须使用INNER JOIN ，只有在逻辑条件中必须使用LEFT JOIN 才能使用

26 字段设计中进行计算的字段不能为空NULL， 必须有默认值

27 核心系统查询中，不允许使用OR 计算符号

28 在一条查询语句中，不能一张表重复出现2次，进行嵌套查询。