postgreSQl pathman 用法语句总结

2019独角兽企业重金招聘Python工程师标准>>>

--新建主表
create table part_test(id int, info text, crt_time timestamp not null);
--插入测试数据
insert into part_test select id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id);
--所有数据都在主表
select * from part_test limit 10;  
--创建分区表（range分区）
--建议
--1. 分区列必须有not null约束
--2. 分区个数必须能覆盖已有的所有记录
--3. 建议使用非堵塞式迁移接口
--4. 建议数据迁移完成后，禁用主表
select create_range_partitions('part_test'::regclass,      -- 主表OID
                        'crt_time',                        -- 分区列名
                        '2016-10-25 00:00:00'::timestamp,  -- 开始值
                        interval '1 month',                -- 间隔；interval 类型，用于时间分区表
                        24,                                -- 分多少个区
                        false) ;  			   -- 是否转移数据
---自动扩展分区，新插入的数据不在已有的分区范围内，会自动创建分区
select set_auto('part_test'::regclass, true);

--生成100条数据
select id,md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,100) t(id);

--转移数据
--注意:  
--1. 分区列必须有not null约束  
--2. 分区个数必须能覆盖已有的所有记录
select partition_table_concurrently('part_test'::regclass,
                             10000,
                             1.0);

--查看迁移任务
select * from pathman_concurrent_part_tasks;

--停止迁移数据
select stop_concurrent_part_task('part_test'::regclass)

--设置主表不可用
select set_enable_parent('part_test'::regclass, false);

--查看查询走分区
explain select * from part_test where crt_time = '2017-8-25 00:00:00'::timestamp;

--指定两个需要合并分区，必须为相邻分区（不是相邻分区，报错），合并后，会删掉其中一个分区表
select merge_range_partitions('part_test_2'::regclass, 'part_test_12'::regclass) ;

--分区分裂
 select split_range_partition('part_test_1'::regclass,      -- 分区oid
                      '2016-11-10 00:00:00'::timestamp,     -- 分裂值
                      'part_test_1_2');   		    -- 分裂后的表名

--删除分区，数据移动到主表
select drop_range_partition('part_test_2',false);

--删除分区，数据也删除，不迁移到主表 
select drop_range_partition('part_test_3',true);

--删除所有分区，数据移动到主表
select drop_partitions('part_test'::regclass, false);

--将分区从主表的继承关系中删除, 不删数据，删除继承关系，删除约束（指定分区名，转换为普通表）
select detach_range_partition('part_test_2');

--hash分区
--注意:分区列必须有not null约束
--1. 分区列必须有not null约束
--2. 建议使用非堵塞式迁移接口
--3. 建议数据迁移完成后，禁用主表
--4. pg_pathman不会受制于表达式的写法，所以select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp;这样的写法也是能走哈希分区的。
--5. hash分区列不局限于int类型的列，会使用hash函数自动转换。
select create_hash_partitions('part_test'::regclass,       -- 主表OID
                        'crt_time',                        -- 分区列名
                        128,                               -- 打算创建多少个分区
                        false) ;                           -- 不迁移数据


--查询每个分区表初次创建时的 interval
select * from pathman_config;

--添加分区，支持指定表空间（在后面追加）
append_range_partition(parent         REGCLASS,            -- 主表OID
                       partition_name TEXT DEFAULT NULL,   -- 新增的分区表名, 默认不需要输入
                       tablespace     TEXT DEFAULT NULL)   -- 新增的分区表放到哪个表空间, 默认不需要输入

--例子（默认根据分区规则往后延一个分区）
select append_range_partition('part_test'::regclass);

--在头部追加分区
select prepend_range_partition('part_test'::regclass);

--分区字段要被更新，需要创建更新触发器
select create_range_update_trigger('part_test'::regclass);

--永久禁止分区表，禁用pg_pathman后，继承关系和约束不会变化，只是pg_pathman不介入custom scan 执行计划。
---disable_pathman_for没有可逆操作，请慎用
select disable_pathman_for('part_test');


--1. 如果在建初始分区时，需要设置分区表的表空间，可以设置会话或事务的参数
set local default_tablespace='tbs1';

--2. disable_pathman_for函数没有可逆操作，请慎用。

--3. 不建议关闭pg_pathman.enable

--4. 不建议开启自动扩展范围分区，一个错误的分区值可能导致创建很多分区。

--5. 推荐使用set_enable_parent禁用主表。

--6. 由于pg_pathman使用了custom scan接口，所以只支持9.5以及以上版本。

--7. 传统哈希分区需要输入分区键值的约束条件，才能正确选择分区。pg_pathman只要输入键值即可。

--8. 目前使用prepared statement会造成性能下降，跟踪到与LWLOCK有关，并不是不支持过滤分区造成的

参考：https://yq.aliyun.com/articles/62314

转载于:https://my.oschina.net/duo8523/blog/860560