postgresql的分区表

postgresql的分区表

1.相关基本介绍：

• pg10.x以前只能通过继承+触发器的方式创建分区表
• postgresql版本在10.x之后提供了内置分区表，只支持range和list分区，11.x版本支持hash分区
• pg11之前只能单独为每个分区表建立索引，且不能在父表上建立主键，索引等。pg11后可以对父表建立索引，分区子表自动创建。
• 分区表不允许其他表作为外键引用
• 分区表的数据是通过操作父表进行插入操作的

继承

定义一张父表后通过关键字inherits创建子表继承父表，子表具有父表的全部字段属性，同时可以定义子表自己的字段

##父表
create table aa(id int4,type init4);

##子表，继续父表同时增加一列speciality
create table aa_child(speciality varchar(10)) inherits(aa);

##通过tableoid隐藏字段区分查询结果来源是子表还是父表
select tableoid,* from aa;

父表与子表的关系

• 子表与父表字段和约束是包含关系
• 操作父表的时候会同时显示父表和子表的结果，除非使用only指定操作父表方式
• 子表不继承父表的索引，若需要则需要对子表手动添加

2.比较不同版本之间分区改进

• pg9使用继承式分区，pg10实现了声明式分区，pg11完善了功能，pg12提升了性能

版本	新增特性
pg9	1.继承式分区 2.手动添加触发器或规则
pg10	1.声明式分区 2.分区索引手动创建，不能基于分区父表创建
pg11	1.新增哈希分区 2.基于的分区表创建索引 3.支持update分区 4.会创建一个默认default分区 5.分区支持创建主键，外键，索引，触发器
pg12	1.新增哈希分区 2.alter table attach partitions不会阻塞查询
pg13	1.可以支持before trigger（不允许改变插入数据的目标分区） 2.分区表可以支持逻辑复制

3.高版本创建分区表步骤(声明式)：

1.创建父表

CREATE TABLE public.tb_aa (
	id serial NOT NULL,
	user_id int4 NOT NULL,
	status varchar(5) NOT NULL DEFAULT '1'::character varying,
	update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT tb_aa_pk PRIMARY KEY (id)
) partition by range(id);

2.创建子分区表

create table tb_aa_20210907 partition of tb_aa for values from (1) to (5);
create table tb_aa_20210908 partition of tb_aa for values from (5) to (10);
create table tb_aa_20210909 partition of tb_aa for values from (10) to (15);

3.插入数据

insert into tb_aa(id,user_id) values(1,1),(2,1),(3,1),(4,1),(5,1),(6,1),(7,1),(8,1),(9,1),(10,1),(11,1),(12,1),(13,1);

4.查看数据

select * from tb_aa_20210907;
select * from tb_aa_20210908;
select * from tb_aa_20210909;

5.修改分区表结构等

create index ix_tb_aa_user_id on tb_aa(user_id);
alter table tb_aa add column "type" int default 0;

结论：(高版本)
1.分区表的数据，只需要对父表进行操作处理即可
2.子分区表会自动继承父表，添加索引等信息的时候，无需单独分别对分区表进行操作
3.通过DDL信息查看分区表权限授予也是和父表的一样

4.分区基本操作

##删除分区
drop table tb_name_20210601;

##分区脱离父表
alter table tb_name_20210601 no inherit tb_name;
或者
alter table tb_name detach tb_name_20210601;

##分区表重新继承
alter table tb_name_20210601 inherit tb_name;

##增加分区
create table tb_name_20210615 partition of tb_name for values from ('2021-06-07') to ('2021-06-15');

create index tb_name_idx_202106015_col2 on 
tb_name_20210615 using btree(col2);

5.分区表的类型

1). range（支持多列）
partition by range() for values from (…) to (…)

2). list（不支持多列）
partition by list() for values in (…)

3). hash（支持多列）
partition by hash() for values with()

6.多级分区
1.SQL 错误 [42P16]: ERROR: invalid bound specification for a range partition
2.可以在list分区下创建range分区

range和list分区支持创建默认分区，即不符合分区约束的数据会被插入到默认分区
create table tb_aa_default partition of tb_aa default;

7.分区关系的操作

##detach分区表
##被detach的分区表只是解除了分区关系，表依旧是存在的
alter table tb_aa detach partition tb_aa_20210910;

##attach分区表
alter table tb_aa attach partition tb_aa_20210910 for values from (15) to (20) ;

##查看给定的分区表的父表信息
select child.relname as partition_name,parent.relname as parent
from pg_catalog.pg_inherits as inh
join pg_class as parent on inh.inhparent=parent.oid
join pg_class as child on inh.inhrelid=child.oid
join pg_namespace as ns1 on ns1.oid=parent.relnamespace
join pg_namespace as ns2 on ns2.oid=child.relnamespace
where ns1='schemaname' and child.relname='tablename';

## 查询给定表名的分区
select  nmsp_parent.nspname AS parent_schema ,
parent.relname AS parent ,
nmsp_child.nspname AS child ,
child.relname AS child_schema 
from pg_inherits 
JOIN pg_class parent 
on pg_inherits.inhparent =parent.oid JOIN pg_class child 
on pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent 
on nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_child
on nmsp_child.oid = child.relnamespace 
where parent.relname = '表名'  order by child.relname;

##查看父表中有哪些分区表
select c.relname
from pg_class c
join pg_inherits i on i.inhrelid = c. oid
join pg_class d on d.oid = i.inhparent
where d.relname = 'table_name';