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

数据库 2024/12/29 佚名

3 2 1

帝王谷资源网 Design By www.wdxyy.com

简介

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

分区的优点

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

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

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

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

分区实现原理

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

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

原分区用法

以继承表的方式实现：

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);

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

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 来控制，

只有三个值

 constraint_exclusion = on

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

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

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

如：

select *from tbl where a = 12345;

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

如何实现数据分发"" src="/UploadFiles/2021-04-09/20210106113120.jpg">

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

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

PostgreSQL 10.x LIST分区方案

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 10.x RANGE分区

创建RANGE分区

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 分区注意事项

语法

1、创建主表

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

2、创建分区

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(可以查看系统表得到)

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 中:

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;      
        }        
……

把它修改一下，增加：

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 中执行：

select id, name from a8;

后台出现：

length of list: 2

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

PostgreSQL,LIST,RANGE,表分区

标签：

PostgreSQL,LIST,RANGE,表分区

帝王谷资源网 Design By www.wdxyy.com

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

帝王谷资源网 Design By www.wdxyy.com

评论“PostgreSQL LIST、RANGE 表分区的实现方案”

暂无评论...

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

简介

分区的优点

分区实现原理

如何实现分区过滤？

PostgreSQL 10.x LIST分区方案

Explain 执行sql解析计划

PostgreSQL 10.x RANGE分区

PostgreSQL 分区注意事项

PostgreSQL,LIST,RANGE,表分区

在postgresql中运行sql文件并导出结果的操作

在postgresql中通过命令行执行sql文件

评论“PostgreSQL LIST、RANGE 表分区的实现方案”

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

更新日志

友情链接

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

简 介

分区的优点

分区实现原理

如何实现分区过滤？

PostgreSQL 10.x LIST分区方案

Explain 执行sql解析计划

PostgreSQL 10.x RANGE分区

PostgreSQL 分区注意事项

PostgreSQL,LIST,RANGE,表分区

在postgresql中运行sql文件并导出结果的操作

在postgresql中通过命令行执行sql文件

评论“PostgreSQL LIST、RANGE 表分区的实现方案”

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

更新日志

友情链接

简介