一、什么是表分區(qū)？它和分庫(kù)分表有什么區(qū)別？

1.1 什么是表分區(qū)（Table Partitioning）？

表分區(qū)是數(shù)據(jù)庫(kù)的一種物理設(shè)計(jì)技術(shù)，它將一個(gè)大表從邏輯上視為一個(gè)整體，但從物理上拆分成多個(gè)子表（分區(qū)），每個(gè)分區(qū)存儲(chǔ)一部分?jǐn)?shù)據(jù)。

• 邏輯上：你仍然像操作一張表一樣查詢它
• 物理上：數(shù)據(jù)分散在多個(gè)子表中，按規(guī)則存儲(chǔ)
• ? 舉個(gè)例子：
  把 jgpt_jzd_test 按年份拆成 p2023、p2024、p2025 三個(gè)分區(qū)，查詢時(shí)仍用 SELECT * FROM jgpt_jzd_test，但數(shù)據(jù)庫(kù)只掃描相關(guān)分區(qū)。

1.2 表分區(qū) vs 分庫(kù)分表：關(guān)鍵區(qū)別

? 簡(jiǎn)單說：

• 表分區(qū)是“數(shù)據(jù)庫(kù)幫你拆”
• 分庫(kù)分表是“你自己寫代碼拆”

二、表分區(qū)的優(yōu)缺點(diǎn)與使用場(chǎng)景

優(yōu)點(diǎn)

缺點(diǎn)

三、PostgreSQL 表分區(qū)的三種方式

PostgreSQL 支持三種分區(qū)策略：

1.Range 分區(qū)（按范圍）

• 適用：時(shí)間、數(shù)值范圍
• 示例：按 gmt_create 按年/月分區(qū)
• 語(yǔ)法：

  PARTITION BY RANGE (gmt_create)

2.List 分區(qū)（按枚舉值）

• 適用：固定分類，如省份、狀態(tài)
• 示例：按 province 分區(qū)
• 語(yǔ)法：

  PARTITION BY LIST (province)

3.Hash 分區(qū)（按哈希值）

• 適用：數(shù)據(jù)均勻分布，無(wú)明顯查詢模式
• 示例：按 id 哈希分 4 份
• 語(yǔ)法：

  PARTITION BY HASH (id)

四、實(shí)戰(zhàn)：jgpt_jzd_test表分區(qū)操作全流程

將 3000 萬(wàn)+ 的 jgpt_jzd_test 表改造為按年分區(qū)的分區(qū)表。

步驟 1：創(chuàng)建分區(qū)主表

-- 創(chuàng)建主表（邏輯表，不存數(shù)據(jù)）
CREATE TABLE jgpt_jzd_test_partitioned (
    id                    varchar(32),
    jzdbh                 varchar(255),
    xzb                   varchar(255),
    yzb                   varchar(255),
    htxxid                varchar(255),
    gmt_create            timestamp(6) NOT NULL,  -- 必須 NOT NULL
    gmt_modified          timestamp(6),
    del_flag              varchar,
    created_user_id       varchar(255),
    created_user          varchar(255),
    last_modified_user_id varchar(255),
    last_modified_user    varchar(255),
    dkh                   varchar(255),
    dkms                  varchar(255),
    batchnum              varchar(255)
) PARTITION BY RANGE (gmt_create);

步驟 2：創(chuàng)建子分區(qū)（按年）

-- 2023 年分區(qū)
CREATE TABLE jgpt_jzd_test_p2023 
    PARTITION OF jgpt_jzd_test_partitioned
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- 2024 年分區(qū)
CREATE TABLE jgpt_jzd_test_p2024 
    PARTITION OF jgpt_jzd_test_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- 2025 年分區(qū)
CREATE TABLE jgpt_jzd_test_p2025 
    PARTITION OF jgpt_jzd_test_partitioned
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

步驟 3：遷移數(shù)據(jù)

-- 從舊表插入到新分區(qū)表（自動(dòng)路由）
INSERT INTO jgpt_jzd_test_partitioned 
SELECT * FROM jgpt_jzd_test;

步驟 4：創(chuàng)建索引

-- 在主表創(chuàng)建索引，所有分區(qū)自動(dòng)繼承
CREATE INDEX idx_jgpt_jzd_test_htxxid ON jgpt_jzd_test_partitioned (htxxid);

步驟 5：切換表名（原子操作）

-- 1. 備份原表
ALTER TABLE jgpt_jzd_test RENAME TO jgpt_jzd_test_backup;

-- 2. 新表啟用原名
ALTER TABLE jgpt_jzd_test_partitioned RENAME TO jgpt_jzd_test;

? 至此，jgpt_jzd_test 已是分區(qū)表！

五、驗(yàn)證表分區(qū)是否成功

1. 查看分區(qū)結(jié)構(gòu)

-- 查詢系統(tǒng)表
SELECT 
    inhrelid::regclass AS child_table,
    inhparent::regclass AS parent_table
FROM pg_inherits 
WHERE inhparent = 'jgpt_jzd_test'::regclass;

2. 驗(yàn)證分區(qū)剪枝是否生效

explain SELECT COUNT(*) FROM jgpt_jzd_test WHERE gmt_create >= '2025-01-01' AND gmt_create < '2026-01-01';

可以看到只查詢了jgpt_jzd_test_p2025一張分區(qū)表

六、安全刪除分區(qū)：DETACHvsDROP（關(guān)鍵區(qū)別）

在表分區(qū)的日常維護(hù)中，刪除歷史數(shù)據(jù)是一個(gè)高頻操作。PostgreSQL 提供了兩種方式來“移除”分區(qū)，但它們的安全性、可逆性和使用場(chǎng)景完全不同。

我們以 jgpt_jzd_test_p2024 分區(qū)為例，對(duì)比兩種操作：

1.DETACH PARTITION—— 安全的“解綁”操作

ALTER TABLE jgpt_jzd_test_partitioned
    DETACH PARTITION jgpt_jzd_test_p2024;

操作特點(diǎn)：

• 數(shù)據(jù)不會(huì)丟失！jgpt_jzd_test_p2024 表變成一個(gè)獨(dú)立的普通表
• 主表 jgpt_jzd_test_partitioned 不再包含該分區(qū)的數(shù)據(jù)
• 可隨時(shí)對(duì) jgpt_jzd_test_p2024 進(jìn)行查詢、導(dǎo)出、備份或重新掛載

適用場(chǎng)景：

• 需要?dú)w檔數(shù)據(jù)
• 刪除前做審計(jì)或備份
• 不確定是否永久刪除

-- 確認(rèn)無(wú)誤后，再刪除
DROP TABLE jgpt_jzd_test_p2024;

? 推薦做法：先 DETACH，再 DROP，避免誤刪。

2.DROP PARTITION—— 永久刪除

ALTER TABLE jgpt_jzd_test_partitioned
    DROP PARTITION jgpt_jzd_test_p2024;

操作特點(diǎn)：

• 數(shù)據(jù)立即永久丟失！無(wú)法通過 DROP 回滾
• 相當(dāng)于執(zhí)行了 DROP TABLE，文件被物理刪除
• 無(wú)法恢復(fù)（除非有數(shù)據(jù)庫(kù)備份）

適用場(chǎng)景：

• 確認(rèn)數(shù)據(jù)不再需要
• 緊急釋放磁盤空間
• 自動(dòng)化腳本中已確認(rèn)安全

對(duì)比總結(jié)

七、自己的理解

1.表分區(qū)之后,你在datagrip或navicat里面看到的還是一張表，數(shù)據(jù)也都在這張表里，但實(shí)際上這張表是主表，沒有存儲(chǔ)數(shù)據(jù)。

2.數(shù)據(jù)實(shí)際存儲(chǔ)在分區(qū)表里，例如jgpt_jzd_test_p2024，因此代碼里面是可以直接調(diào)用這張表的

3.平時(shí)基本不用管分區(qū)表，正常使用主表就行了，比如你插入數(shù)據(jù)，直接往jgpt_jzd_test插入，數(shù)據(jù)庫(kù)會(huì)根據(jù)你的gmt字段自動(dòng)插入到相應(yīng)的分區(qū)表里面，平時(shí)使用基本是無(wú)感的

總結(jié)：我的認(rèn)知升級(jí)

到此這篇關(guān)于PostgreSQL表分區(qū)的三種方式和操作方法的文章就介紹到這了,更多相關(guān)PostgreSQL表分區(qū)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論