@TOC

表分区

针对大的数据仓库事实表，往往我们可以通过对表进行分区的方式来把一个很大的表拆分成多个子表。这样的话，有两个好处：

• 查询优化器可以针对分区表进行优化，如果查询只设计到某些分区，则查询计划只需要扫描这些分区，从而加速查询。
• 如果我们按照日期进行分区的话，我们可以简单的加入分区和删除过期的分区。
  OushuDB 支持基于 Range 和 List 的两种分区方式。

OushuDB 支持基于 Range 和 List 的两种分区方式。

• Range 分区：依据数值范围进行分区，比如日期，价格等。
• List 分区：依据一个值的列表进行分区，比如地区等

下面我们通过例子说明这两种分区的使用方式。

Range 分区

# 创建一个sales表，按照date列Range分区，从2008年到2009年每月创建一个分区

postgres=＃ CREATE TABLE sales (id int, date date, amt decimal(10,2))
PARTITION BY RANGE (date)
( START (date '2020-01-01') INCLUSIVE
   END (date '2021-01-01') EXCLUSIVE
   EVERY (INTERVAL '1 month') );

查看创建的表信息，d+ 给出该表的所有信息：

postgres=# \d+ sales
              Append-Only Table "public.sales"
 Column |     Type      | Modifiers | Storage | Description
--------+---------------+-----------+---------+-------------
 id     | integer       |           | plain   |
 date   | date          |           | plain   |
 amt    | numeric(10,2) |           | main    |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: f
Child tables: sales_1_prt_1,
              sales_1_prt_10,
              sales_1_prt_11,
              sales_1_prt_12,
              sales_1_prt_2,
              sales_1_prt_3,
              sales_1_prt_4,
              sales_1_prt_5,
              sales_1_prt_6,
              sales_1_prt_7,
              sales_1_prt_8,
              sales_1_prt_9
Has OIDs: no
Options: appendonly=true
Distributed randomly
Partition by: (date)

下面是另外一个根据 Range 分区的例子，这次使用的是整型列进行分区。这里面我们添加了一个 DEFAULT PARTITION， 在不满足其他分区的条件下，数据会被插入 DEFAULT PARTITION。

CREATE TABLE rank (id int, rank int, year int, gender char(1), count int)
PARTITION BY RANGE (year)
( START (2010) END (2020) EVERY (1),
  DEFAULT PARTITION extra );

List 分区

下面的例子创建了一个基于 List 的分区表。List 分区表可以基于任意支持等值比较的数据类型。对与 List 分区，你需要 显式的指定所有子分区。

postgres=＃ CREATE TABLE rank (id int, rank int, year int, gender char(1), count int )
PARTITION BY LIST (gender)
( PARTITION girls VALUES ('F'),
  PARTITION boys VALUES ('M'),
  DEFAULT PARTITION other );

查看表信息：

postgres=＃ \d+ rank
              Append-Only Table "public.rank"
 Column |     Type     | Modifiers | Storage  | Description
--------+--------------+-----------+----------+-------------
 id     | integer      |           | plain    |
 rank   | integer      |           | plain    |
 year   | integer      |           | plain    |
 gender | character(1) |           | extended |
 count  | integer      |           | plain    |
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: f
Child tables: rank_1_prt_boys,
              rank_1_prt_girls,
              rank_1_prt_other
Has OIDs: no
Options: appendonly=true
Distributed randomly
Partition by: (gender)

多级分区

可以使用 SUBPARTITION 模版定义多级分区。下面的例子定义了一个两级分区表，第一级按照 date 列进行 Range 分区，第二级按照 region 列进行 List 分区。

CREATE TABLE sales (trans_id int, date date, amount decimal(9,2), region text)
PARTITION BY RANGE (date)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'),
  SUBPARTITION asia VALUES ('asia'),
  SUBPARTITION europe VALUES ('europe'),
  DEFAULT SUBPARTITION other_regions)
(START (date '2020-01-01') INCLUSIVE
 END (date '2021-01-01') EXCLUSIVE
 EVERY (INTERVAL '1 month'),
 DEFAULT PARTITION outlying_dates);

注：当在使用多级分区的时候，系统会产生大量的小表，有些表可能没有数据或包含很少数据，这样会对系统元数据管理产生过多压力。 建议不要创建具有过多分区的表。一般限制分区数在 100 或以内比较合理。

查看分区设计

可以通过 pg_partitions 视图来查看你的分区表设计。例如通过下面的语句可以查看出 sales 表的分区设计：

postgres=# SELECT partitionboundary, partitiontablename, partitionname, partitionlevel, partitionrank
postgres-# FROM pg_partitions
postgres-# WHERE tablename='sales';
                                           partitionboundary                                          | partitiontablename | partitionname | partitionlevel | partitionrank
------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------+---------------
 START ('2020-01-01'::date) END ('2020-02-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_1      |               |              0 |             1
 START ('2020-02-01'::date) END ('2020-03-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_2      |               |              0 |             2
 START ('2020-03-01'::date) END ('2020-04-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_3      |               |              0 |             3
 START ('2020-04-01'::date) END ('2020-05-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_4      |               |              0 |             4
 START ('2020-05-01'::date) END ('2020-06-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_5      |               |              0 |             5
 START ('2020-06-01'::date) END ('2020-07-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_6      |               |              0 |             6
 START ('2020-07-01'::date) END ('2020-08-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_7      |               |              0 |             7
 START ('2020-08-01'::date) END ('2020-09-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_8      |               |              0 |             8
 START ('2020-09-01'::date) END ('2020-10-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_9      |               |              0 |             9
 START ('2020-10-01'::date) END ('2020-11-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_10     |               |              0 |            10
 START ('2020-11-01'::date) END ('2020-12-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_11     |               |              0 |            11
 START ('2020-12-01'::date) END ('2021-01-01'::date) EVERY ('1 mon'::interval) WITH (appendonly=true) | sales_1_prt_12     |               |              0 |            12
(12 rows)

添加分区

ALTER TABLE sales ADD PARTITION
            START (date '2020-02-01') INCLUSIVE
            END (date '2021-03-01') EXCLUSIVE;