PostgreSQL使用建议（非常实用）

一、命名规范

1.DBobject:database,schema,table,view,index,function,trigger等名称
(1)建议使用小写字母、数字、下划线的组合
(2)建议不使用双引号即"包围，除非必须包含大写字母或空格等特殊字符
(3)长度不能超过63个字符
(4)禁止使用SQL关键字，例如type,order等

2.table能包含的column数目,根据字段类型的不同，数目在250到1600之间

3.临时或备份的DBobject:table,view等,建议加上日期,如table_xxx_20150826

4.index命名规则为:表名_列名_idx,如student_name_idx,建议不显式给出indexname,使用DBMS系统默认给出的indexname,如createindexONstudent(name);则默认给出student_name_idx

二、Column设计

1.建议能用varchar(N)就不用char(N),以利于节省存储空间

2.建议能用varchar(N)就不用text,varchar

3.建议使用defaultNULL,而不用default'',以节省存储空间,

4.建议使用ip4,ip4r,ip6,ip6r,ipaddress,iprange来存储IP,IP范围；使用macaddr来存储MAC(MediaAccessControl)address

5.建议使用timestampwithtimezone(timestamptz),而不用timestampwithouttimezone,避免时间函数在对于不同时区的时间点返回值不同,也为业务国际化扫清障碍

6.建议使用NUMERIC(precision,scale)来存储货币金额和其它要求精确计算的数值,而不建议使用real,doubleprecision

7.建议使用hstore来存储非结构化,key-value键值型,对数不定的数据

8.建议使用ltree来存储Top.中国.北京.天安门这种树状层次结构数据

9.建议使用json来存储JSON(JavaScriptObjectNotation)data

10.建议使用GeometricTypes结合PostGIS来实现地理信息数据存储及操作

11.建议使用如下range类型代替字符串或多列来实现范围的存储

三、Constraints设计

1.建议每个table都有主键;

2.建议不要用有业务含义的名称作为主键,比如身份证或者国家名称,尽管其是unique的

3.建议主键的一步到位的写法:idserialprimarykey或idbigserialprimarykey

四、Index设计

1.PostgreSQL提供的index类型:B-tree,Hash,GiST(GeneralizedSearchTree),SP-GiST(space-partitionedGiST)andGIN(GeneralizedInvertedIndex),目前不建议使用Hash,SP-GiST

2.建议create或dropindex时,加CONCURRENTLY参数,这是个好习惯，达到与写入数据并发的效果

3.建议对于频繁update,delete的包含于index定义中的column的table,用createindexCONCURRENTLY,dropindexCONCURRENTLY的方式进行维护其对应index

4.建议用uniqueindex代替uniqueconstraints,便于后续维护

5.建议不要建过多index，一般不要超过6个，核心table（产品，订单）可适当增加index个数

五、关于NULL

1.NULL的判断：ISNULL，ISNOTNULL

2.注意boolean类型取值true，false，NULL

3.小心NOTIN集合中带有NULL元素

postgres=# SELECT * FROM (VALUES(1),(2)) v(a) ; 
 a
 --- 
 1 
 2
 (2 rows)  
postgres=# select 1 NOT IN (1,NULL); 
 ?column?
 ---------- 
 f
 (1 row)  
postgres=# select 2 NOT IN (1,NULL); 
 ?column?
 ---------- 
 
(1 row) 
postgres=# SELECT * FROM (VALUES(1),(2)) v(a) WHERE a NOT IN (1,NULL); 
 a
 ---
(0 rows)

可见，出现这种情况的根本原因在于SELECT只返回WHERE中判断条件结果为true的数据

4.建议对字符串型NULL值处理后，进行||操作

postgres=# select NULL||'PostgreSQL'; 
 ?column?
 ---------- 
 
 (1 row) 
postgres=# select coalesce(NULL,'')||'PostgreSQL';  
 ?column?
 ------------ 
 PostgreSQL
 (1 row)

5.建议对hstore类型进行处理后，进行||操作，避免被NULL吃掉

postgres=# select  NULL::hstore || ('key=>value') ; 
 ?column?
 ---------- 
 
 (1 row) 
postgres=# select  coalesce(NULL::hstore,hstore(array[]::varchar[])) || ('key=>value') ;
?column?
----------------
 "key"=>"value"
 (1 row) 
postgres=# select  coalesce(NULL::hstore,''::hstore) || ('key=>value') ;    
 ?column?    
 ----------------
  "key"=>"value"
  (1 row)

六、其他注意事项

1.建议对DBobject尤其是COLUMN加COMMENT，便于后续维护

2.建议非必须时避免select*,只取所需字段，以减少网络带宽消耗，避免表结构变更对程序的影响

3.建议update时尽量做<>判断,比如updatetable_asetcolumn_b=cwherecolumn_b<>c

4.建议将单个事务的多条SQL操作,分解、拆分，或者不放在一个事务里，让每个事务的粒度尽可能小，尽量lock少的资源，避免lock、deadlock的产生

5.建议向大size的table中addcolumn时，将altertabletaddcolumncoldatatypenotnulldefaultxxx；分解为如下，避免填充default值导致的过长时间锁表

alter table t add column col datatype ； 
alter table t alter column col set default xxx； 
update t set column = default where id = 1; 
.................. 
update t set column = default where id = N; 
------此处,可以用先进的watch来刷------即 
update table t  set column= DEFAULT where id in ( select id from t where column is null limit 1000 ) ; watch 3 
alter table t alter column col set not null；

6.建议执行DDL,比如CRAETE,DROP,ALTER等,不要显式的开transaction,因为加lock的mode非常高,极易产生deadlock

7.建议复杂的统计查询可以尝试窗口函数WindowFunctions

8.建议发给PostgrSQLDBAreview及执行的SQL，无论是使用pgadmin这种图形化工具，还是pg_dump这种命令行工具生成的SQL，都去掉注释(--之后的部分)，双引号"及alterowner等冗余或不应该带到线上生产的dev/betaDB中的信息

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!