PostgreSQL 用 CTE语法 + 继承 实现平滑拆分大表

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

标签

PostgreSQL , 拆分大表 , 继承 , cte


背景

业务设计初期可能不会考虑到表将来会有多大,或者由于数据日积月累,单表会变得越来越大。

后面在考虑分区的话,应该怎么将单表切换成分区表呢?

这里可以用到PostgreSQL的CTE语法,以及继承功能,还有内置的分区表功能。

例子

具体步骤

1、创建分区表

2、创建继承关系,分区表继承自需要拆分的表

3、用cte转移数据

4、全部转移完成后,在事务中切换表名

例子,将tbl_big切换成哈希分区

1、创建被迁移的大表

create table tbl_big (id int primary key, info text, crt_time timestamp);  
  
create index idx_tbl_big on tbl_big (crt_time);  
  
insert into tbl_big select generate_series(1,10000000);  

2、创建分区表

create table tbl ( like tbl_big including all ) ;    
  
  
  
do language plpgsql $$    
declare    
  parts int := 4;    
begin    
  for i in 0..parts-1 loop    
    execute format('create table tbl%s (like tbl including all) inherits (tbl)', i);    
    execute format('alter table tbl%s add constraint ck check(mod(id,%s)=%s)', i, parts, i);    
  end loop;    
end;    
$$;   
  
  
  
create or replace function ins_tbl() returns trigger as $$    
declare    
begin    
  case abs(mod(NEW.id,4))    
    when 0 then    
      insert into tbl0 values (NEW.*);    
    when 1 then    
      insert into tbl1 values (NEW.*);    
    when 2 then    
      insert into tbl2 values (NEW.*);    
    when 3 then    
      insert into tbl3 values (NEW.*);    
    else    
      return NEW;  -- 如果是NULL则写本地父表    
    end case;    
    return null;    
end;    
$$ language plpgsql strict;    
  
    
  
create trigger tg1 before insert on tbl for each row when (NEW.id is not null) execute procedure ins_tbl();    

3、分区表,继承自被迁移的表

alter table tbl inherit tbl_big;  

4、迁移数据

with tmp as (delete from only tbl_big returning *) insert into tbl select * from tmp;  
-- 如果觉得这样做太久了(一次迁移了所有记录),可以拆成一个个小任务来做  
  
-- 一次迁移10万条,多次调用来完成迁移。  
with tmp as (delete from only tbl_big where ctid = any(array(select ctid from only tbl_big limit 100000)) returning *) insert into tbl select * from tmp;  

5、迁移完成后,切换表名。

postgres=# begin;  
  
postgres=# lock table tbl_big in access exclusive mode ;  
  
postgres=# select count(*) from  only tbl_big;  
  count    
---------  
 0  
(1 row)  
  
postgres=# alter table tbl_big rename to tmp_tbl_big;  
  
postgres=#  alter table tbl no inherit tmp_tbl_big;  
  
postgres=# alter table tbl rename to tbl_big;  
  
postgres=# end;  

参考

《PostgreSQL 传统 hash 分区方法和性能》

《PostgreSQL 10 内置分区 vs pg_pathman perf profiling》

《PostgreSQL 10.0 preview 功能增强 - 内置分区表》

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
4月前
|
SQL 自然语言处理 关系型数据库
MySQL的match匹配多个字符串的语法
【8月更文挑战第27天】MySQL的match匹配多个字符串的语法
250 67
|
2天前
|
存储 SQL 关系型数据库
【MySQL基础篇】MySQL约束语法
文章介绍了MySQL中表的约束概念,包括非空、唯一、主键、默认和外键约束,以及如何在创建和修改表时指定这些约束。外键约束用于保持数据的一致性和完整性,文章通过示例展示了添加、删除外键的语法,并讨论了不同的删除/更新行为,如CASCADE和SETNULL。
【MySQL基础篇】MySQL约束语法
|
2天前
|
SQL 存储 关系型数据库
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
本文详细介绍了MySQL中的SQL语法,包括数据定义(DDL)、数据操作(DML)、数据查询(DQL)和数据控制(DCL)四个主要部分。内容涵盖了创建、修改和删除数据库、表以及表字段的操作,以及通过图形化工具DataGrip进行数据库管理和查询。此外,还讲解了数据的增、删、改、查操作,以及查询语句的条件、聚合函数、分组、排序和分页等知识点。
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
|
2月前
|
关系型数据库 MySQL 数据库
MySQL的语法涵盖了数据定义、数据操作、数据查询和数据控制等多个方面
MySQL的语法涵盖了数据定义、数据操作、数据查询和数据控制等多个方面
69 5
|
2月前
|
关系型数据库 MySQL 数据库
MySQL的语法知识
MySQL的语法知识
20 4
|
2月前
|
SQL 关系型数据库 MySQL
MySQL语法
MySQL语法
85 3
|
2月前
|
SQL 存储 关系型数据库
mysql SQL必知语法
本文详细介绍了MySQLSQL的基本语法,包括SELECT、FROM、WHERE、GROUPBY、HAVING、ORDERBY等关键字的使用,以及数据库操作如创建、删除表,数据类型,插入、查询、过滤、排序、连接和汇总数据的方法。通过学习这些内容,读者将能更好地管理和操
38 0
|
3月前
|
关系型数据库 MySQL 数据库
MYSQL索引的分类与创建语法详解
理解并合理应用这些索引类型,能够有效提高MySQL数据库的性能和查询效率。每种索引类型都有其特定的优势,适当地使用它们可以为数据库操作带来显著的性能提升。
176 3
|
2月前
|
SQL 关系型数据库 MySQL
Mysql(2)—SQL语法详解
SQL(Structured Query Language,结构化查询语言)是一种用于管理关系型数据库的标准编程语言。它主要用于数据的查询、插入、更新和删除等操作。SQL最初在1970年代由IBM的研究人员开发,旨在处理关系数据模型。
41 0
|
2月前
|
自然语言处理 关系型数据库 MySQL
MySQL数据库使用Match语法需要安装什么插件吗?
【10月更文挑战第1天】MySQL数据库使用Match语法需要安装什么插件吗?
81 0

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版