4_14.-PostgreSQL-开发规范.md

+## 1.设计规范
+
+【强制】业务表严禁不设主键（特别的表除外，如几乎不用来查询的log表）。
+
+【强制】对经常查询的表的关键列设置合理的索引。
+
+【强制】关联的表应该尽量设计到同一个数据库，用Schema来区分，而不要放到不同的数据库。
+
+【强制】多表中的相同列，以及有JOIN需求的列，必须保证列名一致，数据类型一致。
+
+【强制】btree索引字段不建议超过2000字节，如果有超过2000字节的字段需要建索引，建议使用函数索引（例如哈希值索引），或者使用分词索引。
+
+【强制】尽量不要使用外键，如特殊情况必须使用外键时，如果你使用的PG版本没有自动建立fk的索引，则必须要对foreign key手工建立索引，否则可能影响references列的更新或删除性能。
+
+【强制】尽量不要使用外键，如特殊情况必须使用外键时，一定要设置fk的action，例如cascade，set null，set default。
+
+【强制】对于频繁更新的表，建议建表时指定表的fillfactor=85，每页预留15%的空间给HOT更新使用。
+
+```
+postgres=# create table test123(id int, info text) with(fillfactor=85);  
+CREATE TABLE
+```
+
+【强制】尽量不要在索引列中含有null的值，而使用其它的值代替，比如0
+
+【强制】表结构中字段定义的数据类型与应用程序中的定义保持一致，表之间字段校对规则一致，避免报错或无法使用索引的情况发生。
+
+\(1\).比如A表user\_id字段数据类型定义为varchar，但是SQL语句查询为 where user\_id=1234；
+
+## 2.性能规范
+
+【强制】在代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页语句。
+
+【强制】尽量不要使用游标（几乎所有的循环都可以用其他方案代替，效率更好），必要的情况下游标使用后要及时关闭。
+
+【强制】两阶段提交的事务，要及时提交或回滚，否则可能导致数据库膨胀。
+
+【强制】不要使用delete 全表，性能很差，请使用truncate代替，（truncate是DDL语句，注意加锁等待超时）。
+
+【强制】高并发的应用场合，务必使用预编译\(prepared statement\)，防止数据库硬解析消耗过多的CPU资源。
+
+【强制】不要使用hash index，目前hash index不写REDO，在备库只有结构，没有数据，并且数据库crash后无法恢复。
+
+【强制】在函数中，或程序中，不要使用count\(`*`\)判断是否有数据，很慢。 建议的方法是limit 1
+
+```
+select 1 from tbl where xxx limit 1;  
+if found -- 存在  
+else  -- 不存在
+```
+
+【强制】对于高并发的应用场景，务必使用程序的连接池，否则性能会很低下。
+
+【强制】当业务有近邻查询的需求时，务必对字段建立GIST或SP-GIST索引，加速近邻查询的需求。
+
+```
+create index idx on tbl using gist(col);  
+select * from tbl order by col <-> '(0,100)';
+```
+
+【强制】避免频繁创建和删除临时表，以减少系统表资源的消耗，因为创建临时表会产生元数据，频繁创建，元数据可能会出现碎片。（非必要情况请使用结果集或CTE来代替临时表）
+
+【推荐】高峰期对大表添加包含默认值的字段，会导致表的rewrite，建议只添加不包含默认值的字段，业务逻辑层面后期处理默认值。
+
+【推荐】可以预估SQL执行时间的操作，建议设置语句级别的超时，可以防止雪崩，也可以防止长时间持锁。
+
+例如设置事务中执行的每条SQL超时时间为10秒
+
+```text
+begin;  
+set local statement_timeout = '10s';  
+-- query;  
+end;
+```
+
+【推荐】对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 使用explain可以查看执行计划，如果发现执行计划不优，可以通过索引或者调整QUERY的写法解决。 例如
+
+```text
+begin;
+explain (verbose,costs,timing,buffers,analyze) query;
+rollback;
+......
+```
+
+
+