pgsql在分区表与非分区表中自由切换

        pgsql在版本10中推出了声明式分区表，算是让分区表这个功能走上了正轨。（之前的版本为继承+触发器，和SQL Server的分区视图有点像，算是粗制）。

        随着其版本发展，功能也越来越强，但今天要介绍的是基础功能：如何优雅地在分区表和非分区表切换。

        pgsql分区表有一个笔者非常喜欢的特性，分区列是自由的，不和唯一约束绑定，在诸如SQL Server、MySQL中，当你尝试把普通表转换为分区表时，会强制要求你把分区列加到唯一性约束中（主键、唯一约束/索引），这个硬性要求使得包含大量数据的普通表转分区表的代价非常高，特别是主键的变动，会影响很多东西，时间成本也很高。

        个人一直认为把分区列加到唯一约束这个硬性要求其实没什么道理，毕竟分区是唯一的，唯一性约束保障分区内的唯一主可以了。所以pgssql不要求分区列在唯一约束中，这点是非常对本人口味的。

        下面直接上代码，演示如何优雅地将普通表切换为分区表，整个过程可以做到业务无中断，分区表切换普通表可以用完全相反的过程。

-- 创建测试表 -- 普通表，后续过程将此表切换为分区表
CREATE TABLE tb
AS
SELECT a.* 
FROM pg_settings a,
	pg_settings b,
	pg_settings c,
	pg_settings d
LIMIT 1000000
;
ALTER TABLE tb ADD id serial PRIMARY KEY;


-- 准备将测试表切换为分区，分区表使用 context 列值进行 LIST 分区

-- 分区 -- 添加临时分区列，与分区列 context 保持相同的定义
--         临时分区列的意义：在将正确的值更新到临时分区列之前，所有的记录不匹配任何分区条件，属于 DEFAULT 分区
ALTER TABLE tb ADD _pt_context text;

-- 分区 -- 创建主表
CREATE TABLE tb_primary( LIKE tb )PARTITION BY LIST( _pt_context );

-- 分区 -- 创建子表（根据 context 列值创建与值匹配的子表，并将其 ATTACH 到分区表）
DO $$
DECLARE _$sql text;
BEGIN
SELECT string_agg('
-- ALTER TABLE tb_primary DETACH PARTITION ' || tb || ';
-- DROP TABLE IF EXISTS ' || tb || ';
CREATE TABLE ' || tb || '( LIKE tb INCLUDING ALL, CHECK(_pt_context=' || v || ') );
ALTER TABLE tb_primary ATTACH PARTITION ' || tb || ' FOR VALUES IN(' || v || ');
', '')
INTO _$sql
FROM(
	SELECT
		'tb_' || REPLACE(context, '-', '_') as tb,
		'''' || context || '''' as v
	FROM( SELECT DISTINCT context FROM pg_settings ) A
)REQ
;
EXECUTE _$sql;
END;
$$ LANGUAGE plpgsql;

-- 分区 --  将测试表以 DEFAULT 分区的方式加到分区表（）
--          可选: 先添加 CHECK，可以降低添加到分区时的锁的粒度（会稍微有一点操作时间，如果不加，则添加到分区表的操作会稍微有点操作时间）
-- ALTER TABLE tb ADD CHECK(_pt_context IS NULL)
BEGIN;
ALTER TABLE tb RENAME TO tb_default;
ALTER TABLE tb_primary ATTACH PARTITION tb_default DEFAULT;
ALTER TABLE tb_primary RENAME TO tb;
COMMIT;

-- 分区数据 --  通过更新临时分区列，使数据从 DEFAULT 分区移动到正确的分区
-- 分区数据 --  更新前，使用触发器保证新数据的分区列与临时分区列数据同步
CREATE OR REPLACE FUNCTION f_tr_tb__pt_context()
RETURNS TRIGGER
AS $$
BEGIN
	NEW._pt_context = NEW.context;
	RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_tb_sync_context
	BEFORE UPDATE OR INSERT
	ON tb_default
    FOR EACH ROW
    EXECUTE FUNCTION f_tr_tb__pt_context()
;

-- 分区数据 --  通过任何一种分批更新的方式完成分区列更新（这里是演示，所以一步更新到位），完成之后，所有的数据按照分区列分配到对应的表中
UPDATE tb SET _pt_context=context;

-- 完成分区 -- 在分区数据完成之后进行
BEGIN;
-- 使用 DROP COLUMN 或 RENAME COLUMN 将 context 作废（DROP 会有一点性能损失，但损失不大）
-- ALTER TABLE tb DROP COLUMN context;
ALTER TABLE tb RENAME COLUMN context TO _remove_context;
-- 删除分区列数据同步的触发器
DROP TRIGGER tr_tb_sync_context ON tb_default;
-- 使用临时分区列取代正式分区列
ALTER TABLE tb RENAME COLUMN _pt_context TO context;
COMMIT;
-- 删除分区列数据同步的触发器对应的函数
DROP FUNCTION f_tr_tb__pt_context(); 

        

【本文在个人微信公共号ZJCXC上同步发表】