1、postgres通过partition做范围表分区

目录

• postgres通过partition做范围表分区
  • 1、安装pg_partman扩展
  • 2、创建需要分区表，按学生的入学时间分区
  • 3、创建分区
  • 4、插入数据
  • 5、查询分区表
  • 6、不需要子分区时
  • 7、直接插入子分区表时。
  • 8、navicat可以查看到分区的表与分区的维度

postgres通过partition做范围表分区

表分区是将一个表的数据拆分成多个物理表去存储，查询的时候合并成一个表查询。

首先确保当前的 postgres 版本支持表分区的，如果不支持，可以安装 pg_partman 扩展。

1、安装pg_partman扩展

CREATE EXTENSION pg_partman;

2、创建需要分区表，按学生的入学时间分区

PARTITION BY RANGE 可以通过学生的入学时间 stu_enrol_date 按年去分区。

CREATE TABLE tb_student ( 
	stu_id INT NOT NULL, 
	stu_name VARCHAR ( 100 ) NOT NULL, 
	stu_sex VARCHAR ( 2 ), 
	stu_age INT, 
	stu_grade VARCHAR ( 2 ), 
	stu_enrol_date DATE NOT NULL 
) PARTITION BY RANGE ( stu_enrol_date );

3、创建分区

可以随时增加分区

create table tb_student_2020 partition of tb_student for VALUES from ('2020-01-01') to ('2020-12-31');
create table tb_student_2021 partition of tb_student for VALUES from ('2021-01-01') to ('2021-12-31');
create table tb_student_2022 partition of tb_student for VALUES from ('2022-01-01') to ('2022-12-31');

4、插入数据

插入的数据会自动根据 stu_enrol_date 放入到指定的分区表中

insert into tb_student(stu_id,stu_name,stu_sex,stu_age,stu_grade,stu_enrol_date) values (1,'小米','男',12,'6',to_date('2020-03-01','yyyy-MM-dd'));
insert into tb_student(stu_id,stu_name,stu_sex,stu_age,stu_grade,stu_enrol_date) values (2,'小明','男',11,'6',to_date('2021-03-01','yyyy-MM-dd'));
insert into tb_student(stu_id,stu_name,stu_sex,stu_age,stu_grade,stu_enrol_date) values (3,'小红','男',10,'5',to_date('2020-03-01','yyyy-MM-dd'));
insert into tb_student(stu_id,stu_name,stu_sex,stu_age,stu_grade,stu_enrol_date) values (4,'小王','男',12,'6',to_date('2022-03-01','yyyy-MM-dd'));
insert into tb_student(stu_id,stu_name,stu_sex,stu_age,stu_grade,stu_enrol_date) values (5,'小吴','男',10,'6',to_date('2021-03-01','yyyy-MM-dd'));

5、查询分区表

查询分区表时：合并查询

select * from tb_student;

结果显示全都是刚才插入的数据。

查询子分区表时：

select * from tb_student_2021;

显示stu_id为2、5的记录

6、不需要子分区时

可以指定某个子分区表删除，数据也会跟随一起删除。

drop table tb_student_2021;

7、直接插入子分区表时。

到直接 insert 到 tb_student_2020 表时，会去校验 stu_enrol_date 的约束，是否满足当前分区的结果。

正常：

insert into tb_student_2020(stu_id,stu_name,stu_sex,stu_age,stu_grade,stu_enrol_date) values (6,'小轩','男',12,'6',to_date('2020-03-01','yyyy-MM-dd'));

不满足时：

insert into tb_student_2020(stu_id,stu_name,stu_sex,stu_age,stu_grade,stu_enrol_date) values (7,'小轩','男',12,'6',to_date('2021-03-01','yyyy-MM-dd'));
 

报错:ERROR: new row for relation "tb_student_2020" violates partition constraint DETAIL: Failing row contains (7, 小轩, 男, 12, 6, 2021-03-01).

8、navicat可以查看到分区的表与分区的维度

找到 tb_student ，点击设计表，点击分区就可以查看到分区的详情信息了。