第50讲：PostgreSQL分区表管理完全指南
发表时间: 2024-04-28 17:30
PostgreSQL从小白到专家，是从入门逐渐能力提升的一个系列教程，内容包括对PG基础的认知、包括安装使用、包括角色权限、包括维护管理、、等内容，希望对热爱PG、学习PG的同学们有帮助，欢迎持续关注CUUG PG技术大讲堂。
第50讲：PG分区表管理
内容1：数据分区
内容2：表继承
内容3：表分区
数据分区
分区将表拆分为多个表，并且通常以一种访问表的应用程序注意不到任何差异的方式完成。
PG V10之前的版本：继承表+约束+规则或触发器。
PG V10特性：分区表特性，管理分区方便，数据插入效率高。
表继承
例：创建cities父表，capitals子表继承于cities。
postgres=# create table cities(name text,population float,altitude int);
CREATE TABLE
postgres=# create table capitals(state char(2)) inherits(cities);
CREATE TABLE
inherits就是继承表的关键字，在创建子表时使用。
使用\d+元命令分别查看父表与子表：
Child Tables:capitals -----父表最后显示子表名称
Inherits:cities -----子表最后显示继承于附表
表继承注意事项
1.查看数据：查询父表可看子表，查询子表仅看子表。如果在父表中只想查询父表的数据,需要添加only关键字。
2.删除数据：如果删除父表的数据,那么子表和父表的数据都会被删除,如果删除子表的数据,则不会删除父表的数据。
3.字段继承：一个子表可以从多个父表继承，当同名字段出现在多个父表中(或者父表和子表中)，这些字段会被融合(此时字段类型必须相同，否则会抛出一个错误)。
4.约束继承：父表的检查约束和非空约束会被子表继承。其他约束(如唯一约束，主键，外键)则不会被继承。
5.索引继承：索引将不会被继承。
使用表继承做范围分区(V10之前)
1.创建父表，所有的分区都从它继承。这个表中没有数据，不要在这个表上定义任何检查和约束，除非你想约束所有的分区，同样在其上定义任何索引和约束也没有任何意义。
2.创建几个子表，每个表都是从父表继承，通常这些表不会增加任何字段，我们把子表称为分区，实际上他们就是普通的pg表。
create table p(id int, info text, crt_time timestamp);
create table c1(like p) inherits(p);
create table c2(like p) inherits(p);
create table c3(like p) inherits(p);
3.给分区表增加约束，定义每个分区允许的键值。
alter table c1 add constraint ck check (crt_time>='2018-04-04' and crt_time<'2018-05-04');
alter table c2 add constraint ck check (crt_time>='2018-05-04' and crt_time<'2018-06-04');
alter table c3 add constraint ck check (crt_time>='2018-06-04' and crt_time<'2018-07-04');
4.对于每个分区，在关键字段上创建索引。
(可选操作，由于索引不继承，因此根据需要选择性创建索引)
5.创建触发器调用的函数。
create or replace function ins_tg() returns trigger as $$
declare
begin
if NEW.crt_time>='2018-04-04'and NEW.crt_time<'2018-05-04' then
insert into c1(id, info, crt_time) values(NEW.*);
elseif NEW.crt_time>='2018-05-04' and NEW.crt_time<'2018-06-04' then
insert into c2(id, info, crt_time) values(NEW.*);
elseif NEW.crt_time>='2018-06-04' and NEW.crt_time<'2018-07-04' then
insert into c3(id, info, crt_time) values(NEW.*);
else
raise exception 'crt_time overflow.';
end if;
return null;
end;
$$language plpgsql strict;
6.创建触发器。
create trigger tg1 before insert on p for each row execute procedure ins_tg();
7.插入测试。
insert into p(id, info, crt_time) values(1, 'test', '2018-06-23’);
select * from c3; -----有1条数据
select * from p; -----有1条数据
select * from only p; -----查询不到数据
分区表(V10)
分区表特性是PG V10新加的一个很重要的特性。
PG V10的分区特性也是在内置继承表的基础上实现的，所以创建的分区实质上也是普通的表结构。目前PG V10支持范围分区和列表分区。暂不支持哈希分区。
范围分区—创建主表
创建主表语法：
CREATE TABLE 表名 ( [{ 列名称 数据_类型} [, ... ] ] )
PARTITION BY RANGE ( [{ 列名称 } [, ...] ] );
范围分区的KEY值可由多个字段组成(最多32个字段)。
postgres=# create table test(n int) partition by range(n);
CREATE TABLE
范围分区—创建分区
创建分区语法：
CREATE TABLE 表名 PARTITION OF 主表 FOR VALUES
FROM{ ( 表达式 [, ...] ) | MINVALUE } [, ...]
TO { ( 表达式 [, ...] ) | MAXVALUE } [, ...] [ TABLESPACE 表空间名 ];
postgres=# create table test_1 partition of test for values from (MINVALUE) to (10);
CREATE TABLE
postgres=# create table test_2 partition of test for values from (10) to (100);
CREATE TABLE
postgres=# create table test_3 partition of test for values from (100) to (1000);
CREATE TABLE
postgres=# create table test_4 partition of test for values from (1000) to (10000);
CREATE TABLE
范围分区—查看分区相关信息
使用元命令\d+ 查看主表相关信息：
Partition key : RANGE (n)
Partitions: test_1 FOR VALUES FROM (MINVALUE)TO (10)，
test_2 FOR VALUES FROM(10) To (100),
test_3 FOR VALUES FROM(100)To (1000),test_4 FOR VALUES FROM (1000) TO (10000)
使用元命令\d+查看分区相关信息：
Partition of: test FOR VALUES FROM (MINVALUE) T0 (10)
Partition constraint: ((n lS NOT NULL)AND (n< 10))
范围分区—插入数据测试
插入1w条数据：
postgres=# insert into test select generate_series(0, 9999);
INSERT 0 10000
仅查询主表：
select * from only test;
n
---
(0 rows)
带条件查询分区test_1：
select * from test_1 where n>5;
n
---
6
7
8
9
(4 rows)
带条件查询分区test_2：
select * from test_2 where n<15;
n
----
10
11
12
13
14
(5 rows)
范围分区—索引测试
在主表创建索引：
postgres=# create index test_index on test(n);
ERROR: cannot create index on partitioned table “test“
--------无法创建索引
在子表创建索引：
postgres=# create index test1_index on test_1(n);
CREATE INDEX
列表分区
创建主表语法：
CREATE TABLE 表名 ( [{ 列名称 数据_类型} [, ... ] ] )
PARTITION BY LIST( { 列名称 } ); ---列表的KEY只支持一个字段
创建分区语法：
CREATE TABLE 表名 PARTITION OF 主表 FOR VALUES
IN ( 表达式 [, ...] ) [ TABLESPACE 表空间名 ];
列表分区与范围分区
范围分区就是根据数据库表中某一字段的值的范围来划分分区。
列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。
ATTACH与DETACH操作
ATTACH操作：
ATTACH操作是把和主表有相同表结构的主表变成该主表的一个分区：
范围分区：
ALTER TABLE 主表名 ATTACH PARTITION 表名 FOR VALUES
FROM{ ( 表达式 [, ...] ) | MINVALUE } [, ...] TO { ( 表达式 [, ...] ) | MAXVALUE } [, ...];
列表分区：
ALTER TABLE 主表名 ATTACH PARTITION 表名 FOR VALUES IN ( 表达式 [, ...] );
在对普通表进行ATTACH操作时，如果该普通表中有数据，则表中的数据会进行校验是否符合约束条件。
DETACH操作是把主表的分区变成普通表，是ATTACH的反操作：
语法：
ALTER TABLE 主表名 DETACH PARTITION 分区名;
分区和约束排除
约束排除(Constraint exclusive)是一种查询优化技术，在应用在分区特性中，可以提高分区表检索性能，这也是使用分区特性关键的一点。
排除约束在系统中可以设置开启或关闭，使用参数constraint_exclusion进行设置。
注：constraint_exclusion的默认值是partition，是介于off和on之间的一个中间值，用来只对继承表和分区表做检查约束，on是对所有的表做检查约束，off是不做检查约束。
在PG V10中，使用分区表一定不要禁用constraint_exclusion，否则分区将失去其意义。
分区相关系统表与函数
pg_partitioned_table 记录主表信息的系统表：
partrelid oid 主表OID
partstrat "char" 表分区类型
partnatts smallint KEY字段数
partattrs int2vector KEY字段序号
partclass oidvector KEY字段数据类型操作符
partcollation oidvector KEY字段数据类型排序规则
partexprs pg_node_tree lnformation on partition columns
获取系统信息(分区函数)：
①pg_get_partkeydef (oid relid) -- 根据主表OID返回分区类型及KEY：
select pg_get_partkeydef('test'::regclass);
②
pg_get_partition_constraintdef (oid relid) -- 根据分区OID获取分区约束条件：
select
pg_get_partition_constraintdef('test_1'::regclass);
PG V11新特性
分区表增加哈希分区
分区表支持创建主键、外键、索引
分区表支持UPDATE分区键
分区表增加 Default Partition
-->> 以上就是【PostgreSQL从小白到专家】第50讲：PG分区表管理 的内容，欢迎一起探讨交流--CUUG
鲁公网安备37020202000738号