postgresql實現(xiàn)對已有數(shù)據(jù)表分區(qū)處理的操作詳解
背景：業(yè)務初期創(chuàng)建的一張表，有 20 個字段，沒有超長超大字段。隨著系統(tǒng)運行，數(shù)據(jù)量來到了將近 1.3 億行約 60GB。目前整體穩(wěn)定，考慮到后續(xù)數(shù)據(jù)持續(xù)增長，打算先對表進行分區(qū)處理?？紤]分區(qū)的主要因素是，這張表的數(shù)據(jù)主要是插入，和對最近插入數(shù)據(jù)的查詢，后續(xù)會有少量針對該表的全量查詢操作。
一、對已有數(shù)據(jù)進行備份
創(chuàng)建備份表并將所有的數(shù)據(jù)備份到 t_test_back 表里。這種方式備份，只會復制表結構和表數(shù)據(jù)，不會包含索引和約束。并且這種方式不會涉及到加鎖等操作，整體執(zhí)行很快，60GB 的數(shù)據(jù)大概在 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;
我執(zhí)行的時候，大概十分鐘沒有反應，最后查詢檢查 pg_stat_activity 視圖，發(fā)現(xiàn) public.t_test 還有 active sql 執(zhí)行。
通過下面的方式，將在執(zhí)行中的 sql 全部中斷掉。
-- 找到當前表還在執(zhí)行的查詢 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);
將執(zhí)行中的查詢?nèi)∠螅?code>drop 操作很快完成，正常執(zhí)行預估也是在 5 分鐘左右能執(zhí)行完成。
三、創(chuàng)建分區(qū)表
根據(jù)原表建表語句創(chuàng)建分區(qū)表，其中分區(qū)字段需要作為pk的一部分，我使用時間字段 create_time 作 range 分區(qū)：
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);我這里一年的數(shù)據(jù)量大概是 8 千萬，我按照半年一個分區(qū)建表，最后有一個 DEFAULT 的分區(qū)表，用來存儲分區(qū)以外的數(shù)據(jù)：
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;
我這里將原來的索引直接用在分區(qū)表的主表上：
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;
四、數(shù)據(jù)恢復
由于我的表的數(shù)據(jù)主要使用的是增量數(shù)據(jù)，所以我把 id 最大的一條數(shù)據(jù)插入數(shù)據(jù)庫表后，就可以恢復服務。 最后用 sql 將剩余的數(shù)據(jù)插入新表即可：
insert into public.t_test (select * from public.t_test_back);
到此這篇關于postgresql實現(xiàn)對已有數(shù)據(jù)表分區(qū)處理的操作詳解的文章就介紹到這了,更多相關postgresql數(shù)據(jù)表分區(qū)處理內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了PostgreSQL JSONB的匹配和交集問題，本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-09-09
PostgreSQL數(shù)據(jù)庫備份的幾種實現(xiàn)方法
本文主要介紹了PostgreSQL數(shù)據(jù)庫備份的幾種實現(xiàn)方法,包括pg_dump和pg_dumpall是PostgreSQL備份工具,前者備份單數(shù)據(jù)庫,后者備份整個集群,感興趣的可以了解一下2025-06-06
PostgreSQL如何選擇合適的數(shù)據(jù)類型
本文詳細介紹了PostgreSQL中各種數(shù)據(jù)類型的特性、適用場景、潛在陷阱及最佳實踐,涵蓋了數(shù)值、字符、時間、布爾、枚舉、網(wǎng)絡、JSON、幾何、全文搜索、范圍、自定義類型等核心類別,并通過真實案例說明了數(shù)據(jù)類型選型的邏輯,感興趣的朋友跟隨小編一起看看吧2026-01-01
文章介紹了PostgreSQL在操作符定義方面的創(chuàng)新和靈活性,包括如何使用OPERATOR()語法顯式調(diào)用操作符,以及如何自定義操作符以滿足特定需求,文章還討論了PostgreSQL在自定義操作符方面的優(yōu)勢,如操作符重載、索引綁定和對優(yōu)化器的影響,感興趣的朋友跟隨小編一起看看吧2025-12-12
pgsql之create user與create role的區(qū)別介紹
這篇文章主要介紹了pgsql之create user與create role的區(qū)別介紹，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
在Ubuntu中安裝Postgresql數(shù)據(jù)庫的步驟詳解
PostgreSQL 是一款強大的，開源的，對象關系型數(shù)據(jù)庫系統(tǒng)。它支持所有的主流操作系統(tǒng)，包括 Linux、Unix（AIX、BSD、HP-UX，SGI IRIX、Mac OS、Solaris、Tru64） 以及 Windows 操作系統(tǒng)。本文給大家介紹了在Ubuntu中安裝Postgresql數(shù)據(jù)庫的步驟，需要的朋友可以參考下。2017-09-09
PostgreSQL 8.3版本發(fā)布時,引入了一個名為tablefunc的新擴展,這個擴展提供了一組非常有趣的函數(shù),其中之一是交叉表函數(shù),用于創(chuàng)建數(shù)據(jù)透視表,這就是我們將在本文中討論的內(nèi)容,本文給大家介紹了PostgreSQL實現(xiàn)透視表查詢的方法,需要的朋友可以參考下2024-12-12
在postgreSQL中運行sql腳本和pg_restore命令方式
這篇文章主要介紹了在postgreSQL中運行sql腳本和pg_restore命令方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了mac裝PostgreSQL安裝失敗的問題及解決,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-01-01
postgresql關于like%xxx%的優(yōu)化操作
這篇文章主要介紹了postgresql關于like%xxx%的優(yōu)化操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01