PostgreSQL分区表(Table Partitioning)应用

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
简介: 一、简介  在数据库日渐庞大的今天,为了方便对数据库数据的管理,比如按时间,按地区去统计一些数据时,基数过于庞大,多有不便。

一、简介

  在数据库日渐庞大的今天,为了方便对数据库数据的管理,比如按时间,按地区去统计一些数据时,基数过于庞大,多有不便。很多商业数据库都提供分区的概念,按不同的维度去存放数据,便于后期的管理,PostgreSQL也不例外。

  PostgresSQL分区的意思是把逻辑上的一个大表分割成物理上的几块儿。分区不仅能带来访问速度的提升,关键的是,它能带来管理和维护上的方便。

  分区的具体好处是:

  • 某些类型的查询性能可以得到极大提升。
  • 更新的性能也可以得到提升,因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里,那么在索引上的读和写都会产生更多的磁盘访问。
  • 批量删除可以用简单的删除某个分区来实现。
  • 可以将很少用的数据移动到便宜的、转速慢的存储介质上。

  在PG里表分区是通过表继承来实现的,一般都是建立一个主表,里面是空,然后每个分区都去继承它。无论何时,都应保证主表里面是空的。

  小表分区不实际,表在多大情况下才考虑分区呢?PostgresSQL官方给出的建议是:当表本身大小超过了机器物理内存的实际大小时(the size of the table should exceed the physical memory of the database server),可以考虑分区。

  PG目前(9.2.2)仅支持范围分区和列表分区,尚未支持散列分区。

二、环境

系统环境:CentOS release 6.3 (Final)

PostgreSQL版本:PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit

三、实现分区

3.1 创建主表

复制代码
david=# create table tbl_partition (
david(# id integer,
david(# name varchar(20),
david(# gender boolean,
david(# join_date date,
david(# dept char(4));
CREATE TABLE
david=#
复制代码

3.2 创建分区表

复制代码
david=# create table tbl_partition_201211 (
check ( join_date >= DATE '2012-11-01' AND join_date < DATE '2012-12-01' )       
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201212 (
check ( join_date >= DATE '2012-12-01' AND join_date < DATE '2013-01-01' )      
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201301 (
check ( join_date >= DATE '2013-01-01' AND join_date < DATE '2013-02-01' )      
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201302 (
check ( join_date >= DATE '2013-02-01' AND join_date < DATE '2013-03-01' )  
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201303 (
check ( join_date >= DATE '2013-03-01' AND join_date < DATE '2013-04-01' ) 
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201304 (
check ( join_date >= DATE '2013-04-01' AND join_date < DATE '2013-05-01' )  
) INHERITS (tbl_partition);
CREATE TABLE
david=# create table tbl_partition_201305 (
check ( join_date >= DATE '2013-05-01' AND join_date < DATE '2013-06-01' )  
) INHERITS (tbl_partition);
CREATE TABLE
david=# 
复制代码

3.3 分区键上建索引

复制代码
david=# create index tbl_partition_201211_joindate on tbl_partition_201211 (join_date);
CREATE INDEX
david=# create index tbl_partition_201212_joindate on tbl_partition_201212 (join_date); 
CREATE INDEX
david=# create index tbl_partition_201301_joindate on tbl_partition_201301 (join_date);    
CREATE INDEX
david=# create index tbl_partition_201302_joindate on tbl_partition_201302 (join_date); 
CREATE INDEX
david=# create index tbl_partition_201303_joindate on tbl_partition_201303 (join_date); 
CREATE INDEX
david=# create index tbl_partition_201304_joindate on tbl_partition_201304 (join_date); 
CREATE INDEX
david=# create index tbl_partition_201305_joindate on tbl_partition_201305 (join_date); 
CREATE INDEX
david=# 
复制代码

对于开发人员来说,希望数据库是透明的,只管 insert into tbl_partition。对于数据插向哪个分区,则希望由DB决定。这点,ORACLE实现了,但是PG不行,需要前期人工处理下。

3.4 创建触发器函数

复制代码
david=# CREATE OR REPLACE FUNCTION tbl_partition_insert_trigger()                      
RETURNS TRIGGER AS $$  
BEGIN  
    IF ( NEW.join_date >= DATE '2012-11-01' AND    
         NEW.join_date < DATE '2012-12-01' ) THEN  
        INSERT INTO tbl_partition_201211 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2012-12-01' AND  
            NEW.join_date < DATE '2013-01-01' ) THEN  
        INSERT INTO tbl_partition_201212 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2013-01-01' AND  
            NEW.join_date < DATE '2013-02-01' ) THEN  
        INSERT INTO tbl_partition_201301 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2013-02-01' AND  
            NEW.join_date < DATE '2013-03-01' ) THEN  
        INSERT INTO tbl_partition_201302 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2013-03-01' AND  
            NEW.join_date < DATE '2013-04-01' ) THEN  
        INSERT INTO tbl_partition_201303 VALUES (NEW.*);  
    ELSIF ( NEW.join_date >= DATE '2013-04-01' AND  
            NEW.join_date < DATE '2013-05-01' ) THEN  
        INSERT INTO tbl_partition_201304 VALUES (NEW.*);
    ELSIF ( NEW.join_date >= DATE '2013-05-01' AND
            NEW.join_date < DATE '2013-06-01' ) THEN
        INSERT INTO tbl_partition_201305 VALUES (NEW.*); 
    ELSE  
        RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';  
    END IF;  
    RETURN NULL;  
END;  
$$  
LANGUAGE plpgsql;
CREATE FUNCTION
david=# 
复制代码

说明:如果不想丢失数据,上面的ELSE 条件可以改成 INSERT INTO tbl_partition_error_join_date VALUES (NEW.*); 同时需要创建一张结构和tbl_partition 一样的表tbl_partition_error_join_date,这样,错误的join_date 数据就可以插入到这张表中而不是报错了。

3.5 创建触发器

david=# CREATE TRIGGER insert_tbl_partition_trigger
david-#     BEFORE INSERT ON tbl_partition
david-#     FOR EACH ROW EXECUTE PROCEDURE tbl_partition_insert_trigger();
CREATE TRIGGER
david=# 

四、查看表

4.1 查看所有表

复制代码
david=# \dt
                List of relations
 Schema |         Name         | Type  |  Owner   
--------+----------------------+-------+----------
 public | tbl_partition        | table | postgres
 public | tbl_partition_201211 | table | postgres
 public | tbl_partition_201212 | table | postgres
 public | tbl_partition_201301 | table | postgres
 public | tbl_partition_201302 | table | postgres
 public | tbl_partition_201303 | table | postgres
 public | tbl_partition_201304 | table | postgres
 public | tbl_partition_201305 | table | postgres
(8 rows)

david=# 
复制代码

4.2 查看主表

复制代码
david=# \d tbl_partition
         Table "public.tbl_partition"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 id        | integer               | 
 name      | character varying(20) | 
 gender    | boolean               | 
 join_date | date                  | 
 dept      | character(4)          | 
Triggers:
    insert_tbl_partition_trigger BEFORE INSERT ON tbl_partition FOR EACH ROW EXECUTE PROCEDURE tbl_partition_insert_trigger()
Number of child tables: 7 (Use \d+ to list them.)

david=# 
复制代码

4.3 查看分区表

复制代码
david=# \d tbl_partition_201304
      Table "public.tbl_partition_201304"
  Column   |         Type          | Modifiers 
-----------+-----------------------+-----------
 id        | integer               | 
 name      | character varying(20) | 
 gender    | boolean               | 
 join_date | date                  | 
 dept      | character(4)          | 
Indexes:
    "tbl_partition_201304_joindate" btree (join_date)
Check constraints:
    "tbl_partition_201304_join_date_check" CHECK (join_date >= '2013-04-01'::date AND join_date < '2013-05-01'::date)
Inherits: tbl_partition

david=#
复制代码

五、测试

5.1 插入数据

复制代码
david=# insert into tbl_partition values (1, 'David', '1', '2013-01-10', 'TS');
INSERT 0 0
david=# insert into tbl_partition values (2, 'Sandy', '0', '2013-02-10', 'TS');
INSERT 0 0
david=# insert into tbl_partition values (3, 'Eagle', '1', '2012-11-01', 'TS');
INSERT 0 0
david=# insert into tbl_partition values (4, 'Miles', '1', '2012-12-15', 'SD');
INSERT 0 0
david=# insert into tbl_partition values (5, 'Simon', '1', '2012-12-10', 'SD');
INSERT 0 0
david=# insert into tbl_partition values (6, 'Rock', '1', '2012-11-10', 'SD');
INSERT 0 0
david=# insert into tbl_partition values (7, 'Peter', '1', '2013-01-11', 'SD');
INSERT 0 0
david=# insert into tbl_partition values (8, 'Sally', '0', '2013-03-10', 'BCSC');
INSERT 0 0
david=# insert into tbl_partition values (9, 'Carrie', '0', '2013-04-02', 'BCSC');
INSERT 0 0
david=# insert into tbl_partition values (10, 'Lee', '1', '2013-01-05', 'BMC');
INSERT 0 0
david=# insert into tbl_partition values (11, 'Nicole', '0', '2012-11-10', 'PROJ');
INSERT 0 0
david=# insert into tbl_partition values (12, 'Renee', '0', '2013-01-10', 'TS');
INSERT 0 0
david=# 
复制代码

5.2 查看主表数据

复制代码
david=# select * from tbl_partition;
 id |  name  | gender | join_date  | dept 
----+--------+--------+------------+------
  3 | Eagle  | t      | 2012-11-01 | TS  
  6 | Rock   | t      | 2012-11-10 | SD  
 11 | Nicole | f      | 2012-11-10 | PROJ
  4 | Miles  | t      | 2012-12-15 | SD  
  5 | Simon  | t      | 2012-12-10 | SD  
  1 | David  | t      | 2013-01-10 | TS  
  7 | Peter  | t      | 2013-01-11 | SD  
 10 | Lee    | t      | 2013-01-05 | BMC 
 12 | Renee  | f      | 2013-01-10 | TS  
  2 | Sandy  | f      | 2013-02-10 | TS  
  8 | Sally  | f      | 2013-03-10 | BCSC
  9 | Carrie | f      | 2013-04-02 | BCSC
(12 rows)

david=# 
复制代码

5.3 查看分区表数据

复制代码
david=# select * from tbl_partition_201301 ;
 id | name  | gender | join_date  | dept 
----+-------+--------+------------+------
  1 | David | t      | 2013-01-10 | TS  
  7 | Peter | t      | 2013-01-11 | SD  
 10 | Lee   | t      | 2013-01-05 | BMC 
 12 | Renee | f      | 2013-01-10 | TS  
(4 rows)

david=# 
复制代码

六、管理分区

6.1 移除数据/分区

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

david=# drop table tbl_partition_201304;

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

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

david=# alter table tbl_partition_201304 no inherit tbl_partition;
ALTER TABLE
david=#

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

david=# alter table tbl_partition_201304 inherit tbl_partition;                                                            
ALTER TABLE
david=# 

6.2 增加分区

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

david=# create table tbl_partition_201306 (
check ( join_date >= DATE '2013-06-01' AND join_date < DATE '2013-07-01' )  
) INHERITS (tbl_partition);
david=# create index tbl_partition_201306_joindate on tbl_partition_201306 (join_date);

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

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

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

复制代码
david=# create table tbl_partition_201307
david-# (LIKE tbl_partition INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
CREATE TABLE
david=#

david=# alter table tbl_partition_201307 add constraint tbl_partition_201307_join_date_check 
david-# check ( join_date >= DATE '2013-07-01' AND join_date < DATE '2013-08-01' );
ALTER TABLE
david=#

david=# create index tbl_partition_201307_joindate on tbl_partition_201307 (join_date);

david=# copy tbl_partition_201307 from '/tmp/tbl_partition_201307.sql'; //从文件中拷贝数据,这些数据可以是事前准备的 david
=# alter table tbl_partition_201307 inherit tbl_partition;
复制代码

七、约束排除

约束排除(Constraint exclusion)是一种查询优化技巧,它改进了用上面方法定义的表分区的性能。

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

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

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

7.1 约束排除关闭

复制代码
david=# set constraint_exclusion = off;                                                 
SET
david=# explain select count(*) from tbl_partition where join_date >= DATE '2013-04-01';
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Aggregate  (cost=172.80..172.81 rows=1 width=0)
   ->  Append  (cost=0.00..167.62 rows=2071 width=0)
         ->  Seq Scan on tbl_partition  (cost=0.00..0.00 rows=1 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201211 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201212 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201301 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201302 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201303 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201305 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201304 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201306 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201307 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
(22 rows)

david=# 
复制代码

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

7.2 约束排除开启

复制代码
david=# set constraint_exclusion = on;                                                  
SET
david=# explain select count(*) from tbl_partition where join_date >= DATE '2013-04-01';
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Aggregate  (cost=76.80..76.81 rows=1 width=0)
   ->  Append  (cost=0.00..74.50 rows=921 width=0)
         ->  Seq Scan on tbl_partition  (cost=0.00..0.00 rows=1 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201305 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201304 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201306 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
         ->  Seq Scan on tbl_partition_201307 tbl_partition  (cost=0.00..18.62 rows=230 width=0)
               Filter: (join_date >= '2013-04-01'::date)
(12 rows)

david=#
复制代码

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

八、可选的分区方式

还可以通过建立规则的方式进行分区。

复制代码
CREATE RULE insert_tbl_partition_201211 AS
ON INSERT TO tbl_partition WHERE
    ( join_date >= DATE '2012-11-01' AND join_date < DATE '2012-12-01' )
DO INSTEAD
    INSERT INTO tbl_partition_201211 VALUES (NEW.*);

CREATE RULE insert_tbl_partition_201212 AS
ON INSERT TO tbl_partition WHERE
    ( join_date >= DATE '2012-12-01' AND join_date < DATE '2013-01-01' )
DO INSTEAD
    INSERT INTO tbl_partition_201212 VALUES (NEW.*);

...
    
CREATE RULE insert_tbl_partition_201306 AS
ON INSERT TO tbl_partition WHERE
    ( join_date >= DATE '2013-06-01' AND join_date < DATE '2013-07-01' )
DO INSTEAD
    INSERT INTO tbl_partition_201306 VALUES (NEW.*);
    
CREATE RULE insert_tbl_partition_201307 AS
ON INSERT TO tbl_partition WHERE
    ( join_date >= DATE '2013-07-01' AND join_date < DATE '2013-08-01' )
DO INSTEAD
    INSERT INTO tbl_partition_201307 VALUES (NEW.*);
    
CREATE RULE insert_tbl_partition_error_join_date AS
ON INSERT TO tbl_partition WHERE
    ( join_date >= DATE '2013-08-01' OR join_date < DATE '2012-11-01' )
DO INSTEAD
    INSERT INTO tbl_partition_error_join_date VALUES (NEW.*);
复制代码

九、注意事项

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

十、参考资料


原文网址:https://www.cnblogs.com/mchina/archive/2013/04/09/2973427.html

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
关系型数据库 物联网 PostgreSQL
沉浸式学习PostgreSQL|PolarDB 11: 物联网(IoT)、监控系统、应用日志、用户行为记录等场景 - 时序数据高吞吐存取分析
物联网场景, 通常有大量的传感器(例如水质监控、气象监测、新能源汽车上的大量传感器)不断探测最新数据并上报到数据库. 监控系统, 通常也会有采集程序不断的读取被监控指标(例如CPU、网络数据包转发、磁盘的IOPS和BW占用情况、内存的使用率等等), 同时将监控数据上报到数据库. 应用日志、用户行为日志, 也就有同样的特征, 不断产生并上报到数据库. 以上数据具有时序特征, 对数据库的关键能力要求如下: 数据高速写入 高速按时间区间读取和分析, 目的是发现异常, 分析规律. 尽量节省存储空间
778 1
|
3月前
|
SQL 关系型数据库 数据库
PostgreSQL数据库报错 ERROR: multiple default values specified for column "" of table "" 如何解决?
PostgreSQL数据库报错 ERROR: multiple default values specified for column "" of table "" 如何解决?
355 59
|
人工智能 关系型数据库 Serverless
阿里函数计算FC、文件存储NAS和RDS PostgreSQL的应用体验报告
本次体验的目的,旨在详细介绍如何通过阿里函数计算FC部署ChatGLM6B大语言模型,并借助文件存储NAS和RDS PostgreSQL搭建一个AI知识库问答应用,以实现PDF、TXT、HTML等文件和URL类型资料的轻松读取和处理。
310 62
|
SQL 监控 关系型数据库
PostgreSQL普通表转换成分区表
如何使用pg_rewrite扩展将普遍表转换成分区表
|
7月前
|
关系型数据库 数据库 PostgreSQL
|
7月前
|
存储 JSON 关系型数据库
PostgreSQL Json应用场景介绍和Shared Detoast优化
PostgreSQL Json应用场景介绍和Shared Detoast优化
|
7月前
|
关系型数据库 数据库 PostgreSQL
Docker【应用 03】给Docker部署的PostgreSQL数据库安装PostGIS插件(安装流程及问题说明)
Docker【应用 03】给Docker部署的PostgreSQL数据库安装PostGIS插件(安装流程及问题说明)
422 0
|
7月前
|
关系型数据库 数据库 PostgreSQL
PostgreSQL【应用 01】使用Vector插件实现向量相似度查询(Docker部署的PostgreSQL安装pgvector插件说明)和Milvus向量库对比
PostgreSQL【应用 01】使用Vector插件实现向量相似度查询(Docker部署的PostgreSQL安装pgvector插件说明)和Milvus向量库对比
633 1
|
7月前
|
SQL 关系型数据库 C语言
PostgreSQL【应用 03】Docker部署的PostgreSQL扩展SQL之C语言函数(编写、编译、载入)计算向量余弦距离实例分享
PostgreSQL【应用 03】Docker部署的PostgreSQL扩展SQL之C语言函数(编写、编译、载入)计算向量余弦距离实例分享
100 0
|
7月前
|
SQL 关系型数据库 数据库
PostgreSQL【应用 02】扩展SQL之C语言函数(编写、编译、载入)实例分享
PostgreSQL【应用 02】扩展SQL之C语言函数(编写、编译、载入)实例分享
232 0