postgresql表继承与分区
目标
- 掌握表继承相关知识。
- 掌握分区（V10）相关知识。
数据分区
分区将表拆分为多个表，并且通常以一种访问表的应用程序注意不到任何差异的方式完成。
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 -----子表最后显示继承于附表
表继承注意事项
- 查看数据：查询父表可看子表，查询子表仅看子表。如果在父表中只想查询父表的数据,需要添加only关键字。
- 删除数据：如果删除父表的数据,那么子表和父表的数据都会被删除,如果删除子表的数据,则不会删除父表的数据。
- 字段继承：一个子表可以从多个父表继承，当同名字段出现在多个父表中（或者父表和子表中），这些字段会被融合（此时字段类型必须相同，否则会抛出一个错误）。
- 约束继承：父表的检查约束和非空约束会被子表继承。其他约束（如唯一约束，主键，外键）则不会被继承。
- 索引继承：索引将不会被继承。
使用表继承做范围分区（V10之前）
-
创建父表，所有的分区都从它继承。这个表中没有数据，不要在这个表上定义任何检查和约束，除非你想约束所有的分区，同样在其上定义任何索引和约束也没有任何意义。
-
创建几个子表，每个表都是从父表继承，通常这些表不会增加任何字段，我们把子表称为分区，实际上他们就是普通的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); -
给分区表增加约束，定义每个分区允许的键值。
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'); -
对于每个分区，在关键字段上创建索引。
（可选操作，由于索引不继承，因此根据需要选择性创建索引） -
创建触发器调用的函数。
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; -
创建触发器。
create trigger tg1 before insert on p for each row execute procedure ins_tg(); -
插入测试。
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 VALUESFROM{ ( 表达式 [, ...] ) | MINVALUE } [, ...]TO { ( 表达式 [, ...] ) | MAXVALUE } [, ...] [ TABLESPACE 表空间名 ]; -
例
postgres=# create table test_1 partition of test for values from (MINVALUE) to (10);CREATE TABLEpostgres=# create table test_2 partition of test for values from (10) to (100);CREATE TABLEpostgres=# create table test_3 partition of test for values from (100) to (1000);CREATE TABLEpostgres=# create table test_4 partition of test for values from (1000) to (10000);CREATE TABLE
范围分区—查看分区相关信息
-
使用元命令\d+ 查看主表相关信息：
-
使用元命令\d+查看分区相关信息：
范围分区—插入数据测试
-
插入1w条数据：
postgres=# insert into test select generate_series(0, 9999);INSERT 0 10000-
仅查询主表：
-
带条件查询分区test_1：
select * from test_1 where n>5;
-
带条件查询分区test_2：
select * from test_2 where n<15;
-
-
在主表创建索引：
postgres=# create index test_index on test(n);ERROR: cannot create index on partitioned table “test“ --------无法创建索引PG12支持：
https://www.postgresql.org/docs/12/ddl-partitioning.html
-
在子表创建索引：
postgres=# create index test1_index on test_1(n);CREATE INDEX
列表分区
-
创建主表语法：
CREATE TABLE 表名 ( [{ 列名称 数据_类型} [, ... ] ] )PARTITION BY LIST( { 列名称 } ); ---列表的KEY只支持一个字段 -
创建分区语法：
CREATE TABLE 表名 PARTITION OF 主表 FOR VALUESIN ( 表达式 [, ...] ) [ TABLESPACE 表空间名 ];
列表分区与范围分区
范围分区就是根据数据库表中某一字段的值的范围来划分分区。
列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。
ATTACH与DETACH操作
-
ATTACH操作：
ATTACH操作是把和主表有相同表结构的主表变成该主表的一个分区：
范围分区：
ALTER TABLE 主表名 ATTACH PARTITION 表名 FOR VALUES
FROM{ ( 表达式 [, …] ) | MINVALUE } [, …] TO { ( 表达式 [, …] ) | MAXVALUE } [, …];
列表分区：
ALTER TABLE 主表名 ATTACH PARTITION 表名 FOR VALUES IN ( 表达式 [, …] );
在对普通表进行ATTACH操作时，如果该普通表中有数据，则表中的数据会进行校验是否符合约束条件。 -
DETACH操作:
DETACH操作是把主表的分区变成普通表，是ATTACH的反操作：
语法：
ALTER TABLE 主表名 DETACH PARTITION 分区名;
分区和约束排除
约束排除（Constraint exclusive）是一种查询优化技术，在应用在分区特性中，可以提高分区表检索性能，这也是使用分区特性关键的一点。
排除约束在系统中可以设置开启或关闭，使用参数constraint_exclusion进行设置。
注：constraint_exclusion的默认值是partition，是介于off和on之间的一个中间值，用来只对继承表和分区表做检查约束，on是对所有的表做检查约束，off是不做检查约束。
在PG V10中，使用分区表一定不要禁用constraint_exclusion，否则分区将失去其意义。
分区相关系统表与函数
pg_partitioned_table 记录主表信息的系统表：
获取系统信息（分区函数）：
-
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
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！