PG中文社区 /
mdi-home
首页 社区新闻 中文文档 加入ACE {{ item.text }} 登录
mdi-home 首页 mdi-chat-processing 社区新闻 mdi-book-open-variant 中文文档 mdi-account-multiple-check 加入ACE mdi-file-multiple-outline 相关资料 mdi-blank {{item.text}} mdi-exit-to-app 退出账号
PostgreSQL数据库开发规范(第一章)

原作者:digoal/德哥  创作时间:2016-09-27 16:11:30+08  
doudou586 发布于2016-09-27 16:11:30           评论: 1   浏览: 19721   顶: 6780  踩: 6973 

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


http://www.huodongxing.com/event/8352217821400

PostgreSQL数据库开发规范(第一章)

作者: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



评论:1   浏览: 19721                   顶: 6780  踩: 6973 

请在登录后发表评论,否则无法保存。

1# __ jfhyn 回答于 2016-10-08 15:55:15+08
很棒,学习了!



发表评论:
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:按此访问
社区邮件列表:按此订阅
商业支持
扫码关注
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:按此访问
社区邮件列表:按此订阅
商业支持
扫码关注
© PostgreSQL中文社区 ... (自2010年起)