PostgreSQL技术大讲堂 - 第50讲：PG分区表管理
http://www.cuug.com.cn/uploadfile/2022-12/32793553242.jpgPostgreSQL从小白到专家，是从入门逐渐能力提升的一个系列教程，内容包括对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_range2、创建分区表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_2024013、插入数据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';
https://www.cuug.com.cn/uploadfile/2024-05/37148119400.jpg
2、通过分区表访问
explain select from p2_202402 where order_id=100;
https://www.cuug.com.cn/uploadfile/2024-05/37148122294.jpg
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_range2、创建分区表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;
https://www.cuug.com.cn/uploadfile/2024-05/37148127250.jpg
4、查看执行计划
explain select * from part_list where name='fujian';
https://www.cuug.com.cn/uploadfile/2024-05/37148130664.jpghash分区表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_hash2、创建分区表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_hash3、插入数据insert into part_hash values(generate_series(1,10000),'a');3、查询数据SELECT tableoid::regclass,count(*) FROM part_hash group by tableoid::regclass;
https://www.cuug.com.cn/uploadfile/2024-05/37148133907.jpg
4、查看执行计划
explain select * from part_hash where order_id=1000;
https://www.cuug.com.cn/uploadfile/2024-05/37148137587.jpg
混合分区表
PG分区下面也可以建立子分区构成级联模式，子分区可以有不同的分区方式，这样的分区称为混合分区
https://www.cuug.com.cn/uploadfile/2024-05/37148142000.jpg
混合分区表实现1、创建主表create table part_hunhe (id int not null,name varchar(20),saledate timestamp) partition by range(saledate);\d+ part_hunhe2、创建分区表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_20013、创建子分区表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_30013、插入数据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_30014、查看数据SELECT tableoid::regclass,* FROM part_hunhe;
https://www.cuug.com.cn/uploadfile/2024-05/37148142123.jpg
5、查看执行计划
explain select * from part_hunhe where name='abc';
https://www.cuug.com.cn/uploadfile/2024-05/37148142200.jpg
PostgreSQL分区表总结不支持interval分区，没有自带的自动新增分区功能分区表的分区本身也是表，主表不存储数据，分区表存储数据truncate，vacuum，analyze主表会执行所有分区。truncate only不能在主表上执行，但可以在存数据的分区表上执行，仅清除这个分区表range，hash分区的分区键可以有多个列，list分区的分区键只能是单个列或表达式default分区表会接收不在声明的范围中的数据;如果没有default分区，插入范围外的数据会直接报错如果要新增分区，需要注意default分区中是否有这个新增分区的数据
partition of创建的分区会自动创建主表上定于的索引、约束、行级触发器欢迎持续关注CUUG PostgreSQL技术大讲堂。
页:
[1]