服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - PostgreSQL - PostgreSQL LIST、RANGE 表分区的实现方案

PostgreSQL LIST、RANGE 表分区的实现方案

2021-03-12 21:42将臣三代 PostgreSQL

这篇文章主要介绍了PostgreSQL LIST、RANGE 表分区的实现方案,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧

简 介

PG分区:就是把逻辑上的一个大表分割成物理上的几块。

分区的优点

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

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

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

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

分区实现原理

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

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

原分区用法

以继承表的方式实现:

?
1
2
3
4
create table tbl( a int, b varchar(10) );
create table tbl_1 ( check ( a <= 1000 ) ) INHERITS (tbl);
create table tbl_2 ( check ( a <= 10000 and a >1000 ) ) INHERITS (tbl);
create table tbl_3 ( check ( a <= 100000 and a >10000 ) ) INHERITS (tbl);

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE OR REPLACE FUNCTION tbl_part_tg()
RETURNS TRIGGER AS $$
BEGIN
 IF ( NEW. a <= 1000 ) THEN
 INSERT INTO tbl_1 VALUES (NEW.*);
 ELSIF ( NEW. a > 1000 and NEW.a <= 10000 ) THEN
 INSERT INTO tbl_2 VALUES (NEW.*);
 ELSIF ( NEW. a > 10000 and NEW.a <= 100000 ) THEN
 INSERT INTO tbl_3 VALUES (NEW.*);
 ELSIF ( NEW. a > 100000 and NEW.a <= 1000000 ) THEN
 INSERT INTO tbl_4 VALUES (NEW.*);
 ELSE RAISE EXCEPTION 'data out of range!';
 END IF;
 RETURN NULL;
END;
 $$
LANGUAGE plpgsql;
CREATE TRIGGER insert_tbl_part_tg
  BEFORE INSERT ON tbl
FOR EACH ROW EXECUTE PROCEDURE tbl_part_tg();

分区创建成功

如何实现分区过滤?

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

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

约束排除 是否使用约束排除通过postgresql.conf中参数constraint_exclusion 来控制,

只有三个值

?
1
constraint_exclusion = on

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

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

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

如:

?
1
select *from tbl where a = 12345;

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

如何实现数据分发?

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

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

PostgreSQL LIST、RANGE 表分区的实现方案

错误原因:在本地postgresql.conf 配置了 search_path = ‘$user' ,所以在使用的时候需要先创建当前用户对应的schema,如果不存在,则会提示错误

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

PostgreSQL LIST、RANGE 表分区的实现方案

PostgreSQL 10.x LIST分区方案

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
postgres=# CREATE TABLE list_parted (
postgres(# a int
postgres(# ) PARTITION BY LIST (a);
CREATE TABLE
postgres=# CREATE TABLE part_1 PARTITION OF list_parted FOR VALUES IN (1);
CREATE TABLE
postgres=# CREATE TABLE part_2 PARTITION OF list_parted FOR VALUES IN (2);
CREATE TABLE
postgres=# CREATE TABLE part_3 PARTITION OF list_parted FOR VALUES IN (3);
CREATE TABLE
postgres=# CREATE TABLE part_4 PARTITION OF list_parted FOR VALUES IN (4);
CREATE TABLE
postgres=# CREATE TABLE part_5 PARTITION OF list_parted FOR VALUES IN (5);
CREATE TABLE
postgres=#
postgres=# insert into list_parted values(32); --faled
ERROR: no partition of relation "list_parted" found for row
DETAIL: Failing row contains (32).
postgres=# insert into part_1 values(1);
INSERT 0 1
postgres=# insert into part_1 values(2);--faled
ERROR: new row for relation "part_1" violates partition constraint
DETAIL: Failing row contains (2).
postgres=# explain select *from list_parted where a =1;
       QUERY PLAN
-----------------------------------------------------------------
 Append (cost=0.00..41.88 rows=14 width=4)
 -> Seq Scan on list_parted (cost=0.00..0.00 rows=1 width=4)
   Filter: (a = 1)
 -> Seq Scan on part_1 (cost=0.00..41.88 rows=13 width=4)
   Filter: (a = 1)
(5 rows)

上面是LIST分区表,建表是先建主表,再建子表,子表以 PARTITION OF 方式说明和主表关系,约束条件应该就是后面的in里面。

Explain 执行sql解析计划

cost:数据库自定义的消耗单位,通过统计信息来估计SQL消耗。(查询分析是根据analyze的固执生成的,生成之后按照这个查询计划执行,执行过程中analyze是不会变的。所以如果估值和真是情况差别较大,就会影响查询计划的生成。)

rows:根据统计信息估计SQL返回结果集的行数。

width:返回结果集每一行的长度,这个长度值是根据pg_statistic表中的统计信息来计算的。

PostgreSQL LIST、RANGE 表分区的实现方案

PostgreSQL 10.x RANGE分区

创建RANGE分区

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
postgres=# CREATE TABLE range_parted (
postgres(# a int
postgres(# ) PARTITION BY RANGE (a);
CREATE TABLE
postgres=# CREATE TABLE range_parted1 PARTITION OF range_parted FOR VALUES from (1) TO (1000);
CREATE TABLE
postgres=# CREATE TABLE range_parted2 PARTITION OF range_parted FOR VALUES FROM (1000) TO (10000);
CREATE TABLE
postgres=# CREATE TABLE range_parted3 PARTITION OF range_parted FOR VALUES FROM (10000) TO (100000);
CREATE TABLE
postgres=#
postgres=# insert into range_parted1 values(343);
INSERT 0 1
postgres=#
postgres=# explain select *from range_parted where a=32425;
        QUERY PLAN
---------------------------------------------------------------------
 Append (cost=0.00..41.88 rows=14 width=4)
 -> Seq Scan on range_parted (cost=0.00..0.00 rows=1 width=4)
   Filter: (a = 32425)
 -> Seq Scan on range_parted3 (cost=0.00..41.88 rows=13 width=4)
   Filter: (a = 32425)
(5 rows)
postgres=# set constraint_exclusion = off;
SET
postgres=# explain select *from range_parted where a=32425;
        QUERY PLAN
---------------------------------------------------------------------
 Append (cost=0.00..125.63 rows=40 width=4)
 -> Seq Scan on range_parted (cost=0.00..0.00 rows=1 width=4)
   Filter: (a = 32425)
 -> Seq Scan on range_parted1 (cost=0.00..41.88 rows=13 width=4)
   Filter: (a = 32425)
 -> Seq Scan on range_parted2 (cost=0.00..41.88 rows=13 width=4)
   Filter: (a = 32425)
 -> Seq Scan on range_parted3 (cost=0.00..41.88 rows=13 width=4)
   Filter: (a = 32425)
(9 rows)

上述操作中的 a的取值范围为【0,1000)即插入值若为1000边界值,则会保存在第二个分区表中和LIST差不多,就是语法略有不同,范围表值是一个连续的范围,LIST表是单点或多点的集合。

从上面例子可以看到,显然还是走的约束排除过滤子表的方式。

constraint_exclusion = “on ,off,partition ”; 该参数为postgresql.conf中的参数

on 表示所有的查询都会执行约束排除

off 关闭,所有的查询都不会执行约束排除

partition :表示只对分区的表进行约束排除

分区列的类型必须支持btree索引接口(几乎涵盖所有类型, 后面会说到检查方法)。

更新后的数据如果超出了所在分区的范围,则会报错

PostgreSQL LIST、RANGE 表分区的实现方案

PostgreSQL 分区注意事项

语法

1、创建主表

?
1
[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]

2、创建分区

?
1
2
3
4
5
6
7
8
9
PARTITION OF parent_table [ (
 { column_name [ column_constraint [ ... ] ]
 | table_constraint }
 [, ... ]
) ] FOR VALUES partition_bound_spec
and partition_bound_spec is:
{ IN ( expression [, ...] ) -- list分区
 |
 FROM ( { expression | UNBOUNDED } [, ...] ) TO ( { expression | UNBOUNDED } [, ...] ) } -- range分区, unbounded表示无限小或无限大

语法解释

partition by 指定分区表的类型range或list指定分区列,或表达式作为分区键。

range分区表键:支持指定多列、或多表达式,支持混合(键,非表达式中的列,会自动添加not null的约束)

list分区表键:支持单个列、或单个表达式

分区键必须有对应的btree索引方法的ops(可以查看系统表得到)

?
1
select typname from pg_type where oid in (select opcintype from pg_opclass);

主表不会有任何数据,数据会根据分区规则进入对应的分区表

如果插入数据时,分区键的值没有匹配的分区,会报错

不支持全局的unique, primary key, exclude, foreign key约束,只能在对应的分区建立这些约束

分区表和主表的 列数量,定义 必须完全一致,(包括OID也必须一致,要么都有,要么都没有)

可以为分区表的列单独增加Default值,或约束。

用户还可以对分区表增加表级约束

如果新增的分区表check约束,名字与主表的约束名一致,则约束内容必须与主表一致

当用户往主表插入数据库时,记录被自动路由到对应的分区,如果没有合适的分区,则报错

如果更新数据,并且更新后的KEY导致数据需要移动到另一分区,则会报错,(意思是分区键 可以更新,但是不支持更新后的数据移出到别的分区表)

修改主表的字段名,字段类型时,会自动同时修改所有的分区

TRUNCATE 主表时,会清除所有继承表分区的记录(如果有多级分区,则会一直清除到所有的直接和间接继承的分区)

如果要清除单个分区,请对分区进行操作

如果要删除分区表,可以使用DROP TABLE的DDL语句,注意这个操作会对主表也加access exclusive lock。

补充:对PostgreSQL语法分析中 targetlist 的理解

在 gram.y 中:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
simple_select:               
      SELECT  opt_distinct  target_list         
      into_clause   from_clause   where_clause         
      group_clause   having_clause  window_clause         
        {       
          SelectStmt *n = makeNode(SelectStmt);     
          n->distinctClause = $2;     
          n->targetList = $3;     
          n->intoClause = $4;     
          n->fromClause = $5;     
          n->whereClause = $6;     
          n->groupClause = $7;     
          n->havingClause = $8;     
          n->windowClause = $9;     
          $$ = (Node *)n;     
        }       
……

把它修改一下,增加:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
simple_select:               
      SELECT  opt_distinct  target_list         
      into_clause   from_clause   where_clause         
      group_clause   having_clause  window_clause         
        {       
          SelectStmt *n = makeNode(SelectStmt);     
          n->distinctClause = $2;     
          n->targetList = $3;     
          n->intoClause = $4;     
          n->fromClause = $5;     
          n->whereClause = $6;     
          n->groupClause = $7;     
          n->havingClause = $8;     
          n->windowClause = $9;     
          $$ = (Node *)n;
          fprintf(stderr,"length of list: %d\n", n->targetList->length);     
        }     
……

psql 中执行:

?
1
select id, name from a8;

后台出现:

?
1
length of list: 2

以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。如有错误或未考虑完全的地方,望不吝赐教。

原文链接:https://blog.csdn.net/yaoqiancuo3276/article/details/80208178

延伸 · 阅读

精彩推荐
  • PostgreSQLpostgresql 中的to_char()常用操作

    postgresql 中的to_char()常用操作

    这篇文章主要介绍了postgresql 中的to_char()常用操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...

    J符离13432021-04-12
  • PostgreSQL深入理解PostgreSQL的MVCC并发处理方式

    深入理解PostgreSQL的MVCC并发处理方式

    这篇文章主要介绍了深入理解PostgreSQL的MVCC并发处理方式,文中同时介绍了MVCC的缺点,需要的朋友可以参考下 ...

    PostgreSQL教程网3622020-04-25
  • PostgreSQLPostgresql查询效率计算初探

    Postgresql查询效率计算初探

    这篇文章主要给大家介绍了关于Postgresql查询效率计算的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Postgresql具有一定的参考学习价...

    轨迹4622020-05-03
  • PostgreSQLPostgreSQL标准建表语句分享

    PostgreSQL标准建表语句分享

    这篇文章主要介绍了PostgreSQL标准建表语句分享,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...

    码上得天下7962021-02-27
  • PostgreSQLpostgresql 数据库中的数据转换

    postgresql 数据库中的数据转换

    postgres8.3以后,字段数据之间的默认转换取消了。如果需要进行数据变换的话,在postgresql数据库中,我们可以用"::"来进行字段数据的类型转换。...

    postgresql教程网12482021-10-08
  • PostgreSQLPostgresql开启远程访问的步骤全纪录

    Postgresql开启远程访问的步骤全纪录

    postgre一般默认为本地连接,不支持远程访问,所以如果要开启远程访问,需要更改安装文件的配置。下面这篇文章主要给大家介绍了关于Postgresql开启远程...

    我勒个去6812020-04-30
  • PostgreSQL分布式 PostgreSQL之Citus 架构

    分布式 PostgreSQL之Citus 架构

    节点 Citus 是一种 PostgreSQL 扩展,它允许数据库服务器(称为节点)在“无共享(shared nothing)”架构中相互协调。这些节点形成一个集群,允许 PostgreSQL 保存比单...

    未知802023-05-07
  • PostgreSQLRDS PostgreSQL一键大版本升级技术解密

    RDS PostgreSQL一键大版本升级技术解密

    一、PostgreSQL行业位置 (一)行业位置 在讨论PostgreSQL(下面简称为PG)在整个数据库行业的位置之前,我们先看一下阿里云数据库在全球的数据库行业里的...

    未知1192023-05-07