PG中的分区表

0    1235    7

Tags:

👉 本文共约7323个字,系统预计阅读时间或需28分钟。

表分区简介

http://postgres.cn/docs/13/ddl-partitioning.html

  在数据库日渐庞大的今天,为了方便对数据库数据的管理,比如按时间,按地区去统计一些数据时,基数过于庞大,多有不便。很多商业数据库都提供分区的概念,按不同的维度去存放数据,便于后期的管理,PostgreSQL也不例外。

  PostgresSQL分区的意思是把逻辑上的一个大表分割成物理上的几块儿。分区不仅能带来访问速度的提升,关键的是,它能带来管理和维护上的方便。

  分区的具体好处是:

  • 某些类型的查询性能可以得到极大提升。
  • 更新的性能也可以得到提升,因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里,那么在索引上的读和写都会产生更多的磁盘访问。
  • 批量删除可以用简单的删除某个分区来实现。
  • 可以将很少用的数据移动到便宜的、转速慢的存储介质上。

  PostgreSQL 有两种父、子表关系:分区(partition)和继承(inherit)。

  在PG里,表分区是通过表继承来实现的,一般都是建立一个主表,里面是空,然后每个分区都去继承它。无论何时,都应保证主表里面是空的。 在PG 10之前,只能通过表继承来实现分区,从PG 10开始,可以通过DDL语句来直接创建分区表(内部原理也是通过继承来实现),这被称为声明式分区表(declaratively patitioned table)或内置分区表。PG 10仅支持范围分区和列表分区,尚未支持散列Hash分区,PG 11支持Hash分区。

  小表分区不实际,表在多大情况下才考虑分区呢?PostgresSQL官方给出的建议是:当表本身大小超过了机器物理内存的实际大小时(the size of the table should exceed the physical memory of the database server),可以考虑分区。

分区表注意事项:

  • 不支持全局的唯一、主键、排除、外键约束,只能在对应的分区建立这些约束。

  • 索引只能在分区中创建,在主表创建不能继承到分区中。

  • 更新数据时不能进行数据跨分区移动,否则会报错。

  • 分区表继承特性的限制:

​ i.分区除了主表外,不能继承其他父表;

​ ii.一个普通表不能继承分区表主表。

传统方式实现分区

参考:https://www.xmmup.com/pgzhongdebiaojichengheonlyguanjianci.html

创建分区表

1 创建主表

2 创建分区表

查看分区:

3 分区键上建索引

4 创建触发器函数

对于开发人员来说,希望数据库是透明的,只管 insert into tbl_partition。对于数据插向哪个分区,则希望由DB决定。这点,ORACLE实现了,但是PG不行,需要前期人工处理下。

说明:如果不想丢失数据,上面的ELSE 条件可以改成 INSERT INTO tbl_partition_error_join_date VALUES (NEW.*); 同时需要创建一张结构和tbl_partition 一样的表tbl_partition_error_join_date,这样,错误的join_date 数据就可以插入到这张表中而不是报错了。

5 创建触发器

查看分区表

注意:通过这种方式创建的分区表,视图pg_partitioned_table不显示数据。

1 查看所有表

2 查看主表

3 查看分区表

测试

插入数据

查看主表数据

查看分区表数据

PG 10 新特性

PG10之前实现分区表功能,基本是根据“继承表+约束+规则或触发器”实现,相对于之前的分区实现方式,PG10的分区特性有以下优势:
1)管理分区方便
2)数据插入效率高

注意:

  • 主表和分区分别单独创建
  • 范围分区支持多个字段组成的KEY
  • 列表分区的KEY只能有一个字段

创建主表语法:

创建分区语法:

参数说明:
// FROM … TO 表示分区的起始值和结束值。
// MINVALUE / MAXVALUE 表示无限小值和无限大值。
// 默认FROM后面的值是包括值分区的约束内,TO后面的值不包括。

范围分区

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
PG中的分区表后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部