PostgreSql分区表

最新推荐文章于 2023-08-20 12:33:30 发布
最新推荐文章于 2023-08-20 12:33:30 发布
阅读量695 收藏 4
点赞数
分类专栏： Postgresql 文章标签： postgresql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42032199/article/details/127238887
版权

一、原始表

/**表**/
CREATE TABLE "public"."test1" (
  "id" int8 NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "created_time" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "created_by" int8,
  "updated_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "updated_by" int8,
  "delete_flag" bool NOT NULL DEFAULT false,
  CONSTRAINT "test1_pkey" PRIMARY KEY ("id")
)
;

ALTER TABLE "public"."test1" 
  OWNER TO "postgres";

/**数据**/
INSERT INTO "public"."test1"("id", "name", "created_time", "created_by", "updated_time", "updated_by", "delete_flag")
VALUES (20220801000, '20220801000', '2022-08-01 00:00:00', NULL, NULL, NULL, false);

INSERT INTO "public"."test1"("id", "name", "created_time", "created_by", "updated_time", "updated_by", "delete_flag")
VALUES (20220801001, '20220801001', '2022-08-01 00:00:01', NULL, NULL, NULL, false);

INSERT INTO "public"."test1"("id", "name", "created_time", "created_by", "updated_time", "updated_by", "delete_flag")
VALUES (20220901001, '20220901001', '2022-09-01 00:00:01', NULL, NULL, NULL, false);

INSERT INTO "public"."test1"("id", "name", "created_time", "created_by", "updated_time", "updated_by", "delete_flag")
VALUES (20221001001, '20221001001', '2022-10-01 00:00:01', NULL, NULL, NULL, false);

二、创建分区表

以 test1 表为例，创建分区表 test1_fenqu

 CREATE TABLE "public"."test1_fenqu" (
  "id" int8 NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "created_time" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "created_by" int8,
  "updated_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP,
  "updated_by" int8,
  "delete_flag" bool NOT NULL DEFAULT false,
   CONSTRAINT "test1_fenqu_pkey" PRIMARY KEY ("id","created_time")
)
PARTITION BY RANGE (
  "created_time" "pg_catalog"."timestamp_ops"
)
;

ALTER TABLE "public"."test1_fenqu" 
  OWNER TO "postgres";

三、导入分区脚本

1、“create_partition_fun_init”(“init_time” varchar)

CREATE OR REPLACE FUNCTION "public"."create_partition_fun_init"("init_time" varchar)
  RETURNS "pg_catalog"."void" AS $BODY$
DECLARE
start_time VARCHAR;
BEGIN

 IF "init_time" is not null  THEN
       
        start_time ="init_time";
    ELSE 
       start_time =TO_CHAR(now(), 'YYYY-MM-DD');
      END IF;
      
--M:月  Q :季度 S:半年  Y:一年
 EXECUTE 'select create_partition_fun('''||start_time ||''',''M'',''test1_fenqu'')';

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

ALTER FUNCTION "public"."create_partition_fun_init"("init_time" varchar) OWNER TO "postgres";

2、“create_partition_fun”(“init_time” varchar, “partition_type” varchar, “table_name” varchar)

CREATE OR REPLACE FUNCTION "public"."create_partition_fun"("init_time" varchar, "partition_type" varchar, "table_name" varchar)
  RETURNS "pg_catalog"."void" AS $BODY$
DECLARE
   -- table_name VARCHAR ;//表名
	--	partition_type VARCHAR:='M';M:月  Q :季度 S:半年  Y:一年
		-- 月 从每月的第一天~下月的第一天  季度:当前时间所在的季度首月第一天~下季度的首月第一天   半年:当前年的1月第一天~当前年的七月第一天 或者+6   一年：就是全年一整年 第一年的第一天~第二年的第一天
    now_schema VARCHAR := CURRENT_SCHEMA;
    create_suffix VARCHAR;
    from_date VARCHAR;
    to_date VARCHAR;
		table_exist_mark INTEGER;
		start_time VARCHAR;
		contion INTEGER;
		create_index INTEGER;
		m_mark INTEGER:=0;
BEGIN
    IF "init_time" is not null  THEN
		   
			  start_time ="init_time";
		ELSE 
		   start_time =TO_CHAR(now(), 'YYYY-MM-DD');
			END IF;
		
   create_index = 1;
	 
	  WHILE create_index >0 LOOP
		     EXECUTE ' select case when to_date('''||start_time||''', ''YYYY-MM-DD'')>=now() then 0 else 1 end' INTO contion;
				 
		     IF contion = 0 THEN
				     create_index=0; --终止执行
				 ELSE 
				 
				 IF partition_type = 'M' THEN
						EXECUTE 'SELECT TO_CHAR(to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||1||' month'', ''YYYYMM'')' INTO create_suffix;
            EXECUTE 'SELECT TO_CHAR(to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||1||' month'', ''YYYY-MM'') || ''-01 00:00:00''' INTO from_date;
            EXECUTE 'SELECT TO_CHAR(to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||2||' month'', ''YYYY-MM'') || ''-01 00:00:00''' INTO to_date;
						
						--计算下一个分区时间
						EXECUTE 'SELECT TO_CHAR(to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||1||' month'', ''YYYY-MM-DD'') ' INTO start_time;
						  
				 END IF;
				 IF partition_type = 'Q' THEN
							EXECUTE 'SELECT TO_CHAR(date_trunc(''quarter'',to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||3||' month''), ''YYYYMM'') ' INTO create_suffix;
            EXECUTE 'SELECT TO_CHAR(date_trunc(''quarter'',to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||3||' month''), ''YYYY-MM'') || ''-01 00:00:00''' INTO from_date;
            EXECUTE 'SELECT TO_CHAR(date_trunc(''quarter'',to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||6||' month''), ''YYYY-MM'') || ''-01 00:00:00''' INTO to_date;
						
						--计算下一个分区时间
						EXECUTE 'SELECT TO_CHAR(to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||3||' month'', ''YYYY-MM-DD'') ' INTO start_time;
				 
				 
				 END IF;
				 IF partition_type = 'S' THEN
						m_mark = 0;
						EXECUTE 'SELECT TO_CHAR(to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||6||' month'', ''MM'')::int' INTO m_mark;
						IF m_mark > '6' THEN 
					     	EXECUTE 'SELECT TO_CHAR(date_trunc(''quarter'',to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||6||' month''), ''YYYY'') ||''07''' INTO create_suffix;
                EXECUTE 'SELECT TO_CHAR(date_trunc(''quarter'',to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||6||' month''), ''YYYY'') || ''-07-01 00:00:00''' INTO from_date;
                EXECUTE 'SELECT TO_CHAR(date_trunc(''quarter'',to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||12||' month''), ''YYYY'') || ''-01-01 00:00:00''' INTO to_date;
						
						ELSE 
						  	EXECUTE 'SELECT TO_CHAR(date_trunc(''quarter'',to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||6||' month''), ''YYYY'') ||''01''' INTO create_suffix;
                EXECUTE 'SELECT TO_CHAR(date_trunc(''quarter'',to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||6||' month''), ''YYYY'') || ''-01-01 00:00:00''' INTO from_date;
                EXECUTE 'SELECT TO_CHAR(date_trunc(''quarter'',to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||6||' month''), ''YYYY'') || ''-07-01 00:00:00''' INTO to_date;
						
						END IF;
						--计算下一个分区时间
						EXECUTE 'SELECT TO_CHAR(to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||6||' month'', ''YYYY-MM-DD'') ' INTO start_time;
						
				 END IF;
				 IF partition_type = 'Y' THEN
				     EXECUTE 'SELECT TO_CHAR(date_trunc(''quarter'',to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||1||' year''), ''YYYY'') ||''01''' INTO create_suffix;
            EXECUTE 'SELECT TO_CHAR(date_trunc(''quarter'',to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||1||' year''), ''YYYY'') || ''-01-01 00:00:00''' INTO from_date;
            EXECUTE 'SELECT TO_CHAR(date_trunc(''quarter'',to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||2||' year''), ''YYYY'') || ''-01-01 00:00:00''' INTO to_date;
						
						--计算下一个分区时间
						EXECUTE 'SELECT TO_CHAR(to_date('''||start_time||''', ''YYYY-MM-DD'') + INTERVAL '''||1||' year'', ''YYYY-MM-DD'') ' INTO start_time;
				 
				 
				 END IF;
				 
				 
				 
				 EXECUTE 'SELECT count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_NAME ='''||table_name||'_'||create_suffix||'''' INTO table_exist_mark;
			
						
						
						 IF table_exist_mark = 0 THEN
						/*创建分区表 */
						 EXECUTE 'CREATE TABLE "'||table_name||'_'||create_suffix||'" PARTITION OF "'||now_schema||'"."'||table_name||'" FOR VALUES FROM ('''||from_date||''') TO ('''||to_date||''')';
           
					 /*补充索引*/
					 /*
					 EXECUTE 'ALTER TABLE "'||now_schema||'"."'||table_name||'_'||create_suffix||'" ADD PRIMARY KEY ("id")';
           EXECUTE 'CREATE INDEX "'||table_name||'_'||create_suffix||'_created_time_idx" ON "'||now_schema||'"."'||table_[1]||'_'||create_suffix||'" USING btree ("created_time")';
								*/
						
						
					END IF;
					
		END IF;
		
		END LOOP;
		
				
    
      
            
				
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

ALTER FUNCTION "public"."create_partition_fun"("init_time" varchar, "partition_type" varchar, "table_name" varchar) OWNER TO "postgres";

四、设置分区

查询表数据，需要对已有时间的月份和当前及+1创建分区
在这里插入图片描述

SELECT create_partition_fun_init('2022-07-01');/**创建8月份分区**/
SELECT create_partition_fun_init('2022-08-01');
SELECT create_partition_fun_init('2022-09-01');
SELECT create_partition_fun_init('2022-10-01');

在这里插入图片描述

五、迁移数据

迁移原表（test1）数据至分区表（test_fenqu）

insert into test1_fenqu select * from test1;

六、重命名表

将分区表（test_fenqu）重命名为原表（test1）。

alter table test1 rename to test1_bak;
alter table test1_fenqu rename to test1;

注意索引问题

zzkeung
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
PostgreSQL中的分区表
03-09 5738
PostgreSQL中的分区表 参考：https://www.xmmup.com/pgzhongdefenqubiao.html#PG_11xin_te_xing PostgreSQL分区的意思是把逻辑上的一个大表分割成物理上的几块。分区不仅能带来访问速度的提升，关键的是，它能带来管理和维护上的方便。 分区表的具体好处： 某些类型的查询性能可以得到极大提升 更新的性能也可以得到提升，因为表的每块的索引要比在整个数据集上的索引要小。如果索引不能全部放在内存里，那么在索引上的读和写都会产生更多的磁盘访问
实战 PostgreSQL 分区表
PageThinker
03-04 1160
我们在之前的《实战PostgreSQL》 详细描述的 PostgreSQL 的版本差别、JSON/JSONB、全文检索等相关信息，PostgreSQL 远比上述三点内容，这篇文章将实战 PostgreSQL 分区表。 01，什么是分区表？ 如下图，分区表就是根据分区策略，将数据数据分散到不同的子表中，并通过父表建立关联关系，从而实现数据物理上的分区。 02，PostgreSQL 提供的分区表功...
PostgreSQL分区表
qq_58883939的博客
09-01 6037
PostgreSQL10版本一个重量级的新特性是支持内置分区表，在分区表方面前进了一大步，目前支持范围分区和列表分区。
数据库分区；pgAdmin操作pgsql分区；修改pgsql数据库名字
运气不好努力来凑
05-12 1759
数据库分区是什么；pgAdmin操作pgsql分区；修改pgsql数据库名字，无法成功时应该怎么做
PostgreSQL数据库表分区介绍-四种分区方式
热门推荐
tiger_Angle
08-17 2万+
典型使用场景 随着使用时间的增加，数据库中的数据量也不断增加，因此数据库查询越来越慢。 加速数据库的方法很多，如添加特定的索引，将日志目录换到单独的磁盘分区，调整数据库引擎的参数等。这些方法都能将数据库的查询性能提高到一定程度。 对于许多应用数据库来说，许多数据是历史数据并且随着时间的推移它们的重要性逐渐降低。如果能找到一个办法将这些可能不太重要的数据隐藏，数据库查询速度将会大幅提高。可以通过DELETE来达到此目的，但同时这些数据就永远不可用了。 ...
postgresql内置分区表(声明式划分)
yogima的博客
12-08 2211
PostgreSQL 10.x 开始提供了内置分区表（内置是相对于 10.x 之前的手动方式）。内置分区简化了操作，将部分操作内置，最终简单三步就能够创建分区表。
PostgreSql
qq_37827502的博客
07-20 207
value = #{a.b}的用法中读取和提交分别到后台执行了什么方法 **seam @Scope---Session, Conversation都什么意思 a:support, ajax support的onsubmit, action, oncomplete分别什么意思 request, seesion, application三个级别什么区别 converstion是结余request和session之间的状态 后台对象在同一个cid, conversion ID下不会创建新的 什么是索引，什
PostgreSQL分区表（partitioning）应用实例详解
01-21
项目中有需求要垂直分表，即按照时间区间将数据拆分到n个表中，PostgreSQL提供了分区表的功能。分区表实际上是把逻辑上的一个大表分割成物理上的几小块，提供了很多好处，比如： 1、查询性能大幅提升 2、删除历史...
PostgreSQL分区表实践与思考.pptx
10-14
PostgreSQL 官方分区表功能的演进包括传统分区表、内置分区表、增强分区表和高性能分区表等。 PostgreSQL 9.x 及之前的版本使用传统分区表，通过继承和触发器实现分区表关系。从 PostgreSQL 10 版本开始，官方支持...
PostgreSQL 创建表分区
12-16
分区表是主表的子集，它们继承自主表的结构。每个分区都有一个特定的条件来确定它包含哪些数据。在这个例子中，我们创建了两个分区`users_0`和`users_1`，分别用于存储`uid`在0到99和100及以上的数据。 ```sql ...
PostgreSQL之分区表
toyong的博客
08-20 684
需要注意的是，PostgreSQL 的查询优化器会尽可能地将查询分发到正确的分区，以提高查询性能。但是，为了获得最佳性能，可能需要正确设置索引和统计信息，以便查询优化器能够做出最优的决策。查询性能还取决于你的查询条件和数据分布。如果分区键的范围和查询条件非常匹配，查询性能会更好。如果查询条件涵盖了多个分区，查询性能可能会受到影响。每个分区子表都可以有自己的存储设置，索引和约束，从而允许更高效地管理和查询数据。3、创建索引，为每个分区子表创建适当的索引，可以提高查询性能。1、首先需要创建分区父表。
postgresql分区表（partitioning）
YangliAguo_123的博客
08-10 424
postgresql分区表
Pgsql建表
weixin_42949219的博客
04-27 1万+
Pgsql建表 1，创建表，指定主键并默认自增（从1开始） DROP TABLE IF EXISTS "tb_test"; CREATE TABLE "public"."tb_test" ( "id" SERIAL8 NOT NULL, "username" varchar(50) COLLATE "pg_catalog"."default", "password" varchar(50) COLLATE "pg_catalog"."default", "conclusion" varcha
Postgresql建分区表步骤sql
cainiaobulan的博客
09-29 3357
建分区表步骤
PostgreSQL表分区详解
极道Jdon的技术博客
08-06 1754
由于该表是按birth_date分区的，因此已将两条记录添加到分区people_y2000中，一条记录添加到people_y2001中，而people_y2002仍然为空。通过将表的内容划分为较小的子表（称为分区），可以减小表的整体大小，从而显着提高性能。最明显的解决方案是添加一个新分区，但如果我们必须为世界上的每个国家/地区执行此操作，我们最终会得到数百个记录数量很少的表。当我们无法逻辑地划分数据，但我们只能通过将行分散到许多较小的分区来减小表大小时，这种类型非常有用。幸运的是，可以定义默认分区！
PG数据库-分区
LCLYY09的博客
02-27 2569
pg 表分区
postgresql 分区表
最新发布
08-23
PostgreSQL 支持分区表，它允许将表数据根据特定的条件拆分成多个子表，从而提高查询性能和管理数据。下面是一些关于 PostgreSQL 分区表的基本信息： 1. 分区表定义：在创建表时，可以使用 PARTITION BY 子句指定分区键。常见的分区键类型包括范围（range）、列表（list）和哈希（hash）。 2. 范围分区（Range partitioning）：根据某个列的值范围进行分区，例如按时间范围、按数值范围等。可以使用 CREATE TABLE 语句的 PARTITION OF 子句定义每个分区。 3. 列表分区（List partitioning）：根据某个列的值列表进行分区，例如按地区、按部门等。也可以使用 CREATE TABLE 语句的 PARTITION OF 子句定义每个分区。 4. 哈希分区（Hash partitioning）：根据某个列的哈希值进行分区，通常用于数据平均分布的场景。使用 CREATE TABLE 语句的 PARTITION OF 子句定义每个分区。 5. 分区表管理：分区表可以通过 ALTER TABLE 添加或删除分区。还可以使用 EXCHANGE PARTITION 子句将数据从非分区表或已有分区中交换进入分区表。 6. 查询优化：PostgreSQL 的查询优化器会在执行查询时自动识别并只查询相关分区，从而提高查询性能。同时，可以通过查询约束来进一步减少查询的分区范围。 需要注意的是，分区表在数据库中的使用需要根据具体的业务需求和数据特点来决定，同时需要合理设计和规划分区键，以及考虑数据维护和查询优化等方面的因素。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值