SRE文档

深浅模式

事务设计规范 ​

高并发场景，有时候会看到开发同学将很长的事务逻辑放在一个事务里面实现，其实这样对数据库并不友好。

我们应尽量避免单个事务过大、过长、过于复杂，建议将单个事务中多条SQL操作,分解、拆分，或者不放在同一个事务里，让每个事务的粒度尽可能小，这样可以尽量lock较少的资源，减少lock阻塞 、dead lock的产生

#sesseion1把所有数据都更新而不提交，一下子锁了2000千万条记录
postgres=# begin;
BEGIN
postgres=# update tab_pgsql_main set mc='tab_pgsql_1.3';
UPDATE 200000000

#sesseion2 等待
postgres=# update tab_pgsql_main set mc='tab_pgsql_1.4'  where id=1;

#sesseion3 等待
postgres=# update tab_pgsql_main set mc='tab_pgsql_1.5'  where id=2;

如果#sesseion1分布批更新的话，则session2和session3中就能部分提前完成，
这样可以避免大量的锁等待和出现大量的session占用系统资源，

在做全表更新时请使用这种方法来执行。如下所示:

postgres=# begin;
BEGIN
postgres=# update tab_pgsql_main set mc='tab_pgsql_1.3' where id>0 and id <=100000;
UPDATE 100000
postgres=#COMMIT;

postgres=# begin;
BEGIN
postgres=# update tab_pgsql_main set mc='tab_pgsql_1.3' where id>100000 and id <=200000;
UPDATE 100000
postgres=#COMMIT;
#sesseion1把所有数据都更新而不提交，一下子锁了2000千万条记录
postgres=# begin;
BEGIN
postgres=# update tab_pgsql_main set mc='tab_pgsql_1.3';
UPDATE 200000000

#sesseion2 等待
postgres=# update tab_pgsql_main set mc='tab_pgsql_1.4'  where id=1;

#sesseion3 等待
postgres=# update tab_pgsql_main set mc='tab_pgsql_1.5'  where id=2;

如果#sesseion1分布批更新的话，则session2和session3中就能部分提前完成，
这样可以避免大量的锁等待和出现大量的session占用系统资源，

在做全表更新时请使用这种方法来执行。如下所示:

postgres=# begin;
BEGIN
postgres=# update tab_pgsql_main set mc='tab_pgsql_1.3' where id>0 and id <=100000;
UPDATE 100000
postgres=#COMMIT;

postgres=# begin;
BEGIN
postgres=# update tab_pgsql_main set mc='tab_pgsql_1.3' where id>100000 and id <=200000;
UPDATE 100000
postgres=#COMMIT;

Index索引设计规范 ​

1.建议对频繁update, delete的index字段, 用create index CONCURRENTLY , drop index CONCURRENTLY方式维护来保证并发效果；

2.建议用unique index 代替unique constraints，便于后续维护；

3.建议对where 中带多个字段and条件的高频 query，参考数据分布情况，建多个字段的联合index；

4.建议对固定条件的（一般有特定业务含义）且选择比好（数据占比低）的query，建带where的Partial Indexes:

 select * from test where status=1 and col=?; -- 其中status=1为固定的条件
 create index on test (col) where status=1;
 select * from test where status=1 and col=?; -- 其中status=1为固定的条件
 create index on test (col) where status=1;

5.建议对经常使用表达式作为查询条件的query，可以使用表达式或函数索引加速query:

select * from test where exp(xxx);
create index on test (exp(xxx));
select * from test where exp(xxx);
create index on test (exp(xxx));

Constraints设计规范 ​

1.为保证业务逻辑，建议建表时把主键或者唯一索引一并建立。 2.分布式环境中，建议禁用外键约束，外键对分布式系统性能影响较大。

3.分布式环境中，建议每个table都使用shard key做为主键或者唯一索引。

*建议非必须时避免select ，只写所需字段

去掉非必要是select list，简短的select list可以减少CS之间、节点之间网络带宽的消耗：

postgres=#  explain (verbose) select * from tab_pgsql_main where id=1;
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Index Scan using idx_tab_pgsql_main_id on public.tab_pgsql_main  (cost=0.15..8.17 rows=1 width=36)
   Output: id, mc
   Index Cond: (tab_pgsql_main.id = 1)
(3 rows)

postgres=#  explain (verbose) select tableoid from tab_pgsql_main where id=1;   
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Index Scan using idx_tab_pgsql_main_id on public.tab_pgsql_main  (cost=0.15..8.17 rows=1 width=4)
   Output: tableoid
   Index Cond: (tab_pgsql_main.id = 1)
(3 rows)
postgres=#  explain (verbose) select * from tab_pgsql_main where id=1;
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Index Scan using idx_tab_pgsql_main_id on public.tab_pgsql_main  (cost=0.15..8.17 rows=1 width=36)
   Output: id, mc
   Index Cond: (tab_pgsql_main.id = 1)
(3 rows)

postgres=#  explain (verbose) select tableoid from tab_pgsql_main where id=1;   
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Index Scan using idx_tab_pgsql_main_id on public.tab_pgsql_main  (cost=0.15..8.17 rows=1 width=4)
   Output: tableoid
   Index Cond: (tab_pgsql_main.id = 1)
(3 rows)

表字段COLUMN设计规范 ​

1.建议小字节数类型，就不要用大字节数类型；

2.建议能用varchar(N)、text就不用char(N)；

3.建议使用default NULL,而不用default ‘’；

4.建议如有国际货业务的话，使用timestamp with time zone(timestamptz),而不用timestamp without time zone,避免时间函数在对于不同时区的时间点返回值不同,也为业务国际化扫清障碍;

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

6.建议对DB object 尤其是COLUMN 加COMMENT，便于后续了解业务及维护:

postgres=# \d+ tab_pgsql_main
                      Table "public.tab_pgsql_main"
 Column |  Type   | Modifiers | Storage  | Stats target | Description 
--------+---------+-----------+----------+--------------+-------------
 id     | integer |           | plain    |              | 
 mc     | text    |           | extended |              | 
Indexes:
    "idx_main_id" UNIQUE, btree (id)
Has OIDs: no
Distribute By SHARD(id)
        Location Nodes: ALL DATANODES

postgres=# comment on column tab_pgsql_main.id is 'id号';
COMMENT

postgres=# comment on column tab_pgsql_main.mc is '产品名称';
COMMENT

postgres=# \d+ tab_pgsql_main
                      Table "public.tab_pgsql_main"
 Column |  Type   | Modifiers | Storage  | Stats target | Description 
--------+---------+-----------+----------+--------------+-------------
 id     | integer |           | plain    |              | id号
 mc     | text    |           | extended |              | 产品名称
Indexes:
    "idx_main_id" UNIQUE, btree (id)
Has OIDs: no
Distribute By SHARD(id)
        Location Nodes: ALL DATANODES
postgres=# \d+ tab_pgsql_main
                      Table "public.tab_pgsql_main"
 Column |  Type   | Modifiers | Storage  | Stats target | Description 
--------+---------+-----------+----------+--------------+-------------
 id     | integer |           | plain    |              | 
 mc     | text    |           | extended |              | 
Indexes:
    "idx_main_id" UNIQUE, btree (id)
Has OIDs: no
Distribute By SHARD(id)
        Location Nodes: ALL DATANODES

postgres=# comment on column tab_pgsql_main.id is 'id号';
COMMENT

postgres=# comment on column tab_pgsql_main.mc is '产品名称';
COMMENT

postgres=# \d+ tab_pgsql_main
                      Table "public.tab_pgsql_main"
 Column |  Type   | Modifiers | Storage  | Stats target | Description 
--------+---------+-----------+----------+--------------+-------------
 id     | integer |           | plain    |              | id号
 mc     | text    |           | extended |              | 产品名称
Indexes:
    "idx_main_id" UNIQUE, btree (id)
Has OIDs: no
Distribute By SHARD(id)
        Location Nodes: ALL DATANODES

考虑使用物化视图优化大表统计 ​

建议对报表类的或生成基础数据的查询，使用物化视图(MATERIALIZED VIEW)定期固化数据快照， 避免对多表（尤其多写频繁的表）重复跑相同的查询。

PostgreSQL中物化视图支持并发更新：

REFRESH MATERIALIZED VIEW CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY
原理和优缺点与index CONCURRENTLY类似，即以时间来换取锁，并发刷新的速度会比非CONCURRENTLY慢，但后者会阻塞其他从该物化视图读数据的请求。

假设程序需要不断查询大表的总记录数，那么我们可以这样做



postgres=# select count(1) from tab_pgsql_main;
 count  
--------
 200004
(1 row)
Time: 27.948 ms

postgres=# create MATERIALIZED VIEW tab_pgsql_main_count as select count(1) as num from tab_pgsql_main;
SELECT 1
Time: 322.372 ms

postgres=# select num from  tab_pgsql_main_count ;
  num   
--------
 200004
(1 row)
Time: 0.421 ms
原理和优缺点与index CONCURRENTLY类似，即以时间来换取锁，并发刷新的速度会比非CONCURRENTLY慢，但后者会阻塞其他从该物化视图读数据的请求。

假设程序需要不断查询大表的总记录数，那么我们可以这样做



postgres=# select count(1) from tab_pgsql_main;
 count  
--------
 200004
(1 row)
Time: 27.948 ms

postgres=# create MATERIALIZED VIEW tab_pgsql_main_count as select count(1) as num from tab_pgsql_main;
SELECT 1
Time: 322.372 ms

postgres=# select num from  tab_pgsql_main_count ;
  num   
--------
 200004
(1 row)
Time: 0.421 ms

性能提高上百倍

当数据变化时刷新方法：

postgres=#  copy  tab_pgsql_main from  '/data/pgsql/tab_pgsql_main.txt';
COPY 100002
Time: 1201.774 ms

postgres=# select count(1) from tab_pgsql_main;
 count  
--------
 300006
(1 row)
Time: 23.164 ms

postgres=# REFRESH MATERIALIZED VIEW tab_pgsql_main_count;         
REFRESH MATERIALIZED VIEW
Time: 49.486 ms

postgres=# select num from tab_pgsql_main_count ;
  num   
--------
 300006
(1 row)
Time: 0.301 ms
postgres=#  copy  tab_pgsql_main from  '/data/pgsql/tab_pgsql_main.txt';
COPY 100002
Time: 1201.774 ms

postgres=# select count(1) from tab_pgsql_main;
 count  
--------
 300006
(1 row)
Time: 23.164 ms

postgres=# REFRESH MATERIALIZED VIEW tab_pgsql_main_count;         
REFRESH MATERIALIZED VIEW
Time: 49.486 ms

postgres=# select num from tab_pgsql_main_count ;
  num   
--------
 300006
(1 row)
Time: 0.301 ms

两表join时尽量的使用分布键进行join

在分布式环境，在创建业务主表、明细表时，考虑使用他们的关联键来做分布键，如下所示：

[pgsql@~]$ psql -p 15001              
psql (PostgreSQL 10 (tab_pgsql 2.01))
Type "help" for help.

postgres=# create table tab_pgsql_main(id integer,mc text) distribute by shard(id);
CREATE TABLE

postgres=# create table tab_pgsql_detail(id integer,tab_pgsql_main_id integer,mc text) distribute by shard(tab_pgsql_main_id);   
CREATE TABLE

postgres=# explain select tab_pgsql_detail.* from tab_pgsql_main,tab_pgsql_detail where tab_pgsql_main.id=tab_pgsql_detail.tab_pgsql_main_id;       
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001, dn002
(2 rows)
postgres=# explain (verbose) select tab_pgsql_detail.* from tab_pgsql_main,tab_pgsql_detail where tab_pgsql_main.id=tab_pgsql_detail.tab_pgsql_main_id; 
                                                                                 QUERY PLAN                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Output: tab_pgsql_detail.id, tab_pgsql_detail.tab_pgsql_main_id, tab_pgsql_detail.mc
   Node/s: dn001, dn002
   Remote query: SELECT tab_pgsql_detail.id, tab_pgsql_detail.tab_pgsql_main_id, tab_pgsql_detail.mc FROM public.tab_pgsql_main, public.tab_pgsql_detail WHERE (tab_pgsql_main.id = tab_pgsql_detail.tab_pgsql_main_id)
(4 rows)
postgres=#
[pgsql@~]$ psql -p 15001              
psql (PostgreSQL 10 (tab_pgsql 2.01))
Type "help" for help.

postgres=# create table tab_pgsql_main(id integer,mc text) distribute by shard(id);
CREATE TABLE

postgres=# create table tab_pgsql_detail(id integer,tab_pgsql_main_id integer,mc text) distribute by shard(tab_pgsql_main_id);   
CREATE TABLE

postgres=# explain select tab_pgsql_detail.* from tab_pgsql_main,tab_pgsql_detail where tab_pgsql_main.id=tab_pgsql_detail.tab_pgsql_main_id;       
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001, dn002
(2 rows)
postgres=# explain (verbose) select tab_pgsql_detail.* from tab_pgsql_main,tab_pgsql_detail where tab_pgsql_main.id=tab_pgsql_detail.tab_pgsql_main_id; 
                                                                                 QUERY PLAN                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Output: tab_pgsql_detail.id, tab_pgsql_detail.tab_pgsql_main_id, tab_pgsql_detail.mc
   Node/s: dn001, dn002
   Remote query: SELECT tab_pgsql_detail.id, tab_pgsql_detail.tab_pgsql_main_id, tab_pgsql_detail.mc FROM public.tab_pgsql_main, public.tab_pgsql_detail WHERE (tab_pgsql_main.id = tab_pgsql_detail.tab_pgsql_main_id)
(4 rows)
postgres=#

分布式环境的分布键用唯一索引代替主键 ​

唯一索引后期的维护成本比主键要低很多

postgres=# create unique index uidx_pgsql_main_id on tab_pgsql_main using btree(id);
postgres=# create unique index uidx_pgsql_main_id on tab_pgsql_main using btree(id);

CREATE INDEX ​

分布键无法建立唯一索引则要建立普通索引，提高查询的效率

postgres=# create index idx_pgsql_detail_id on tab_pgsql_detail using btree(id);                   
CREATE INDEX
postgres=# create index idx_pgsql_detail_id on tab_pgsql_detail using btree(id);                   
CREATE INDEX

DB object包含: tablespace，database, schema, table, column, view, index, sequence, function, trigger等。 (1) 对象按类别带上标识，表tab,索引idx，视图v，函数fun等。 (2) 不建议以dba_、pgxl_、pg_、pgxc_开头，避免与系统对象、dba人员使用对象混淆。 (3) 建议使用小写字母、数字、下划线的组合。

(4) 临时中间表或临时备份表建议加上日期, 如tab_tmp_2020_10_12、tab_bak_2020_10_12；