命名规范
强制】库名、表名限制命名长度，建议表名及字段名字符总长度小于等于63。
【强制】对象名（表名、列名、函数名、视图名、序列名、等对象名称）规范，对象名务必只使用小写字母，下划线，数字。不要以pg开头，不要以数字开头，不要使用保留字。
保留字参考
https://www.postgresql.org/docs/9.5/static/sql-keywords-appendix.html
【强制】query中的别名不要使用 "小写字母，下划线，数字" 以外的字符，例如中文。
【推荐】主键索引应以 pk_ 开头， 唯一索引要以 uk_ 开头，普通索引要以 idx_ 打头。
【推荐】临时表以 tmp_ 开头，子表以规则结尾，例如按年分区的主表如果为tbl, 则子表为tbl_2016，tbl_2017，。。。
【推荐】库名最好与应用名称一致，或便于辨识。
【推荐】不建议使用public schema(不同业务共享的对象可以使用public schema)，应该为每个应用分配对应的schema，schema_name最好与user name一致。
【推荐】comment不要使用中文，因为编码可能不一样，如果存进去和读取时的编码不一致，导致可读性不强。 pg_dump时也必须与comment时的编码一致，否则可能乱码。
设计规范
【强制】多表中的相同列，必须保证列名一致，数据类型一致。
【强制】btree索引字段不建议超过2000字节，如果有超过2000字节的字段需要建索引，建议使用函数索引（例如哈希值索引），或者使用分词索引。
【强制】使用外键时，如果你使用的PG版本没有自动建立fk的索引，则必须要对foreign key手工建立索引，否则可能影响references列的更新或删除性能。
1 postgres=# create table tbl(id int primary key,info text); 2 CREATE TABLE 3 postgres=# create table tbl1(id int references tbl(id), info text); 4 CREATE TABLE 5 postgres=# d tbl 6 Table "public.tbl" 7 Column | Type | Modifiers 8 --------+---------+----------- 9 id | integer | not null 10 info | text | 11 Indexes: 12 "tbl_pkey" PRIMARY KEY, btree (id) 13 Referenced by: 14 TABLE "tbl1" CONSTRAINT "tbl1_id_fkey" FOREIGN KEY (id) REFERENCES tbl(id) 15 16 postgres=# d tbl1 17 Table "public.tbl1" 18 Column | Type | Modifiers 19 --------+---------+----------- 20 id | integer | 21 info | text | 22 Foreign-key constraints: 23 "tbl1_id_fkey" FOREIGN KEY (id) REFERENCES tbl(id) 24 25 postgres=# di 26 List of relations 27 Schema | Name | Type | Owner | Table 28 --------+----------+-------+----------+------- 29 public | tbl_pkey | index | postgres | tbl 30 (1 row) 31 32 postgres=# create index idx_tbl1_id on tbl1(id); 33 CREATE INDEX
【强制】使用外键时，一定要设置fk的action，例如cascade，set null，set default。
1 postgres=# create table tbl2(id int references tbl(id) on delete cascade on update cascade, info text); 2 CREATE TABLE 3 postgres=# create index idx_tbl2_id on tbl2(id); 4 CREATE INDEX 5 postgres=# insert into tbl values (1,'test'); 6 INSERT 0 1 7 postgres=# insert into tbl2 values (1,'test'); 8 INSERT 0 1 9 postgres=# update tbl set id=2; 10 UPDATE 1 11 postgres=# select * from tbl2; 12 id | info 13 ----+------ 14 2 | test 15 (1 row)
【强制】对于频繁更新的表，建议建表时指定表的fillfactor=85，每页预留15%的空间给HOT更新使用。
1 postgres=# create table test123(id int, info text) with(fillfactor=85); 2 CREATE TABLE
【强制】表结构中字段定义的数据类型与应用程序中的定义保持一致，表之间字段校对规则一致，避免报错或无法使用索引的情况发生。
说明：
(1).比如A表user_id字段数据类型定义为varchar，但是SQL语句查询为 where user_id=1234；
【推荐】如何保证分区表的主键序列全局唯一。
使用多个序列，每个序列的步调不一样，或者每个序列的范围不一样即可。QUENCE
1 postgres=# create sequence seq_tab1 increment by 10000 start with 1; 2 CREATE SEQUENCE 3 postgres=# create sequence seq_tab2 increment by 10000 start with 2; 4 CREATE SEQUENCE 5 postgres=# create sequence seq_tab3 increment by 10000 start with 3; 6 CREATE SEQUENCE 7 postgres=# create table tab1 (id int primary key default nextval('seq_tab1') check(mod(id,10000)=1), info text); 8 CREATE TABLE 9 postgres=# create table tab2 (id int primary key default nextval('seq_tab2') check(mod(id,10000)=2), info text); 10 CREATE TABLE 11 postgres=# create table tab3 (id int primary key default nextval('seq_tab3') check(mod(id,10000)=3), info text); 12 CREATE TABLE 13 14 postgres=# insert into tab1 (info) select generate_series(1,10); 15 INSERT 0 10 16 postgres=# insert into tab2 (info) select generate_series(1,10); 17 INSERT 0 10 18 postgres=# insert into tab3 (info) select generate_series(1,10); 19 INSERT 0 10 20 postgres=# select * from tab1; 21 id | info 22 -------+------ 23 1 | 1 24 10001 | 2 25 20001 | 3 26 30001 | 4 27 40001 | 5 28 50001 | 6 29 60001 | 7 30 70001 | 8 31 80001 | 9 32 90001 | 10 33 (10 rows) 34 35 postgres=# select * from tab2; 36 id | info 37 -------+------ 38 2 | 1 39 10002 | 2 40 20002 | 3 41 30002 | 4 42 40002 | 5 43 50002 | 6 44 60002 | 7 45 70002 | 8 46 80002 | 9 47 90002 | 10 48 (10 rows) 49 50 postgres=# select * from tab3; 51 id | info 52 -------+------ 53 3 | 1 54 10003 | 2 55 20003 | 3 56 30003 | 4 57 40003 | 5 58 50003 | 6 59 60003 | 7 60 70003 | 8 61 80003 | 9 62 90003 | 10 63 (10 rows)
1 postgres=# create sequence seq_tb1 increment by 1 minvalue 1 maxvalue 100000000 start with 1 no cycle ; 2 CREATE SEQUENCE 3 postgres=# create sequence seq_tb2 increment by 1 minvalue 100000001 maxvalue 200000000 start with 100000001 no cycle ; 4 CREATE SEQUENCE 5 postgres=# create sequence seq_tb3 increment by 1 minvalue 200000001 maxvalue 300000000 start with 200000001 no cycle ; 6 CREATE SEQUENCE 7 8 postgres=# create table tb1(id int primary key default nextval('seq_tb1') check(id >=1 and id<=100000000), info text); 9 CREATE TABLE 10 postgres=# create table tb2(id int primary key default nextval('seq_tb2') check(id >=100000001 and id<=200000000), info text); 11 CREATE TABLE 12 postgres=# create table tb3(id int primary key default nextval('seq_tb3') check(id >=200000001 and id<=300000000), info text); 13 CREATE TABLE 14 postgres=# insert into tb1 (info) select * from generate_series(1,10); 15 INSERT 0 10 16 postgres=# insert into tb2 (info) select * from generate_series(1,10); 17 INSERT 0 10 18 postgres=# insert into tb3 (info) select * from generate_series(1,10); 19 INSERT 0 10 20 postgres=# select * from tb1; 21 id | info 22 ----+------ 23 1 | 1 24 2 | 2 25 3 | 3 26 4 | 4 27 5 | 5 28 6 | 6 29 7 | 7 30 8 | 8 31 9 | 9 32 10 | 10 33 (10 rows) 34 35 postgres=# select * from tb2; 36 id | info 37 -----------+------ 38 100000001 | 1 39 100000002 | 2 40 100000003 | 3 41 100000004 | 4 42 100000005 | 5 43 100000006 | 6 44 100000007 | 7 45 100000008 | 8 46 100000009 | 9 47 100000010 | 10 48 (10 rows) 49 50 postgres=# select * from tb3; 51 id | info 52 -----------+------ 53 200000001 | 1 54 200000002 | 2 55 200000003 | 3 56 200000004 | 4 57 200000005 | 5 58 200000006 | 6 59 200000007 | 7 60 200000008 | 8 61 200000009 | 9 62 200000010 | 10 63 (10 rows)
【推荐】建议有定期历史数据删除需求的业务，表按时间分区，删除时不要使用DELETE操作，而是DROP或者TRUNCATE对应的表。
【推荐】为了全球化的需求，所有的字符存储与表示，均以UTF-8编码，那么字符计数方法注意：
1 # 计算字符长度 2 postgres=# select length('阿里巴巴'); 3 length 4 -------- 5 4 6 (1 row) 7 8 9 # 计算字节数 10 postgres=# select octet_length('阿里巴巴'); 11 octet_length 12 -------------- 13 12 14 (1 row) 15 16 17 # 其他长度相关接口 18 Schema | Name | Result data type | Argument data types | Type 19 ------------+------------------------+------------------+---------------------+-------- 20 pg_catalog | array_length | integer | anyarray, integer | normal 21 pg_catalog | bit_length | integer | bit | normal 22 pg_catalog | bit_length | integer | bytea | normal 23 pg_catalog | bit_length | integer | text | normal 24 pg_catalog | char_length | integer | character | normal 25 pg_catalog | char_length | integer | text | normal 26 pg_catalog | character_length | integer | character | normal 27 pg_catalog | character_length | integer | text | normal 28 pg_catalog | json_array_length | integer | json | normal 29 pg_catalog | jsonb_array_length | integer | jsonb | normal 30 pg_catalog | length | integer | bit | normal 31 pg_catalog | length | integer | bytea | normal 32 pg_catalog | length | integer | bytea, name | normal 33 pg_catalog | length | integer | character | normal 34 pg_catalog | length | double precision | lseg | normal 35 pg_catalog | length | double precision | path | normal 36 pg_catalog | length | integer | text | normal 37 pg_catalog | length | integer | tsvector | normal 38 pg_catalog | lseg_length | double precision | lseg | normal 39 pg_catalog | octet_length | integer | bit | normal 40 pg_catalog | octet_length | integer | bytea | normal 41 pg_catalog | octet_length | integer | character | normal 42 pg_catalog | octet_length | integer | text | normal
【推荐】对于值与堆表的存储顺序线性相关的数据，如果通常的查询为范围查询，建议使用BRIN索引。
例如流式数据，时间字段或自增字段，可以使用BRIN索引，减少索引的大小，加快数据插入速度。
1 2 create index idx on tbl using brin(id);
【推荐】设计时应尽可能选择合适的数据类型，能用数字的坚决不用字符串，能用树类型的，坚决不用字符串。 使用好的数据类型，可以使用数据库的索引，操作符，函数，提高数据的查询效率。
PostgreSQL支持的数据类型如下
精确的数字类型
浮点
货币
字符串
字符
字节流
日期
时间
布尔
枚举
几何
网络地址
比特流
文本
UUID
XML
JSON
数组
复合类型
范围类型
对象
行号
大对象
ltree 树结构类型
cube 多维类型
earth 地球类型
hstore KV类型
pg_trgm 相似类型
PostGIS（点、线段、面、路径、经纬度、raster、拓扑、。。。。。。）
【推荐】应该尽量避免全表扫描(除了大数据量扫描的数据分析)，PostgreSQL支持几乎所有数据类型的索引。
索引接口包括
btree
hash
gin
gist
sp-gist
brin
rum (扩展接口)
bloom (扩展接口)
【推荐】对于网络复杂并且RT要求很高的场景，如果业务逻辑冗长，应该尽量减少数据库和程序之间的交互次数，尽量使用数据库存储过程(如plpgsql)，或内置的函数。
PostgreSQL内置的plpgsql函数语言功能非常强大，可以处理复杂的业务逻辑。
PostgreSQL内置了非常多的函数，包括分析函数，聚合函数，窗口函数，普通类型函数，复杂类型函数，数学函数，几何函数，。。。等。
【推荐】应用应该尽量避免使用数据库触发器，这会使得数据处理逻辑复杂，不便于调试。
【推荐】如果应用经常要访问较大结果集的数据（例如100条），可能造成大量的离散扫描。
建议想办法将数据聚合成1条，例如经常要按ID访问这个ID的数据，建议可以定期按ID聚合这些数据，查询时返回的记录数越少越快。
如果无法聚合，建议使用IO较好的磁盘。
【推荐】流式的实时统计，为了防止并行事务导致的统计空洞，建议业务层按分表并行插入，单一分表串行插入。
例如
table1, table2, ...table100;
每个线程负责一张表的插入，统计时可以按时间或者表的自增ID进行统计
1 select xxx from table1 where id>=上一次统计的截至ID group by yyy;
【推荐】范围查询，应该尽量使用范围类型，以及GIST索引，提高范围检索的查询性能。
例如
使用范围类型存储IP地址段，使用包含的GIST索引检索，性能比两个字段的between and提升20多倍。
1 CREATE TABLE ip_address_pool_3 ( 2 id serial8 primary key , 3 start_ip inet NOT NULL , 4 end_ip inet NOT NULL , 5 province varchar(128) NOT NULL , 6 city varchar(128) NOT NULL , 7 region_name varchar(128) NOT NULL , 8 company_name varchar(128) NOT NULL , 9 ip_decimal_segment int8range 10 ) ; 11 12 CREATE INDEX ip_address_pool_3_range ON ip_address_pool_3 USING gist (ip_decimal_segment); 13 14 select province,ip_decimal_segment from ip_address_pool_3 where ip_decimal_segment @> :ip::int8;
【推荐】未使用的大对象，一定要同时删除数据部分，否则大对象数据会一直存在数据库中，与内存泄露类似。
vacuumlo可以用来清理未被引用的大对象数据。
【推荐】对于固定条件的查询，可以使用部分索引，减少索引的大小，同时提升查询效率。
1 select * from tbl where id=1 and col=?; -- 其中id=1为固定的条件 2 create index idx on tbl (col) where id=1;
【推荐】对于经常使用表达式作为查询条件的语句，可以使用表达式或函数索引加速查询。
1 select * from tbl where exp(xxx); 2 create index idx on tbl ( exp );
【推荐】如果需要调试较为复杂的逻辑时，不建议写成函数进行调试，可以使用plpgsql的online code.
1 do language plpgsql 2 3 $$ 4 5 declare 6 begin 7 -- logical code 8 end; 9 10 $$ 11 ;
【推荐】当业务有中文分词的查询需求时，建议使用PostgreSQL的分词插件zhparser或jieba，用户还可以通过接口自定义词组。
建议在分词字段使用gin索引，提升分词匹配的性能。
【推荐】当用户有规则表达式查询，或者文本近似度查询的需求时，建议对字段使用trgm的gin索引，提升近似度匹配或规则表达式匹配的查询效率，同时覆盖了前后模糊的查询需求。如果没有创建trgm gin索引，则不推荐使用前后模糊查询例如like %xxxx%。
【推荐】当用户有prefix或者 suffix的模糊查询需求时，可以使用索引，或反转索引达到提速的需求。
1 select * from tbl where col ~ '^abc'; -- 前缀查询 2 select * from tbl where reverse(col) ~ '^def'; -- 后缀查询使用反转函数索引
【推荐】用户应该对频繁访问的大表（通常指超过8GB的表，或者超过1000万记录的表）进行分区，从而提升查询的效率、更新的效率、备份与恢复的效率、建索引的效率等等，（PostgreSQL支持多核创建索引后，可以适当将这个限制放大）。
【推荐】用户在设计表结构时，建议规划好，避免经常需要添加字段，或者修改字段类型或长度。 某些操作可能触发表的重写，例如加字段并设置默认值，修改字段的类型。
如果用户确实不好规划结构，建议使用jsonb数据类型存储用户数据。