,,PostgreSQL之分区表(partitioning)

,,PostgreSQL之分区表(partitioning)

通过合理的设计,可以选择一定的规则将大表分成多个子表,不重不缺。这就是传说中的分区。比如我们可以按时间划分,每天一个子表。比如我们可以用其他一些字段来划分,简而言之,我们可以把它分成几个部分,提高查询的效率。

PostgreSQL有一个很有用的函数,分区表,或者说分区。当一个表中有很多记录,几千万甚至更多的时候,我们实际上需要把它分成子表。一张巨大的桌子,就像一个水果仓库,无数的苹果、桃子、橘子杂乱地堆放着,不方便寻找,性能降低。把仓库分成三个分区,苹果、桃子、橙子分别放在不同的桌子上,这样比较合理。一个大桌子变成了三个小桌子的集合。

通过合理的设计,可以选择一定的规则将大表分成多个子表,不重不缺。这就是传说中的分区。比如我们可以按时间划分,每天一个子表。比如,我们可以根据其他领域来划分。简而言之就是分成几部分,提高查询效率。

这个分区表的功能是怎么实现的?

1建立大表。

2创建分区继承

3定义规则还是触发器?

下面根据一个简单的例子来描述这个过程。我们按照60分以下和不低于60分把学生分成两个子表。

1 建立大表

创建表student (student_id bigserial,name varchar(32),score smallint)

2 创建分区继承。

创建表student _ qualified(CHECK(score=60))INHERITS(student);

创建表student_nqualified (CHECK(分数60))INHERITS(student);

创建了两个分区表student_qualified和student _ qualified,它们继承了大表student的所有字段,并设置了约束,即检查条件。

3 定义Rule或者Trigger。

虽然我们定义了检查条件,但是在向student中插入数据时,PostgreSQL无法根据分数是否低于60插入正确的子表。原因是您没有定义这个规则来告诉数据这样做。我们需要定义规则或触发器来将数据插入正确的分区表。

先看规则的定义:

创建或替换规则insert_student_qualified

学生插页上的

其中得分=60

改为做

插入student_qualified值(新。*);

创建或替换规则insert_student_nqualified

学生插页上的

其中得分60

改为做

插入到student_nqualified值(新。*);

这两个规则告诉PostgreSQL,在向通用表中插入数据时,如果分数为60,则插入student_qualified,如果分数为60,则插入student _ qualified。注意这个划分一定要准确。如果我们不小心把=60条件的“=”丢掉了,等于60分的记录就会被录入大表学生,这个是没有的。

我们插入一些记录:

插入学生(姓名,分数)值(' Jim ',77);

插入学生(姓名,分数)值(' Frank ',56);

插入到学生(姓名,分数)值(' Bean ',88);

插入学生(姓名,分数)值(' John ',47);

插入学生(姓名,分数)值(' Albert ',' 87 ');

插入到学生(姓名,分数)值中(“Joey”,“60”);

我们来看看数据分布,是否分布到正确的分区表:

SELECT p.relname,c.tableoid,c.*

来自学生c,pg_class p

其中c.tableoid=p.oid

输出如下所示:

我们可以看到,虽然我们插入的是一个大表,但是数据有对应的分区子表。满足我们的期望。同时也不影响查询。

Rule是一种导流方式,TRIGGER也可以让正确的数据流向正确的分区子表。

我们先定义一个函数。

创建或替换函数student_insert_trigger()

将触发器返回为

$$

开始

IF(NEW.score=60)然后

插入student_qualified值(新。*);

其他

插入到student_nqualified值(新。*);

结束IF;

返回NULL

结束;

$$

语言plpgsql

然后定义触发器,该触发器将在插入学生时触发:

创建触发器insert_student

在插入学生之前

对于每一行

执行过程学生_插入_触发器();

我们首先通过删除桌生,测试下引发方式。

下降表学生级联

创建表学生(student_id bigserial,name varchar(32),score smallint);

创建表student _ qualified(CHECK(分数=60))INHERITS(学生);

创建表学生_不合格(检查(分数60))继承(学生);

然后执行定义功能和定义引发的语句。就可以查看了。

为了确认我们的触发器的确触发了,我们打开存储过程的统计开关:

在postgresql.conf中,找到轨道_功能,改成全部

轨道_功能=全部

插入之前先看下函数学生_插入_触发器的统计信息:

执行插入:

插入学生(姓名,分数)值(‘吉姆’,77);

插入学生(姓名,分数)值(‘弗兰克’,56);

插入到学生(姓名,分数)值(‘豆’,88);

插入学生(姓名,分数)值(‘约翰’,47);

插入学生(姓名,分数)值(‘艾伯特’,‘87’);

插入到学生(姓名,分数)值中(《乔伊》,《60》);

插入后,看下函数学生_插入_触发器的统计信息

我们看到引发触发了6次。

执行下查询:

SELECT p.relname,c.tableoid,c.*

来自学生丙、pg _级

其中c.tableoid=p.oid

输出如下:

参考文献

一个一种数据库系统文档

郑重声明:本文由网友发布,不代表盛行IT的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。

留言与评论(共有 条评论)
   
验证码: