PostgreSQL的功能非常强大，但是要把PostgreSQL用好，开发人员是非常关键的。
下面将针对PostgreSQL数据库原理与特性，输出一份开发规范，希望可以减少大家在使用PostgreSQL数据库过程中遇到的困惑。
目标是将PostgreSQL的功能、性能发挥好，她好我也好。
【强制】库名、表名限制命名长度，建议表名及字段名字符总长度小于等于63。
【强制】query中的别名不要使用 "小写字母，下划线，数字" 以外的字符，例如中文。
【推荐】主键索引应以 pk_ 开头， 唯一索引要以 uk_ 开头，普通索引要以 idx_ 打头。
【推荐】临时表以 tmp_ 开头，子表以规则结尾，例如按年分区的主表如果为tbl, 则子表为tbl_2016，tbl_2017，。。。
【推荐】库名最好以部门名字开头 + 功能，如 xxx_yyy，xxx_zzz，便于辨识， 。。。
【推荐】库名最好与应用名称一致，或便于辨识。
【推荐】不建议使用public schema(不同业务共享的对象可以使用public schema)，应该为每个应用分配对应的schema，schema_name最好与user name一致。
【推荐】comment不要使用中文，因为编码可能不一样，如果存进去和读取时的编码不一致，导致可读性不强。 pg_dump时也必须与comment时的编码一致，否则可能乱码。
【强制】多表中的相同列，必须保证列名一致，数据类型一致。
【强制】btree索引字段不建议超过2000字节，如果有超过2000字节的字段需要建索引，建议使用函数索引（例如哈希值索引），或者使用分词索引。
【强制】对于频繁更新的表，建议建表时指定表的fillfactor=85，每页预留15%的空间给HOT更新使用。
或
【推荐】建议有定期历史数据删除需求的业务，表按时间分区，删除时不要使用DELETE操作，而是DROP或者TRUNCATE对应的表。
计算字节数
其他长度相关接口
【推荐】应用应该尽量避免使用数据库触发器，这会使得数据处理逻辑复杂，不便于调试。
【推荐】当用户有规则表达式查询，或者文本近似度查询的需求时，建议对字段使用trgm的gin索引，提升近似度匹配或规则表达式匹配的查询效率，同时覆盖了前后模糊的查询需求。如果没有创建trgm gin索引，则不推荐使用前后模糊查询例如like %xxxx%。
【推荐】用户应该对频繁访问的大表（通常指超过8GB的表，或者超过1000万记录的表）进行分区，从而提升查询的效率、更新的效率、备份与恢复的效率、建索引的效率等等，（PostgreSQL支持多核创建索引后，可以适当将这个限制放大）。
【强制】不要使用count(列名)或count(常量)来替代count()，count()就是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。
说明：count(*)会统计NULL值（真实行数），而count(列名)不会统计。
因此注意sum(col)的NPE问题，如果你的期望是当SUM返回NULL时要得到0，可以这样实现
【强制】除非是ETL程序，否则应该尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
【强制】任何地方都不要使用 select from t ，用具体的字段列表代替，不要返回用不到的任何字段。另外表结构发生变化也容易出现问题。
【强制】数据订正时，删除和修改记录时，要先select，避免出现误删除，确认无误才能提交执行。
【强制】业务系统，开发测试账号，不要使用数据库超级用户。非常危险。
【推荐】应该为每个业务分配不同的数据库账号，禁止多个业务共用一个数据库账号。
【推荐】在发生主备切换后，新的主库在开放给应用程序使用前，建议使用pg_prewarm预热之前的主库shared buffer里的热数据。
【推荐】快速的装载数据的方法，关闭autovacuum, 删除索引，数据导入后，对表进行analyze同时创建索引。
【推荐】大批量数据入库的优化，如果有大批量的数据入库，建议使用copy语法，或者 insert into table values (),(),...(); 的方式。 提高写入速度。
【强制】在代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页语句。
【强制】游标使用后要及时关闭。
【强制】两阶段提交的事务，要及时提交或回滚，否则可能导致数据库膨胀。
【强制】不要使用delete 全表，性能很差，请使用truncate代替，（truncate是DDL语句，注意加锁等待超时）。
【强制】应用程序一定要开启autocommit，同时避免应用程序自动begin事务，并且不进行任何操作的情况发生，某些框架可能会有这样的问题。
【强制】高并发的应用场合，务必使用绑定变量(prepared statement)，防止数据库硬解析消耗过多的CPU资源。
【强制】避免频繁创建和删除临时表，以减少系统表资源的消耗，因为创建临时表会产生元数据，频繁创建，元数据可能会出现碎片。
【强制】必须选择合适的事务隔离级别，不要使用越级的隔离级别，例如READ COMMITTED可以满足时，就不要使用repeatable read和serializable隔离级别。
【推荐】高峰期对大表添加包含默认值的字段，会导致表的rewrite，建议只添加不包含默认值的字段，业务逻辑层面后期处理默认值。
获取数据
如果要前滚页，加SCROLL打开游标
【推荐】TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但是TRUNCATE是DDL，锁粒度很大，故不建议在开发代码中使用DDL语句，除非加了lock_timeout锁超时的会话参数或事务参数。
【推荐】PostgreSQL支持DDL事务，支持回滚DDL，建议将DDL封装在事务中执行，必要时可以回滚，但是需要注意事务的长度，避免长时间堵塞DDL对象的读操作。
更正为
zhparser分词插件的其他配置
从Root往树末梢递归
从末梢往树ROOT递归
【推荐】应尽量避免长事务，长事务可能造成垃圾膨胀。
生成一批测试数据
创建一个统计结果表, 其中bitmap表示统计的字段组合, 用位置符0,1表示是否统计了该维度
在bitmap上创建索引方便取数据
用户勾选几个维度，取出数据
【推荐】PostgreSQL 的insert on conflict语法如下
例如
增量刷新物化视图
需要找出某个产品，这个产品只有一个公司生产。
又如，根据指定窗口，查询当前行与以窗口为范围取其avg,max,min,sum,count,offset,rank,dist等，同时输出当前行。例如与第一名的差距，与前一名的差距，与全国第一名的差距，与全班第一名的差距，同时还输出当前记录的详情。
【推荐】应该尽量在业务层面避免死锁的产生，例如一个用户的数据，尽量在一个线程内处理，而不要跨线程（即跨数据库会话处理）。
带PK的列col级别去重
不带PK的列级别去重(以业务逻辑为准，可以选择其他的条件删除)
方法 2. 取出N条连续的随机记录.(此处用到函数)
以下举例取出10条连续的随机记录
【推荐】线上表结构的变更包括添加字段，索引操作在业务低峰期进行。
【推荐】查询条件要和索引匹配，例如查询条件是表达式时，索引也要是表达式索引，查询条件为列时，索引就是列索引。
另外还有IS NOT DISTINCT FROM的用法 。
3. 约束
在查询列上有约束的情况下，如果!=或<>与约束违背，则可以提前返回查询，不会扫描表。
【推荐】很多时候用 exists 代替 in 是一个好的选择：
用下面的语句替换：
【推荐】尽量使用数组变量来代替临时表。如果临时表有非常庞大的数据时，才考虑使用临时表。
隐式的JOIN例子
例如
或者设置会话级别的join_collapse_limit=1;
修改SQL，固定为最佳JOIN顺序。
调整SQL如下
如果你是阿里云RDS PGSQL的用户，推荐你参考一下规范，阿里云RDS PGSQL提供了很多有趣的特性帮助用户解决社区版本不能解决的问题。
【推荐】对RT要求高的业务，请使用SLB链路 或 PROXY透传模式连接数据库。
【推荐】为RDS报警设置多位接收人，并设置合适的报警阀值。
【推荐】为RDS设置合适的白名单，加固数据访问的安全性。
【推荐】尽量禁止数据库被公网访问，如果真的要访问，一定要设置白名单。
版权声明：
本文来源网络，所有图片文章版权属于原作者，如有侵权，联系删除。
本文网址：https://www.mushiming.com/mjsbk/2668.html