2019-05-17修改
最新版本的pg_pathman已经支持主表的存储参数,如果你使用最新版本的pg_pathman可以不再使用下面的方法.
2018-06-05修改
- 强烈建议在postgresql.conf关闭autovacuum_analyze_scale_factor和autovacuum_vacuum_scale_factor(设置为0即可),因为表有大有小,避免按百分比计算时一些不必要的vacuum和analyze.
- 使用autovacuum_vacuum_threshold和autovacuum_analyze_threshold来控制autovacuum和autoanalyze,在postgresql.conf设置一个相对比较大的值比如10000.然后根据每个表的业务情况为每个表单独设置autovacuum_vacuum_threshold和autovacuum_analyze_threshold,表中设置的autovacuum_vacuum_threshold和autovacuum_analyze_threshold值不要超过postgresql.conf中配置的值,本例中小于等于10000.
pg_pathman创建的子表不会创建父表中的存储参数,例如
1 创建表及设置分区表
select drop_partitions('test'::regclass,true);
drop table if exists test;
create table test(
objectid bigint not null,
parentid bigint not null,
generate timestamptz default now() not null,
constraint pk_test_objectid primary key(objectid) with (fillfactor=80),
constraint fk_test_parentid foreign key(parentid) references catalogs(objectid) on delete cascade
)/* 分区表不会创建存储参数*/
with (fillfactor=80,
autovacuum_enabled=true,toast.autovacuum_enabled=true,
autovacuum_vacuum_threshold=500,autovacuum_analyze_threshold=500,
toast.autovacuum_vacuum_threshold=500);
select create_range_partitions(
'test'::regclass, --主表OID
'generate', --分区列名
'2018-01-01'::timestamptz, --开始值
make_interval(years=>1), --间隔
1, --初时化创建的表数量
false --是否立即将数据从主表迁移到分区
);
select set_auto('test'::regclass,true); --是否自动添加分区表
select set_enable_parent('test'::regclass, false); --禁用主表
查看子表test_1详细定义
\dS+ test_1
Table "public.test_1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+--------------------------+-----------+----------+---------+---------+--------------+-------------
objectid | bigint | | not null | | plain | |
generate | timestamp with time zone | | not null | now() | plain | |
Indexes:
"test_1_pkey" PRIMARY KEY, btree (objectid) WITH (fillfactor='80')
Check constraints:
"pathman_test_1_check" CHECK (generate >= '2018-01-01 00:00:00+08'::timestamp with time zone AND generate < '2019-01-01 00:00:00+08'::timestamp with time zone)
Inherits: test
可以看到索引中的存储参数在子表中和主表一致,但主表中设置的以下参数在子表中均未定义.
- fillfactor
- autovacuum_enabled-
- autovacuum_vacuum_scale_factor
- autovacuum_vacuum_threshold
- autovacuum_analyze_scale_factor
- autovacuum_analyze_threshold
2 解决方案
设置pg_pathman初始化时回调函数.回调函数在创建分区表时自动调用,可以修改为任何你需要的操作.在本例中博主设置表的存储参数.
本节重点:
- set_init_callback(relation REGCLASS, callback REGPROC DEFAULT 0),注意类型转换
- callback函数原型为function (jsonb),只有一个参数类型为jsonb,返回类型为void,如果执行函数异常,不会创建分区表
回调函数参数jsonb格式定义
parttype(分区类型)为1时表示hash分区表, 2表示范围分区表
当分类类型为范围分区时,create_range_xxx
{
"parent": "abc", --父表名称
"parent_schema": "public", --父表模式
"parttype": "2", --分区类型
"partition": "abc_4", --当前分区表名称
"partition_schema": "public", --当前分区表模式
"range_max": "401", --分区范围最大
"range_min": "301" --分区范围最小
}
当分类类型为范围分区时,create_hash_partitions
{
"parent": "abc", --父表名称
"parent_schema": "public", --父表模式
"parttype": "2", --分区类型
"partition": "abc_4", --当前分区表名称
"partition_schema": "public", --当前分区表模式
}
/****************************************************************************************
创建分区表
****************************************************************************************/
--创建分区表时回调函数,如回调函数异常不会创建分区表
create or replace function test_callback(param jsonb)
returns void as
$$
declare
v_cmd text;
v_fillfactor integer;
v_analyze_threshold integer;
v_vacuum_threshold integer;
v_tanalyze_threshold integer;
v_tvacuum_threshold integer;
begin
--根据业务情况设置表参数,经常修改和删除的表设置小一点例如500(fillfactor=50),不经常修改和删除的表设置大一点例如5000(fillfactor=90),但是不要超过10000(postgresql.conf)
v_fillfactor := 80;
v_analyze_threshold := 1000;
v_vacuum_threshold := 1000;
v_tvacuum_threshold := 1000;
--设置表的存储参数结束
v_cmd := format('alter table %s.%s set (
fillfactor=%s,
autovacuum_enabled=true,toast.autovacuum_enabled=true,
autovacuum_analyze_threshold=%s,
autovacuum_vacuum_threshold=%s,
toast.autovacuum_vacuum_threshold=%s
)',
param->>'partition_schema',param->>'partition',
v_fillfactor,
v_analyze_threshold,
v_vacuum_threshold,
v_tvacuum_threshold
);
--raise notice '%',v_cmd;
execute v_cmd;
end
$$language plpgsql;
--注册创建表时回调函数(注意强制转换类型)
select set_init_callback('test'::regclass,'test_callback'::regproc);
--启用自动添加分区表
select set_auto('test'::regclass,true);
--禁用主表
select set_enable_parent('test'::regclass, false);
--创建分区表
select create_range_partitions(
'test'::regclass, --主表OID
'generate', --分区列名
'2018-01-01'::timestamptz, --开始值
make_interval(years=>1), --间隔
1, --初时化创建的表数量
false --是否立即将数据从主表迁移到分区
);
3 pg_pathman视图和表
查看设置的分区表和分区字段
select * from pathman_config
查看分区表配置参数
select * from pathman_config_params
查看目前正在运行任务(pg_pathman),数据迁移到分区时才会有内容
select * from pathman_concurrent_part_tasks
所有现有分区以及它们的父级和范围边界(HASH分区为NULL)。
select * from pathman_partition_list
显示内存消耗,必须以postgres用户执行
select * from pathman_cache_stats