PG中的分区表

0    628    6

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

简介

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后面的值不包括。

范围分区

多个key:

n1范围从0到10,n2范围从0到100。

示例

列表分区

绑定分区

分区表支持把普通表绑定成父表的一个分区,也支持把分区解绑为普通表。

若普通表中有数据,ATTACH操作时,默认会做数据校验。

查询

获取系统信息(系统表):
pg_partitioned_table 记录主表信息的系统表:

PG中的分区表

分区的信息记录在pg_class相关的字段中:
PG中的分区表

PG 11新特性

PostgreSQL 11 为分区表功能提供更多的改进。这些特性包括:hash 分区、索引增强、DML改进,以及性能优化:faster partition pruning、run-time partition pruning,、partition-wise join。

  • pg11新增了hash partition
  • pg11中可以建立default partition(默认分区)
  • pg11中可以对partition key进行更新
  • pg11中在partitioned table(主表)上建立索引,索引会自动建立在partition(子表)上
  • pg11中可以在partitioned table上建立unique constraints
  • 在pg11中attach分区时,会自动给该新分区建立唯一约束和索引
  • pg_partition_tree函数,返回分区表详细信息,例如分区名称、上一级分区名称、是否叶子结点、层级,层级为零表示顶层主表。
  • pg_partition_root函数用于当已知partition时查询出分区表顶层主表的名字
  • pg_partition_ancestors返回上层分区名称,包括本层分区名称。

hash分区语法

哈希分区支持多列分区,下面给出示例:

示例2:

默认分区

PostgreSQL 11新特性,防止插入失败,对于不符合分区约束的数据将会插入到默认分区。目前,range/list支持默认分区,hash分区不支持

语法:

示例:

默认分区可以防止插入失败:

多级分区

ATTACH/DETACH 分区

语法:

以hash分区示例进行演示

detach 示例:

attach 示例:

外部表做为分区表

简单介绍如何添加外部表作为分区表,另外还有10版本存在的几个疑问是否得到解决,详见示例。

示例:

索引增强

主要以下改进:

  1. 分区主表可以有索引
  2. 分区主表可以创建唯一约束,需要包含分区键(示例 2)
  3. 分区主表可以创建外键,但是不能参照分区表创建外键(示例 3)
  4. 分区主表可以创建 FOR EACH ROW 触发器

前两条换句话说,可以创建主键

示例 1:

在以前的版本,主表不能创建索引,需要单独在子表创建,可以发现,主表创建的索引,自动在子表创建了。

示例 2:

示例 3:

示例 4:

DML改进

有以下三处改进,给出简单示例。

UPDATE可以在分区之间移动行

如果更新的行满足之前的分区约束,这依旧在原分区,反之,则会找到应该包含该元组的分区,插入到新分区,删除旧分区记录。

INSERT/COPY可以路由到外部分区

注意:这里可以与上面的UPDATE功能一起使用,可以将元组移动到外部分区,不能从外部分区移动。

INSERT .. ON CONFLICT DO UPDATE/NOTHING

分区表也支持insert on conflict 了。

管理分区

移除数据/分区

实现分区表之后,我们就可以很容易地移除不再使用的旧数据了,最简单的方法就是:

这样可以快速移除大量数据,而不是逐条删除数据。

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

另一个推荐做法是将分区从分区表中移除,但是保留访问权限。

和直接DROP 相比,该方式仅仅是使子表脱离了原有的主表,而存储在子表中的数据仍然可以得到访问,因为此时该表已经被还原成一个普通的数据表了。这样对于数据库的DBA来说,就可以在此时对该表进行必要的维护操作,如数据清理、归档等,在完成诸多例行性的操作之后,就可以考虑是直接删除该表(DROP TABLE),还是先清空该表的数据(TRUNCATE TABLE),之后再让该表重新继承主表。

增加分区

我们可以像之前那样增加一个分区

同时,需要修改触发器函数,将插入条件改成相应的值。

说明:创建触发器函数时,最好把插入条件写更未来一点,比如多写十年,这样以后增加新分区时就不需要重新创建触发器函数了,也可以避免一些不必要的错误。

绑定分区

另外,还可以如下增加新的分区:

约束排除

约束排除(Constraint exclusion)是一种查询优化技巧,应用在分区特性中,可以提高分区表检索性能,这也是使用分区特性关键的一点。

注:constraint_exclusion的默认值是partition,是介于off和on之间的一个中间值,用来只对继承表和分区表做检查约束,on是对所有的表做检查约束,off是不做检查约束。

确保postgresql.conf 里的配置参数constraint_exclusion 是打开的。没有这个参数,查询不会按照需要进行优化。这里我们需要做的是确保该选项在配置文件中没有被注释掉。

如果没有约束排除,查询会扫描tbl_partition 表中的每一个分区。打开了约束排除之后,规划器将检查每个分区的约束然后再试图证明该分区不需要被扫描,因为它不能包含任何符合WHERE子句条件的数据行。如果规划器可以证明这个,它就把该分区从查询规划里排除出去。

可以使用EXPLAIN 命令显示一个规划在constraint_exclusion 关闭和打开情况下的不同:

约束排除关闭

从上面的查询计划中可以看出,PostgreSQL 扫描了所有分区。下面我们再看一下打开约束排除之后的查询计划:

约束排除开启

可以看到,PostgreSQL 只扫描四月份以后的分区表。

VACUUM 或 ANALYZE tbl_partition 只会对主表起作用,要想分析表,需要分别分析每个分区表。

    头像

    小麦苗

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

    您可能还喜欢...

    发表评论

    您的电子邮箱地址不会被公开。

    16 + 8 =

     

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

    • 麦老师QQ聊天
    • 个人邮箱
    • 点击加入QQ群
    • 个人微店

    • 回到顶部
    返回顶部