租户端 数据库 TDSQL-A PostgreSQL 版 开发指南

设计规范

最近更新时间: 2024-10-17 17:10:00

命名规范

  • DB object:database、schema、table、column、view、index、sequence、function、trigger 等名称：

    • 建议使用小写字母、数字、下划线的组合。

    • 建议不使用双引号"包围，除非必须包含大写字母或空格等特殊字符。

    • 长度不能超过63个字符。

    • 不建议以 pg_ 开头或者 pgxc_（避免与系统 DB object 混淆），不建议以数字开头。

    • 禁止使用 SQL 关键字，例如 type、order 等。

  • table 能包含的 column 数目，根据字段类型的不同，数目在250到1600之间。

  • 临时或备份的 DB object:table、view 等，建议加上日期，如 dbaa_ops.b2c_product_summay_2014_07_12 (其中 dba_ops 为 DBA 专用 schema)。

  • index 命名规则：普通索引为表名_列名_idx，唯一索引为表名_列名_uidx，如 student_name_idx,student_id_uidx。

COLUMN 设计

  • 建议使用数值类型，不用字符类型。

  • 建议使用 varchar(N) ，不用 char(N)，以节省存储空间。

  • 建议使用 varchar(N) ，不用 text、varchar。

  • 建议使用 default NULL，不用default ''，以节省存储空间。

  • 建议国际业务时，使用 timestamp with time zone(timestamptz)，不用 timestamp without time zone，避免时间函数在不同时区的时间点返回值不同，也为业务国际化扫清障碍。

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

Constraints 设计

  • 建议每个 table 都使用 shard key 作为主键或者唯一索引，主键或唯一索引必须带着分布键。

  • 建议建表时一步到位，一起建立主键或者唯一索引。

  • 目前列存表暂不支持外键

Index 设计

  • TDSQL-A for PostgreSQL版 提供的 index 类型：B-tree、Hash、GiST (Generalized Search Tree)、SP-GiST (space-partitioned GiST)、GIN (Generalized Inverted Index)、BRIN (Block Range Index)，目前不建议使用 Hash，通常情况下使用 B-tree。目前列存表只支持B-tree和Hash索引。

  • 建议 create 或 drop index 时，加 CONCURRENTLY 参数，可以达到与写入数据并发的效果。

  • 建议对频繁 update、delete 的包含于 index 定义中的 column 的 table，使用 create index CONCURRENTLY、drop index CONCURRENTLY 的方式进行维护其对应 index。

  • 建议用 unique index 代替 unique constraints，便于后续维护。

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

  • 建议对固定条件的（一般有特定业务含义）且数据占比低的 query，建议带 where 的 Partial Indexes。

  select * from test where status=1 and col=?; -- 其中status=1为固定的条件
create index on test (col) where status=1;
  • 建议对经常使用表达式作为查询条件的 query，可以使用表达式或函数索引加速 query。
  select * from test where exp(xxx);
create index on test ( exp(xxx) );
  • 建议不要建过多 index，一般不要超过6个，核心 table（产品，订单）可适当增加 index 个数。

关于 NULL

  • NULL 的判断：IS NULL、IS NOT NULL。

  • 注意 boolean 类型取值 true、false、NULL。

  • 注意 NOT IN 集合中带有 NULL 元素。

postgres=# select * from tdapg;
id |   nickname    
----+-------------------------------
1 | hello TDSQL-A for PostgreSQL
2 | TDSQL-A for PostgreSQL好
3 | TDSQL-A for PostgreSQL好
4 | TDSQL-A for PostgreSQL default
(4 rows)
postgres=# select * from tdapg where id not in (null);
id | nickname 
----+----------
(0 rows)
  • 建议对字符串型 NULL 值处理后，进行 || 操作。
postgres=# select id,nickname from tdapg limit 1;
id |  nickname   
----+-----------------------------
1 | hello TDSQL-A for PostgreSQL
(1 row) 
postgres=# select id,nickname||null from tdapg limit 1;
id | ?column? 
----+----------
1 | 
(1 row) 
postgres=# select id,nickname||coalesce(null,'') from tdapg limit 1;
id |  ?column?   
----+-----------------------------
1 | hello TDSQL-A for PostgreSQL
(1 row)

  • 建议使用 count(1) 或 count(*) 来统计行数，不建议使用 count(col) 来统计行数，因为 NULL 值不会计入。

    注意：

    count(多列列名)时，多列列名必须使用括号，例如 count( (col1,col2,col3) )，注意多列的 count，即使所有列都为 NULL，该行也被计数，所以效果与 count(*) 一致。

postgres=# select * from tdapg ;
id |   nickname    
----+-------------------------------
1 | hello TDSQL-A for PostgreSQL
2 | TDSQL-A for PostgreSQL好
5 | 
3 | TDSQL-A for PostgreSQL好
4 | TDSQL-A for PostgreSQL default
(5 rows)
postgres=# select count(1) from tdapg;
count 
\-------
   5
(1 row) 
postgres=# select count(*) from tdapg; 
count 
\-------
   5
(1 row)     
postgres=# select count(nickname) from tdapg;        
count 
\-------
   4
(1 row)
postgres=# select count((id,nickname)) from tdapg;   
count 
\-------
   5
(1 row)

  • count(distinct col) 计算某列的非 NULL 不重复数量，NULL 不被计数。

    注意：

    count(distinct (col1,col2,...) ) 计算多列的唯一值时，NULL 会被计数，同时 NULL 与 NULL会被认为是相同的。

postgres=# select count(distinct nickname) from tdapg;             
count 
\-------
   3
(1 row)
postgres=# select count(distinct (id,nickname)) from tdapg; 
count 
\-------
   5
(1 row)
两个 NULL 的对比方法。
postgres=# select null is not  distinct from null as Tdapgnull;      
Tdapgnull 
\-----------
t
(1 row)

开发相关规范

1）建议对 DB object 尤其是 COLUMN 加 COMMENT，便于后续新人了解业务及维护。

注释前后的数据表可读性对比：

postgres=# \d+ TDAPG_main
                    Table "public.tdapg_main"
Column |  Type   | Modifiers | Storage  | Stats target | Description 
--------+---------+-----------+----------+--------------+-------------
id     | integer |           | plain    |              | 
mc     | text    |           | extended |              | 
Indexes:
  "TDAPG_main_id_uidx" UNIQUE, btree (id)
Has OIDs: no
Distribute By SHARD(id)
      Location Nodes: ALL DATANODES
postgres=# comment on column TDAPG_main.id is 'id号';
COMMENT
postgres=# comment on column TDAPG_main.mc is '产品名称';
COMMENT
postgres=# \d+ TDAPG_main
                    Table "public.tdapg_main"
Column |  Type   | Modifiers | Storage  | Stats target | Description 
--------+---------+-----------+----------+--------------+-------------
id     | integer |           | plain    |              | id号
mc     | text    |           | extended |              | 产品名称
Indexes:
  "TDAPG_main_id_uidx" UNIQUE, btree (id)
Has OIDs: no
Distribute By SHARD(id)
      Location Nodes: ALL DATANODES

2）建议非必须时避免 select *，只取所需字段，以减少（包括不限于）网络带宽消耗。

postgres=#  explain (verbose) select * from tdapg_main where id=1;
                                       QUERY PLAN                                          
\---------------------------------------------------------------------------------------------
Index Scan using Tdapg_main_id_uidx on public.tdapg_main  (cost=0.15..8.17 rows=1 width=36)
 Output: id, mc
 Index Cond: (Tdapg_main.id = 1)
(3 rows) 
postgres=#  explain (verbose) select tableoid from tdapg_main where id=1;   
                                       QUERY PLAN                                         
\--------------------------------------------------------------------------------------------
Index Scan using Tdapg_main_id_uidx on public.tdapg_main  (cost=0.15..8.17 rows=1 width=4)
 Output: tableoid
 Index Cond: (Tdapg_main.id = 1)
(3 rows)
  • 是返回36个字符，而另一个一条记录只能4个字段的长度。

3）建议 update 时，尽量做 <> 判断，如 update table_a set column_b = c where column_b <> c；

postgres=# update tdapg_main set mc='TDSQL-A for PostgreSQL' ;         
UPDATE 1
postgres=# select xmin,* from tdapg_main;
xmin | id |  mc   
------+----+-----------------------
2562 |  1 | TDSQL-A for PostgreSQL
(1 row) 
postgres=# update tdapg_main set mc='TDSQL-A for PostgreSQL' ;
UPDATE 1
postgres=# select xmin,* from tdapg_main;    
xmin | id |  mc   
------+----+-----------------------
2564 |  1 | TDSQL-A for PostgreSQL
(1 row)
postgres=# update tdapg_main set mc='TDSQL-A for PostgreSQL' where mc!='TDSQL-A for PostgreSQL';
UPDATE 0
postgres=# select xmin,* from tdapg_main;                     
xmin | id |  mc   
------+----+-----------------------
2564 |  1 | TDSQL-A for PostgreSQL
(1 row)

以上效果是一样的，但带条件的更新不会产生一个新的版本记录，不需要系统执行 vacuum 回收垃圾数据。

4）建议将单个事务的多条 SQL 操作，分解、拆分，或者不放在一个事务里，让每个事务的粒度尽可能小，尽量 lock 少的资源，避免 lock 、dead lock 的产生。

#session1 把所有数据都更新而不提交，一下子锁了2000千万条记录。

postgres=# begin;
BEGIN
postgres=# update tdapg_main set mc='TDAPG_1.3';
UPDATE 20000000

#session2 等待。

postgres=# update tdapg_main set mc='TDAPG_1.4'  where id=1;

#session3 等待。

postgres=# update tdapg_main set mc='TDAPG_1.5'  where id=2;

如果#session1分批更新的话，如下所示：

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

则 session2 和 session3 中就能部分提前完成，这样可以避免大量的锁等待和出现大量的 session 占用系统资源，在做全表更新时请使用这种方法来执行。

5）建议大批量的数据入库时，使用 copy ，不建议使用 insert，以提高写入速度。如下，性能相差5倍。

postgres=# insert into tdapg_main select t,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' from generate_series(1,100000) as t;
INSERT 0 100000
Time: 9511.755 ms
postgres=# copy  TDAPG_main to '/data/pgxz/TDAPG_main.txt';      
COPY 100002
Time: 179.428 ms
postgres=# copy  TDAPG_main from  '/data/pgxz/TDAPG_main.txt';
COPY 100002
Time: 1625.803 ms
postgres=#

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

如有一个程序需要不断查询 TDAPG_main 的总记录数，可参考如下：

postgres=# select count(1) from tdapg_main;
count  
\--------
200004
(1 row)
Time: 27.948 ms
postgres=# create MATERIALIZED VIEW TDAPG_main_count as select count(1) as num from tdapg_main;
SELECT 1
Time: 322.372 ms
postgres=# select num from  TDAPG_main_count ;
 num   
\--------
200004
(1 row)
Time: 0.421 ms

性能提高上百倍。

有数据变化时刷新方法：

postgres=#  copy  tdapg_main from  '/data/pgxz/TDAPG_main.txt';
COPY 100002
Time: 1201.774 ms
postgres=# select count(1) from tdapg_main;
count  
\--------
300006
(1 row)
Time: 23.164 ms
postgres=# REFRESH MATERIALIZED VIEW TDAPG_main_count;         
REFRESH MATERIALIZED VIEW
Time: 49.486 ms
postgres=# select num from tdapg_main_count ;
 num   
\--------
300006
(1 row)
Time: 0.301 ms

7）建议复杂的统计查询可以尝试窗口函数。

请参见 窗口函数应用。

8）两表 join 时，尽量的使用分布 key 进行 join。

在建立业务的主表、明细表时，需要使用他们的关联键来做分布键，如下所示：

[pgxz@VM_0_29_centos pgxz]$ psql -p 15001              
psql (PostgreSQL 10 (TDAPG 2.01))
Type "help" for help.
postgres=# create table tdapg_main(id integer,mc text) distribute by shard(id);
CREATE TABLE
postgres=# create table tdapg_detail(id integer,tdapg_main_id integer,mc text) distribute by shard(TDAPG_main_id);   
CREATE TABLE
postgres=# explain select TDAPG_detail.* from tdapg_main,TDAPG_detail where TDAPG_main.id=TDAPG_detail.TDAPG_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 TDAPG_detail.* from tdapg_main,TDAPG_detail where TDAPG_main.id=TDAPG_detail.TDAPG_main_id; 
                                                                                    QUERY PLAN                                                                                     
\------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
  Output: TDAPG_detail.id, TDAPG_detail.TDAPG_main_id, TDAPG_detail.mc
  Node/s: dn001, dn002
  Remote query: SELECT TDAPG_detail.id, TDAPG_detail.TDAPG_main_id, TDAPG_detail.mc FROM public.TDAPG_main, public.TDAPG_detail WHERE (TDAPG_main.id = TDAPG_detail.TDAPG_main_id)
(4 rows)
postgres=#

9）分布键用唯一索引代替主键。

postgres=# create unique index TDAPG_main_id_uidx on TDAPG_main using btree(id);
CREATE INDEX

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

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

postgres=# create index TDAPG_detail_TDAPG_main_id_idx on TDAPG_detail using btree(TDAPG_main_id);                   
CREATE INDEX

这样两表在 join 查询时，返回少量数据时的效率才会高。

11）不对字段建立外键。

目前 T 不支持多 dn 外键约束，除非能确定数据关联键的数据全部落在同一个 dn 上面。