Postgres大象会2016官方报名通道：

作者：digoal

背景

PostgreSQL的功能非常强大，但是要把PostgreSQL用好，开发人员是非常关键的。

下面将针对PostgreSQL数据库原理与特性，输出一份开发规范，希望可以减少大家在使用PostgreSQL数据库过程中遇到的困惑。 目标是将PostgreSQL的功能、性能发挥好，她好我也好。

PostgreSQL 使用规范

开发规范

【强制】不要使用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。

【强制】在代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页语句。

【强制】游标使用后要及时关闭。

【强制】两阶段提交的事务，要及时提交或回滚，否则可能导致数据库膨胀。

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

例如

begin;  
set local lock_time = '10s';  
-- DDL query;  
end;  

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

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

【强制】comment不要使用中文，query中别名不要使用中文。

【强制】不要使用delete 全表，性能很差，请使用truncate代替。

【强制】btree索引字段不建议超过2000字节，如果有超过2000字节的字段需要建索引，建议使用函数索引（例如哈希值索引），或者使用分词索引。

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

例如

postgres=# create table tbl(id int primary key,info text);  
CREATE TABLE  
postgres=# create table tbl1(id int references tbl(id), info text);  
CREATE TABLE  
postgres=# \d tbl  
      Table "public.tbl"  
 Column |  Type   | Modifiers   
--------+---------+-----------  
 id     | integer | not null  
 info   | text    |   
Indexes:  
    "tbl_pkey" PRIMARY KEY, btree (id)  
Referenced by:  
    TABLE "tbl1" CONSTRAINT "tbl1_id_fkey" FOREIGN KEY (id) REFERENCES tbl(id)  

postgres=# \d tbl1  
     Table "public.tbl1"  
 Column |  Type   | Modifiers   
--------+---------+-----------  
 id     | integer |   
 info   | text    |   
Foreign-key constraints:  
    "tbl1_id_fkey" FOREIGN KEY (id) REFERENCES tbl(id)  

postgres=# \di  
              List of relations  
 Schema |   Name   | Type  |  Owner   | Table   
--------+----------+-------+----------+-------  
 public | tbl_pkey | index | postgres | tbl  
(1 row)  

postgres=# create index idx_tbl1_id on tbl1(id);  
CREATE INDEX  

【强制】使用外键时，一定要设置fk的action，例如cascade，set null，set default。

例如

postgres=# create table tbl2(id int references tbl(id) on delete cascade on update cascade, info text);  
CREATE TABLE  
postgres=# create index idx_tbl2_id on tbl2(id);  
CREATE INDEX  
postgres=# insert into tbl values (1,'test');  
INSERT 0 1  
postgres=# insert into tbl2 values (1,'test');  
INSERT 0 1  
postgres=# update tbl set id=2;  
UPDATE 1  
postgres=# select * from tbl2;  
 id | info   
----+------  
  2 | test  
(1 row)  

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

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

例如

begin;  
explain analyze query;  
rollback;  

【强制】应用程序一定要开启autocommit，同时避免应用程序自动begin事务，并且不进行任何操作的情况发生，某些框架可能会有这样的问题。

【强制】高并发的应用场合，务必使用绑定变量(prepared statement)，防止数据库硬解析消耗过多的CPU资源。

【强制】不要使用hash index，目前hash index不写REDO，在备库只有结构，没有数据，并且数据库crash后无法恢复。

同时不建议使用unlogged table ，道理同上，但是如果你的数据不需要持久化，则可以考虑使用unlogged table来提升数据的写入和修改性能。

【强制】秒杀场景，一定要使用 advisory_lock先对唯一ID进行锁定，拿到AD锁再去对数据进行更新操作。 拿不到锁时，可以尝试重试拿锁。

例如

CREATE OR REPLACE FUNCTION public.f(i_id integer)    
 RETURNS void    
 LANGUAGE plpgsql    
AS $function$   
declare   
  a_lock boolean := false;  
begin   
  select pg_try_advisory_xact_lock(i_id) into a_lock;  
  --拿到锁，更新  
  if a_lock then  
    update t1 set count=count-1 where id=i_id;   
  end if;  
  exception when others then    
    return;   
end;   
$function$;    

select f(id) from tbl where id=? and count>0;  

你可以再根据实际情况设计，原理如上即可。函数可以如返回布尔，或者唯一ID，或者数字等。

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

postgres=# create table test123(id int, info text) with(fillfactor=85);  
CREATE TABLE  

【强制】在函数中，或程序中，不要使用count(*)判断是否有数据，很慢。 建议的方法是limit 1;

例如

select 1 from tbl where xxx limit 1;  
if found -- 存在  
else  -- 不存在  

【强制】对于高并发的应用场景，务必使用程序的连接池，否则性能会很低下。

如果程序没有连接池，建议在应用层和数据库之间架设连接池，例如使用pgbouncer或者pgpool-II作为连接池。

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

例如

create index CONCURRENTLY idx on tbl(id);  

【强制】当业务有近邻查询的需求时，务必对字段建立GIST或SP-GIST索引，加速近邻查询的需求。

例如

create index idx on tbl using gist(col);  
select * from tbl order by col <-> '(0,100)';  

【强制】为数据库访问账号设置复杂密码。

说明：密码由小写字母，数字、下划线组成、字母开头，字母或数字结尾，禁止123456，hello123等简单密码。

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

说明： 比如A表user_id字段数据类型定义为varchar，但是SQL语句查询为 where user_id=1234；

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

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

【强制】避免频繁创建和删除临时表，以减少系统表资源的消耗，因为创建临时表会产生元数据，频繁创建，元数据可能会出现碎片。

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

【强制】必须选择合适的事务隔离级别，不要使用越级的隔离级别，例如READ COMMITTED可以满足时，就不要使用repeatable read和serializable隔离级别。

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

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

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

【推荐】不需要精确分页数时，请使用快速评估分页数的方法。可参见：https://yq.aliyun.com/articles/39682

例如

CREATE OR REPLACE FUNCTION countit(text)                    
RETURNS float4           
LANGUAGE plpgsql AS          
$$DECLARE               
    v_plan json;                
BEGIN                      
    EXECUTE 'EXPLAIN (FORMAT JSON) '||$1                                
        INTO v_plan;                                                                       
    RETURN v_plan #>> '{0,Plan,"Plan Rows"}';  
END;  
$$;  
postgres=# create table t1234(id int, info text);  
CREATE TABLE  
postgres=# insert into t1234 select generate_series(1,1000000),'test';  
INSERT 0 1000000  
postgres=# analyze t1234;  
ANALYZE  
postgres=# select countit('select * from t1234 where id<1000');  
 countit   
---------  
     954  
(1 row)  
postgres=# select countit('select * from t1234 where id between 1 and 1000 or (id between 100000 and 101000)');  
 countit   
---------  
    1931  
(1 row)  

【推荐】建议通过游标返回分页结果，避免越后面的页返回越慢的情况。

例如

postgres=# declare cur1 cursor for select * from sbtest1 where id between 100 and 1000000 order by id;  
DECLARE CURSOR  
Time: 0.422 ms  

获取数据

postgres=# fetch 100 from cur1;  
。。。

如果要前滚页，加SCROLL打开游标

declare cur1 SCROLL cursor for select * from sbtest1 where id between 100 and 1000000 order by id;  

【推荐】可以预估SQL执行时间的操作，建议设置语句级别的超时，可以防止雪崩，也可以防止长时间持锁。

例如设置事务中执行的每条SQL超时时间为10秒

begin;  
set local statement_timeout = '10s';  
-- query;  
end;  

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

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

例如, 计算字符长度

postgres=# select length('阿里巴巴');  
 length   
--------  
      4  
(1 row)  

计算字节数

postgres=# select octet_length('阿里巴巴');  
 octet_length   
--------------  
           12  
(1 row)  

其他长度相关接口

   Schema   |          Name          | Result data type | Argument data types |  Type    
------------+------------------------+------------------+---------------------+--------  
 pg_catalog | array_length           | integer          | anyarray, integer   | normal  
 pg_catalog | bit_length             | integer          | bit                 | normal  
 pg_catalog | bit_length             | integer          | bytea               | normal  
 pg_catalog | bit_length             | integer          | text                | normal  
 pg_catalog | char_length            | integer          | character           | normal  
 pg_catalog | char_length            | integer          | text                | normal  
 pg_catalog | character_length       | integer          | character           | normal  
 pg_catalog | character_length       | integer          | text                | normal  
 pg_catalog | json_array_length      | integer          | json                | normal  
 pg_catalog | jsonb_array_length     | integer          | jsonb               | normal  
 pg_catalog | length                 | integer          | bit                 | normal  
 pg_catalog | length                 | integer          | bytea               | normal  
 pg_catalog | length                 | integer          | bytea, name         | normal  
 pg_catalog | length                 | integer          | character           | normal  
 pg_catalog | length                 | double precision | lseg                | normal  
 pg_catalog | length                 | double precision | path                | normal  
 pg_catalog | length                 | integer          | text                | normal  
 pg_catalog | length                 | integer          | tsvector            | normal  
 pg_catalog | lseg_length            | double precision | lseg                | normal  
 pg_catalog | octet_length           | integer          | bit                 | normal  
 pg_catalog | octet_length           | integer          | bytea               | normal  
 pg_catalog | octet_length           | integer          | character           | normal  
 pg_catalog | octet_length           | integer          | text                | normal  

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

【推荐】TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但是TRUNCATE是DDL，锁粒度很大，故不建议在开发代码中使用DDL语句，除非加了lock_timeout锁超时的会话参数或事务参数。

【推荐】PostgreSQL支持DDL事务，支持回滚DDL，建议将DDL封装在事务中执行，必要时可以回滚。

【推荐】如果用户需要在插入数据和，删除数据前，或者修改数据后马上拿到插入或被删除或修改后的数据，建议使用insert into .. returning ..; delete .. returning ..或update .. returning ..; 语法。减少数据库交互次数。

例如

postgres=# create table tbl4(id serial, info text);  
CREATE TABLE  
postgres=# insert into tbl4 (info) values ('test') returning *;  
 id | info   
----+------  
  1 | test  
(1 row)  

INSERT 0 1  

postgres=# update tbl4 set info='abc' returning *;  
 id | info   
----+------  
  1 | abc  
(1 row)  

UPDATE 1  

postgres=# delete from tbl4 returning *;  
 id | info   
----+------  
  1 | abc  
(1 row)  

DELETE 1  

【推荐】自增字段建议使用序列，序列分为2字节，4字节，8字节几种。按实际情况选择。 切勿使用触发器产生序列值。

例如

postgres=# create table tbl4(id serial, info text);  
CREATE TABLE  

【推荐】如果对全表的很多字段有任意字段匹配的查询需求，建议使用行级别全文索引，或行转数组的数组级别索引。

例如

select * from t where phonenum='digoal' or info ~ 'digoal' or c1='digoal' or ......;  

更正为

postgres=# create or replace function f1(text) returns tsvector as $$            
select to_tsvector($1);     
$$ language sql immutable strict;  
CREATE FUNCTION  

postgres=# alter function record_out(record) immutable;  
ALTER FUNCTION  
postgres=# alter function textin(cstring) immutable;  
ALTER FUNCTION  
postgres=# create index idx_t_1 on t using gin (f1('jiebacfg'::regconfig,t::text)) ;  
CREATE INDEX  

postgres=# select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & post') ;  
 phonenum | info | c1 | c2 | c3 | c4   
----------+------+----+----+----+----  
(0 rows)  
postgres=# select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & china') ;  
  phonenum   |            info             | c1  |  c2   |              c3              |             c4               
-------------+-----------------------------+-----+-------+------------------------------+----------------------------  
 13888888888 | i am digoal, a postgresqler | 123 | china | 中华人民共和国，阿里巴巴，阿 | 2016-04-19 11:15:55.208658  
(1 row)  

postgres=# select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & 阿里巴巴') ;  
  phonenum   |            info             | c1  |  c2   |              c3              |             c4               
-------------+-----------------------------+-----+-------+------------------------------+----------------------------  
 13888888888 | i am digoal, a postgresqler | 123 | china | 中华人民共和国，阿里巴巴，阿 | 2016-04-19 11:15:55.208658  
(1 row)  

postgres=# explain select * from t where f1('jiebacfg'::regconfig,t::text) @@ to_tsquery('digoal & 阿里巴巴') ;  
                                              QUERY PLAN                                                
------------------------------------------------------------------------------------------------------  
 Seq Scan on t  (cost=0.00..1.52 rows=1 width=140)  
   Filter: (to_tsvector('jiebacfg'::regconfig, (t.*)::text) @@ to_tsquery('digoal & 阿里巴巴'::text))  
(2 rows)  

【推荐】中文分词的token mapping一定要设置，否则对应的token没有词典进行处理。

例如

ALTER TEXT SEARCH CONFIGURATION testzhcfg ADD MAPPING FOR a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z WITH simple;

zhparser分词插件的其他配置

zhparser.punctuation_ignore = f
zhparser.seg_with_duality = f
zhparser.dict_in_memory = f
zhparser.multi_short = f
zhparser.multi_duality = f
zhparser.multi_zmain = f
zhparser.multi_zall = f

参考
https://yq.aliyun.com/articles/7730
http://www.xunsearch.com/scws/docs.php#libscws