postgresql 创建分区表以及拆分分区表（修改分区）

yang_z_1

已于 2022-01-26 08:57:20 修改

阅读量1.1w

点赞数 33

分类专栏： postgresql 文章标签：运维新星计划 postgresql 数据库

于 2021-06-09 15:54:34 首次发布

本文链接：https://blog.csdn.net/yang_z_1/article/details/117730158

版权

postgresql 创建分区表以及拆分分区表（修改分区）
本文使用的数据库是 postgresql 11.5

文章目录

前言
一什么是分区表？
二分区的应用
三修改分区范围

前言

在日常维护啊数据库的过程中，往往发现一部分表由于日积月累的原因，表内的的数据量越来越大，会影响我们的查询效率，特别是往往只查询部分数据时（比如时间范围）查询会相当慢，那有没有解决办法吗？
办法肯定有的，那就不得不说我们今天的主角了：分区表 那什么是分区表?下面请听我一一讲解：

一什么是分区表？

1.1 分区表的概念

分区表就是根据分区策略，将数据数据分散到不同的子表中，并通过父表建立关联关系，从而实现数据物理上的分区。

1.2 分区的好处

在某些情况下查询性能能够显著提升，特别是当那些访问压力大的行在一个分区或者少数几个分区时。划分可以取代索引的主导列、减小索引尺寸以及使索引中访问压力大的部分更有可能被放在内存中。
当查询或更新访问一个分区的大部分行时，可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问，这样可以改善性能。
如果需求计划使用划分设计，可以通过增加或移除分区来完成批量载入和删除。执行ALTER TABLE DETACH PARTITION或者使用DROP TABLE 删除一个单独的分区都远快于一个批量操作。这些命令也完全避免了由批量DELETE造成的VACUUM负载。
很少使用的数据可以被迁移到便宜且较慢的存储介质上。

1.3 分区的类型

postgresql 11 以上数据库支持3种类型

范围划分
表被根据一个关键列或一组列划分为“范围”，不同的分区的范围之间没有重叠。例如，我们可以根据日期范围划分，或者根据特定业务对象的标识符划分。
列表划分
通过显式地列出每一个分区中出现的键值来划分表
哈希分区
通过为每个分区指定模数和余数来对表进行分区。每个分区所持有的行都满足：分区键的值除以为其指定的模数将产生为其指定的余数。

1.4 分区创建

原表数据结构

CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int);

创建主表（父表）
通过指定#PARTITION BY子句把measurement表创建为分区表，该子句包括分区方法（这个例子中是RANGE）以及用作分区键的列列表。

CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int) PARTITION BY RANGE (logdate);

创建子分区

 CREATE TABLE measurement_y2006m03 PARTITION OF measurement FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
-- ...
 CREATE TABLE measurement_y2007m11 PARTITION OF measurement FOR VALUES FROM ('2007-11-01') TO ('2007-12-01');
 CREATE TABLE measurement_y2007m12 PARTITION OF measurement FOR VALUES FROM ('2007-12-01') TO ('2008-01-01') TABLESPACE fasttablespace;
 CREATE TABLE measurement_y2008m01 PARTITION OF measurement FOR VALUES FROM ('2008-01-01') TO ('2008-02-01') WITH (parallel_workers = 4)

注意

每个分区的定义必须指定对应于父表的分区方法和分区键的边界。注意，如果指定的边界使得新分区的值会与已有分区中的值重叠，则会导致错误。向父表中插入无法映射到任何现有分区的数据将会导致错误，这种情况下应该手工增加一个合适的分区。

1.5 分区维护

删除分区
移除旧数据最简单的选择是删除掉不再需要的分区：

 DROP TABLE measurement_y2006m02;

脱离主表
把分区从分区表中移除，但是保留它作为一个独立的表

 ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;

二分区的应用

我们知道了啥是分区表，现在我们就实际应用一下，就如前面所说的，把一个大数据量的表该给分区表吧。

原表结构：

CREATE TABLE "hrmw"."tb_cs_fqb-1" (
  "id" varchar(36) COLLATE "pg_catalog"."default" NOT NULL,
  "code" varchar(36) COLLATE "pg_catalog"."default" NOT NULL,
  "calc_time" timestamp(0) NOT NULL,
  "linked" char(1) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '0'::bpchar,
  "online" char(1) COLLATE "pg_catalog"."default" NOT NULL DEFAULT '0'::bpchar