PostgreSQL 数据库开发规范——QUERY 规范 & 管理规范

官方文档：

PostgreSQL 数据库开发规范：https://yq.aliyun.com/articles/60899

【强制】不要使用count(列名)或count(常量)来替代count()，count()就是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。
说明：count(*)会统计NULL值（真实行数），而count(列名)不会统计。

【强制】count(多列列名)时，多列列名必须使用括号，例如count( (col1,col2,col3) )。注意多列的count，即使所有列都为NULL，该行也被计数，所以效果与count(*)一致。

postgres=# create table t123(c1 int,c2 int,c3 int);  
CREATE TABLE  
postgres=# insert into t123 values (null,null,null),(null,null,null),(1,null,null),(2,null,null),(null,1,null),(null,2,null);  
INSERT 0 6  
postgres=# select count((c1,c2)) from t123;  
 count   
-------  
     6  
(1 row)  
postgres=# select count((c1)) from t123;  
 count   
-------  
     2  
(1 row)  

【强制】count(distinct col) 计算该列的非NULL不重复数量，NULL不被计数。

postgres=# select count(distinct (c1)) from t123;  
 count   
-------  
     2  
(1 row)  

【强制】count(distinct (col1,col2,...) ) 计算多列的唯一值时，NULL会被计数，同时NULL与NULL会被认为是想同的。

postgres=# select count(distinct (c1,c2)) from t123;  
 count   
-------  
     5  
(1 row)  
postgres=# select count(distinct (c1,c2,c3)) from t123;  
 count   
-------  
     5  
(1 row)  

【强制】count(col)对 "是NULL的col列" 返回为0，而sum(col)则为NULL。

postgres=# select count(c1),sum(c1) from t123 where c1 is null;  
 count | sum   
-------+-----  
     0 |      
(1 row)  

因此注意sum(col)的NPE问题，如果你的期望是当SUM返回NULL时要得到0，可以这样实现

SELECT coalesce( SUM(g)), 0, SUM(g) ) FROM table;  

【强制】NULL是UNKNOWN的意思，也就是不知道是什么。 因此NULL与任意值的逻辑判断都返回NULL。

NULL<>NULL 的返回结果是NULL，不是false。
NULL=NULL的返回结果也是NULL，不是true。
NULL值与任何值的比较都为NULL，即NULL<>1，返回的是NULL，而不是true。

【强制】除非是ETL程序，否则应该尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

【强制】任何地方都不要使用 select from t ，用具体的字段列表代替，不要返回用不到的任何字段。另外表结构发生变化也容易出现问题。

管理规范

【强制】数据订正时，删除和修改记录时，要先select，避免出现误删除，确认无误才能提交执行。

【强制】DDL操作必须设置锁等待，可以防止堵塞所有其他与该DDL锁对象相关的QUERY。

begin;  
set local lock_timeout = ‘10s‘;  
-- DDL query;  
end;  

【强制】用户可以使用explain analyze查看实际的执行计划，但是如果需要查看的执行计划设计数据的变更，必须在事务中执行explain analyze，然后回滚。

begin;  
explain analyze query;  
rollback; 

【强制】如何并行创建索引，不堵塞表的DML，创建索引时加CONCURRENTLY关键字，就可以并行创建，不会堵塞DML操作，否则会堵塞DML操作。

【强制】如何并行创建索引，不堵塞表的DML，创建索引时加CONCURRENTLY关键字，就可以并行创建，不会堵塞DML操作，否则会堵塞DML操作。

create index CONCURRENTLY idx on tbl(id);  

【强制】为数据库访问账号设置复杂密码。
说明：密码由小写字母，数字、下划线组成、字母开头，字母或数字结尾，禁止123456，hello123等简单密码。

【强制】业务系统，开发测试账号，不要使用数据库超级用户。非常危险。

【推荐】多个业务共用一个PG集群时，建议为每个业务创建一个数据库。 如果业务之间有数据交集，或者事务相关的处理，强烈建议在程序层处理数据的交互。

不能在程序中处理时，可以将多个业务合并到一个库，但是使用不同的schema将多个业务的对象分开来。

【推荐】应该为每个业务分配不同的数据库账号，禁止多个业务共用一个数据库账号。

【推荐】在发生主备切换后，新的主库在开放给应用程序使用前，建议使用pg_prewarm预热之前的主库shared buffer里的热数据。

【推荐】快速的装载数据的方法，关闭autovacuum, 删除索引，数据导入后，对表进行analyze同时创建索引。

【推荐】如何加快创建索引的速度，调大maintenance_work_mem，可以提升创建索引的速度，但是需要考虑实际的可用内存。

begin;  
set local maintenance_work_mem=‘2GB‘;  
create index idx on tbl(id);  
end;  

【推荐】如何防止长连接，占用过多的relcache, syscache。
当系统中有很多张表时，元数据会比较庞大，例如1万张表可能有上百MB的元数据，如果一个长连接的会话，访问到了所有的对象，则可能会长期占用这些syscache和relcache。
建议遇到这种情况时，定期释放长连接，重新建立连接，例如每个小时释放一次长连接。

PS
阿里云的RDS PGSQL版本提供了主动释放syscache和 relcache的接口，不需要断开连接。

【推荐】大批量数据入库的优化，如果有大批量的数据入库，建议使用copy语法，或者 insert into table values (),(),...(); 的方式。 提高写入速度。

PostgreSQL 数据库开发规范——QUERY 规范 & 管理规范

原文：https://www.cnblogs.com/panpanwelcome/p/12470133.html