怎么在PostgreSQL数据库中实现一个列表与范围分区表 - 行业资讯

　　介绍

今天就跟大家聊聊有关怎么在PostgreSQL数据库中实现一个列表与范围分区表,可能很多人都不太了解,为了让大家更加了解,小编给大家总结了以下内容,希望大家根据这篇文章可以有所收获。

分区的优点

1。某些类型的查询性能得到提升

2。更新的性能也可以得到提升,因为某块的索引要比在整个数据集上的索引要小。

3。批量删除可以通过简单的删除某个分区来实现。

4。可以将很少用的数据移动到便宜的,转速慢的存储介质上。

分区实现原理

10。x版本之前PG表分区的实现原理:PG中是通过表的继承来实现的,建立一个主表,里面是空的,然后每个分区去继承它。无论何时,该主表里面都必须是空的

官网建议:只有当表本身大小超过了机器物理内存的实际大小时,才考虑分区。

原分区用法

以继承表的方式实现:

create table 资源(,a , int, b varchar (10),),, 　　create table tbl_1 (, check (, a & lt;=, 1000,),), INHERITS (台),, 　　create table tbl_2 (, check (, a & lt;=, 10000,以及a 在1000年,),),INHERITS (台); 　　create table tbl_3 (, check (, a & lt;=, 100000,以及a 在10000,),),INHERITS (台),

再通过创建触发器或者规则,实现数据分发,只需要向子表插入数据则会自动分配到子表中

create 或是REPLACE FUNCTION tbl_part_tg (), 　　RETURNS TRIGGER AS $ $, 　　BEGIN 　　,IF (,新只a & lt;=, 1000,), THEN 　　,INSERT INTO tbl_1 VALUES (新。*),, 　　,ELSIF (,新只a 祝辞,1000,以及NEW.a & lt;=, 10000,), THEN 　　,INSERT INTO tbl_2 VALUES (新。*),, 　　,ELSIF (,新只a 祝辞,10000,以及NEW.a & lt;=, 100000,), THEN 　　,INSERT INTO tbl_3 VALUES (新。*),, 　　,ELSIF (,新只a 祝辞,100000,以及NEW.a & lt;=, 1000000,), THEN 　　,INSERT INTO tbl_4 VALUES (新。*),, 　　,ELSE RAISE EXCEPTION & # 39; data out of 范围! & # 39;,, 　　,最终获得;如果; 　　,RETURN 零; 　　结束; 　　$ $大敌; 　　LANGUAGE plpgsql, 　　CREATE TRIGGER insert_tbl_part_tg 　　BEFORE 才能;INSERT 提醒tbl 　　FOR EACH ROW EXECUTE PROCEDURE tbl_part_tg ();

分区创建成功

如何实现分区过滤吗?

对于分区表来说,如果有50个分区表,对于某个条件的值如果能确定,那么很可能直接过滤掉49个分区,大大提高扫描速度,当然分区表也能放在不同的物理盘上,提高IO速度。

对于查询是怎么实现分区表过滤呢?

约束排除是否使用约束排除通过postgresql。会议中参数constraint_exclusion来控制,

只有三个值

, constraint_exclusion =,

:所有情况都会进行约束排除检查

:关闭,所有约束都不生效

分区:对分区表或者继承表进行约束排查,默认为分区

如:

select *得到tbl where a =, 12345;

首先找到主表台,然后通过台找到它的子表,找到后再对再拿着谓词条件=12345对一个个子表约束进行检查,不符合条件表就去掉不扫描,实现分区表过滤、下面简单介绍下约束排除源码逻辑。

如何实现数据分发吗?

基于规则的话,会在查询重写阶段按时替换规则生成新的插入语句,基于触发器会在插入主表前触发另外一个插入操作,这两个逻辑都比较简单,相关代码不再介绍。

错误描述:在新建分区主表时提示以下错误信息

怎么在PostgreSQL数据库中实现一个列表与范围分区表

错误原因:在本地PostgreSQL。参看配置了search_path=' $用户# 39;,所以在使用的时候需要先创建当前用户对应的模式,如果不存在,则会提示错误

解决方法:在创建表时指定创建的schemal,即可成功。

怎么在PostgreSQL数据库中实现一个列表与范围分区表

PostgreSQL 10。x列表分区方案

postgres=#, CREATE TABLE list_parted ( 　　postgres (#, a int 　　postgres (#,), PARTITION BY LIST (一); 　　CREATE 表　　postgres=#, CREATE TABLE part_1 PARTITION OF list_parted FOR VALUES 拷贝(1); 　　CREATE 表　　postgres=#, CREATE TABLE part_2 PARTITION OF list_parted FOR VALUES 拷贝(2); 　　CREATE 表　　postgres=#, CREATE TABLE part_3 PARTITION OF list_parted FOR VALUES 拷贝;(3); 　　CREATE 表　　postgres=#, CREATE TABLE part_4 PARTITION OF list_parted FOR VALUES 拷贝;(4); 　　CREATE 表　　postgres=#, CREATE TABLE part_5 PARTITION OF list_parted FOR VALUES 拷贝;(5); 　　CREATE 表　　postgres=# 　　postgres=#, insert into list_parted 价值(32);——假的　　错误:,no partition of relation “list_parted" found for 行　　细节:,Failing row contains (32)。　　postgres=#, insert into part_1 价值(1); 　　INSERT 0, 1 　　postgres=#, insert into part_1 价值(2);——假的　　错误:,new row for relation “part_1" violates partition 约束　　细节:,Failing row contains (2)。　　postgres=#, explain select *得到list_parted where a =1; 　　,,,,,,QUERY 计划　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null 　　null