使用postgreSQL做分区表

最新推荐文章于 2024-06-29 16:18:58 发布
最新推荐文章于 2024-06-29 16:18:58 发布
阅读量731 收藏 2
点赞数
分类专栏： postgreSQL 文章标签： postgresql 分区表
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/neversaycode/article/details/107523413
版权
本文介绍了如何在postgreSQL中使用分区表，强调其无需修改项目代码的优点。详细步骤包括创建父表student，建立继承关系的子表，设置CHECK约束，编写用于自动分配数据到正确分区的程序，创建触发器以及插入测试数据。通过这种方式，数据根据日期自动存储到不同的分区表，确保高效管理和检索。
摘要由CSDN通过智能技术生成

基于postgreSQL的分区表实现

首先说一下为什么使用postgre来做分区表，最大的原因是使用postgre做分区表，对于项目的代码是没有任何侵入的，不需要改动任何代码。下面是创建分区表的过程。

  1. 创建student父表
CREATE TABLE "public"."student" (
  "id" int4 NOT NULL,
  "name" varchar(20) COLLATE "pg_catalog"."default",
  "score" numeric,
  "create_time" timestamp(6),
  CONSTRAINT "student_pkey" PRIMARY KEY ("id")
);

2.接下来创建子表，所有子表都继承student表


                

                
                
        

        

    




        

            

                    
                      最低0.47元/天 解锁文章
                        
                    
            

        

    

      

        

            

              
                
                  NeverSayCode
                
              
            

            

                关注
              
            

        

        

          
    
            
  • 
              
                
                
                
                
                      0
                
              
              
    点赞
    
            
    • 
            
  • 
              
                
                
                
              
              
    踩
    
            
    • 
            
  • 
              
                
                
                
                
                    2
                
              
              
    
                
    
                  
                    收藏
                  
                
    
              
    
              
    
                
    
                  觉得还不错?
                  
                    一键收藏
                  
                 
                
    
              
    
            
    • 
          
  • 
            
    
              
              
            
    
              
              
              
                    0
              
            
            
    评论
    
          
    • 
          
  • 
          
    • 
          
  • 
            
              
            
              
    
            
              
                
            
    
          
    • 
        

      

      

            

                专栏目录
          










                



	

		

			

				
					
postgresql分区表

				
			

			

				

					qq_35298337的博客
				

				

					11-27
					
					1325
					
				

			

		

		

			
				
#postgresql分区表重要参数配置
constraint_exclusion = partition
enable_partitionwise_aggregate = on   #(分区级别的聚合：PostgreSQL 11附带了一个名为enable_partitionwise_aggregate的新选项，可以打开该选项以使查询计划程序将聚合推送到分区级别。 默认情况下，此选项已关闭）
#创...

			
		

	



                

              
                



	

		

			

				
					
POSTGRESQL 分区表初次体验

				
			

			

				

					四爷
				

				

					12-15
					
					3078
					
				

			

		

		

			
				
POSTGRESQL的分区和MYSQL不同，MYSQL是有专门的分区表， 而POSTGRESQL的分区则利用它本身的面向对象的特性来做。 下面我们来简单的体验下。

			
		

	



                


  
              

                


	

		

			

				
					
PostgreSQL分区表（partitioning）应用实例详解

				
			

			

				

					01-21
				

			

		

		

			
				
前言
项目中有需求要垂直分表，即按照时间区间将数据拆分到n个表中，PostgreSQL提供了分区表的功能。分区表实际上是把逻辑上的一个大表分割成物理上的几小块，提供了很多好处，比如：
1、查询性能大幅提升
2、删除历史数据更快
3、可将不常用的历史数据使用表空间技术转移到低成本的存储介质上
那么什么时候该使用分区表呢？官方给出的指导意见是：当表的大小超过了数据库服务器的物理内存大小则应当使用分区表，接下来结合一个例子具体记录一下创建分区表的详细过程。
创建分区表
首先看一下需求，现在有一张日志表，现在需要按表中的操作时间字段（operation_time）分区，如下图： 



这个需求就是一

			
		

	





	

		

			

				
					
PostgreSQL分区表

					
最新发布

				
			

			

				

					m0_37559973的博客
				

				

					06-29
					
					1309
					
				

			

		

		

			
				
分区表是一种数据库优化技术，它允许将一个大表逻辑上划分为多个较小的、可管理的部分，这些部分被称为分区或子表。分区表在物理上是分开存储的，但在逻辑上仍作为一个整体呈现给用户。这一特性特别适用于处理大量数据的场景，旨在提高查询性能、管理和维护大数据集的效率。

			
		

	



		

			
		



	

		

			

				
					
Postgresql普通表转换分区表(上亿条数据)

				
			

			

				

					qq_47255712的博客
				

				

					01-26
					
					6400
					
				

			

		

		

			
				
Postgresql普通表转换为分区表
前言：这里处理的是postgresql数据库的上亿条数据，方法有很多，这里是用的是先创建分区表在导入数据，最后更改数据库表名字达成目的。
查看表普通表
select * from table_原表;


创建分区表 t_range,这里根据时间table_time分区
CREATE TABLE t_range (
  "table_id" varchar(36) NOT NULL,
  "table_time" timestamp(0) NOT NULL,
  "ta

			
		

	





	

		

			

				
					
postgre sql 将非分区表转化为分区表，动态创建和删除分区

				
			

			

				

					qq_41982570的博客
				

				

					09-15
					
					1739
					
				

			

		

		

			
				
postgre sql  将非分区表转化为分区表，动态创建和删除分区

			
		

	





	

		

			

				
					
pgsql在分区表与非分区表中自由切换

				
			

			

				

					zjcxc(邹建)的Blog - SQL Server
				

				

					09-11
					
					1114
					
				

			

		

		

			
				
pgsql在版本10中推出了声明式分区表，算是让分区表这个功能走上了正轨。（之前的版本为继承+触发器，和SQL Server的分区视图有点像，算是粗制）。

    随着其版本发展，功能也越来越强，但今天要介绍的是基础功能：如何优雅地在分区表和非分区表切换。

    pgsql分区表有一个笔者非常喜欢的特性，分区列是自由的，不和唯一约束绑定，在诸如SQL Server、MySQL中，当你尝试把普通表转换为分区表时，会强制要求你把分区列加到唯一性约束中（主键、唯一约束/索引）...

			
		

	





	

		

			

				
					
PostgreSQL将普通表转换为分区表插件之pg_rewrite

				
			

			

				

					snowyar的专栏
				

				

					08-16
					
					577
					
				

			

		

		

			
				
甚至对目标表的额外约束也是一个问题，因为扩展只假设它复制的所有数据确实满足源表上的约束，但是它不会根据目标表上的额外约束来验证它们。多次超过锁定时间会报错。在PG 9.6以前的版本，大表的freeze带来的IOPS影响较大，体现在数据文件读写、WAL日志大量产生。如果大表分区了，我们可以通过drop历史分区或者truncate历史分区的方式清理历史数据，不用担心大量wal日志的产生，而且执行时间很快。单表的只能位于单个表空间，对应到单个目录，并不能像分区表那样将各个分区放到不同的表空间，不同的目录上去。

			
		

	





	

		

			

				
					
Postgresql内置分区表的使用总结

				
			

			

				

					12-14
				

			

		

		

			
				
Postgresql内置分区表的使用总结 内置分区表简介 在postgresql10之后，引入了内置分区表，用户不需要先在父表上定义insert,update,delete触发器，对父表的DML操作会自动路由到相应分区，相比传统分区表大幅度降低了...

			
		

	





	

		

			

				
					
PostgreSQL 创建表分区

				
			

			

				

					12-16
				

			

		

		

			
				
 分区表是主表的子集，它们继承自主表的结构。每个分区都有一个特定的条件来确定它包含哪些数据。在这个例子中，我们创建了两个分区`users_0`和`users_1`，分别用于存储`uid`在0到99和100及以上的数据。   ```sql  ...

			
		

	





	

		

			

				
					
PostgreSQL之分区表(partitioning)

				
			

			

				

					09-09
				

			

		

		

			
				
分区表在PostgreSQL数据库系统中是一项强大的功能，它允许用户将一个大表按照特定规则划分为多个不重叠的子表，从而优化查询性能、管理大量数据。这就像将一个大型仓库按照商品类型划分成不同的区域，使得存取更加...

			
		

	





	

		

			

				
					
Postgresql杂谈 10—Postgresql中的分区表

				
			

			

				

					Hyman的博客
				

				

					07-31
					
					2746
					
				

			

		

		

			
				
一、关于分区表

   表分区是在大数据优化中的一种常见的分表方案，通过将大数据按照一定的规则（最常见的是按照时间）进行分表处理，将逻辑上的一个大表分割成物理上的几块表，插入数据时，数据会自动插入到不同的分区表中，从而实现查询或者其它操作的性能优化。相比于一个大表，分区表具有以下优点：

（1）当查询或者更新一个分区的大部分记录时，采用顺序扫描而不是随机扫描，可以获得巨大的性能提升。

（2）使用不频繁的历史数据可以转移到一些低廉的存储介质上，而热数据放到性能较好的存储介质上，可以最大限度的减少成本...

			
		

	





	

		

			

				
					
SQL优化（三） PostgreSQL Table Partitioning

				
			

			

				

					技术世界
				

				

					01-04
					
					1940
					
				

			

		

		

			
				
本文介绍了数据库分区表的使用场景，优势，原理，及在PostgreSQL中的实现和注意事项。

			
		

	





	

		

			

				
					
非分区表变为分区表_PostgreSQL 13新特性之二: 支持异构分区表逻辑复制。

				
			

			

				

					weixin_31125161的博客
				

				

					01-06
					
					244
					
				

			

		

		

			
				
作者简介谭峰，网名francs，PostgreSQL中文社区委员，《PostgreSQL实战》作者之一，《PostgreSQL 9 Administration Cookbook》译者之一，致力于PostgreSQL技术分享，博客 https://postgres.fun，三墩IT人。背景PostgreSQL 13 的逻辑复制新增了对分区表的支持，使得分区表也能够进行逻辑复制。实现方式...

			
		

	





	

		

			

				
					
普通表转分区表

				
			

			

				

					sun1995sun的博客
				

				

					05-18
					
					1057
					
				

			

		

		

			
				
备份完成后对原表进行删除（注意，此数据是冷数据，不在使用中，如果生产环境，请先将数据指向备份表中，原表数据完全断开，避免数据丢失）重新建分区表，注意（分区字段一定要添加为主键）[PARTITION BY LIST (根据哪个字段进行分区)]以上整个创建分区并恢复数据的过程就完成了，如果在实际使用中，可以一次创建多个分区以免插入数据无对应分区而报错。当一张表数据过大时，可以进行垂直拆分（每张表存储部分字段）和水平拆分（每张表字段完整，数据只存储一部分）恢复数据（恢复数据时一定要将对应的分区表建好）

			
		

	





	

		

			

				
					
PostgreSQL在线转分区表（避免长锁）

				
			

			

				

					Focus on PostgreSQL
				

				

					03-26
					
					2832
					
				

			

		

		

			
				
非分区表如何在线转分区表呢，怎样才能不影响业务？
使用pg_pathman可以直接做到，参考：PostgreSQL普通表转分区表
那么使用pg原生分区该如何实现呢？
做之前我们可以先想想在线转分区表的难点在哪里呢？
1、转分区表过程中，数据仍然在插入怎么办？
2、如何避免长时间锁表？
首先，避免数据实时插入，我们需要创建触发器来新插入的数据路由到新的表中。为了避免长时间锁表我们可以在后台进行数据迁移，分批进行迁移等等。
例子：
1、创建普通表并插入测试数据
bill=# create table orig_

			
		

	





	

		

			

				
					
postgresql实现表分区

				
			

			

				

					单调枯燥的CC的博客
				

				

					11-23
					
					55
					
				

			

		

		

			
				
业务背景
有一个学习记录表数据达到亿级，后台有众多复杂的统计查询，随着数据量增大，查询性能逐渐下降，且每周有仍400万多的数据记录，因此考虑分表将大表拆分，优化查询速度。
表分区的概念
将一个大表分成物理上小片的表，但在逻辑上一个完整的表。
表分区的好处
1.在某些情况下查询性能能够显著提升，特别是当那些访问压力大的行在一个分区或者少数几个分区时
2.当查询或更新访问一个分区的大部分行时，可以通...

			
		

	





	

		

			

				
					
postgresql给分区表创建分区表

				
			

			

				

					04-15
				

			

		

		

			
				
当您使用 PostgreSQL 给分区表创建分区时，您需要按照以下步骤操作：  1. 创建分区表的主表，例如：  CREATE TABLE my_partition_table (  partition_key int PRIMARY KEY,  column1 text,  column2 int );  2. 创建...

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

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

              

            

          

      

      余额充值