使用DETACH和ATTACH避免子分區轉冷存時鎖定父表-雲原生資料庫 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  |