postgresql实现对已有数据表分区处理的操作详解
（福利推荐：你还在原价购买阿里云服务器？现在阿里云0.8折限时抢购活动来啦！4核8G企业云服务器仅2998元/3年，立即抢购>>>：9i0i.cn/aliyun）
背景：业务初期创建的一张表，有 20 个字段，没有超长超大字段。随着系统运行，数据量来到了将近 1.3 亿行约 60GB。目前整体稳定，考虑到后续数据持续增长，打算先对表进行分区处理。考虑分区的主要因素是，这张表的数据主要是插入，和对最近插入数据的查询，后续会有少量针对该表的全量查询操作。
一、对已有数据进行备份
创建备份表并将所有的数据备份到 t_test_back 表里。这种方式备份，只会复制表结构和表数据，不会包含索引和约束。并且这种方式不会涉及到加锁等操作，整体执行很快，60GB 的数据大概在 5 分钟左右备份完成。
create table public.t_test_back as (select * from public.t_test);
二、删除原表
删除原表之前，记得先保留好建表语句，原表索引和约束，原建表语句如下：
CREATE TABLE public.t_test (
id bigserial NOT NULL,
column1 int8 NOT NULL,
column2 varchar NOT NULL,
column3 varchar NULL,
create_time timestamptz NULL,
CONSTRAINT t_test_pkey PRIMARY KEY (id)
);
CREATE INDEX column1_idx ON public.t_test USING btree (column1);
CREATE INDEX column2_idx ON public.t_test USING btree (column2);
删除原表直接使用 drop 语句：
drop table public.t_test;
我执行的时候，大概十分钟没有反应，最后查询检查 pg_stat_activity 视图，发现 public.t_test 还有 active sql 执行。
通过下面的方式，将在执行中的 sql 全部中断掉。
-- 找到当前表还在执行的查询 select pid, query, state from pg_stat_activity where state = 'state' and query like '%t_test%'; -- 可以取消查询 select pg_cancel_backend(pid); -- 也可以强制中止会话 select pg_terminate_backend(pid);
将执行中的查询取消后，drop 操作很快完成，正常执行预估也是在 5 分钟左右能执行完成。
三、创建分区表
根据原表建表语句创建分区表，其中分区字段需要作为pk的一部分，我使用时间字段 create_time 作 range 分区：
CREATE TABLE public.t_test (
id bigserial NOT NULL,
column1 int8 NOT NULL,
column2 varchar NOT NULL,
column3 varchar NULL,
create_time timestamptz NOT null DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT t_test_pkey PRIMARY KEY (id, create_time)
) partition by range(create_time);我这里一年的数据量大概是 8 千万，我按照半年一个分区建表，最后有一个 DEFAULT 的分区表，用来存储分区以外的数据：
create table public.t_test_2022_02 partition of public.t_test for values from ('2022-06-01 00:00:00') to ('2022-12-31 23:59:59');
create table public.t_test_2023_01 partition of public.t_test for values from ('2023-01-01 00:00:00') to ('2023-06-30 23:59:59');
create table public.t_test_2023_02 partition of public.t_test for values from ('2023-07-01 00:00:00') to ('2023-12-31 23:59:59');
create table public.t_test_2024_01 partition of public.t_test for values from ('2024-01-01 00:00:00') to ('2024-06-30 23:59:59');
create table public.t_test_2024_02 partition of public.t_test for values from ('2024-07-01 00:00:00') to ('2024-12-31 23:59:59');
create table public.t_test_default partition of public.t_test DEFAULT;
我这里将原来的索引直接用在分区表的主表上：
CREATE INDEX column1_idx ON public.t_test USING btree (column1); CREATE INDEX column2_idx ON public.t_test USING btree (column2);
由于我使用的 postgresql 的 serial 类型做 id 字段的自增序列，所以还需要将序列的最新值更新到比之前表的最大 id 还要大。防止主键冲突。
alter sequence public.t_test_id_seq restart 340000000;
四、数据恢复
由于我的表的数据主要使用的是增量数据，所以我把 id 最大的一条数据插入数据库表后，就可以恢复服务。 最后用 sql 将剩余的数据插入新表即可：
insert into public.t_test (select * from public.t_test_back);
到此这篇关于postgresql实现对已有数据表分区处理的操作详解的文章就介绍到这了,更多相关postgresql数据表分区处理内容请搜索程序员之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持程序员之家！
相关文章
如何将postgresql数据库表内数据导出为excel格式(推荐)
这篇文章主要介绍了如何将postgresql数据库表内数据导出为excel格式(推荐),本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下2021-03-03
本文我们将深入探讨PostgreSQL数据库中的一个关键设置SET?statement_timeout,这个设置对于管理数据库性能和优化查询执行时间非常重要,让我们一起来了解它的工作原理以及如何有效地使用它2024-01-01
SpringBoot连接使用PostgreSql数据库的方法
这篇文章主要介绍了SpringBoot连接使用PostgreSql数据库的方法,本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下2021-01-01
这篇文章主要介绍了postgresql 查看当前用户名的实现，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧2021-01-01
这篇文章主要介绍了PostgreSql 重建索引的操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧2021-02-02
这篇文章主要介绍了如何为PostgreSQL的表自动添加分区,本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下2021-01-01
PostgreSQL pg_ctl start启动超时实例分析
这篇文章主要给大家介绍了关于PostgreSQL pg_ctl start启动超时的相关资料，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧2019-01-01
这篇文章主要介绍了postgresql 12版本搭建及主备部署操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧2021-01-01
这篇文章主要介绍了postgresql 实现多表关联删除操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧2021-01-01
PostgreSQL是一款简介而又性能强大的数据库应用程序,其在日期时间数据方面所支持的功能也都非常给力,这篇文章主要给大家介绍了关于PostgreSQL时间相差天数的相关资料,需要的朋友可以参考下2023-11-11
最新评论