2.11.?Partition表分区

2.11.1. 范围分区
2.11.2. 列表分区

2.11.1.?范围分区

列存表被分区到由键列或列集定义的“范围”中,分配给不同分区的值范围之间没有重叠。例如,可以按日期范围进行分区, 也可以按特定业务对象的标识符范围进行分区。

  • 创建列存分区表

    CREATE TABLE column01 (id int,name text,age int) PARTITION BY RANGE(id) WITH(appendonly=true,orientation=column);

    parttition by指定分区方式和分区键。

  • 创建分区,以创建5个分区子表为例

    CREATE TABLE column01_1000 PARTITION OF column01 FOR VALUES FROM('1') TO('1000') WITH(appendonly=true,orientation=column);
    CREATE TABLE column01_2000 PARTITION OF column01 FOR VALUES FROM('1000') TO('2000') WITH(appendonly=true,orientation=column);
    CREATE TABLE column01_3000 PARTITION OF column01 FOR VALUES FROM('2000') TO('3000') WITH(appendonly=true,orientation=column);
    CREATE TABLE column01_4000 PARTITION OF column01 FOR VALUES FROM('3000') TO('4000') WITH(appendonly=true,orientation=column);
    CREATE TABLE column01_5000 PARTITION OF column01 FOR VALUES FROM('4000') TO('5000') WITH(appendonly=true,orientation=column);
  • 查看分区子表

    \d column01_1000
  • 插入数据

    insert into column01 values (generate_series(1,4999),'name1',34);
  • 查看分区表数据

    EXPLAIN select * from column01;
  • 删除分区子表

    DROP TABLE column01_1000;

2.11.2.?列表分区

列存表通过明确列出每个分区中出现的键值进行分区。

  • 创建分区主表

    create table cs_list_part (id int8,random_char varchar(100),day_id varchar(8)) PARTITION BY LIST(day_id) WITH(appendonly=true,orientation=column);
  • 创建分区从表

    CREATE TABLE cs_list_part_p20171130 PARTITION OF cs_list_part FOR VALUES in ('20171130');
    CREATE TABLE cs_list_part_p20171201 PARTITION OF cs_list_part FOR VALUES in ('20171201');
    CREATE TABLE cs_list_part_p20171202 PARTITION OF cs_list_part FOR VALUES in ('20171202');
    CREATE TABLE cs_list_part_p20171203 PARTITION OF cs_list_part FOR VALUES in ('20171203');
  • 插入数据

    insert into cs_list_part select * from (
    	select generate_series(1, 5) as id, md5(random()::text) as info , '20171130' as day_id union all
    	select generate_series(1, 5) as id, md5(random()::text) as info , '20171201' as day_id union all
    	select generate_series(1, 5) as id, md5(random()::text) as info , '20171202' as day_id union all
    	select generate_series(1, 5) as id, md5(random()::text) as info , '20171203' as day_id 
    ) t0;
  • 查询分区表数据

    //分区主表
    select * from cs_list_part order by day_id,id;
    //分区从表
    select * from cs_list_part_p20171130;
  • 使用不存在的分区值20171129插入记录,报错

    insert into cs_list_part select * from (
    	select generate_series(1, 5) as id, md5(random()::text) as info , '20171129' as day_id 
    ) t0;
    
    ERROR:  no partition of relation "cs_list_part" found for row
    DETAIL:  Partition key of the failing row contains (day_id) = (20171129).
XML 地图 | Sitemap 地图