PG数据库分区表的学习-复合分区与单表下多表空间多磁盘目录的使用

背景

想学习一下PG对大数据量表的执行持情况. 

自己的思路是通过分区表来进行相关的数据准备. 
然后准备话时间整理学习一下相关的知识.

PG数据库原生支持的分区表类型挺多有 LIST/RANGE/HASH 等
这里主要是想使用 RANGE 类型进行学习和处理. 
语法都是类似的. 

本次学习的组要目标是 能够使用复合分区
使用tablespace 将不同分区部署到不同的物理路径上面.
提高整体的吞吐量.

分区表的创建

DROP TABLE IF EXISTS "mytabletest";

CREATE TABLE  "mytabletest" (
  "id" varchar(36) COLLATE "pg_catalog"."default" NOT NULL,
  "bizdate" date,
  "bizdocno" varchar(80) COLLATE "pg_catalog"."default",
  "datasrc" int4,
  "datatype" int4,
) PARTITION BY RANGE (bizdate)
;

CREATE TABLE mytabletest_202201 PARTITION OF mytabletest FOR VALUES FROM ('2022-01-01') TO ('2022-01-31');
CREATE TABLE mytabletest_202202 PARTITION OF mytabletest FOR VALUES FROM ('2022-02-01') TO ('2022-02-28');
CREATE TABLE mytabletest_202203 PARTITION OF mytabletest FOR VALUES FROM ('2022-03-01') TO ('2022-03-31');
CREATE TABLE mytabletest_202410 PARTITION OF mytabletest FOR VALUES FROM ('2024-10-01') TO ('2024-10-31');
CREATE TABLE mytabletest_202411 PARTITION OF mytabletest FOR VALUES FROM ('2024-11-01') TO ('2024-11-30');
CREATE TABLE mytabletest_202412 PARTITION OF mytabletest FOR VALUES FROM ('2024-12-01') TO ('2024-12-31');

默认分区和复合分区

CREATE TABLE mytabletest_202412 PARTITION OF mytabletest  default ; 

#创建不同的tablespace
#分不同分区表到不同tablespace的验证
# shell脚本:
mkdir -p pgdata/{pg01,pg02,pg03}
chown postgres pgdata/ -R

create tablespace pg01 location '/pgdata/pg01'; 
create tablespace pg02 location '/pgdata/pg02'; 
create tablespace pg03 location '/pgdata/pg03'; 


# 复合分区到不同的表空间
CREATE TABLE mytabletest_202501 PARTITION OF mytabletest FOR VALUES FROM ('2025-01-01') TO ('2025-01-31') PARTITION BY LIST(datatype);
CREATE TABLE mytabletest_202501_datatype1 PARTITION OF mytabletest_202501 FOR VALUES in ('1') tablespace pg01;
CREATE TABLE mytabletest_202501_datatype2 PARTITION OF mytabletest_202501 FOR VALUES in (2) tablespace pg02;
CREATE TABLE mytabletest_202501_datatypedefault PARTITION OF mytabletest_202501  default tablespace pg02;

分区表合并-PG17之前

# 注意需要先detach分区, 不然无法创建新的分区
ALTER TABLE mytabletest DETACH PARTITION mytabletest_202201;
ALTER TABLE mytabletest DETACH PARTITION mytabletest_202202;
ALTER TABLE mytabletest DETACH PARTITION mytabletest_202203;

# 创建一个覆盖detach分区的分区.
CREATE TABLE mytabletest_2022Q1 PARTITION OF mytabletest FOR VALUES FROM ('2022-01-01') TO ('2022-03-31');

# 注意 因为detach了, 必须使用 分区才能查询到, 使用原表无法查询. 
INSERT INTO mytabletest_2022Q1
select * from mytabletest_202201 
union all 
select * from mytabletest_202202 
union all 
select * from mytabletest_202203 ;

# 删除分区 减少空间占用.
drop table mytabletest_202201 ;
drop table mytabletest_202202 ;
drop table mytabletest_202203 ;

PG17的语法

ALTER TABLE mytabletest MERGE PARTITIONS mytabletest_202201,mytabletest_202202,mytabletest_202203   INTO PARTITION mytabletest_2022Q1