如何实现分区表不锁表转冷存_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

通过SQL语句ALTER TABLE tablename_part1 SET TABLESPACE oss将子分区表转冷存时，整个父分区表将被锁定，即在转存过程中无法对分区表进行读写操作。如果子分区表的数据量较大，转存所需的时间可能较长，长时间的锁表将对业务产生影响。为了解决此类问题，您可以通过DETACH和ATTACH的方式，实现子分区表在不锁表的情况下进行转冷存操作。

注意事项

当前方案仅适用于分区表中的子分区表转冷存场景，不适用于父分区表或非分区表。
仅支持范围分区，不支持列表分区以及HASH分区。
分区表上未创建全局索引。
转存过程中，仅对子分区表进行锁定，业务上需确保对该子分区表没有读写请求。转存完成后，可以正常进行读写操作。

准备环境

创建父分区表sales。该表按销售日期 sale_date 进行范围分区。

-- 创建一个按年分区的销售记录父表
CREATE TABLE sales (
    sale_id      INT NOT NULL,
    sale_date    DATE NOT NULL,
    amount       DECIMAL(10, 2)
) PARTITION BY RANGE (sale_date);

-- 为父表添加注释
COMMENT ON TABLE sales IS '销售记录主表（按年分区）';

创建子分区。为2023年和2024年创建两个子分区。

-- 创建 2023 年的子分区
CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- 创建 2024 年的子分区
CREATE TABLE sales_2024 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

插入示例数据。向两个分区中插入一些数据，以便后续验证。

-- 向 2023 分区插入数据
INSERT INTO sales (sale_id, sale_date, amount) VALUES
(1, '2023-03-15', 100.00),
(2, '2023-09-20', 150.50);

-- 向 2024 分区插入数据
INSERT INTO sales (sale_id, sale_date, amount) VALUES
(3, '2024-01-10', 200.00),
(4, '2024-02-05', 250.75);

卸载子分区

将sales_2023子分区从父表sales中卸载。卸载后，sales_2023成为一个独立的普通表，此时对父表sales的读写（例如对sales_2024分区的操作）不会被阻塞。

-- 将 sales_2023 子分区从父表分离
ALTER TABLE sales DETACH PARTITION sales_2023;

将卸载后的表转存到冷存储

现在对独立的sales_2023表执行转冷存操作。此操作仅会锁定sales_2023表本身，而不会影响父表sales及其它分区。

-- 将 sales_2023 表的数据移动到名为 oss 的表空间
ALTER TABLE sales_2023 SET TABLESPACE oss;

将子分区重新挂载回父表

转存完成后，将sales_2023重新挂载回sales表。

-- 将已转存到冷存储的 sales_2023 表重新挂载回父表
ALTER TABLE sales ATTACH PARTITION sales_2023 FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

为了提高ATTACH PARTITION的速度，PostgreSQL 14版本增加了NOCHECK_CONSTRAINT语法用于跳过分区约束的检查。

说明

使用NOCHECK_CONSTRAINT时，您需确保ATTACH分区和DEFAULT分区中的数据已经满足分区约束，否则会出现数据准确性问题。

ALTER TABLE sales ATTACH PARTITION sales_2024 FOR VALUES FROM ('2024-01-01') TO ('2025-01-01') NOCHECK_CONSTRAINT;

验证结果

查询新挂载分区的数据。通过父表sales查询位于2023年的数据，验证数据是否仍然可以被正常访问。

-- 通过父表查询已转存到冷存储的 2023 年分区的数据
SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';

查询应成功返回最初插入到sales_2023分区的数据。预期输出结果如下：

 sale_id  |   sale_date        | amount   |
---------+--------------------------------+
   1      |   2023-03-15       |  100.00  |      
   2      |   2023-09-20       |  150.50  |