分区表的两大刚需｜拆分分区｜合并分区

原创多米爸比 2024-05-02

901

下半年即将发布的PG 17对分区表的管理工作增加了两项刚需功能：拆分分区和合并分区

当前分区表的限制

PG 17之前，分区表的工作流程管理局限于：创建分区、挂接分区、卸载分离。

参考语法为：

CREATE TABLE ... PARTITION BY ... 
CREATE TABLE ... PARTITION OF ...

ALTER TABLE ... ATTACH PARTITION ...

ALTER TABLE ... DETACH PARTITION ...

分区结构一旦设计确定后，无法重新设计：例如，将多个分区合并为单个，将单个分区拆分为多个。此时只能设计新的分区结构，然后迁移数据。

PG 17增加了SPLIT PARTITION，可将现有的单个分区拆分为两个或多个新分区。同时也增加了MERGE PARTITIONS，支持对现有的多个分区合并为单个新分区。

合并分区：MERGE PARTITIONS

现有一张订单销售表sales：

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

设计为按月进行分区，每年需要创建12个分区：

CREATE TABLE sales_202401 PARTITION OF sales FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE sales_202402 PARTITION OF sales FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE sales_202403 PARTITION OF sales FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

...

受经济环境影响，订单数据下降严重，想调整为按季度分区，此时可以使用MERGE PARTITIONS命令对现有分区进行合并：

ALTER TABLE sales
MERGE PARTITIONS (sales_202401, sales_202402, sales_202403)
INTO sales_2024_s1;

MERGE PARTITIONS将1、2、3月分区合并为1季度的分区sales_2024_s1。

拆分分区：SPLIT PARTITION

四月份经济基本恢复正常，数据分区恢复为按月分区：

CREATE TABLE sales_202404 PARTITION OF sales FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

CREATE TABLE sales_202405 PARTITION OF sales FOR VALUES FROM ('2024-05-01') TO ('2024-06-01');

...

五月份经济快速爆发，需要调整为按天分区，此时可以使用SPLIT PARTITION命令对五月份的分区进行拆分：

ALTER TABLE sales SPLIT PARTITION sales_202405 INTO (
    PARTITION sales_202405_d01 
       FOR VALUES FROM ('2024-05-01') TO ('2024-05-02'),
    PARTITION sales_202405_d02 
       FOR VALUES FROM ('2024-05-02') TO ('2024-05-03'),
...
    PARTITION sales_202405_d31 
       FOR VALUES FROM ('2024-05-31') TO ('2024-06-01')
);

注意：拆分的边界范围必须全部覆盖到新分区。

拆分分区需要获取父表的排它锁，这是一个长锁，当前拆分分区并不支持非阻塞方式创建，期待将来的新版本支持CONCURRENTLY非阻塞方式来处理庞大的表，减少数据迁移的分区管理工作。

不过可以使用如下的变通方式：

先卸载分区
再对卸载的分区进行拆分
重新挂接分区

虽然操作步骤变多了，并且卸载离线后的分区还需要借助仿造的父表才能进行拆分，但锁转移到离线后的表结构上，可以避免父表潜在的长锁阻止写入。

下面是具体的操作步骤：

1.以非阻塞方式卸载1季度的分区sales_2024_s1

ALTER TABLE sales
DETACH PARTITION sales_2024_s1 CONCURRENTLY;

2.仿造sales父表结构，创建sales_fake伪父表

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

3.sales_fake挂接sales_2024_s1表，再进行离线拆分

ALTER TABLE sales_fake
ATTACH PARTITION sales_2024_s1 
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

4.借助分离的sales_fake父表，进行拆分

ALTER TABLE sales_fake SPLIT PARTITION sales_2024_s1 INTO (
    PARTITION sales_202401
       FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'),
    PARTITION sales_202402
       FOR VALUES FROM ('2024-02-01') TO ('2024-03-01'),
    PARTITION sales_202403 
       FOR VALUES FROM ('2024-03-01') TO ('2024-04-01')
);

5.将拆分的分区从sales_fake卸载

ALTER TABLE sales_fake
DETACH PARTITION sales_202401;

ALTER TABLE sales_fake
DETACH PARTITION sales_202402;

ALTER TABLE sales_fake
DETACH PARTITION sales_202403;

6.再挂接到原父表

ALTER TABLE sales
ATTACH PARTITION sales_202401
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

ALTER TABLE sales
ATTACH PARTITION sales_202402
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

ALTER TABLE sales
ATTACH PARTITION sales_202403 
FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');