PostgreSQL技术大讲堂 - 第50讲：PG分区表管理

时间：2024-05-09 来源：

PostgreSQL从小白到专家，是从入门逐渐能力提升的一个系列教程，内容包括对PG基础的认知、包括安装使用、包括角色权限、包括维护管理、、等内容，希望对热爱PG、学习PG的同学们有帮助，欢迎持续关注CUUG PG技术大讲堂。

第50讲：PG分区表管理

内容1：分区表特点

内容2：范围分区介绍

内容3：list分区介绍

内容4：hash分区介绍

内容5：混合分区介绍

分区表特点

分而治之是分区表最大的特点，将表数据分成更小的物理分片，减少搜索范围，以此可以查询提高性能。分区表是关系型数据库中比较常见的对大表的优化方式，数据库管理系统一般都提供了分区管理，而业务可以直接访问分区表而不需要调整业务架构，当然好的性能需要合理的分区访问方式。

分区的具体好处是：改善查询性能、增强可用性、维护方便、均衡I/O。

PostgreSQL分区表特点

PG数据库表分区表的结构是由主表(父表)与分区表(子表)组成，主表是创建子表的模板，它是一个正常的普通表，正常情况下它并不储存任何数据;分区表继承并属于一个主表，分区表中存储所有的数据，主表与分区表属于一对多的关系，也就是说，一个主表包含多个分区表，而一个分区表只从属于一个主表

官方声明分区实现方式：声明式分区、继承分区;除此之外还支持其它的第三方分区管理方式，比如pathman扩展等

声明分区也叫原生分区，从PG10版本开始支持，相当于“官方支持”的分区表，也是最为推荐的分区方式。虽然与继承分区不一样，但是其内部也是用继承表实现的。声明式分区支持：范围分区、list分区、hash分区

范围分区表

范围分区表一般指的一个分区的范围，然后把满足条件的行存放在该分区中，最常见的是以日期做为分区条件，根据时间段分为不同的分区，存放不同时间段的数据。

范围分区实现：1、创建主表

CREATE TABLE part_range ( order_id int, name varchar(50) NULL, saledate timestamp NOT NULL DEFAULT now()) PARTITION BY RANGE(saledate);alter table part_range add primary key(id,saledate);\d+ part_range

2、创建分区表

create table p1_202401 partition of part_range for values from ('2024-01-01 00:00:00') to ('2024-02-01 00:00:00');create table p2_202402 partition of part_range for values from ('2024-02-01 00:00:00') to ('2024-03-01 00:00:00');\d+ p1_202401

3、插入数据

INSERT INTO part_range SELECT random() * 10000, md5(g::text),g FROM generate_series('2024-01-01'::date, '2024-02-28'::date, '1 minute') as g;

4、查看数据

select tableoid::regclass,count(*) FROM part_range group by tableoid::regclass;

在某些情况下，需要知道特定行来自哪个表，每个表中都有一个名为 tableoid 的系统列，使用 regclass 别名类型，它将象征性地打印表 OID，可以列出行的原始表。

访问分区表：

1、通过主表访问

explain select * from part_range where saledate ='2024-02-05';

2、通过分区表访问

explain select from p2_202402 where order_id=100;

list分区表

list分区以指定的分区值将数据存放到对应的分区上，然后把满足条件的行存放在该分区中，最常见的是以某列值为分区条件，根据不同的列值存放在不同的分区。

list分区实现：

1、创建主表

CREATE TABLE part_list ( city_id int not null, name varchar(30), population int) PARTITION BY LIST (name);Create index part_list_idx on part_list (name);\d+ part_range

2、创建分区表

CREATE TABLE p1_list PARTITION OF part_list FOR VALUES IN ('fujian', 'zhejiang');CREATE TABLE p2_list PARTITION OF part_list FOR VALUES IN ('shandong', 'jiangxi');

3、插入数据

insert into part_list (city_id,name,population) values(1,'fujian',10);insert into part_list (city_id,name,population) values(2,'zhejiang',20); insert into part_list (city_id,name,population) values(3,'shandong',10);insert into part_list (city_id,name,population) values(4,'jiangxi',20);

3、查看数据

SELECT tableoid::regclass,* FROM part_list;

4、查看执行计划

explain select * from part_list where name='fujian';

hash分区表

hash分区将数据散列存储在各个分区上，以打散热点数据存放到对应的分区上，然后把满足条件的行存放在该分区中，最常见的是平均的把数据放在不同的分区。

hash分区实现：

1、创建主表

CREATE TABLE part_hash (order_id int,name varchar(10)) PARTITION BY HASH (order_id);Create index part_hash_idx on part_hash (order_id);\d+ part_hash

2、创建分区表

CREATE TABLE p1_hash PARTITION OF part_hash FOR VALUES WITH (MODULUS 3, REMAINDER 0); CREATE TABLE p2_hash PARTITION OF part_hash FOR VALUES WITH (MODULUS 3, REMAINDER 1);CREATE TABLE p3_hash PARTITION OF part_hash FOR VALUES WITH (MODULUS 3, REMAINDER 2);\d+ p1_hash

3、插入数据

insert into part_hash values(generate_series(1,10000),'a');

3、查询数据

SELECT tableoid::regclass,count(*) FROM part_hash group by tableoid::regclass;

4、查看执行计划

explain select * from part_hash where order_id=1000;

混合分区表

PG分区下面也可以建立子分区构成级联模式，子分区可以有不同的分区方式，这样的分区称为混合分区

混合分区表实现

1、创建主表

create table part_hunhe (id int not null,name varchar(20),saledate timestamp) partition by range(saledate);\d+ part_hunhe

2、创建分区表

create table part_2001 partition of part_hunhe for values from ('2023-01-01 00:00:00') to ('2023-02-01 00:00:00') partition by list(name) ;create table part_2002 partition of part_hunhe for values from ('2023-02-01 00:00:00') to ('2023-03-01 00:00:00') partition by list(name) ;create table part_2003 partition of part_hunhe for values from ('2023-03-01 00:00:00') to ('2023-04-01 00:00:00') partition by list(name) ;\d+ part_2001

3、创建子分区表

create table part_3001 partition of part_2001 FOR VALUES IN ('abc');create table part_3002 partition of part_2001 FOR VALUES IN ('def');create table part_3003 partition of part_2001 FOR VALUES IN ('jkl');\d+ part_3001

3、插入数据

insert into part_hunhe values(random() * 10000,'abc','2023-01-01 08:00:00');insert into part_hunhe values(random() * 10000,'def','2023-01-01 08:00:00');\d+ part_3001

4、查看数据

SELECT tableoid::regclass,* FROM part_hunhe;

5、查看执行计划

explain select * from part_hunhe where name='abc';

PostgreSQL分区表总结

不支持interval分区，没有自带的自动新增分区功能

分区表的分区本身也是表，主表不存储数据，分区表存储数据

truncate，vacuum，analyze主表会执行所有分区。truncate only不能在主表上执行，但可以在存数据的分区表上执行，仅清除这个分区表

range，hash分区的分区键可以有多个列，list分区的分区键只能是单个列或表达式

default分区表会接收不在声明的范围中的数据;如果没有default分区，插入范围外的数据会直接报错

如果要新增分区，需要注意default分区中是否有这个新增分区的数据

partition of创建的分区会自动创建主表上定于的索引、约束、行级触发器

欢迎持续关注CUUG PostgreSQL技术大讲堂。