MogDB与PostgreSQL分区策略语法测试


1.父子继承表

目前MogDB还不支持inherits继承特性。

omm=# CREATE TABLE tab_t2(age int) inherits(tab_t1);
ERROR:  CREATE TABLE ... INHERITS is not yet supported.

PostgreSQL支持继承,版本10之前的分区表都是通过继承特性来实现,每个分区实际上都是一个独立的表。数据更新可通过触发器trigger或者规则rule来实现。

下面演示PostgreSQL中的继承特性:

CREATE TABLE tab_t1(id int primary key,name varchar(20) not null);
CREATE TABLE tab_t2(age int) inherits(tab_t1);

对父表增加字段:

alter table tab_t1 add create_date date;

查看表结构:

postgres=# \d tab_t1
                       Table "public.tab_t1"
  Column    |         Type          | Collation | Nullable | Default
-------------+-----------------------+-----------+----------+---------
id          | integer               |           | not null |
name        | character varying(20) |           | not null |
create_date | date                  |           |          |
Indexes:
   "tab_t1_pkey" PRIMARY KEY, btree (id)
Number of child tables: 1 (Use \d+ to list them.)

postgres=# \d tab_t2
                       Table "public.tab_t2"
  Column    |         Type          | Collation | Nullable | Default
-------------+-----------------------+-----------+----------+---------
id          | integer               |           | not null |
name        | character varying(20) |           | not null |
age         | integer               |           |          |
create_date | date                  |           |          |
Inherits: tab_t1

我们不通过触发器或者规则路由数据,直接插入数据:

INSERT INTO tab_t1 VALUES (1,'data 1 in tab_t1',now());
INSERT INTO tab_t1 VALUES (2,'data 2 in tab_t1',now());
INSERT INTO tab_t2 VALUES (3,'data 3 in tab_t2',18,now());
INSERT INTO tab_t2 VALUES (4,'data 4 in tab_t2',20,now());

从父表中查询数据将显示父表及子表的所有数据:

postgres=# SELECT * from tab_t1;
id |       name       | create_date
----+------------------+-------------
 1 | data 1 in tab_t1 | 2021-04-11
 2 | data 2 in tab_t1 | 2021-04-11
 3 | data 3 in tab_t2 | 2021-04-11
 4 | data 4 in tab_t2 | 2021-04-11
(4 rows)

通过ONLY关键字实现只对父表的查询:

postgres=#  SELECT * from ONLY tab_t1;
id |       name       | create_date
----+------------------+-------------
 1 | data 1 in tab_t1 | 2021-04-11
 2 | data 2 in tab_t1 | 2021-04-11
(2 rows)

从子表中查询只显示子表中的数据:

postgres=# select * from tab_t2;
id |       name       | age | create_date
----+------------------+-----+-------------
 3 | data 3 in tab_t2 |  18 | 2021-04-11
 4 | data 4 in tab_t2 |  20 | 2021-04-11
(2 rows)

继承特性使用注意点:

  • 子表并不能完全继承父表的所有属性,比如唯一约束、主键、外键,检查约束与非空约束可以继承。

  • 修改父表的结构,子表结构同时被修改。

  • 父表不存数据时,不建议在父表上创建索引和或唯一约束,应该在每个子表上分别创建。

2.声明式分区:范围分区

将数据基于范围映射到每一个分区,这个范围是由创建分区表时指定的分区键决定的。这种分区方式较为常用,并且分区键经常采用日期。

PostgreSQL从版本10开始支持,范围分区声明式语法分两步:

1.通过指定PARTITION BY子句把表创建为分区表,包括分区方法以及用作分区键的column列表。

CREATE TABLE measurement (
   city_id         int not null,
   logdate         date not null,
   peaktemp        int,
   unitsales       int
) PARTITION BY RANGE (logdate)

2.创建分区,每个分区的定义必须指定对应于父表的分区方法和分区键的边界。

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
   FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
   FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
...

MogDB范围分区声明式语法可以一步完成,范围分区从句语法有两种格式:

  • VALUES LESS THAN语法格式(范围分区策略的分区键最多支持4列)

  • START END语法格式(范围分区策略的分区键仅支持1列)

注意上面两种从句语法不能混用,START END语法格式使用gs_dump时会转变为VALUES LESS THAN语法格式。

MogDB范围分区例子:

CREATE TABLE tab_part (
   id int not null,
   create_date date not null
) PARTITION BY RANGE(create_date)
(
PARTITION p_20210401 VALUES LESS THAN(to_date('2021-04-01','yyyy-mm-dd')),
PARTITION p_20210402 VALUES LESS THAN(to_date('2021-04-02','yyyy-mm-dd')),
PARTITION p_max VALUES LESS THAN(MAXVALUE)
);

查看系统表可看到分区策略为“r”,range分区。

omm=# select relname,partstrategy from pg_partition where relname='tab_part';
relname  | partstrategy
----------+--------------
tab_part | r
(1 row)

查看分区及边界:

select relname,parttype,parentid,boundaries
from pg_partition
where parentid in(select oid from pg_class where relname='tab_part');
 relname   | parttype | parentid |       boundaries        
------------+----------+----------+-------------------------
tab_part   | r        |    16412 |
p_20210401 | p        |    16412 | {"2021-04-01 00:00:00"}
p_20210402 | p        |    16412 | {"2021-04-02 00:00:00"}
p_max      | p        |    16412 | {NULL}
(4 rows)

接下来插入三条数据:

insert into tab_part values(1,'2021-03-31');
insert into tab_part values(2,'2021-04-01');
insert into tab_part values(3,'9999-12-31');

查询分区,按分区名p_20210402,也可以按分区边界值(PARTITION FOR)。

omm=# select * from tab_part PARTITION (p_20210402);
id |     create_date    
----+---------------------
 2 | 2021-04-01 00:00:00
(1 row)

3.声明式分区:列表分区

通过显式地列出每一个分区中出现的键值来划分表。

与前面范围分区一样,PostgreSQL列表分区声明式语法也是两步,从版本10开始支持,MogDB只需一步完成。

MogDB列表分区例子:

CREATE TABLE tab_list(
   dept_no number,
   part_no varchar2(20),
   country varchar2(20),
   dtime date,
   amount number
)
PARTITION BY LIST(country)(
   PARTITION europe VALUES('FRANCE', 'ITALY'),
   PARTITION asia VALUES('INDIA', 'PAKISTAN'),
   PARTITION americas VALUES('US', 'CANADA')
);

查看系统表可看到分区策略为“l”,list分区。

omm=# select relname,partstrategy from pg_partition where relname='tab_list';
relname  | partstrategy
----------+--------------
tab_list | l
(1 row)

查看分区及边界:

select relname,parttype,parentid,boundaries
from pg_partition
where parentid in(select oid from pg_class where relname='tab_list');
relname  | parttype | parentid |    boundaries    
----------+----------+----------+------------------
tab_list | r        |    16389 |
americas | p        |    16389 | {US,CANADA}
asia     | p        |    16389 | {INDIA,PAKISTAN}
europe   | p        |    16389 | {FRANCE,ITALY}
(4 rows)

4.声明式分区:哈希分区

将数据通过哈希映射到每一个分区,每一个分区中存储了具有相同哈希值的记录。

PostgreSQL哈希分区声明式语法也是两步,从版本11开始支持,MogDB只需一步完成。

MogDB哈希分区例子:

CREATE TABLE tab_hash(
   dept_no number,
   part_no varchar2(20),
   country varchar2(20),
   dtime date,
   amount number
)PARTITION BY HASH(part_no)(
   PARTITION p1,
   PARTITION p2,
   PARTITION p3
);

查看系统表可看到分区策略为“h”,hash分区。

omm=# select relname,partstrategy from pg_partition where relname='tab_hash';
relname  | partstrategy
----------+--------------
tab_hash | h
(1 row)

查看分区及边界:

select relname,parttype,parentid,boundaries
from pg_partition
where parentid in(select oid from pg_class where relname='tab_hash');
relname  | parttype | parentid | boundaries
----------+----------+----------+------------
tab_hash | r        |    16405 |
p3       | p        |    16405 | {2}
p2       | p        |    16405 | {1}
p1       | p        |    16405 | {0}
(4 rows)

5.基于范围分区的自动扩展间隔分区

间隔分区(Interval-Partition)是针对Range类型分区的一种功能拓展。对连续数据类型的Range分区,如果插入的新数据值与当前分区均不匹配,Interval-Partition特性可以实现自动的分区创建。分区字段必须是时间类型(date或timestamp)。

PostgreSQL目前还不支持该语法,MogDB从版本已经支持。

MogDB间隔分区例子:

CREATE TABLE tab_range_interval (
   id int not null,
   create_date date not null
) PARTITION BY RANGE(create_date) INTERVAL('1 month')
(
PARTITION p1 VALUES LESS THAN(to_date('2021-01-29','yyyy-mm-dd'))
);

查看系统表可看到分区策略为“i”,interval分区。

omm=# select relname,partstrategy,interval from pg_partition where relname='tab_range_interval';
relname  | partstrategy | interval  
----------+--------------+-----------
tab_part | i            | {"1 month"}
(1 row)

接下来插入三条数据:

insert into tab_range_interval values(1,'2021-01-29');
insert into tab_range_interval values(2,'2021-02-28');
insert into tab_range_interval values(3,'2022-03-29');

插入数据后检查是否自动创建了相应的分区:

omm=# select relname,parttype,parentid,boundaries
from pg_partition
where parentid in(select oid from pg_class where relname='tab_range_interval');
     relname       | parttype | parentid |  boundaries  
--------------------+----------+----------+--------------
tab_range_interval | r        |    16572 |
p1                 | p        |    16572 | {2021-01-29}
sys_p1             | p        |    16572 | {2021-02-28}
sys_p2             | p        |    16572 | {2021-03-28}
sys_p3             | p        |    16572 | {2022-04-28}
(5 rows)

可以看到sys_p1,sys_p2,sys_p3为系统自动生成的分区,并且自动处理了月末问题。

注意:

1.从2.0.0版本开始,模板库默认字符集由SQL_ASCII改为了UTF8,同时数据库兼容性由Oracle改为PostgreSQL,对本测试的影响是date数据类型。

2.目前只支持INTERVAL-RANGE,其它方式不支持。

3.间隔分区字段必须是时间类型(date或timestamp)。

6.子分区

MogDB目前还不支持子分区,PostgreSQL声明式分区是支持的。

PostgreSQL子分区例子:

先创建分区表:

CREATE TABLE sales(
       id serial,
       sales_count int,
       sales_date date not null
) PARTITION BY RANGE(sales_date);

再按年创建分区:sales_2018、sales_2019、sales_2020:

CREATE TABLE sales_2018 PARTITION OF sales
FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');

CREATE TABLE sales_2019 PARTITION OF sales
FOR VALUES FROM ('2019-01-01') TO ('2020-01-01');

CREATE TABLE sales_2020 PARTITION OF sales
FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');

2021年新建分区时可以创建子分区,按月分区:

create table sales_2021 partition of sales
for values from ('2021-01-01') to ('2022-01-01') partition by range(sales_date);

create table sales_2021_01 partition of sales_2021
for values from ('2021-01-01') to ('2021-02-01');
create table sales_2021_02 partition of sales_2021
for values from ('2021-02-01') to ('2021-03-01');
create table sales_2021_03 partition of sales_2021
for values from ('2021-03-01') to ('2021-04-01');
create table sales_2021_04 partition of sales_2021
for values from ('2021-04-01') to ('2021-05-01');
create table sales_2021_05 partition of sales_2021
for values from ('2021-05-01') to ('2021-06-01');
create table sales_2021_06 partition of sales_2021
for values from ('2021-06-01') to ('2021-07-01');
create table sales_2021_07 partition of sales_2021
for values from ('2021-07-01') to ('2021-08-01');
create table sales_2021_08 partition of sales_2021
for values from ('2021-08-01') to ('2021-09-01');
create table sales_2021_09 partition of sales_2021
for values from ('2021-09-01') to ('2021-10-01');
create table sales_2021_10 partition of sales_2021
for values from ('2021-10-01') to ('2021-11-01');
create table sales_2021_11 partition of sales_2021
for values from ('2021-11-01') to ('2021-12-01');
create table sales_2021_12 partition of sales_2021
for values from ('2021-12-01') to ('2022-01-01');

下面我们通过\d+查看sale表,可以看到分区sales_2021比其它分区多一个PARTITIONED属性,说明它是一个子分区。

                                          Partitioned table "postgres.sales"
  Column    |  Type   | Collation | Nullable |              Default              | Storage | Stats target | Description
-------------+---------+-----------+----------+-----------------------------------+---------+--------------+-------------
id          | integer |           | not null | nextval('sales_id_seq'::regclass) | plain   |              |
sales_count | integer |           |          |                                   | plain   |              |
sales_date  | date    |           | not null |                                   | plain   |              |
Partition key: RANGE (sales_date)
Partitions: sales_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
           sales_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
           sales_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
           sales_2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'), PARTITIONED

我们可以通过\d+查看sales_2021:

                                        Partitioned table "postgres.sales_2021"
  Column    |  Type   | Collation | Nullable |              Default              | Storage | Stats target | Description
-------------+---------+-----------+----------+-----------------------------------+---------+--------------+-------------
id          | integer |           | not null | nextval('sales_id_seq'::regclass) | plain   |              |
sales_count | integer |           |          |                                   | plain   |              |
sales_date  | date    |           | not null |                                   | plain   |              |
Partition of: sales FOR VALUES FROM ('2021-01-01') TO ('2022-01-01')
Partition constraint: ((sales_date IS NOT NULL) AND (sales_date >= '2021-01-01'::date) AND (sales_date < '2022-01-01'::date))
Partition key: RANGE (sales_date)
Partitions: sales_2021_01 FOR VALUES FROM ('2021-01-01') TO ('2021-02-01'),
           sales_2021_02 FOR VALUES FROM ('2021-02-01') TO ('2021-03-01'),
           sales_2021_03 FOR VALUES FROM ('2021-03-01') TO ('2021-04-01'),
           sales_2021_04 FOR VALUES FROM ('2021-04-01') TO ('2021-05-01'),
           sales_2021_05 FOR VALUES FROM ('2021-05-01') TO ('2021-06-01'),
           sales_2021_06 FOR VALUES FROM ('2021-06-01') TO ('2021-07-01'),
           sales_2021_07 FOR VALUES FROM ('2021-07-01') TO ('2021-08-01'),
           sales_2021_08 FOR VALUES FROM ('2021-08-01') TO ('2021-09-01'),
           sales_2021_09 FOR VALUES FROM ('2021-09-01') TO ('2021-10-01'),
           sales_2021_10 FOR VALUES FROM ('2021-10-01') TO ('2021-11-01'),
           sales_2021_11 FOR VALUES FROM ('2021-11-01') TO ('2021-12-01'),
           sales_2021_12 FOR VALUES FROM ('2021-12-01') TO ('2022-01-01')

总结

1.MogDB目前只支持声明式分区,支持范围分区、列表分区、哈希分区以及INTERVAL-RANGE的自动扩展间隔分区。PostgreSQL支持继承及声明式分区,不支持自动扩展间隔分区。

2.自动扩展间隔分区的分区字段目前只支持时间类型(date或timestamp)。

3.MogDB目前不支持子分区,PostgreSQL支持声明式子分区。

4.对于声明式分区的分区来说,分区必须具有和分区表正好相同的列集合,表结构必须严格一致,而在表继承中,子表可以有父表中没有出现过的额外列,同时表继承允许多继承。


墨天轮原文链接:https://www.modb.pro/db/49865?sjhy(复制到浏览器或者点击“阅读原文”立即查看)

关于作者

彭冲,云和恩墨PG技术顾问,网名“多米爸比”,PG社区认证专家,中国首期PostgreSQL ACE Partner,多年从事基于PostgreSQL数据库的软件研发,擅长于PL/PGSQL业务迁移及优化,Oracle到PostgreSQL的迁移升级,异构数据库整合;作为墨天轮PostgreSQL实践专栏作者,热衷于PostgreSQL实践技术分享,在自己的岗位积极推广PostgreSQL,致力为PG社区多做奉献。

来源:https://my.oschina.net/u/3954670/blog/5403614


码神部落- 版权声明 1、本主题所有言论和图片纯属会员个人意见,与码神部落立场无关。
2、本站所有主题由该帖子作者发表,该帖子作者木槿暖夏码神部落享有帖子相关版权。
3、码神部落管理员和版主有权不事先通知发贴者而删除本文。
4、其他单位或个人使用、转载或引用本文时必须同时征得该帖子作者木槿暖夏码神部落的同意。
5、帖子作者须承担一切因本文发表而直接或间接导致的民事或刑事法律责任。
6、本帖部分内容转载自其它媒体,但并不代表本站赞同其观点和对其真实性负责。
7、如本帖侵犯到任何版权问题,请立即告知本站,本站将及时予与删除并致以最深的歉意。

最新回复 (0)
    • 码神部落
      2
        立即登录 立即注册 GitHub登录
返回
发新帖
作者最近主题: