PostgreSQL 分區(qū)最佳實踐

概述

分區(qū)的本質(zhì)是將一張大的物理表從邏輯上拆分，為 N 個較小的物理表。

分區(qū)表按照官方的解釋如下：

The partitioned table itself is a “virtual” table having no storage of its own. Instead, the storage belongs to partitions, which are otherwise-ordinary tables associated with the partitioned table. Each partition stores a subset of the data as defined by its partition bounds. All rows inserted into a partitioned table will be routed to the appropriate one of the partitions based on the values of the partition key column(s). Updating the partition key of a row will cause it to be moved into a different partition if it no longer satisfies the partition bounds of its original partition.

分區(qū)表本體作為 「虛擬表」 ?存在，自身不持有實際存儲空間。其物理存儲由關(guān)聯(lián)的分區(qū)（即普通物理表，子表）承擔(dān)，每個分區(qū)通過預(yù)定義的分區(qū)邊界（Partition Bounds）存儲對應(yīng)的數(shù)據(jù)子集。所有插入操作將依據(jù)分區(qū)鍵列（Partition Key）的值自動路由到目標分區(qū)。若更新某行的分區(qū)鍵值導(dǎo)致其超出原分區(qū)的邊界，該行將被遷移至新的分區(qū)。

按照上面的解釋，我們可以得出以下的一些結(jié)論：

數(shù)據(jù)存儲：分區(qū)表的主表是一張邏輯表（虛擬表），它不負責(zé)存儲數(shù)據(jù)，只負責(zé)數(shù)據(jù)的分發(fā)，所有的數(shù)據(jù)都是存儲在子表中。因此，主表的數(shù)據(jù)操作是不會產(chǎn)生 WAL 日志，他的 WAL 日志會由基礎(chǔ)的子表產(chǎn)生。所以，我們的數(shù)據(jù)實時同步，應(yīng)該監(jiān)聽的是子表，才能獲取到 WAL 日志。
數(shù)據(jù)分發(fā)：當(dāng)對主表的任何一個操作，PG 會經(jīng)過處理轉(zhuǎn)化下發(fā)到指定的子表。
- 數(shù)據(jù)插入&刪除：根據(jù)分區(qū)鍵的分區(qū)策略，將操作數(shù)據(jù)自動路由到指定子分區(qū)。
- 數(shù)據(jù)更新：
  - 路由查找：數(shù)據(jù)更新的 WHERE ?語句中包含分區(qū)鍵，則會自動到路由到指定的子表。如果沒有，則會將此 UPDATE ?路由到所有的分區(qū)，找到待更新的數(shù)據(jù)。
  - 數(shù)據(jù)更新中帶有分區(qū)字段：假如數(shù)據(jù)更新中，更新了分區(qū)字段，且此分區(qū)字段的值改變了此條數(shù)據(jù)的所屬分區(qū)，則會執(zhí)行兩個操作：
    - 現(xiàn)所屬分區(qū)刪除數(shù)據(jù)：因為此數(shù)據(jù)已不屬于該分區(qū)，所以此條更新的數(shù)據(jù)將從此分區(qū)刪除。
    - 新分區(qū)插入數(shù)據(jù)：將 UPDATE ?后的數(shù)據(jù) INSERT ?到新分區(qū)。
- 數(shù)據(jù)查詢：
  - 查詢條件中帶有分區(qū)字段且能夠定位到指定的一個分區(qū)：直接查詢該分區(qū)的數(shù)據(jù)，然后返回。
  - 查詢條件中無分區(qū)字段或分區(qū)字段的值只能定位到一個模糊的分區(qū)范圍：查詢定位到的 N 個分區(qū)，然后再把查詢出來的數(shù)據(jù)，進行二次處理，返回。
DDL 操作：在父表上面的任何 DDL 操作，都會經(jīng)過處理，分配到每個子表上面。

分區(qū)方式

PostgreSQL 提供了以下的分區(qū)方式

范圍分區(qū)（Range Partitioning)

基于分區(qū)鍵列（單列或多列）劃分連續(xù)且互斥的數(shù)值區(qū)間。例如按日期范圍（如 2023-Q1）或業(yè)務(wù) ID 區(qū)間劃分。邊界規(guī)則：包含下限值，不包含上限值（即左閉右開）。
示例：分區(qū) A 范圍[1,10)，分區(qū) B 范圍[10,20)，數(shù)值 10 歸屬分區(qū) B。

列表分區(qū) (List Partitioning)

通過顯式枚舉分區(qū)鍵值定義分區(qū)。每個分區(qū)存儲指定的離散值集合。
示例：按地區(qū)分區(qū)，華東分區(qū)包含('上海','江蘇','浙江')。

哈希分區(qū) (Hash Partitioning)

通過取模運算分配數(shù)據(jù)：指定模數(shù)（modulus）和余數(shù)（remainder），分區(qū)鍵哈希值取模后匹配余數(shù)的行存入對應(yīng)分區(qū)。
示例：模數(shù)=4，余數(shù)=0 的分區(qū)存儲哈希值 mod 4 = 0 的數(shù)據(jù)行。

對比

維度	范圍分區(qū) (Range)	列表分區(qū) (List)	哈希分區(qū) (Hash)
分區(qū)邏輯	連續(xù)區(qū)間（數(shù)值/日期等）	離散值枚舉（地區(qū)/狀態(tài)等）	哈希取模運算
邊界定義	?`FROM A TO B`（左閉右開）	?`IN (v1, v2...)`?	?`WITH (MODULUS N, REMAINDER M)`?
數(shù)據(jù)分布	可能不均勻（如歷史數(shù)據(jù)集中）	人工指定，靈活但需預(yù)定義	強制均勻分布
查詢優(yōu)化	?? 高效支持范圍查詢 ?? 分區(qū)剪枝優(yōu)化	?? 精準匹配查詢快 ?? 等值查詢優(yōu)化	?? 等值查詢快 ?? 并行掃描均衡
典型場景	時間序列（日志、銷售記錄）	業(yè)務(wù)分類（地區(qū)、產(chǎn)品線）	分布式存儲（用戶 ID、隨機鍵）
邊界管理	需防區(qū)間重疊	需防值重復(fù)	余數(shù)需覆蓋 0 到(modulus-1)
縮容成本	高（需重組相鄰分區(qū)）	中（修改枚舉列表）	極高（需重分布所有數(shù)據(jù)）
擴容成本	低（增加新分區(qū)即可）	低（增加新分區(qū)即可）	極高（需重分布所有數(shù)據(jù)）
子分區(qū)支持	? 多級分區(qū)（如年 → 月）	? 多級分區(qū)（如國家 → 城市）	?? 僅單層

優(yōu)缺點

優(yōu)點

查詢性能優(yōu)化
- 分區(qū)剪枝：自動跳過無關(guān)分區(qū)（如 WHERE date > '2023-01-01' 僅掃描新分區(qū)）
- 局部索引：高頻分區(qū)索引常駐內(nèi)存，減少 I/O
- 并行掃描：不同分區(qū)可由多個 Worker 同時讀取
數(shù)據(jù)管理高效
- 秒級刪除舊數(shù)據(jù)：DROP TABLE partition_2020 比 DELETE 快 1000 倍以上
- 零碎片化：避免 DELETE 導(dǎo)致的表膨脹和 VACUUM 壓力
運維靈活性
- 滾動維護：分區(qū)級 VACUUM 不鎖全表
- 動態(tài)掛載：ATTACH/DETACH PARTITION 實現(xiàn)數(shù)據(jù)秒級切換
- 避免出現(xiàn)超級大表：超級大表的維護會異常的困難（例如添加索引、字段和修復(fù)數(shù)據(jù)等操作），消耗的性能和花費是時間都會讓表的維護異常的困難！

缺點

設(shè)計復(fù)雜性
- 需要合理的分區(qū)設(shè)計：需要合理的選擇分區(qū)方案，假如分區(qū)方案選擇不合理，會加大系統(tǒng)的負載和分區(qū)管理的復(fù)雜，導(dǎo)致運維起來更為復(fù)雜。
功能限制
- 全局約束受限：唯一索引必須包含所有分區(qū)鍵
- 跨分區(qū)事務(wù)缺失：不支持分布式 ACID（如跨分區(qū)行級鎖）
- 子分區(qū)擴展列禁止：所有分區(qū)必須與父表列完全一致
性能陷阱
- 分區(qū)鍵更新代價高：觸發(fā)行遷移（等效 DELETE + INSERT）
- 規(guī)劃器超時風(fēng)險：超過 1000 個分區(qū)時查詢計劃生成延遲顯著增加
- 元數(shù)據(jù)內(nèi)存膨脹：每個會話緩存分區(qū)樹，消耗額外 RAM
運維成本
- 統(tǒng)計信息收集繁瑣：需對每個分區(qū)單獨 ANALYZE?
- 工具鏈兼容性差：部分 ORM/備份工具無法正確處理分區(qū)表
- 版本升級風(fēng)險：PG 10-13 的分區(qū)管理性能遠低于 PG 14+
對開發(fā)要求更高：
- 合理使用分區(qū)特性門檻較高： 分區(qū)表的高效查詢插入需要指定條件才能觸發(fā)，如果使用不當(dāng)，反而會加大數(shù)據(jù)庫的負載！
- 分區(qū)表日常維護更為復(fù)雜： 分區(qū)表的索引、字段和分區(qū)的維護比單表更為復(fù)雜，需要詳細了解才能避免各種風(fēng)險！

分區(qū)操作

下面以這張 parcel ?表來示例，我們是如何合理的進行分區(qū)操作：

CREATE TABLE parcel
(
    id              INTEGER      DEFAULT NEXTVAL('parcel_id_seq'::REGCLASS) NOT NULL,
    tracking_number VARCHAR,
    created_at      TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP                  NOT NULL,
    updated_at      TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,
    transporter     VARCHAR(255),
    platform        VARCHAR(50),
    failed_count    INTEGER      DEFAULT 0,
    server_name     VARCHAR(255),
    archived        BOOLEAN      DEFAULT FALSE                              NOT NULL,
    PRIMARY KEY (id)
);

分區(qū)規(guī)則

首先，我們的分區(qū)規(guī)則如下：

一級分區(qū)主要根據(jù) archived 字段分區(qū)：

archived=FALSE : 則數(shù)據(jù)保留在 ord_parcel_hot 子表中
archived=TRUE : 則數(shù)據(jù)保留在 ord_parcel_history 子表中

二級分區(qū)是再根據(jù) created_at 等時間字段，在 ord_parcel_history 的基礎(chǔ)上再進行劃分分區(qū)

archived=TRUE & created_at = '2025-07-21 00:00:00' ：數(shù)據(jù)表留在 ord_parcel_history_2025 分區(qū)
archived=TRUE & created_at = '2024-07-21 00:00:00' ：數(shù)據(jù)表留在 ord_parcel_history_2024 分區(qū)
...

簡單來說，archived 控制是否在 hot 表，還是在 history 表，created_at 控制在那張 history 表

history 分區(qū)范圍不一定要是按照年分區(qū)，假如數(shù)據(jù)量比較大，則也可以改為半年或季度分區(qū)，這個主要取決于數(shù)據(jù)量大小，建議單個 history 分區(qū)的數(shù)據(jù)量 < 5000w

分區(qū)表的結(jié)構(gòu)如下：

--| parcel
  └--|parcel_hot
  └--|parcel_history
     └--|parcel_2025
     └--|parcel_2024
     └--|parcel_2023
     └--|parcel_xxxx
     └--|parcel_before

創(chuàng)建分區(qū)表

創(chuàng)建 parcel ?主表

要點：

主鍵為 (id, archived, created_at)?

分區(qū)方式和分區(qū)鍵：PARTITION BY LIST (archived)?

分區(qū)方式為 LIST 分區(qū)

分區(qū)鍵為 archived 字段

CREATE TABLE parcel
(
    id              INTEGER      DEFAULT NEXTVAL('parcel_id_seq'::REGCLASS) NOT NULL,
    tracking_number VARCHAR,
    created_at      TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP                  NOT NULL,
    updated_at      TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,
    transporter     VARCHAR(255),
    platform        VARCHAR(50),
    failed_count    INTEGER      DEFAULT 0,
    server_name     VARCHAR(255),
    archived        BOOLEAN      DEFAULT FALSE                              NOT NULL,
    PRIMARY KEY (id, archived, created_at)
)
    PARTITION BY LIST (archived);

創(chuàng)建 parcel_hot 數(shù)據(jù)表掛載在 parcel ?數(shù)據(jù)表下面

-- 為 parcel 添加分區(qū)：
-- 當(dāng) archived = FALSE，則分配至 hot 表
CREATE TABLE parcel_hot PARTITION OF parcel 
        FOR VALUES IN (FALSE);

創(chuàng)建二級分區(qū)的主表 parcel_history，掛載在 parcel ?數(shù)據(jù)表下面

-- 當(dāng) archived = TRUE，則分配至 history 表,且此表再根據(jù) created_at 的 RANGE 分區(qū)方式，再進行分區(qū)
CREATE TABLE parcel_history PARTITION OF parcel 
    FOR VALUES IN (TRUE)
    PARTITION BY RANGE (created_at);

創(chuàng)建 parcel_history ?下面的子表，均掛載到 parcel_history ?二級分區(qū)的主表下

-- 添加 clr_parcel_clearance_history_2025  至 clr_parcel_clearance_history
CREATE TABLE parcel_history_2025  PARTITION OF parcel_history 
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE parcel_history_2024  PARTITION OF parcel_history
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE parcel_history_2023  PARTITION OF parcel_history 
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

以上，就完成了一張分區(qū)表的創(chuàng)建！

索引維護

官方文檔：

As mentioned earlier, it is possible to create indexes on partitioned tables so that they are applied automatically to the entire hierarchy. This can be very convenient as not only will all existing partitions be indexed, but any future partitions will be as well. However, one limitation when creating new indexes on partitioned tables is that it is not possible to use the CONCURRENTLY qualifier, which could lead to long lock times. To avoid this, you can use CREATE INDEX ON ONLY ?the partitioned table, which creates the new index marked as invalid, preventing automatic application to existing partitions. Instead, indexes can then be created individually on each partition using CONCURRENTLY and attached to the partitioned index on the parent using ALTER INDEX ... ATTACH PARTITION. Once indexes for all the partitions are attached to the parent index, the parent index will be marked valid automatically.

如前所述，在分區(qū)表上創(chuàng)建索引時可使其自動應(yīng)用于整個分區(qū)層次結(jié)構(gòu)。這種方式非常便捷——不僅所有現(xiàn)有分區(qū)會建立索引，未來新增的分區(qū)也將自動同步創(chuàng)建。但需要注意，分區(qū)表創(chuàng)建新索引時存在一項限制：無法使用 CONCURRENTLY ?修飾符，這可能導(dǎo)致長時間鎖定表。

為避免此問題，可采用 CREATE INDEX ... ONLY ?語法在分區(qū)表上創(chuàng)建索引，此時新建索引會被標記為無效狀態(tài)，且不會自動應(yīng)用到現(xiàn)有分區(qū)。隨后可執(zhí)行以下操作：

在每個分區(qū)上使用 CONCURRENTLY ?分別創(chuàng)建索引

通過 ALTER INDEX ... ATTACH PARTITION 將分區(qū)索引掛載至父表的索引

當(dāng)所有分區(qū)索引都完成掛載后，父級索引將自動標記為生效狀態(tài)。

由上面的官方文檔我們可以得知：

分區(qū)表的父表是虛擬表，所以它的索引也是虛擬索引，當(dāng)操作父表的索引的時候，它會在所有的子表上面，都創(chuàng)建和父表等效的索引。
創(chuàng)建主表的索引，無法使用 CONCURRENTLY ?關(guān)鍵字，這意味著在主表上面操作索引，會進行長時間的鎖表。
- 官方建議使用 CREATE INDEX ... ONLY 解決鎖表問題

因此，我們創(chuàng)建索引有兩種方式：父表創(chuàng)建索引和子表創(chuàng)建索引，兩種創(chuàng)建索引的對比：

特性	父表（Partitioned Table）創(chuàng)建索引	子表（Partition）創(chuàng)建索引
索引定義方式	?`CREATE INDEX idx_parent ON parent_table (key);`? （自動級聯(lián)到所有子表）	需在每個子表單獨創(chuàng)建： `CREATE INDEX idx_child1 ON child1 (key);`?
索引物理存儲	虛擬索引（無實際數(shù)據(jù)），實際數(shù)據(jù)在各子表的本地索引	獨立的物理索引
查詢優(yōu)化器行為	自動識別分區(qū)剪枝，僅掃描相關(guān)分區(qū)的本地索引	需手動確保所有子表有索引，否則未索引分區(qū)全表掃描
新增分區(qū)支持	自動為新分區(qū)創(chuàng)建索引	需手動為新分區(qū)創(chuàng)建索引
索引類型限制	不支持表達式索引/部分索引（需在子表單獨創(chuàng)建）	支持任意索引類型
唯一約束實現(xiàn)	必須包含分區(qū)鍵（全局唯一性難保障）	可創(chuàng)建子表局部唯一索引（但無法跨分區(qū)唯一）

索引添加

因此，根據(jù)以上的信息，假如我們需要在數(shù)據(jù)表上面添加索引，按照下面的例子：

本次我們目前需要在 parcel_history ?上面添加 tracking_number ?索引。

使用 CREATE INDEX ... ONLY ?關(guān)鍵字在主表 parcel_history ?上面添加索引：

-- 注意添加 ONLY 關(guān)鍵字
CREATE INDEX idx_parcel_history_tracking_number
    ON ONLY parcel_history (tracking_number);

使用此 SQL ?查詢當(dāng)前父表 parcel_history ?索引是否標記為有效：

-- 當(dāng)前索引狀態(tài)應(yīng)該返回 FALSE
SELECT
    c.relname AS index_name,
    i.indisvalid AS is_valid
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'idx_parcel_history_tracking_number';

在對應(yīng)子表上面使用 CONCURRENTLY ?關(guān)鍵字添加索引，避免鎖表操作：


CREATE INDEX CONCURRENTLY idx_parcel_history_2025_tracking_number
    ON parcel_history_2025 (tracking_number);

CREATE INDEX CONCURRENTLY idx_parcel_history_2024_tracking_number
    ON parcel_history_2024 (tracking_number);

-- ...

將新加的索引，通過 ATTACH PARTITION ?操作，添加到 parcel_history ?表 idx_parcel_history_tracking_number ?上面：

ALTER INDEX idx_parcel_history_tracking_number
    ATTACH PARTITION idx_parcel_history_2021_tracking_number;

ALTER INDEX idx_parcel_history_tracking_number
    ATTACH PARTITION idx_parcel_history_2022_tracking_number;

待所有子表都添加完索引后，校驗父表 parcel_history ?索引標記是否有效：

-- 當(dāng)前索引狀態(tài)應(yīng)該返回 TRUE
SELECT
    c.relname AS index_name,
    i.indisvalid AS is_valid
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'idx_parcel_history_tracking_number';

此步可忽略：校驗階段，添加一個新的分區(qū)表，查看新分區(qū)表是否添加了對應(yīng)的索引：

CREATE TABLE parcel_history_2026  PARTITION OF parcel_history 
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

索引刪除

假如子表的索引是由父表進行維護，則當(dāng)通過子表去刪除索引的時候，這個操作是不允許的，PostgreSQL ?會直接拒絕掉這個操作：

DROP INDEX idx_parcel_history_2025_tracking_number;
-- ERROR: cannot drop index parcel_2023_tracking_number_idx because index idx_parcel_history_tracking_number requires it
-- 建議：You can drop index idx_parcel_history_tracking_number instead.

所以，假如需要某個分區(qū)表的索引，則一定需要刪除父表索引，子表的索引就會自動刪除！

DROP INDEX idx_parcel_history_tracking_number;
-- completed in 400 ms

創(chuàng)建新分區(qū)

在 parcel_history ?數(shù)據(jù)表上面，創(chuàng)建 parcel_history_2026 ?新分區(qū)

CREATE TABLE parcel_history_2026  PARTITION OF parcel_history 
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

掛載分區(qū)

流程介紹

掛載分區(qū)的操作和創(chuàng)建新分區(qū)來對比，復(fù)雜了很多！因為創(chuàng)建新分區(qū)是生成一張全新的數(shù)據(jù)表，PostgreSQL 只需要維護對應(yīng)的元數(shù)據(jù)（字段、索引、分區(qū)約束和主鍵等等），而掛載新分區(qū)的時候，因為待掛載的分區(qū)已經(jīng)存在大量的數(shù)據(jù)了，在掛載到分區(qū)表之前，PostgreSQL 需要做一堆數(shù)據(jù)校驗工作，以下是 PostgreSQL 文檔的原文：

Note that when running the ATTACH PARTITION command, the table will be scanned to validate the partition constraint while holding an ACCESS EXCLUSIVE lock on that partition. As shown above, it is recommended to avoid this scan by creating a CHECK constraint matching the expected partition constraint on the table prior to attaching it. Once the ATTACH PARTITION is complete, it is recommended to drop the now-redundant CHECK ?constraint. If the table being attached is itself a partitioned table, then each of its sub-partitions will be recursively locked and scanned until either a suitable CHECK constraint is encountered or the leaf partitions are reached.

請注意，執(zhí)行 ATTACH PARTITION 命令時，將對分區(qū)表加 ?ACCESS EXCLUSIVE? 鎖，并掃描表內(nèi)數(shù)據(jù)以驗證分區(qū)約束。如前所述，建議在掛載分區(qū)之前，在目標表上預(yù)先創(chuàng)建一個與預(yù)期分區(qū)約束相匹配的 ?CHECK? 約束，以規(guī)避此掃描操作。ATTACH PARTITION 操作完成后，建議刪除此時已冗余的 ?CHECK? 約束。如果待掛載的表本身也是一個分區(qū)表，那么它的每個子分區(qū)都將被遞歸地加鎖并掃描，直到遇到匹配的 ?CHECK? 約束或到達葉子分區(qū)為止。

For each index in the target table, a corresponding one will be created in the attached table; or, if an equivalent index already exists, it will be attached to the target table's index, as if ALTER INDEX ATTACH PARTITION had been executed.

對于目標表中的每個索引，系統(tǒng)將在被掛載的表中新建一個對應(yīng)索引；或者，若該表上已存在結(jié)構(gòu)等效的索引，則直接將該索引掛載至目標表的索引層級——該操作等同于自動執(zhí)行了 ?ALTER INDEX ATTACH PARTITION? 命令。

根據(jù)上面的官網(wǎng)信息，我們可以得知以下幾點：

?ATTACH PARTITION ?操作，會為數(shù)據(jù)表添加 ?ACCESS EXCLUSIVE?（訪問獨占鎖，阻塞該表的所有操作），這個操作將導(dǎo)致數(shù)據(jù)表鎖死，嚴重影響業(yè)務(wù)系統(tǒng)的操作
?ATTACH PARTITION ?操作，有兩個比較耗時的操作，但是目前這兩個操作，官方都提供了解決方案！
- 對待添加的分區(qū)表添加 ?CHECK? ?約束校驗，校驗改分區(qū)內(nèi)的所有數(shù)據(jù)，是否都滿足分區(qū)鍵的約束！
- 校驗待添加的分區(qū)表中，是否存在和父表的等效索引，以維護父表的索引在子表中的傳遞！

下面的流程圖，是 DeepSeek ?對 1000w 的數(shù)據(jù)表執(zhí)行 ATTACH PARTITION ?大致流程：

graph TD A[開始ATTACH PARTITION] --> B[立即請求 ACCESS EXCLUSIVE 鎖] B --> C{是否預(yù)創(chuàng)建已驗證的 CHECK 約束？} C -- 是 --> D[跳過全表掃描<br>直接信任約束] C -- 否 --> E[在鎖保護下掃描數(shù)據(jù)<br>SSD:20-50min] D --> F{新分區(qū)是否有等效索引？} E --> F F -- 無索引 --> G[在鎖保護下創(chuàng)建索引<br>SSD:10-30min] F -- 有索引 --> H[掛載索引<br>0.1-1s] G --> I[更新元數(shù)據(jù)] H --> I I --> J[刪除預(yù)創(chuàng)建約束] J --> K[結(jié)束釋放鎖] classDef red fill:#f9d5d5,stroke:#e88; classDef green fill:#d5f0d5,stroke:#8e8; class E,G red; class D,H green;

由流程圖可以得知，假如我們控制好約束和索引，則 ATTACH PARTITION ?基本上可以在秒級執(zhí)行（這點我已經(jīng)做過測試）！

實際操作-hot 表

目前我們需要將一張 4000w 的 parcel_hot ?掛載到 parcel ?數(shù)據(jù)表。

parcel ?表目前有如下特征：

索引：有一個 tracking_number ?的索引，idx_parcel_tracking_number?
主鍵：主鍵為 id, archived, created_at，主鍵名稱為 parcel_pkey?

?parcel_hot ?有如下特征：

索引：無任何索引
主鍵：有一個 id, created_at ?主鍵，主鍵名稱為 parcel_hot_pkey?

分區(qū)校驗

首先，我們需要提前執(zhí)行好 parcel_hot ?的分區(qū)約束，避免執(zhí)行 ATTACH PARTITION ?時，鎖表太長時間。parcel_hot ?分區(qū)約束比較簡單：archived=FALSE。下面是約束執(zhí)行的詳細 SQL：

-- 添加 parcel_hot_archived_false 約束，并且只對新數(shù)據(jù)執(zhí)行，老數(shù)據(jù)不執(zhí)行校驗
ALTER TABLE parcel_hot
    ADD CONSTRAINT parcel_hot_archived_false
        CHECK (archived = FALSE) NOT VALID;
-- completed in 244 ms

-- 校驗 parcel_hot_archived_false 老數(shù)據(jù)校驗
ALTER TABLE parcel_hot
VALIDATE CONSTRAINT parcel_hot_archived_false;
-- completed in 37 s 561 ms

主鍵替換

目前 parcel_hot ?的主鍵為 id, created_at，而 parcel ?的主鍵為 id, archived, created_at，而這主鍵不一致，因此無法進行掛載，所以需要我們手動更換主鍵，對齊兩張數(shù)據(jù)表的主鍵！下面是更換主鍵的 SQL 操作：

-- 增加新的主鍵
CREATE UNIQUE INDEX CONCURRENTLY parcel_hot_pkey_new
ON parcel_hot (id, archived, created_at);
-- Time: 160.987s

-- 替換主鍵
BEGIN;
ALTER TABLE parcel_hot DROP CONSTRAINT parcel_hot_pkey;
ALTER TABLE parcel_hot ADD PRIMARY KEY USING INDEX parcel_hot_pkey_new;
COMMIT;
-- 此事務(wù) 0.5s 左右

等效索引

目前 parcel ?有一個 tracking_number ?索引，而 parcel_hot ?無任何索引，因此需要在 parcel_hot ?也添加 tracking_number ?索引。下面是添加索引的 SQL：

CREATE INDEX CONCURRENTLY idx_parcel_hot_tracking_number
    ON parcel_hot (tracking_number);
-- completed in 53 s 704 ms

掛載分區(qū)

以上的操作鈞執(zhí)行完成后，現(xiàn)在就可以執(zhí)行掛載分區(qū)的操作了！

ALTER TABLE parcel ATTACH PARTITION parcel_hot
    FOR VALUES IN (FALSE)
-- Time: 0.277s

刪除分區(qū)校驗

ALTER TABLE parcel_hot
DROP CONSTRAINT parcel_hot_archived_false;

實際操作-history

目前我們需要將一張 4000w 的 parcel_history_2023 ?掛載到 parcel_history ?數(shù)據(jù)表。

?parcel_history ?表目前有如下特征：

索引：有一個 tracking_number ?的索引，idx_parcel_history_tracking_number?
主鍵：主鍵為 id, archived, created_at，主鍵名稱為 parcel_history_pkey?

?parcel_history_2023 ?有如下特征：

索引：無任何索引
主鍵：有一個 id, created_at ?主鍵，主鍵名稱為 parcel_history_2023_pkey?

分區(qū)校驗

首先，我們需要提前執(zhí)行好 parcel_history_2023 ?的分區(qū)約束，避免執(zhí)行 ATTACH PARTITION ?時，鎖表太長時間。parcel_history_2023 ?分區(qū)約束比較簡單：archived=FALSE AND created_at >= '2023-01-01' AND created_at < '2024-01-01'。

注意：一定不能夠?qū)⒎謪^(qū)條件寫為下面這樣： archived=FALSE AND created_at BETWEEN '2023-01-01' AND '2024-01-01'?

因為 FOR VALUES FROM ('2026-01-01') TO ('2027-01-01') ?的時間區(qū)間為：['2023-01-01 00:00:00', '2024-01-01 00:00:00)?

而 BETWEEN '2023-01-01' AND '2024-01-01' ?的時間取件為：['2023-01-01 00:00:00', '2024-12-01 00:00:00]?

當(dāng)寫成 archived=FALSE AND created_at BETWEEN '2023-01-01' AND '2024-01-01' ?這樣，依舊會執(zhí)行分區(qū)校驗的 SQL，導(dǎo)致鎖表時間大大的加長了！

下面是約束執(zhí)行的詳細 SQL：

-- 添加 parcel_hot_archived_false 約束，并且只對新數(shù)據(jù)執(zhí)行，老數(shù)據(jù)不執(zhí)行校驗
ALTER TABLE parcel_history_2023
    ADD CONSTRAINT parcel_history_2023_archived_true_created_at
        CHECK (archived = TRUE AND created_at >= '2023-01-01' AND created_at < '2024-01-01') NOT VALID;
-- Time: 0.194s

-- completed in 244 ms

-- 校驗 parcel_hot_archived_false 老數(shù)據(jù)校驗
ALTER TABLE parcel_history_2023
VALIDATE CONSTRAINT parcel_history_2023_archived_true_created_at;
-- Time: 72.051s

主鍵替換

目前 parcel_history_2023 ?的主鍵為 id, created_at，而 parcel_history ?的主鍵為 id, archived, created_at，而這主鍵不一致，因此無法進行掛載，所以需要我們手動更換主鍵，對齊兩張數(shù)據(jù)表的主鍵！下面是更換主鍵的 SQL 操作：

-- 增加新的主鍵
CREATE UNIQUE INDEX CONCURRENTLY parcel_history_2023_new_key
ON parcel_history_2023 (id, archived, created_at);
-- Time: 160.987s

-- 替換主鍵
BEGIN;
ALTER TABLE parcel_hot DROP CONSTRAINT parcel_history_2023_pkey;
ALTER TABLE parcel_hot ADD PRIMARY KEY USING INDEX parcel_history_2023_pkey_new;
COMMIT;
-- 此事務(wù) 0.5s 左右

等效索引

目前 parcel_history ?有一個 tracking_number ?索引，而 parcel_history_2023 ?無任何索引，因此需要在 parcel_history_2023 ?也添加 tracking_number ?索引。下面是添加索引的 SQL：

CREATE INDEX CONCURRENTLY idx_parcel_history_2023_tracking_number
    ON parcel_history_2023 (tracking_number);
-- completed in 53 s 704 ms

掛載分區(qū)

以上的操作鈞執(zhí)行完成后，現(xiàn)在就可以執(zhí)行掛載分區(qū)的操作了！

ALTER TABLE parcel_history ATTACH PARTITION parcel_history_2023
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')
-- Time: 0.277s

刪除分區(qū)校驗

ALTER TABLE parcel_history_2023
DROP CONSTRAINT parcel_history_2023_archived_true_created_at;

卸載分區(qū)

卸載分區(qū)一般速度都比較快，所以相對來說比較安全。卸載分區(qū)后，卸載的分區(qū)將以獨立的數(shù)據(jù)表存在，且不再與主表有任何關(guān)聯(lián)。

將 parcel_history_2023 ?從 parcel_history ?分區(qū)卸載：

鎖表卸載分區(qū)（鎖的時間很短）：

ALTER TABLE parcel_history  DETACH PARTITION parcel_history_2023;

并發(fā)卸載分區(qū)（不鎖表）：

ALTER TABLE parcel_history  DETACH PARTITION parcel_history_2023 CONCURRENTLY;

參考文檔

PostgreSQL: Documentation: 17: ALTER TABLE?

PostgreSQL: Documentation: 17: 5.12. Table Partitioning

posted @ 2025-07-30 14:18 booleandev 閱讀(560) 評論(0) 收藏舉報

刷新頁面返回頂部

Jantor的博客

PostgreSQL 分區(qū)最佳實踐

概述

分區(qū)方式

范圍分區(qū)（Range Partitioning)

列表分區(qū) (List Partitioning)

哈希分區(qū) (Hash Partitioning)

對比

優(yōu)缺點

優(yōu)點

缺點

分區(qū)操作

分區(qū)規(guī)則

創(chuàng)建分區(qū)表

索引維護

索引添加

索引刪除

創(chuàng)建新分區(qū)

掛載分區(qū)

流程介紹

實際操作-hot 表

分區(qū)校驗

主鍵替換

等效索引

掛載分區(qū)

刪除分區(qū)校驗

實際操作-history

分區(qū)校驗

主鍵替換

等效索引

掛載分區(qū)

刪除分區(qū)校驗

卸載分區(qū)

參考文檔

公告