设计开发规范
最近更新时间: 2024-06-12 15:06:00
设计准则
命名准则
数据库对象名命名准则
数据库对象: 模式名, 表名,视图名, 字段名，索引名等名称：
使用小写字母、数字、下划线的组合。
禁用双引号即"包围，除非必须包含大写字母。
注意TDSQL PostgreSQL 版的对象名称长度不能超过63个字符。
不要以pg_开头或者pgxc_（避免与系统表，系统视图名称混淆），禁用以数字开头。
禁止使用 SQL 关键字例如 type, order 等，所有关键字见TDSQL PostgreSQL 版关键字列表。
尽量用英文单词，这样使用者根据英文单词也可以大概知道数据库对象的用途，不用再去查找文档；
由于数据库对象名不能够超过63个字符，所以当超过63个字符时，英文单词应该缩写。
表名命名规则
同一业务的表名前缀保持一致： 保险业务相关可以写成：policy_basic，policy_xxxx。
视图命名规则
同一业务的表名前缀保持一致，视图带上对象类型为view如： 保险业务相关可以写成：policy_basic_view。
物化视图命名规则
同一业务的物化视图名前缀保持一致，物化视图的对象类型为mv如： 保险业务相关可以写成：policy_basic_mv。
序列命名规则
序列命名为【表名_字段名_seq】：如表policy_basic的id字段使用的序列
policy_basic_id_seq主键命名规则
主键命名规则为【表名_字段名_pkey】，如果是多个字段组合成主键，则每个字段使用下划画隔开，如
policy_basic_id_pkey唯一索引命名规则
唯一索引命名规则为【表名_字段名_uidx】，如果是多个字段组合成的唯一索引，则每个字段使用下划画隔开，如
policy_basic_id_uidx普通索引命名规则
普通索引命名规则为【表名_字段名_idx】，如果是多个字段组合成的索引，则每个字段使用下划画隔开，如
policy_basic_id_idx临时表命名规则
临时表命名规则为【表名_tmp】，如
policy_basic_tmp转储表命名规则
转储表命名规则为【表名_his】，如
policy_basic_his字段命名规则
字段名称必须用字母开头，采用有特征含义的单词或缩写，不能用双引号包含。如
inserttime,updatetime使用准则
shard（分片表）数据表设计准则
这里的数据表包括普通表，普通分区表，冷热分区表。
所有数据表都必需有主键。
业务表都必需有插入时间截和最后更新时间截字段，并且在业务发生时保存对应时间截。
单个表大小建议不要超过32G，有效的控制表大小方法，增加集群节点数可以降低单个表的大小，使用分区表可以降低单数据表的大小。
对表名增加COMMENT中文注释，便于后续新人了解业务及维护，其它对象也需要遵守这个规则。
分区表使用准则
分区表带来的好处就是表大小得到有效控制，索引的高度低，更新性能更好，坏处就是全表扫描更慢，系统元数据更大，DDL操作开销更大，使用分区表准则如下。
带有时间截字段（如插入时间）的业务数据表。
对该表进行查询时，业务大多数是限制在一定时间范围内进行。
对于高并发的查询业务，需要指定到某个分区范围执行查询才能发挥分区表的带来的好处。
删除记录和更新记录时必须限定在某个分区表内，否则更新效率低。
冷热分区表使用准则
冷热分区表使用除了具有分区表的特性外，还有这一点要特别注意，运维成本大一些，冷热数据搬迁需要占用磁盘IO，网络带宽，热group的数据无法与冷group的数据进行跨DN库join。
冷热分区表继承分区表的使用情景。
历史数据查询少。
历史数据存储容量大。
热数据存储介质成本大，冷GROUP可以使用便宜的SAS盘，解决存储成本的问题。
禁止热group和冷group join操作。
满足上面的场景的话就可以考虑使用冷热分区表。
全局表使用准则
全局表是所有节点都有全量数据，对于大数据量的数据表不适合。
全局表更新性能较低，控制不好容易产生死锁，对于经常更新的业务不适合使用全局表。
数据经常要跨库JOIN的小数据量表可以考虑使用全局表。
序列使用准则
无特别要求，禁止在系统中使用序列，主要是序列需要与GTM频繁的通信，增加了GTM负载，降低了系统TPS值
使用 TDSQL PostgreSQL 版自带的UUID函数或应用程序的UUID代替。
外键使用准则
无特别要求，禁止使用外键，外键对应程序和运维带来极高要求，同时也增加了数据库性能开销。
可以使用事务来替代外键达到数据一致性要求。
触发器使用准则
无特别要求，禁止使用触发器，触发器需要把数据拉回接入层CN计算，数据路由复杂，性能低，开销大。
触发器不利于应用程序和数据库功能解偶，排错复杂。
存储过程使用准则
无特别要求，禁止在存储过程业务中使用
存储过程使用增加数据库服务器的额外负担。
存储过程也不利于应用程序和数据库功能解偶，业务还是放到业务代码中。
物化视图使用准则
无特殊要求，禁止使用物化视图。
物化视图生成数据存放于CN节点，如果需要使用物化视图，需要确保CN节点有足够的空间和良好的IO能力。
物化视图与其它表JOIN时，数据需要拉回协调节点CN进行计算，不适合于大量数据JOIN业务使用。
索引使用准则
能使用Btree 索引的就不要使用GIN索引。
有唯一约束要求的字段必需使用UNIQUE来约束。
索引越多，更新越慢。
索引也需要占用物理空间。
在线建立索引请使用CONCURRENTLY方式。
建议对固定条件的（一般有特定业务含义）且选择比好（数据占比低）的query，创建带where的索引；
select * from test where status=1 and col=?; -- 其中status=1为固定的条件 create index test_col_idx on test (col) where status=1;建议对经常使用表达式作为查询条件的query，可以使用表达式或函数索引加速query；
select * from test where exp(xxx); create index test_xxx_idx on test ( exp(xxx) );建议不要建过多index，一般不要超过6个，核心表（保订单）可适当增加index个数。
通过查询系统视图pg_stat_all_indexes可以评估索引使用情况 。
字段设计准则
能用数值类型的，就不用字符类型。
能用varchar(N)就不用char(N),以利于节省存储空间，而且varchar拉长时不需要更新全表数据。
能用varchar(N) 就不用text,varchar。
使用default NULL,而不用default '',以节省存储空间。
使用NUMERIC(precision, scale)来存储货币金额和其它要求精确计算的数值,而不建议使用real, double precision
逻辑复制表禁止bytea大对象。
用户及权限设计准则
不准许在业务操作中使用DBA角色用户。
每个独立的应用都需要有独立的数据库用户。
开发准则
GROUP拆分原则
TDSQL PostgreSQL 版可以把集群中的部分数据节点定义成一个GROUP，然后在建立数据表时指定数据存放到这个GROUP中，实现不同业务表的物理资源使用隔离。如可以按不同的地区来进行拆分，这样拆分的好处
各个地区的数据完全隔离，互不影响。
各个地区可以独立扩容，包括该地区的冷热节点。
各个地区可以独立做灰度升级，互不影响。
某个数据节点DN故障倒换只会影响到该个地区的业务。
每个地区上业务时只需要评估该地区的资源要求。
对于非分布键的查询，更新，删除不会占用到其它地区服务器资源，而且访问的节点更少，性能及可靠性更高。
数据库拆分原则
TDSQL PostgreSQL 版的逻辑隔离有两个级别，分别是database（数据库）和schema（模式）级别，请使用使用schema级别，这样拆分后的好处
用户访问不同schema下的资源需要授权后才能访问，所以schema级别来说已经足够安全。
schema拆分只是访问权限上的隔离，放开权限后，同一个数据源就可以访问不同schema下的数据表。
不同schema下的数据表可以在接入层CN进行JOIN查询，而database级别是无法这样操作。
从 TDSQL PostgreSQL 版的设计上来看，不同数据库连接增加了协调节点CN到数据节点DN这间连接池的维护工作，即随着数据库的增加，要维护的连接数会更多，占用资源更大
参考其它DB的使用习惯来看，oracle使用的是一用户对应一个schema。
数据库设计原则
数据库编码
数据库编码统一使用UTF8
建立UTF8编码数据库方法
create database postgres ENCODING 'utf8';查询现在数据库编码方法
postgres=# select datname,pg_encoding_to_char(encoding) from pg_database ;
datname | pg_encoding_to_char
-----------+---------------------
template1 | UTF8
template0 | UTF8
postgres | UTF8
(3 rows)
**注意：数据库编码建立后就无法再修改。**
#### 数据库默认排序分组规则
数据库排序分组规则，使用zh_CN.utf8或者C，但推荐使用zh_CN.utf8，主要是因为zh_CN.utf8支持模糊查询和全文搜索，还有汉字排序时使用拼音首字母。
- 指定数据库排序分组规则
``` bash
create database postgres ENCODING 'utf8' lc_collate 'zh_CN.utf8' LC_CTYPE
'zh_CN.utf8';查询数据库排序分组规则
postgres=# select datname,datcollate,datctype from pg_database; datname | datcollate | datctype -----------+------------+----------- template1 | zh_CN.utf8 | zh_CN.utf8 template0 | zh_CN.utf8 | zh_CN.utf8 postgres | zh_CN.utf8 | zh_CN.utf8 (3 rows)
schema设计技巧
在 TDSQL PostgreSQL 版中schema与用户不存在必然的联系，但建议为每个用户建立一个相同名称的schema，并且将该schema分配给其同名的用户，如
create schema picc_gd AUTHORIZATION picc_gd;这样做好处就是使用picc_gd这个用户连接上来后，访问数据表时最先搜索的路径为picc_gd这个schema，这处简单的设计就能实现同一套程序匹配多套库的功能。
数据库用户设计原则
绝对不准许在业务中使用DBA角色用户。
不同应用请使用不同的用户来隔离数据表的访问。
用户停止使用后要禁用
建立普通用户的方法
create role picc_gd with login password 'picc_gd@123';禁用用户方法
alter role picc_gd with nolgin;
分布键的选择原则
分布键只能选择一个字段。
如果有主键，则选择主键做分布键。
如果主键是复合字段组合，则选择字段值选择性多的字段做分布键。
也可以把复合字段拼接成一个新的字段来做分布键。
没有主键的可以使用UUID来做分布键。
总之一定要让数据尽可能的分布得足够散。
索引的设计原则
btree索引
创建方法
create table policy_basis(id int not null,cardid varchar(20) not null,primary
key(id));
create index policy_basis_cardid_idx on policy_base using btree(cardid);什么情况下会使用到索引
单表=,>,>=,<=,between x and x,in操作，如
Select * from policy_basis where cardid=xxx; Select * from policy_basis where cardid>xxx; Select * from policy_basi where cardid>=xxx; Select * from policy_basis where cardid>xxx and cardid<xxx; Select * from policy_basis where cardid between xx and xxx; Select * from policy_basis where cardid in(’xx’,’xx’);like左匹配模糊查询用法
要使用like左匹配模糊查询，需要指定排序方法为 collate "C"
create table t_like (id varchar(32),cardid varchar(20),primary key(id) ); create index t_like_cardid_idx on t_like using btree(cardid collate "C");然后这样的语句就能走索引
select * from t_like where cardid like '99999%';
但其它操作符需要这样写才能使用索引
select * from t_like where cardid COLLATE "C" = '99999'; select * from t_like where cardid COLLATE "C" > '99999';
- order by + limit x 之类返回部分数据，如
``` bash
create table t_order_by(id int not null,cardid varchar(20) not null,primary
key(id));
insert into t_order_by select t,t from generate_series(1,1000000) as t;
select * from t_order_by order by id limit 10;
select * from t_order_by order by cardid limit 10;上面的查询不管是分布键还是非分布键都要对排序的字段建立索引 create index t_order_by_id_idx on t_order_by(id);
create index t_order_by_cardid_idx on t_order_by(cardid); 如果是全表排序，则无需要建立索引。join连接查询
“分布键与分布键”，“分布键与非分布键”，“非分布键与非分布键”，只要是join连接扫描的数据量占比量少，建立索引都是能大大提高查询速度
ccreate table t_join_main(id int not null,cardid int,primary key(id)); create table t_join_detail(id int not null,cardid int,phone varchar(20),primary key(id)); insert into t_join_main select t,t from generate_series(1,1000000) as t; insert into t_join_detail select t,t from generate_series(1,1000000) as t; select * from t_join_main,t_join_detail where t_join_main.id=t_join_detail.id and t_join_main.id=1; select * from t_join_main,t_join_detail where t_join_main.id=t_join_detail.cardid and t_join_main.id=1; select * from t_join_main,t_join_detail where t_join_main.cardid=t_join_detail.cardid and t_join_detail.cardid=2;上面几个join查询对应的字段都要建立索引
create index t_join_detail_cardid_idx on t_join_detail(cardid); create index t_join_main_cardid_idx on t_join_main(cardid);
数组gin索引
创建方法
create table t_array(id int not null, cardid varchar(32)[],primary key(id));
insert into t_array select t,('{'||md5(t::text)||'}')::text[] from
generate_series(1,1000000) as t;
insert into t_array values(1000001,'{1,2,3}');
insert into t_array values(1000002,'{1,2,3,4,5}');
create index t_array_cardid_idx on t_array using gin(cardid);使用方法
select * from t_array where cardid @>
('{c4ca4238a0b923820dcc509a6f75849b}')::text[];
select * from t_array where cardid @> ('{1,2}')::text[];
select * from t_array where cardid @> ('{1,2,5}')::text[];jsonb使用索引
创建方法
create table t_jsonb(id int not null,f_jsonb jsonb,primary key(id));
create index t_jsonb_f_jsonb_idx on t_jsonb using gin(f_jsonb);使用方法
select * from t_jsonb where f_jsonb @> '{"phone":"81838898"}';pg_trgm全模糊走索引
创建方法
create extension pg_trgm;
create table t_trgm(id int not null,cardid varchar(500),primary key(id));
create index t_trgm_cardid_idx on t_trgm using gin(cardid gin_trgm_ops);使用方法
select * from t_trgm where cardid like '%440521%';使用限制
数据库排序规则需要使用lc_collate 'zh_CN.utf8';
查询字符不能少于3个
存储的内容不能大于8k
任意字段都可使用索引
创建方法
create table gin_mul(f1 int, f2 int, f3 timestamp, f4 text, f5 numeric, f6
text);
create extension btree_gin;
create index gin_mul_gin_idx on gin_mul using gin(f1,f2,f3,f4,f5,f6);使用方法
这样建立索引后，这个表的任意字段就能组合使用索引查询，如
select * from gin_mul where f1=xx;
select * from gin_mul where f3=xx;
select * from gin_mul where f1=xx and f2=xx;
select * from gin_mul where f1=xx and f2=xx and f5=xxx;使用注意
btree_gin索引的容量一般是数据表2倍左右，索引的维护成本非常大，适合于更新少，需要任意字段组合查询使用场景。
如何检查查询是否使用了索引
postgres=# explain select * from t1 where f1=1;
QUERY PLAN
--------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn001
-> Index Scan using t1_f1_idx on t1 (cost=0.42..4.44 rows=1 width=8)
Index Cond: (f1 = 1)
(4 rows)
postgres=#
postgres=# explain select * from t_array where mc @>
('{'||md5('1')||'}')::text[];
QUERY PLAN
-------------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn001, dn002
-> Bitmap Heap Scan on t_array (cost=31.40..3174.64 rows=2503 width=61)
Recheck Cond: (mc @> ('{c4ca4238a0b923820dcc509a6f75849b}'::cstring)::text[])
-> Bitmap Index Scan on t_array_mc_idx (cost=0.00..30.78 rows=2503 width=0)
Index Cond: (mc @> ('{c4ca4238a0b923820dcc509a6f75849b}'::cstring)::text[])
(6 rows)
postgres=# explain select * from t_join_1,t_join_2 where
t_join_1.f1=t_join_2.f2 and t_join_1.f1=1;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Nested Loop (cost=0.25..8.29 rows=1 width=16)
-> Remote Subquery Scan on all (dn001) (cost=100.12..104.16 rows=1 width=8)
-> Index Scan using t_join_1_f1_idx on t_join_1 (cost=0.12..4.14 rows=1
width=8)
Index Cond: (f1 = 1)
-> Materialize (cost=100.12..104.16 rows=1 width=8)
-> Remote Subquery Scan on all (dn001,dn002) (cost=100.12..104.16 rows=1
width=8)
-> Index Scan using t_join_2_f2_idx on t_join_2 (cost=0.12..4.14 rows=1
width=8)
Index Cond: (f2 = 1)
(8 rows)
postgres=# explain select * from t_join_1,t_join_2 where
t_join_1.f1=t_join_2.f1 and t_join_1.f1=1;
QUERY PLAN
---------------------------------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0)
Node/s: dn001, dn002
-> Nested Loop (cost=14.51..1359.15 rows=989 width=16)
-> Index Scan using t_join_1_f1_idx on t_join_1 (cost=0.42..4.44 rows=1
width=8)
Index Cond: (f1 = 1)
-> Bitmap Heap Scan on t_join_2 (cost=14.09..1344.82 rows=989 width=8)
Recheck Cond: (f1 = 1)
-> Bitmap Index Scan on t_join_2_f1_idx (cost=0.00..13.84 rows=989 width=0)
Index Cond: (f1 = 1)
(9 rows)增删改查使用原则
insert
一次插入多条记录
insert into tbase(id,nickname) values(1,'hello TBase'),(2,'TBase hello');备注：一次性提交效率更高
插入记录存在时更新
insert into tbase values(1,'pgxz') ON CONFLICT (id) DO UPDATE SET nickname =
'tbase';插入记录，记录存在时变成更新，使用ON CONFLICT
2.8.1.3 插入记录后，查询返回新增的记录
insert into tbase(nickname) values(1,'TBase') returning *; 使用returning相当insert+select操作
delete
分布键做为删除条件效率高，扩展性好
不使用分布键 ，则删除语句发往所有节点。
删除分区表数据
删除分区表数据除了带分布键做为删除条件外，还要带上分区键，否则会扫描所有分区表。
删除也要考虑使用索引
带索引的字段做为过滤条件，往往执行效率更高。
使用returning需要返回数据
postgres=# delete from tbase where id=2 returning *;
id | nickname
----+-----------
2 | TBase
(1 row)两表关联删除只支持分布键关联
delete from tbase using t_appoint_col where tbase.id=t_appoint_col.id;这里 t_appoint_col.id和tbase.id 必需都是分布键
删除所有数据请使用truncate
truncate table tbase;
truncate效率高，而且能直接释放空间，但对于做主主复制的系统，需要两个集群各自操作一次。另外还要考虑数据同步到kafka的问题。
update
分布键不支持更新
update tbase set id=8 where id=1;
ERROR: Distribute column or partition column can't be updated in current version 分区表的分区键不支持更新
update t_time_range set inserttime='2017-09-02';
ERROR: Distributed column or partition column "inserttime" can't be updated in
current version 更新并返回记录请使用returning
update tbase set nickname = nickname where id = (random()*2)::integer returning
*;
id | nickname
----+-----------
2 | TBase
(1 row)两表关联更新只支持分布键关联
update tbase set nickname ='Good TBase' from t_appoint_col where
t_appoint_col.id=tbase.id;这里 t_appoint_col.id和tbase.id 必需都是分布键
分布键做为更新条件效率高，扩展性好
不使用分布键 ，则更新语句发往所有节点。
分区表数据更新
更新分区表数据除了带分布键做为更新条件外，还要带上分区键，否则会扫描所有分区表。
更新也要考虑使用索引
带索引的字段做为过滤条件，往往执行效率更高。
select
分布键查询效率高，扩展性好
如
select * from tbase where nickname=’tbase’ ; #扩展性差
select * from tbase where nickname=’tbase’ and id=1; #效率高，具有良好扩展性分区表查询需要带上分区时间
如 select * from t_time_range where id=1;#搜索所有分区，效率低，60个分区表，无数据，查询一次12ms select * from t_time_range where id=1 and inserttime>=’2019-01-01’ and inserttime<’2019-02-01’;#搜索单一分区，效率高，查询一次2ms
not in 中包含了null，结果全为真
select * from tbase where id not in (3,5,null);这样的查询返回为空，一条记录都查询不出来
尽量分页提取数据
select * from policy_basic order by id limit 10 offset 0;
select * from policy_basic order by id limit 10 offset 9;offset 0表示第一条开始，另外 offset x只是不提出数据，但还要查询出来。
大数据抽取请使用游标
游标抽取数据效率更高。
分布式事务使用原则
tbase支持全局acid级别分布式事务，但应用设计时尽可能减少，分布式事务在最后提交时开销更大。
字段类型使用原则
数字类型
| 名字 | 存储尺寸 | 描述 | 范围 |
|------------------|--------------|--------------------|-----------------------------------------------|
| smallint | 2字节 | 小范围整数 | -32768 to +32767 |
| integer | 4字节 | 整数的典型选择 | -2147483648 to +2147483647 |
| bigint | 8字节 | 大范围整数 | -9223372036854775808 to +9223372036854775807 |
| numeric | 可变 | 用户指定精度，精确 | 最高小数点前131072位，以及小数点后16383位 |
| double precision | 8字节 | 可变精度，不精确 | 15位十进制精度 |
整数smallint,integer,bigint请按最大可能数进行选择，这样可以在存入超出范围的数字时由数据库做最后的检查保证。
小数字可以减少不必需的存储空间。
小数字减少查询时扫描的数据块。
numeric用于需要精度准确的业务，如存储金额。
double precision用于精度要求不高的业务，其性能要好于numeric。
字符类型
| 名字 | 描述 |
|--------------------------------------|----------------|
| character varying(n), varchar(n) | 有限制的变长 |
| character(n), char(n) | 定长，空格填充 |
| text | 1G |
如果字段将来存在变更长度的可能，则禁止使用char。
varchar类型要指定长度使用，不指定长度的varchar与text一样可以保持1G数据。
禁止使用text来存储副件图片之类的数据。
二进制数据类型
| 名字 | 存储尺寸 | 描述 |
|----------|----------------------------|------------------------------|
| bytea | 1或4字节外加真正的二进制串 | 变长二进制串，最大存储1G数据 |
TDSQL PostgreSQL 版支持二进制数据存储，但访问效率远低于对象存储系统，无特别要求禁止使用。
日期类型
| 名字 | 存储尺寸 | 描述 | 范围 |
|--------------|--------------|----------------|-------------|
| timestamp | 8字节 | 包括日期和时间 | 精确到1微秒 |
| timestamp(0) | 8字节 | 包括日期和时间 | 精确到1秒 |
| date | 4字节 | 只包括日期 | |
| time | 8字节 | 一天中的时间 | 精确到1微秒 |
| Time(0) | 8字节 | 一天中的时间 | 精确到1秒 |
如果存入的数据只想精确到秒，则使用timestamp(0),time(0)
注意oracle转换过来的应用，oracle的date是精确到秒，对应的tbase类型为timestamp(0)
null值拼接字段串需要处理
不处理返回为null值
postgres=# select id,nickname||null from tbase limit 1;
id | ?column?
----+----------
1 |
(1 row)
postgres=# select id,nickname||coalesce(null,'') from tbase limit 1;
id | ?column?
----+-------------
1 | hello TBase
(1 row)count函数使用
建议使用count(1) 或count() 来统计行数，而不建议使用count(col) 来统计行数，因为NULL值不会计入； *注意:**
count(多列列名)时，多列列名必须使用括号，例如count( (col1,col2,col3) );
多列的count，即使所有列都为NULL，该行也被计数，所以效果与count(*) 一致；
postgres=# select * from tbase ; id | nickname ----+--------------- 1 | hello TBase 2 | TBase 5 | 3 | TBase 4 | TBase default (5 rows) postgres=# select count(1) from tbase; count ------- 5 (1 row) postgres=# select count(*) from tbase; count ------- 5 (1 row) postgres=# select count(nickname) from tbase; count ------- 4 (1 row) postgres=# select count((id,nickname)) from tbase; count ------- 5 (1 row)
只取所需字段
建议非必须时避免select *，只取所需字段，以减少包括不限于网络带宽消耗
过滤不必要更新
建议update 时尽量做 <> 判断,比如update table_a set column_b = c where column_b <> c；
postgres=# update tbase_main set mc='TBase' ;
UPDATE 1
postgres=# select xmin,* from tbase_main;
xmin | id | mc
------+----+-------
2562 | 1 | TBase
(1 row)
postgres=# update tbase_main set mc='TBase' ;
UPDATE 1
postgres=# update tbase_main set mc='TBase' where mc!='TBase';
UPDATE 0上面的效果是一样的，但带条件的更新不会产生一个新的版本记录，不需要系统执行vacuum回收垃圾数据。
大数据量更新尽量折分
建议将单个事务的多条SQL操作,分解、拆分，或者不放在一个事务里，让每个事务的粒度尽可能小，尽量少锁定资源，避免死锁的产生； #sesseion1把所有数据都更新而不提交，一下子锁了2000千万条记录
postgres=# begin;
BEGIN
postgres=# update tbase_main set mc='TBase_1.3';
UPDATE 200000000
#sesseion2 等待
postgres=# update tbase_main set mc='TBase_1.4' where id=1;
#sesseion3 等待
postgres=# update tbase_main set mc='TBase_1.5' where id=2;
如果#sesseion1分布批更新的话，如下所示
postgres=# begin;
BEGIN
postgres=# update tbase_main set mc='TBase_1.3' where id>0 and id <=100000;
UPDATE 100000
postgres=#COMMIT;
postgres=# begin;
BEGIN
postgres=# update tbase_main set mc='TBase_1.3' where id>100000 and id
<=200000;
UPDATE 100000
postgres=#COMMIT;
则session2和session3中就能部分提前完成，这样可以避免大量的锁等待和出现大量的session占用系统资源，在做全表更新时请使用这种方法来执行禁止在业务代码中使用DDL
TDSQL PostgreSQL 版在扩容，冷热数据搬迁时DDL都会禁止执行。
跨库访问规范
所有不按分布键的查询都是属于跨库访问，需要按下面的规则来限制
如果查询需要将数据拉回协调节点(CN)计算，只要涉及计算数据量比较少，这样在协调节点(CN)上面的聚集也是高性能的。
针对于单表聚集统计之类的查询，由于可以实现查询下推，只是汇集最后的结果，这样的查询只要DN的处理能力足够，使用上也没什么问题。
如果需要拉回大量数据在协调节点(CN)进行计算，就需要考虑使用提前计算好的中间表，宽表，或者按业务维度分布的数据表，减少协调节点(CN)和数据节点(DN)之间的数据量传输，如果做不到，需要禁止这样的查询使用。
高可用数据源接入方式
TDSQL PostgreSQL 版的每一个cn都可以接入，看到的信息都是全局一致的，可以使用下面的方法实现高可用
应用程序配置多个IP+PORT数据源，轮循选择接入。
前置CLB，F5或者LVS，应该程序直接访问前置的VIP
防SQL注入
为了防止SQL注入，需要把传入的变量值做处理，处理方法如下，如
select * from tbase where nickname='$1';上面的$1 参数如果传入成这样 123';delete from tbase ;select ' 就会变成这样的语句
select * from tbase where nickname='123';delete from tbase ;select ''; 这样就会把数据表tbase的数据给删除掉 所以在程序中需要对传入的参数进行处理（使用扩展协议不需要处理这个问题），把参数值中的单引号'变成两个''，最终变成这样
select * from tbase where nickname='123'';delete from tbase ;select ''';这样就变成只查询一个字符串，如下所示
postgres=# select * from tbase where nickname='123'';delete from tbase ;select
''';
id | nickname
----+----------
(0 rows)
postgres=# select '123'';delete from tbase ;select ''';
?column?
----------------------------------
123';delete from tbase ;select '
(1 row)应用程序升级规范
概述
执行工具
TDSQL PostgreSQL 版之客户端工具psql
连接环境
如无特别约定，连接到 TDSQL PostgreSQL 版集群之任意一个CN都可以
操作方法
DDL更新
操作前环境配置
DDL需要锁表，所以执行DDL操作时都要配置拿到表锁的最大时长，特别是在线服务项目，下在配置某个节点在3秒内如果无法获取表锁就rollback
psql -h 172.16.0.29 -p 11000 -d postgres -U tbase
psql (PostgreSQL 10.0 TBase V2)
Type "help" for help.
postgres=# set lock_timeout to 3000;
SET
postgres=#
postgres=# alter table tbase add column phone varchar(12);
ALTER TABLE
postgres=#更新超时提示
postgres=# alter table tbase add column phone varchar(12);
ERROR: canceling statement due to lock timeout下面情况下禁止执行DDL
添加新节点，节点扩容，冷热数据搬迁，节点异常
主主复制表DDL变更准则
对于主主复制表DDL变更，两个集群DDL需要保持一致的操作顺序，每次更新后务必检查主主复制进程是否能正常同步数据。
DML更新
INSERT插入数据
直接在线执行即可。
UPDATE或者DELETE
如果是全表的UPDATE,DELETE，则需要使用分批更新法，如
UPDATE 表名 set 字段名=xxxx WHERE id>1 and id<=100 and 字段名!=xxxx ;
UPDATE 表名 set 字段名=xxxx WHERE id>100 and id<=200 and 字段名!=xxxx ;
上面语句好处就是减少行锁数，尽量做到对业务影响最小化
原子性批量更新
Begin;
Insert ....
Update...
...
Commit;把所有更新语句合在一个事务中执行来保证更新的原子性，但要注意更新过程持有的锁时间会更长。
数据管理
数据复制
TDSQL PostgreSQL 版目前提供了三种不同级别的复制方案
流复制
流复制用于保证数据节点DN节点高可用和异地容灾使用。同步的级别分别同步和异步，同时支持配置多个备机同步。 部署建议
建议配置最少一主一备同步，由于同步在备机异常时会卡住主节点写入，所以需要准备一个备机用于同步备机异常时做为后备接入，即一个IDC为1主2备。
如果有同城IDC，要求网络的延迟在3ms以内，则同城的另一个IDC可以部署一个同步备机。
异地IDC备节点只能使用异步同步方式。
以上数据同步复制只支持同集群同构同版本。
逻辑复制
对于不同 TDSQL PostgreSQL 版集群之间数据复制， TDSQL PostgreSQL 版提供了表级逻辑复制能力，逻辑复制可以实现A集群的A表在数据发生变化时同步至B集群，使用逻辑复制注意点。
同步的数据表需要有主键。
逻辑复制在数据约束冲突时会导致复制服务停止。
逻辑复制不支持强同步复制。
修改字段名，删除字段的DDL操作需要确保无增量数据需要同步，否则会导致增量数据无法同步。
逻辑复制的数据表做DDL维护时两边需要保持一致。
逻辑复制只支持INSERT,DELETE,UPDATE操作的数据变更，对于COPY,TRUNCATE是无法进行逻辑解释。
逻辑解释至kafka
TDSQL PostgreSQL 版支持将增加的数据逻辑解释同步到kafka集群，其它需要同步数据的服务再通过订阅kafka的方式来实现数据同步。
冷热数据分离
TDSQL PostgreSQL 版支持集群内使用异构机型，机型之间配置差异化，满足高性能的同时，节约业务存储成本和冷热数据访问隔离。 TDSQL PostgreSQL 版目前支持按月度划分数据分区，从而在业务规划时，可以将频繁访问月份的数据存储在高IO性能机型（例如SSD磁盘），将非频繁访问数据存储在大存储容量机型（例如SAS磁盘）。 冷热分区表使用注意事项： 冷热分区表需要在建立数据表时就定义好，普通表无法修改成冷热分区表。