PostgreSQL数据库开发规范——命名规范设计规范
原⽂：
命名规范
强制】库名、表名限制命名长度，建议表名及字段名字符总长度⼩于等于63。
【强制】对象名（表名、列名、函数名、视图名、序列名、等对象名称）规范，对象名务必只使⽤⼩写字母，下划线，数字。不要以pg开头，不要以数字开头，不要使⽤保留字。
保留字参考
【强制】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列的更新或删除性能。
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)
【强制】对于频繁更新的表，建议建表时指定表的fillfactor=85，每页预留15%的空间给HOT更新使⽤。
postgres=# create table test123(id int, info text) with(fillfactor=85);
CREATE TABLE
【强制】表结构中字段定义的数据类型与应⽤程序中的定义保持⼀致，表之间字段校对规则⼀致，避免报错或⽆法使⽤索引的情况发⽣。说明：
(1).⽐如A表user_id字段数据类型定义为varchar，但是SQL语句查询为 where user_id=1234；
【推荐】如何保证分区表的主键序列全局唯⼀。
使⽤多个序列，每个序列的步调不⼀样，或者每个序列的范围不⼀样即可。
postgres=# create sequence seq_tab1 increment by 10000 start with 1;
CREATE SEQUENCE
postgres=# create sequence seq_tab2 increment by 10000 start with 2;
CREATE SEQUENCE
postgres=# create sequence seq_tab3 increment by 10000 start with 3;
CREATE SEQUENCE
postgres=# create table tab1 (id int primary key default nextval('seq_tab1') check(mod(id,10000)=1), info text);
CREATE TABLE
postgres=# create table tab2 (id int primary key default nextval('seq_tab2') check(mod(id,10000)=2), info text);
CREATE TABLE
postgres=# create table tab3 (id int primary key default nextval('seq_tab3') check(mod(id,10000)=3), info text);
CREATE TABLE
postgres=# insert into tab1 (info) select generate_series(1,10);
INSERT 0 10
postgres=# insert into tab2 (info) select generate_series(1,10);
INSERT 0 10
postgres=# insert into tab3 (info) select generate_series(1,10);
INSERT 0 10
postgres=# select * from tab1;
id  | info
-------+------
1 | 1
10001 | 2
20001 | 3
30001 | 4
40001 | 5
50001 | 6
60001 | 7
70001 | 8
80001 | 9
90001 | 10
(10 rows)
postgres=# select * from tab2;
id  | info
-------+------
2 | 1
10002 | 2
20002 | 3
30002 | 4
40002 | 5
50002 | 6
70002 | 8
80002 | 9
90002 | 10
(10 rows)
postgres=# select * from tab3;
id  | info
-
------+------
3 | 1
10003 | 2
20003 | 3
30003 | 4
40003 | 5
50003 | 6
60003 | 7
70003 | 8
80003 | 9
90003 | 10
(10 rows)
postgres=# create sequence seq_tb1 increment by 1 minvalue 1 maxvalue 100000000 start with 1 no cycle ;
CREATE SEQUENCE
postgres=# create sequence seq_tb2 increment by 1 minvalue 100000001 maxvalue 200000000 start with 100000001 no cycle ; CREATE SEQUENCE
postgres=# create sequence seq_tb3 increment by 1 minvalue 200000001 maxvalue 300000000 start with 200000001 no cycle ; CREATE SEQUENCE
postgres=# create table tb1(id int primary key default nextval('seq_tb1') check(id >=1 and id<=100000000), info text); CREATE TABLE
postgres=# create table tb2(id int primary key default nextval('seq_tb2') check(id >=100000001 and id<=200000000), info text); CREATE TABLE
postgres=# create table tb3(id int primary key default nextval('seq_tb3') check(id >=200000001 and id<=300000000), info text); CREATE TABLE
postgres=# insert into tb1 (info) select * from generate_series(1,10);
INSERT 0 10
postgres=# insert into tb2 (info) select * from generate_series(1,10);
INSERT 0 10
postgres=# insert into tb3 (info) select * from generate_series(1,10);truncate的数据如何恢复
INSERT 0 10
postgres=# select * from tb1;
id | info
----+------
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
9 | 9
10 | 10
(10 rows)
postgres=# select * from tb2;
id    | info
-
----------+------
100000001 | 1
100000002 | 2
100000003 | 3
100000004 | 4
100000005 | 5
100000006 | 6
100000007 | 7
100000008 | 8
100000009 | 9
100000010 | 10
(10 rows)
postgres=# select * from tb3;
id    | info
-----------+------
200000001 | 1
200000002 | 2
200000003 | 3
200000004 | 4
200000005 | 5
200000006 | 6
200000007 | 7
200000008 | 8
200000009 | 9
200000010 | 10
(10 rows)
【推荐】建议有定期历史数据删除需求的业务，表按时间分区，删除时不要使⽤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
【推荐】对于值与堆表的存储顺序线性相关的数据，如果通常的查询为范围查询，建议使⽤BRIN索引。
例如流式数据，时间字段或⾃增字段，可以使⽤BRIN索引，减少索引的⼤⼩，加快数据插⼊速度。
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进⾏统计
select xxx from table1 where id>=上⼀次统计的截⾄ID group by yyy;
【推荐】范围查询，应该尽量使⽤范围类型，以及GIST索引，提⾼范围检索的查询性能。
例如
使⽤范围类型存储IP地址段，使⽤包含的GIST索引检索，性能⽐两个字段的between and提升20多倍。
CREATE TABLE ip_address_pool_3 (
id serial8 primary key ,
start_ip inet NOT NULL ,
end_ip inet NOT NULL ,
province varchar(128) NOT NULL ,
city varchar(128) NOT NULL ,
region_name varchar(128) NOT NULL ,

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。