PostgreSQL 数据库开发规范——命名规范 & 设计规范

命名规范

　　【强制】库名、表名限制命名长度，建议表名及字段名字符总长度小于等于63。

　　【强制】对象名(表名、列名、函数名、视图名、序列名、等对象名称)规范，对象名务必只使用小写字母，下划线，数字。不要以pg开头，不要以数字开头，不要使用保留字。

　　保留字参考：https://www.postgresql.org/docs/9.5/static/sql-keywords-appendix.html

　　【强制】query中的别名不要使用 "小写字母，下划线，数字" 之外的字符，例如中文。

　　【推荐】主键索引应以 pk_ 开头， 惟一索引要以 uk_ 开头，普通索引要以 idx_ 打头。

　　【推荐】临时表以 tmp_ 开头，子表以规则结尾，例如按年分区的主表若是为tbl, 则子表为tbl_2016，tbl_2017

　　【推荐】库名最好与应用名称一致，或便于辨识。

　　【推荐】不建议使用public schema(不一样业务共享的对象可使用public schema)，应该为每一个应用分配对应的schema，schema_name最好与user name一致。

　　【推荐】comment不要使用中文，由于编码可能不同，若是存进去和读取时的编码不一致，致使可读性不强。 pg_dump时也必须与comment时的编码一致，不然可能乱码。

设计规范

　　【强制】多表中的相同列，必须保证列名一致，数据类型一致。

　　【强制】btree索引字段不建议超过2000字节，若是有超过2000字节的字段须要建索引，建议使用函数索引(例如哈希值索引)，或者使用分词索引。

　　【强制】使用外键时，若是你使用的PG版本没有自动创建fk的索引，则必需要对foreign key手工创建索引，不然可能影响references列的更新或删除性能。

　　【强制】对于频繁更新的表，建议建表时指定表的fillfactor=85，每页预留15%的空间给HOT更新使用。

　　【强制】表结构中字段定义的数据类型与应用程序中的定义保持一致，表之间字段校对规则一致，避免报错或没法使用索引的状况发生。

　　说明：

　　(1).好比A表user_id字段数据类型定义为varchar，可是SQL语句查询为 where user_id=1234;

　　【推荐】如何保证分区表的主键序列全局惟一。

　　使用多个序列，每一个序列的步调不同，或者每一个序列的范围不同便可。 

　　【推荐】建议有按期历史数据删除需求的业务，表按时间分区，删除时不要使用DELETE操做，而是DROP或者TRUNCATE对应的表。

　　【推荐】为了全球化的需求，全部的字符存储与表示，均以UTF-8编码，那么字符计数方法注意：

# 计算字符长度
postgres=# select length('阿里巴巴');
length -------- 4 (1 row)
# 计算字节数
postgres=# select octet_length('阿里巴巴'); 

　　【推荐】对于值与堆表的存储顺序线性相关的数据，若是一般的查询为范围查询，建议使用BRIN索引。

　　例如流式数据，时间字段或自增字段，可使用BRIN索引，减小索引的大小，加快数据插入速度。

create index idx on tbl using brin(id);

　　【推荐】设计时应尽量选择合适的数据类型，能用数字的坚定不用字符串，能用树类型的，坚定不用字符串。 使用好的数据类型，可使用数据库的索引，操做符，函数，提升数据的查询效率。

　　PostgreSQL支持的数据类型以下

　　精确的数字类型

　　浮点

　　货币

　　字符串

　　字符

　　字节流

　　日期

　　时间

　　布尔

　　枚举

　　几何

　　网络地址

　　比特流

　　文本

　　UUID

　　XML

　　JSON

　　数组

　　复合类型

　　范围类型

　　对象

　　行号

　　大对象

　　ltree 树结构类型

　　cube 多维类型

　　earth 地球类型

　　hstore KV类型

　　pg_trgm 类似类型

　　PostGIS(点、线段、面、路径、经纬度、raster、拓扑、。。。。。。)

　　【推荐】应该尽可能避免全表扫描(除了大数据量扫描的数据分析)，PostgreSQL支持几乎全部数据类型的索引。

　　索引接口包括

　　btree

　　hash

　　gin

　　gist

　　sp-gist

　　brin

　　rum (扩展接口)

　　bloom (扩展接口)

　　【推荐】对于网络复杂而且RT要求很高的场景，若是业务逻辑冗长，应该尽可能减小数据库和程序之间的交互次数，尽可能使用数据库存储过程(如plpgsql)，或内置的函数。

　　PostgreSQL内置的plpgsql函数语言功能很是强大，能够处理复杂的业务逻辑。

　　PostgreSQL内置了很是多的函数，包括分析函数，聚合函数，窗口函数，普通类型函数，复杂类型函数，数学函数，几何函数，。。。等。

　　【推荐】应用应该尽可能避免使用数据库触发器，这会使得数据处理逻辑复杂，不便于调试。

　　【推荐】若是应用常常要访问较大结果集的数据(例如100条)，可能形成大量的离散扫描。

　　建议想办法将数据聚合成1条，例如常常要按ID访问这个ID的数据，建议能够按期按ID聚合这些数据，查询时返回的记录数越少越快。

　　若是没法聚合，建议使用IO较好的磁盘。

　　【推荐】流式的实时统计，为了防止并行事务致使的统计空洞，建议业务层按分表并行插入，单一分表串行插入。

　　例如

　　table1, table2, ...table100;

　　每一个线程负责一张表的插入，统计时能够按时间或者表的自增ID进行统计

select xxx from table1 where id>=上一次统计的截至ID group by yyy;

　　【推荐】范围查询，应该尽可能使用范围类型，以及GIST索引，提升范围检索的查询性能。

　　例如

　　使用范围类型存储IP地址段，使用包含的GIST索引检索，性能比两个字段的between and提高20多倍。

　　【推荐】未使用的大对象，必定要同时删除数据部分，不然大对象数据会一直存在数据库中，与内存泄露相似。

　　vacuumlo能够用来清理未被引用的大对象数据。

　　【推荐】对于固定条件的查询，可使用部分索引，减小索引的大小，同时提高查询效率。

select * from tbl where id=1 and col=?; -- 其中id=1为固定的条件

create index idx on tbl (col) where id=1;

　　【推荐】对于常用表达式做为查询条件的语句，可使用表达式或函数索引加速查询。

select * from tbl where exp(xxx);

create index idx on tbl ( exp );

　　【推荐】若是须要调试较为复杂的逻辑时，不建议写成函数进行调试，可使用plpgsql的online code.

do language plpgsql
$$ declare
begin
　　-- logical code
end;
$$
;

　　【推荐】当业务有中文分词的查询需求时，建议使用PostgreSQL的分词插件zhparser或jieba，用户还能够经过接口自定义词组。

　　建议在分词字段使用gin索引，提高分词匹配的性能。

　　【推荐】当用户有规则表达式查询，或者文本近似度查询的需求时，建议对字段使用trgm的gin索引，提高近似度匹配或规则表达式匹配的查询效率，同时覆盖了先后模糊的查询需求。若是没有建立trgm gin索引，则不推荐使用先后模糊查询例如like %xxxx%。

　　【推荐】当用户有prefix或者 suffix的模糊查询需求时，可使用索引，或反转索引达到提速的需求。

select * from tbl where col ~ '^abc'; -- 前缀查询

select * from tbl where reverse(col) ~ '^def'; -- 后缀查询使用反转函数索引

　　【推荐】用户应该对频繁访问的大表(一般指超过8GB的表，或者超过1000万记录的表)进行分区，从而提高查询的效率、更新的效率、备份与恢复的效率、建索引的效率等等，(PostgreSQL支持多核建立索引后，能够适当将这个限制放大)。

　　【推荐】用户在设计表结构时，建议规划好，避免常常须要添加字段，或者修改字段类型或长度。 某些操做可能触发表的重写，例如加字段并设置默认值，修改字段的类型。若是用户确实很差规划结构，建议使用jsonb数据类型存储用户数据。