pg表分区

pg表分区

官网链接：http://www.postgres.cn/docs/11/ddl-partitioning.html

一、声明式划分

PostgreSQL提供了表分区特性。通常支持范围RANGE划分，比如按时间。列表划分、哈希分区。

特点：

a.无法把一个常规表转换成分区表，反之亦然。不过，可以把一个包含数据的常规表或者分区表作为分区加入到另一个分区表，或者从分区表中移走一个分区并且把它变成一个独立的表

b.分区本身也可能被定义为分区表，这种用法被称为子分区。分区可以有自己的与其他分区不同的索引、约束以及默认值

c.在分区表的键列上创建一个索引，还有其他需要的索引（键索引并不是必需的，但是大部分场景中它都能很有帮助）。这会自动在每个分区上创建一个索引，并且后来创建或者附着的任何分区也将会包含索引

d.由于分区表并不直接拥有任何数据，尝试在分区表上使用TRUNCATE ONLY将总是返回错误

e .如果NOT NULL约束在父表中存在，那么就不能删除分区的列上的对应的NOT NULL约束。分区表的CHECK约束和NOT NULL约束总是会被其所有的分区所继承。不允许在分区表上创建标记为NO INHERIT的CHECK约束。只要分区表中不存在分区，则支持使用ONLY仅在分区表上增加或者删除约束。一旦分区存在，那样做就会导致错误，因为当分区存在时是不支持仅在分区表上增加或删除约束的。不过，分区表本身上的约束可以被增加（如果它们不出现在父表中）和删除。

示例：

1.通过指定PARTITION BY子句把measurement表创建为分区表

CREATE TABLE public.measurement (
	city_id int4 NOT NULL,
	logdate date NOT NULL,
	peaktemp int4 NULL,
	unitsales int4 NULL
)
PARTITION BY RANGE (logdate);

2.创建分区子表（约束在子表创建,无法在主表创建跨越所有分区的约束）

CREATE TABLE public.measurement_y2006m03 PARTITION OF public.measurement (
	CONSTRAINT measurement_y2006m03_pk PRIMARY KEY (city_id)
)FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

3.索引创建(主表创建，分区也生效)

CREATE INDEX measurement_logdate_idx ON ONLY public.measurement USING btree (logdate);

4.分区维护

--删除掉不再需要的分区
DROP TABLE measurement_y2006m03;
--分区从分区表中移除，但是保留它作为一个独立的表
ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;
--创建默认的表分区（版本11以及以上支持）
 create table  measurement_default partition of  measurement  default;

5.存储函数（实战）

创建分区任务

/*
-- function: Create partitions for table measurement.

-- parameter:
-- p_partition_unit: Partition unit. month, day; 
-- p_partiton_cnt: Count of partitions to be created when this function is called. The default value is 7.
-- p_start_utc_time: One UTC timestamp value that is in the range of the first partition's partition-key, in milliseconds. The default value is now.

-- return:
-- success: 0

-- 模板复用方法：
--  1. measurement 替换为新的表名
--  2. (city_id) 替换为新的表的 (主键字段)

*/

create or replace function func_create_partition_for_measurement(p_partition_unit text default 'month', p_partiton_cnt int default 6, p_start_time timestamp default now()) returns  int  as
$body$
declare
    v_child_tbname text;
    v_start_time timestamp with time zone;
    v_end_time timestamp with time zone;
    v_has_default int default 0;
    v_parent_tbname text;
    v_partition_defaultname text;
    v_partition_defaultname_tmp text;
    partition_interval interval;
    time_format text;
begin
    v_parent_tbname := 'measurement';
    v_partition_defaultname := v_parent_tbname || '_default';
    v_partition_defaultname_tmp := v_partition_defaultname || '_tmp';
    --pg_inherits为系统表用来记录表间的继承关系 inhparent为主表oid  inhrelid为分区表oid regclass是oid的别名 oid是一个库中对象的唯一标识
    perform * from pg_inherits where cast(cast(inhparent as regclass) as text) = v_parent_tbname and  cast(cast(inhrelid as regclass) as text) = v_partition_defaultname;
    if found then
        v_has_default := 1;
        --如果默认分区*_default在系统表中已经存在,解除该表的分区(但是数据未删除)
        execute  'alter  table ' || v_parent_tbname || ' detach partition ' || v_partition_defaultname ;
    end if;

    partition_interval := (case p_partition_unit
        when 'month' then interval '1 month'
        when 'day' then interval '1 day'
        end);

    time_format := (case p_partition_unit
        when 'month' then 'YYYYMM'
        when 'day' then 'YYYYMMDD'
        end);

    if p_partition_unit in ('month', 'day') then
   		 --按照月分割开始时间的时间戳
        v_start_time := date_trunc(p_partition_unit, p_start_time);
        for v_i in 0..(p_partiton_cnt - 1) loop
            v_child_tbname := v_parent_tbname || '_' || to_char(v_start_time, time_format);