帮助PostgreSQL使用者快速入门，在开发及管理过程中有章可循. 1命名规范2Column设计3Constraints设计4Index设计5关于NULL6开发相关规范7管理相关规范 1命名规范
1.DBobject:database,schema,table,column,view,index,sequence,function,trigger等名称
(1)建议使用小写字母、数字、下划线的组合
(2)建议不使用双引号即"包围，除非必须包含大写字母或空格等特殊字符
(3)长度不能超过63个字符
(4)不建议以pg开头（避免与系统DBobject混淆），不建议以数字开头
(4)禁止使用SQL关键字，例如type,order等 2.table能包含的column数目,根据字段类型的不同，数目在250到1600之间
3.临时或备份的DBobject:table,view等,建议加上日期,如dba_ops.b2c_product_summay_2014_07_12(其中dba_ops为DBA专用schema) 4.index命名规则为:表名_列名_idx,如student_name_idx,建议不显式给出indexname,使用DBMS默认给出的indexname,如createindexONstudent(name),则默认给出的index名称为student_name_idx 2Column设计
1.建议能用数值类型的，就不用字符类型
2.建议能用varchar(N)就不用char(N),以利于节省存储空间
3.建议能用varchar(N)就不用text,varchar
4.建议使用defaultNULL,而不用default'',以节省存储空间
5.建议使用ip4,ip4r,ip6,ip6r,ipaddress,iprange来存储IP,IP范围；使用macaddr来存储MAC(MediaessControl)address
6.建议使用timestampwithtimezone(timestamptz),而不用timestampwithouttimezone,避免时间函数在对于不同时区的时间点返回值不同,也为业务国际化扫清障碍
7.建议使用NUMERIC(precision,scale)来存储货币金额和其它要求精确计算的数值,而不建议使用real,doubleprecision
8.建议使用hstore来存储非结构化,key-value键值型,对数不定的数据
9.建议使用ltree来存储Top.中国.北京.海淀区.苏州街.维亚大厦这种树状层次结构数据 10.建议使用jsonb(比json更有优势)来存储JSON(JavaScriptObjectNotation)data 11.建议使用GeometricTypes结合PostGIS来实现地理信息数据存储及操作 12.建议使用如下range类型代替字符串或多列来实现范围的存储 int4range—Rangeofintegerint8range—Rangeofbigintnumrange—Rangeofnumerictsrange—Rangeoftimestampwithouttimezonetstzrange—Rangeoftimestampwithtimezonedaterange—Rangeofdate 3Constraints设计
1.建议每个table都有主键;
2.建议不要用有业务含义的名称作为主键,比如身份证或者国家名称,尽管其是unique的
3.建议主键的一步到位的写法:idserialprimarykey或idbigserialprimarykey4.建议内容系统中size较大的table主键的等效写法如下,便于后续维护 createtabletest(idserialnotnull);createuniqueindexCONCURRENTLYONtest(id); 4Index设计
1.PostgreSQL提供的index类型:B-tree,Hash,GiST(GeneralizedSearchTree),SP-GiST(space-partitionedGiST),GIN(GeneralizedInvertedIndex),BRIN(BlockRangeIndex),目前不建议使用Hash2.建议create或dropindex时,加CONCURRENTLY参数,这是个好习惯，达到与写入数据并发的效果
3.建议对于频繁update,delete的包含于index定义中的column的table,用createindexCONCURRENTLY,dropindexCONCURRENTLY的方式进行维护其对应index4.建议用uniqueindex代替uniqueconstraints,便于后续维护
5.建议对where中带多个字段and条件的高频query，参考数据分布情况，建多个字段的联合index6.建议对固定条件的（一般有特定业务含义）且选择比好（数据占比低）的query，建带where的PartialIndexes select*fromtestwherestatus=1andcol=?
;--status=1createindexontest(col)wherestatus=1;
7.建议对经常使用表达式作为查询条件的query，可以使用表达式或函数索引加速query select*fromtestwhereexp(xxx);createindexontest(exp(xxx));
8.建议不要建过多index，一般不要超过6个，核心table（产品，订单）可适当增加index个数 5关于NULL
1.NULL的判断：ISNULL，ISNOTNULL2.注意boolean类型取值true，false，NULL3.小心NOTIN集合中带有NULL元素 mydb=#SELECT*FROM(VALUES
(1),
(2))v(a);a --12 (2rows) mydb=#select1NOTIN(
1,NULL);?
column?
---------f (1row) mydb=#select2NOTIN(
1,NULL);?
column?
---------- (1row) mydb=#SELECT*FROM(VALUES
(1),
(2))v(a)WHEREaNOTIN(
1,NULL);a --(0rows) --SELECTWHEREtrue
4.建议对字符串型NULL值处理后，进行||操作 mydb=#selectNULL||'PostgreSQL';?
column?
---------- (1row) mydb=#select?
column?
-----------PostgreSQL (1row) coalesce(NULL,'')||'PostgreSQL';
5.建议对hstore类型进行处理后，进行||操作，避免被NULL吃掉 mydb=#select?
column?
---------- NULL::hstore||('key=>value'); (1row) mydb=#selectcoalesce(NULL::hstore,hstore(array[]::varchar[]))||('key=>value');?
column?
---------------"key"=>"value" (1row) mydb=#selectcoalesce(NULL::hstore,''::hstore)||('key=>value');?
column?
---------------"key"=>"value" (1row)
6.建议使用count
(1)或count(*)来统计行数，而不建议使用count(col)来统计行数，因为NULL值不会计入注意:count(多列列名)时，多列列名必须使用括号，例如count((col1,col2,col3));注意多列的count，即使所有列都为NULL，该行也被计数，所以效果与count(*)一致7.count(distinctcol)计算某列的非NULL不重复数量，NULL不被计数注意:count(distinct(col1,col2,...))计算多列的唯一值时，NULL会被计数，同时NULL与NULL会被认为是相同的
8.NULL的count与sum selectcount
(1),count(a), NULL; count|count|sum -------+-------+----- 1| 0| (1row) sum(a) from(SELECT*FROM(VALUES(NULL),
(2))v(a))asfoowhereais
9.判断两个值是否相同（将NULL视为相同的值） selectnullisdistinctfromnull;?
column?
---------f(1row) selectnullisdistinctfrom1;?
column?
---------t(1row) selectnullisnot?
column?
---------t (1row) distinctfromnull; mydb=#selectnullisnotdistinctfrom1;?
column?
---------f (1row) 6开发相关规范
1.建议对DBobject尤其是COLUMN加COMMENT，便于后续新人了解业务及维护
2.建议非必须时避免select*，只取所需字段，以减少包括不限于网络带宽消耗，避免表结构变更对程序的影响（比如某些preparequery）
3.建议update时尽量做<>判断,比如updatetable_asetcolumn_b=cwherecolumn_b<>c4.建议将单个事务的多条SQL操作,分解、拆分，或者不放在一个事务里，让每个事务的粒度尽可能小，尽量lock少的资源，避免lock、deadlock的产生
5.建议大批量的数据入库时，使用copy，不建议使用insert，以提高写入速度
6.建议对报表类的或生成基础数据的查询，使用物化视图(MATERIALIZEDVIEW)定期固化数据快照，避免对多表（尤其多写频繁的表）重复跑相同的查询，且物化视图支持REFRESHMATERIALIZEDVIEWCONCURRENTLY,支持并发更新
7.建议复杂的统计查询可以尝试窗口函数WindowFunctions8.state为idleintransaction的连接，如果出现在Master,会无谓的lock住相应的资源,可导致后续产生lock,甚至deadlock;出现在Slave,可导致卡住主从同步 a.pythonpsycopg2mitfalse,,slavetransaction,trueb.psqlpgadminDB,begin;xxx;mit;
9.建议在MyBatis框架中使用SQL语句时，不要写useGeneratedKeys="true"，或直接设置useGeneratedKeys="false"，避免returning*的产生，从而浪费带宽，如果需要返回某个字段，比如id，可以按照如下设置 StatementHandlerPreparedStatementHandleuseGeneratedKeys="true";keyColumn="id";keyProperty="id" 10.建议发给PostgreSQLDBA发布的DDL，附带常用DML:SELECT,INSERT,DELETE,UPDATE，便于DBA给出createindexCONCURRENTLY等其他优化建议 7管理相关规范
1.建议清空表时，使用truncate，不建议使用delete2.建议向大size的table中addcolumn时，将altertabletaddcolumncoldatatypenotnulldefaultxxx；分解为如下，避免填充default值导致的过长时间锁表 altertabletaddcolumncoldatatype altertabletaltercolumncolsetdefaultxxx updatetsetcolumn=defaultwhereid=1; .................. updatetsetcolumn=defaultwhereid=N; ------,\watch------updatetablet1000);\watch3 setcolumn=DEFAULTwhereidin(selectidfromtwherecolumnisnulllimit altertabletaltercolumncolsetnotnull
3.建议执行DDL,比如CRAETE、DROP、ALTER等,尤其多条，不要显式的开transaction,因为加lock的mode非常高,极易产生deadlock4.建议对频繁更新的表，建表时指定fillfactor，一般建议值为85 createtabletest(idint)with(fillfactor=85);
5.建议运行在SSD上的实例,random_page_cost(默认值为4)设置为1.0~2.0之间,使查询规划器更倾向于使用索引扫描
6.建议在需要使用explainanalyze查看实际真正执行计划与时间时，如果是写入query，强烈建议先开启事务，然后回滚