当前位置: hcjggs->postgresql > PostgreSQL技术大讲堂 - 第50讲:PG分区表管理

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

2024-04-29作者:hcjggs来源:www.hcjggs.com


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客服

  • 11月18日直播!杭州峰会大咖晚宴煮酒论英雄+PG技术大讲堂(34)
  • 2023年12月,PostgreSQL认证培训红头文件【工信部人才交流中心】
  • 2024年第13届PostgreSQL中国技术大会来啦!
  • DB-Engines:PostgreSQL is the DBMS of the Year 2023
  • PostgreSQL技术大讲堂 - 第44讲:pg流复制部署
  • PostgreSQL技术大讲堂 - 第45讲:poc-tpcc测试
  • PostgreSQL技术大讲堂 - 第46讲:poc-tpch测试
  • PostgreSQL技术大讲堂 - 第47讲:JMETER工具使用
  • PostgreSQL技术大讲堂 - 第48讲:PG高可用实现keepalived
  • PostgreSQL技术大讲堂 - 第50讲:PG分区表管理
  • 2024-02-02,恭喜CUUG 刘同学通过Oracle考试获得OCP 19c证书
  • OCP认证能不能在家中考试,不去VUE考点考试吗?
  • 恭喜CUUG Guo同学以较高分数通过19c OCM认证考试!
  • 3月30日,工信部人才交流中心PostgreSQL认证考试顺利结束
  • 2024年4月8日,工信人才发布红头文件:PostgreSQL数据库管理人才研修与评测班
  • 恭喜CUUG入选2024年工业和信息化重点领域人才能力评价支撑机构
  • 天津职业技术师范大学《PolarDB开源数据库工作室》授牌仪式顺利完成
  • 温州大学国产开源数据库工作室成功举办PostgreSQL技能培训活动
  • oracle ocp证书有效期多长时间
  • 5月16日,开源驱动教育创新研讨会(青岛站)成功举办-CUUG
  • 5月17日,PolarDB开源数据库沙龙(青岛站)成功举办-CUUG
  • PostgreSQL技术大讲堂 - 第52讲:与德哥背后的男人们聊如何实现自动性能调优
  • 11月27日,CUUG新鲜出炉的Oracle DB 19C OCP证书
  • Oracle 19c OCM认证好考吗?CUUG OCM成绩公布
  • postgresql技术大讲堂 - 第40讲:数据库不完全恢复
  • 1月17日阿里云PolarDB开发者大会PolarDB DevCon
  • 2024-1-12,恭喜CUUG 王同学获得Oracle OCP证书
  • 温州大学 - 开源国产数据库工作室成立揭牌仪式圆满结束
  • postgresql技术大讲堂 - 第39讲:数据库完全恢复
  • PostgreSQL技术大讲堂 - 第41讲:表空间备份与恢复
  • PostgreSQL技术大讲堂 - 第42讲:pg_rman部署与使用
  • PostgreSQL技术大讲堂 - 第43讲:流复制原理
  • 5月25日,温州大学49名学生参加工信人才PostgreSQL认证考试!
  • 5月30日,PG中级证书来了!工信人才PostgreSQL管理员认证证书!
  • 6月1日,汇华学院12名学生参加工信人才PostgreSQL认证考试!
  • PG技术大讲堂 - 第55讲:通义大模型+向量数据库实现AI的外脑
  • PostgreSQL技术大讲堂 - 第53讲:老陈与德哥开讲PostgreSQL 17新特性
  • PostgreSQL技术大讲堂 - 第54讲:如何在上线前精准评估PG SQL性能
  • 有大奖!第13届PostgreSQL中国技术大会:聚焦云端创新,汇聚智慧共享
  • PolarDB开源社区走进金蝶,开源数据库沙龙成功举办!
  • 阿里云PolarDB再获顶会SIGMOD最佳论文奖
  • 阿里云斩获国际数据库顶会ICDE 2024最佳论文
  • 腾讯云数据库TDSQL荣获深圳市科技进步奖一等奖
  • Oracle OCP认证还值得考吗 考OCP证书需要门槛吗
  • 阿里云PolarDB开发者大会圆满结束,CUUG两次获奖
  • 2024年首张Oracle OCP证书-CUUG胡同学
  • 北京培黎职业学院 - PolarDB开源国产数据库工作室成立揭牌 - CUUG
  • 今天(5月6日),CUUG 赵同学收到19c OCM认证考试证书!
  • Oracle数据库加入AI功能,Database 23c改名为Database 23ai
  • PostgreSQL技术大讲堂 - 第51讲:老陈与德哥聊一聊数据库调优