PostgreSql分区表

最新推荐文章于 2024-04-23 11:46:19 发布
最新推荐文章于 2024-04-23 11:46:19 发布
阅读量686 收藏 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分区表
qq_58883939的博客
09-01 6016
PostgreSQL10版本一个重量级的新特性是支持内置分区表，在分区表方面前进了一大步，目前支持范围分区和列表分区。
数据库分区；pgAdmin操作pgsql分区；修改pgsql数据库名字
运气不好努力来凑
05-12 1738
数据库分区是什么；pgAdmin操作pgsql分区；修改pgsql数据库名字，无法成功时应该怎么做
如何在PostgreSQL中对表进行重命名或修改表结构，以及这可能带来的潜在风险和解决方案
最新发布
程序员墨松
04-23 1023
总之，在PostgreSQL中对表进行重命名或修改表结构是常见的操作，但需要谨慎处理以避免潜在的风险。通过充分了解操作的影响、保持最新的数据库备份以及在适当的时间执行这些操作，可以最大程度地降低风险并确保数据库的稳定性和安全性。：在执行重命名操作之前，确保数据库备份是最新的，以便在出现问题时可以恢复数据。同时，在执行此类操作时，尽量在低峰时段进行，以减少对业务的影响。：对于大型表，修改表结构可能需要较长时间，并可能对数据库性能产生影响。：与重命名表类似，修改表结构也可能影响依赖于该表的数据库对象。
postgresql分区表（partitioning）
YangliAguo_123的博客
08-10 413
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分区表（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表分区详解
极道Jdon的技术博客
08-06 1740
由于该表是按birth_date分区的，因此已将两条记录添加到分区people_y2000中，一条记录添加到people_y2001中，而people_y2002仍然为空。通过将表的内容划分为较小的子表（称为分区），可以减小表的整体大小，从而显着提高性能。最明显的解决方案是添加一个新分区，但如果我们必须为世界上的每个国家/地区执行此操作，我们最终会得到数百个记录数量很少的表。当我们无法逻辑地划分数据，但我们只能通过将行分散到许多较小的分区来减小表大小时，这种类型非常有用。幸运的是，可以定义默认分区！
PostgreSQL实战之分区表
Auspicious航的博客
04-10 2129
分区表是关系型数据库提供的一个亮点特性，比如Oracle对分区表的支持已经非常成熟，广泛使用于生产系统，PostgreSQL也支持分区表，只是道路有些曲折，早在10版本之前PostgreSQL分区表一般通过继承加触发器方式实现，这种分区方式不能算是内置分区表，而且步骤非常烦琐，PostgreSQL10版本一个重量级的新特性是支持内置分区表，在分区表方面前进了一大步，目前支持范围分区和列表分区。
PostgreSQL之分区表(partitioning)
09-09
通过合理的设计，可以将选择一定的规则，将大表切分多个不重不漏的子表，这就是传说中的partitioning。比如，我们可以按时间切分，每天一张子表，比如我们可以按照某其他字段分割，总之了就是化整为零，提高查询的效能
实战 PostgreSQL 分区表：分区修改
PageThinker
03-09 2225
在 实战 PostgreSQL 分区表 中并没有涉及到如何修改分区表的问题。这篇内容将聚焦如何进行分区表的修改。 我们使用上一篇 实战 PostgreSQL 分区表 中的 Order 数据的场景，关于如何初始化这部分数据，请参考 实战 PostgreSQL 分区表。 00，场景 1，拆分分区 2，合并分区 01，拆分分区：操作流程 (1)，确认分区规则 原来的分区情况如下：父表 order...
9.x - 13.0 postgresql 分区表新特性及简单用法
Hehuyi_In的博客
12-08 4569
一、 分区表定义与意义 1. 分区表的定义 把一个大的物理表分成若干个小物理表，并使得这些小物理表在逻辑上可以被当成一张表来使用。 主表/父表/Master Table 主表是创建子表的模板，是一个正常的普通表，一般主表并不存任何数据。 子表/分区表/Chlid Table/Partition Table 子表继承并属于一个主表，与主表是一对多的关系，子表中存储所有的数据 2....
postgresql常用命令行命令，重命名表，字段，查看数据库字符编码，主键自增,增加索引
LeoHan
05-22 4416
后台进入postgresql命令行： psql 列出所有数据库： postgres=# \l 切换数据库 postgres=# \c 数据库名称 创建数据库： create database 数据库名称 查看当前库所有模式： \dn 删除数据库： drop database 数据库名称 创建模式 schema: create schema 模式名称 获取当前库下所有表： select * from pg_tables; 查看某个表的详细信息： postgres=# \d 表名称 重命名表：
postgres分区表修改分区规则
shangcunshanfu的博客
02-09 746
需求描述：如按列表分区，有一个分区表里存放的是 l_type in ('1'),现在想把这个分区改为t_type in ('1', '3')。 postgres好像没有直接提供这样的方法，需要间接地进行一下修改，这里会用到 ATTACH PARTITION 和 DETACH PARTITION。 为了演示，准备工作就是把数据建出来 创建父表 create table list_partition_test(id serial, l_type char(1)) partition by list( l_typ
postgresql----表分区
weixin_33949359的博客
06-22 307
--下面的描述不记得在哪里抄来的了？！ 表分区就是把逻辑上一个大表分割成物理上的多个小块，表分区可提供如下若干好处： 1.某些类型的查询性能可以得到极大提升。 2.更新的性能可以得到提升，因为表的每块索引要比整个数据集上的索引要小，如果索引不能全部放在内存里，那么在索引上的读写都会产生磁盘访问。 3.批量删除可以用简单的删除某个分区 4.将很少使用的数据移动到便宜的慢一些的存储介质上。 ...
postgresql的分区表
归来仍少年
07-17 4158
postgresql的分区表
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、付费专栏及课程。

余额充值