PostgreSQL 分區最佳實(shi)踐(jian)
概述
分區(qu)的(de)(de)本質是(shi)將(jiang)一張大(da)的(de)(de)物(wu)(wu)理表(biao)從邏(luo)輯上拆分，為(wei) N 個較小的(de)(de)物(wu)(wu)理表(biao)。
分(fen)區表按照(zhao)官方的(de)解(jie)釋如(ru)下：
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.
分區表本體作為
「虛擬表」?存在，自身不持有實際存儲空間。其物理存儲由關聯的分區（即普通物理表，子表）承擔，每個分區通過預定義的分區邊界（Partition Bounds）存儲對應的數據子集。所有插入操作將依據分區鍵列（Partition Key）的(de)(de)值(zhi)自動路由到(dao)目(mu)標分區(qu)(qu)(qu)。若(ruo)更新某行的(de)(de)分區(qu)(qu)(qu)鍵值(zhi)導(dao)致其超出原分區(qu)(qu)(qu)的(de)(de)邊(bian)界，該(gai)行將被遷移(yi)至(zhi)新的(de)(de)分區(qu)(qu)(qu)。
按照上面的解釋，我們可以(yi)得(de)出(chu)以(yi)下的一些結論：
-
數(shu)據(ju)(ju)存儲：分區表(biao)(biao)(biao)的(de)主(zhu)表(biao)(biao)(biao)是(shi)(shi)一張邏輯表(biao)(biao)(biao)（虛擬表(biao)(biao)(biao)），它(ta)不(bu)(bu)負責(ze)存儲數(shu)據(ju)(ju)，只負責(ze)數(shu)據(ju)(ju)的(de)分發，所有(you)的(de)數(shu)據(ju)(ju)都是(shi)(shi)存儲在子(zi)表(biao)(biao)(biao)中。因此，主(zhu)表(biao)(biao)(biao)的(de)數(shu)據(ju)(ju)操作(zuo)是(shi)(shi)不(bu)(bu)會(hui)產生 WAL 日(ri)志(zhi)，他的(de) WAL 日(ri)志(zhi)會(hui)由基礎的(de)子(zi)表(biao)(biao)(biao)產生。所以，我們的(de)數(shu)據(ju)(ju)實時同步，應該監聽(ting)的(de)是(shi)(shi)子(zi)表(biao)(biao)(biao)，才能獲取(qu)到(dao) WAL 日(ri)志(zhi)。
-
數據分發(fa)：當對主(zhu)表(biao)的任(ren)何一個(ge)操作，PG 會經過處理轉化(hua)下發(fa)到指定的子表(biao)。
-
數據插(cha)入&刪(shan)除：根據分區(qu)鍵的分區(qu)策略，將操(cao)作(zuo)數據自動路由到指定子分區(qu)。
-
數據更新：
-
路由查找：數據更新的
WHERE?語句中包含分區鍵，則會自動到路由到指定的子表。如果沒有，則會將此UPDATE?路由到所有的(de)分(fen)區，找(zhao)到待(dai)更(geng)新的(de)數(shu)據。 -
數(shu)據(ju)更新(xin)中(zhong)帶有分(fen)區(qu)字(zi)段(duan)(duan)：假如數(shu)據(ju)更新(xin)中(zhong)，更新(xin)了(le)分(fen)區(qu)字(zi)段(duan)(duan)，且(qie)此分(fen)區(qu)字(zi)段(duan)(duan)的值改變(bian)了(le)此條(tiao)數(shu)據(ju)的所屬分(fen)區(qu)，則會執(zhi)行兩個操作：
- 現所屬分區刪除數據：因為此數據已不屬于該分區，所以此條更新的數據將從此分區刪除。
- 新分區插入數據：將
UPDATE?后的數據INSERT?到新分區。
-
-
數據查詢：
- 查詢條件中帶有分區字段且能夠定位到指定的一個分區：直接查詢該分區的數據，然后返回。
- 查詢條件中無分區字段或分區字段的值只能定位到一個模糊的分區范圍：查詢定位到的 N 個分區，然后再把查詢出來的數據，進行二次處理，返回。
-
-
DDL 操作(zuo)：在(zai)父(fu)表上面(mian)的任何 DDL 操作(zuo)，都會經(jing)過處(chu)理，分配到每個子表上面(mian)。
分區方式
PostgreSQL 提(ti)供(gong)了以(yi)下的分區(qu)方式
范圍分區（Range Partitioning)
基于分區鍵列（單列或多列）劃分連續且互斥的數值區間。例如按日期范圍（如 2023-Q1）或業務 ID 區間劃分。邊界規則：包含下限值，不包含上限值（即左閉右開）。
示例：分區 A 范圍[1,10)，分區 B 范圍[10,20)，數值 10 歸屬分區 B。
列表分區 (List Partitioning)
通過顯式枚舉分區鍵值定義分區。每個分區存儲指定的離散值集合。
示例：按地區分區，華東分區包含('上海','江蘇','浙江')。
哈希分區 (Hash Partitioning)
通過取模運算分配數據：指定模數（modulus）和余數（remainder），分區鍵哈希值取模后匹配余數的行存入對應分區。
示例：模數=4，余數=0 的分區存儲哈希值 mod 4 = 0 的數據行。
對比
| 維度 | 范圍分區 (Range) | 列表分區 (List) | 哈希分區 (Hash) |
|---|---|---|---|
| 分區邏輯 | 連續區間（數值/日期等） | 離散值枚舉（地區/狀態等） | 哈希取模運算 |
| 邊界定義 | ?FROM A TO B（左閉右開） |
?IN (v1, v2...)? |
?WITH (MODULUS N, REMAINDER M)? |
| 數據分布 | 可能不均勻（如歷史數據集中） | 人工指定，靈活但需預定義 | 強制均勻分布 |
| 查詢優化 | ?? 高效支持范圍查詢 ?? 分區剪枝優化 |
?? 精準匹配查詢快 ?? 等值查詢優化 |
?? 等值查詢快 ?? 并行掃描均衡 |
| 典型場景 | 時間序列（日志、銷售記錄） | 業務分類（地區、產品線） | 分布式存儲（用戶 ID、隨機鍵） |
| 邊界管理 | 需防區間重疊 | 需防值重復 | 余數需覆蓋 0 到(modulus-1) |
| 縮容成本 | 高（需重組相鄰分區） | 中（修改枚舉列表） | 極高（需重分布所有數據） |
| 擴容成本 | 低（增加新分區即可） | 低（增加新分區即可） | 極高（需重分布所有數據） |
| 子分區支持 | ? 多級分區（如年 → 月） | ? 多級分區（如國家 → 城市） | ?? 僅單層 |
優缺點
優點
-
查詢性能優化
- 分區剪枝：自動跳過無關分區（如
WHERE date > '2023-01-01'僅掃描新分區） - 局部索引：高頻分區索引常駐內存，減少 I/O
- 并行掃描：不同分區可由多個 Worker 同時讀取
- 分區剪枝：自動跳過無關分區（如
-
數據管理高效
- 秒級刪除舊數據：
DROP TABLE partition_2020比DELETE快 1000 倍以上 - 零碎片化：避免
DELETE導致的表膨脹和VACUUM壓力
- 秒級刪除舊數據：
-
運維靈活性
- 滾動維護：分區級
VACUUM不鎖全表 - 動態掛載：
ATTACH/DETACH PARTITION實現數據秒級切換 - 避免出現超級大表：超級大表的維護會異常的困難（例如添加索引、字段和修復數據等操作），消耗的性能和花費是時間都會讓表的維護異常的困難！
- 滾動維護：分區級
缺點
-
設計復雜性
- 需要合理的分區設計：需要合理的選擇分區方案，假如分區方案選擇不合理，會加大系統的負載和分區管理的復雜，導致運維起來更為復雜。
-
功能限制
- 全局約束受限：唯一索引必須包含所有分區鍵
- 跨分區事務缺失：不支持分布式 ACID（如跨分區行級鎖）
- 子分區擴展列禁止：所有分區必須與父表列完全一致
-
性能陷阱
- 分區鍵更新代價高：觸發行遷移（等效
DELETE+INSERT） - 規劃器超時風險：超過 1000 個分區時查詢計劃生成延遲顯著增加
- 元數據內存膨脹：每個會話緩存分區樹，消耗額外 RAM
- 分區鍵更新代價高：觸發行遷移（等效
-
運維成本
- 統計信息收集繁瑣：需對每個分區單獨
ANALYZE? - 工具鏈兼容性差：部分 ORM/備份工具無法正確處理分區表
- 版本升級風險：PG 10-13 的分區管理性能遠低于 PG 14+
- 統計信息收集繁瑣：需對每個分區單獨
-
對開發要求更高：
- 合理使用分區特性門檻較高： 分區表的高效查詢插入需要指定條件才能觸發，如果使用不當，反而會加大數據庫的負載！
- 分區表日常維護更為復雜： 分區表的索引、字段和分區的維護比單表更為復雜，需要詳細了解才能避免各種風險！
?
分區操作
下面以這張 parcel ?表來示例，我們是如何合理的進(jin)行分區操作(zuo)：
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)
);
分區規則
首先，我們(men)的(de)分區規(gui)則如下：
一級(ji)分區主(zhu)要根(gen)據 archived 字段分區：
- archived=FALSE : 則數據保留在 ord_parcel_hot 子表中
- archived=TRUE : 則數據保留在 ord_parcel_history 子表中
二級分區是再根據(ju) created_at 等時間(jian)字段，在 ord_parcel_history 的(de)基礎上再進行(xing)劃分分區
- archived=TRUE & created_at = '2025-07-21 00:00:00' ：數據表留在 ord_parcel_history_2025 分區
- archived=TRUE & created_at = '2024-07-21 00:00:00' ：數據表留在 ord_parcel_history_2024 分區
- ...
- 簡單來說，archived 控制是否在 hot 表，還是在 history 表，created_at 控制在那張 history 表
- history 分區范圍不一定要是按照年分區，假如數據量比較大，則也可以改為半年或季度分區，這個主要取決于數據量大小，建議單個 history 分區的數據量 < 5000w
分區表的結構如下：
--| parcel
└--|parcel_hot
└--|parcel_history
└--|parcel_2025
└--|parcel_2024
└--|parcel_2023
└--|parcel_xxxx
└--|parcel_before
創建分區表
創建 parcel ?主表
要點：
主鍵為
(id, archived, created_at)?分區方式和分區鍵：
PARTITION BY LIST (archived)?
- 分區方式為
LIST分區- 分區鍵為
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);
創建 parcel_hot 數據表掛載在 parcel ?數據表下面
-- 為 parcel 添加分區：
-- 當 archived = FALSE，則分配至 hot 表
CREATE TABLE parcel_hot PARTITION OF parcel
FOR VALUES IN (FALSE);
創建二級分區的主表 parcel_history，掛載在 parcel ?數據表下面
-- 當 archived = TRUE，則分配至 history 表,且此表再根據 created_at 的 RANGE 分區方式，再進行分區
CREATE TABLE parcel_history PARTITION OF parcel
FOR VALUES IN (TRUE)
PARTITION BY RANGE (created_at);
創建 parcel_history ?下面的子表，均掛載到 parcel_history ?二(er)級(ji)分區(qu)的主表下
-- 添加 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');
以上，就完成了一張分(fen)區(qu)表的創(chuang)建！
索引維護
官方文檔：
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
CONCURRENTLYqualifier, which could lead to long lock times. To avoid this, you can useCREATE 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 usingCONCURRENTLYand attached to the partitioned index on the parent usingALTER INDEX ... ATTACH PARTITION. Once indexes for all the partitions are attached to the parent index, the parent index will be marked valid automatically.如前所述，在分區表上創建索引時可使其自動應用于整個分區層次結構。這種方式非常便捷——不僅所有現有分區會建立索引，未來新增的分區也將自動同步創建。但需要注意，分區表創建新索引時存在一項限制：無法使用
CONCURRENTLY?修飾符，這可能導致(zhi)長時間鎖(suo)定表。為避免此問題，可采用
CREATE INDEX ... ONLY?語(yu)法在分區表(biao)上創建索引(yin)，此(ci)時新建索引(yin)會(hui)被標(biao)記為無效(xiao)狀態(tai)，且(qie)不(bu)會(hui)自(zi)動(dong)應用到現(xian)有(you)分區。隨后(hou)可執(zhi)行以下操作：
- 在每個分區上使用
CONCURRENTLY?分別創建索引- 通過
ALTER INDEX ... ATTACH PARTITION將分區索引掛載至父表的索引當所有(you)分區(qu)索(suo)引都完成掛(gua)載后，父(fu)級(ji)索(suo)引將自動標記為(wei)生效狀(zhuang)態。
由上面的官(guan)方文檔我們可以(yi)得知：
-
分區表(biao)(biao)(biao)的(de)(de)父(fu)(fu)表(biao)(biao)(biao)是虛擬表(biao)(biao)(biao)，所(suo)以(yi)它(ta)的(de)(de)索引(yin)也(ye)是虛擬索引(yin)，當操作父(fu)(fu)表(biao)(biao)(biao)的(de)(de)索引(yin)的(de)(de)時候，它(ta)會在所(suo)有的(de)(de)子表(biao)(biao)(biao)上面，都創建和(he)父(fu)(fu)表(biao)(biao)(biao)等(deng)效(xiao)的(de)(de)索引(yin)。
-
創建主表的索引，無法使用
CONCURRENTLY?關鍵字，這意味(wei)著(zhu)在主表上面操作索引(yin)，會進行長時間的鎖表。- 官方建議使用
CREATE INDEX ... ONLY解決鎖表問題
- 官方建議使用
因此(ci)，我(wo)們創(chuang)建(jian)(jian)索引有兩種方式：父表創(chuang)建(jian)(jian)索引和子表創(chuang)建(jian)(jian)索引，兩種創(chuang)建(jian)(jian)索引的對比：
| 特性 | 父表（Partitioned Table）創建索引 | 子表（Partition）創建索引 |
|---|---|---|
| 索引定義方式 | ?CREATE INDEX idx_parent ON parent_table (key);?（自動級聯到所有子表） |
需在每個子表單獨創建：CREATE INDEX idx_child1 ON child1 (key);? |
| 索引物理存儲 | 虛擬索引（無實際數據），實際數據在各子表的本地索引 | 獨立的物理索引 |
| 查詢優化器行為 | 自動識別分區剪枝，僅掃描相關分區的本地索引 | 需手動確保所有子表有索引，否則未索引分區全表掃描 |
| 新增分區支持 | 自動為新分區創建索引 | 需手動為新分區創建索引 |
| 索引類型限制 | 不支持表達式索引/部分索引（需在子表單獨創建） | 支持任意索引類型 |
| 唯一約束實現 | 必須包含分區鍵（全局唯一性難保障） | 可創建子表局部唯一索引（但無法跨分區唯一） |
索引添加
因此，根據以上(shang)的(de)信息，假如(ru)我們需(xu)要在數據表上(shang)面添加索引(yin)，按照(zhao)下面的(de)例子：
本次我們目前需要在 parcel_history ?上面添加 tracking_number ?索引。
使用 CREATE INDEX ... ONLY ?關鍵字在主表 parcel_history ?上面添加索引：
-- 注意添加 ONLY 關鍵字
CREATE INDEX idx_parcel_history_tracking_number
ON ONLY parcel_history (tracking_number);
使用此 SQL ?查詢當前父表 parcel_history ?索引是否標記為有效：
-- 當前索引狀態應該返回 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';
在對應子表上面使用 CONCURRENTLY ?關鍵字添加索引，避免鎖(suo)表(biao)操作(zuo)：
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 ?索引標(biao)記是否有效：
-- 當前索引狀態應該返回 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';
此步可忽略：校(xiao)驗階段，添(tian)加一個新(xin)(xin)的分區(qu)表(biao)，查看新(xin)(xin)分區(qu)表(biao)是(shi)否添(tian)加了對應的索引：
CREATE TABLE parcel_history_2026 PARTITION OF parcel_history
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
索引刪除
假如子表的索引是由父表進行維護，則當通過子表去刪除索引的時候，這個操作是不允許的，PostgreSQL ?會直接拒絕掉這個操(cao)作：
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.
所(suo)以，假(jia)如需要(yao)某(mou)個(ge)分(fen)區表(biao)的索引(yin)，則一定需要(yao)刪除(chu)父表(biao)索引(yin)，子表(biao)的索引(yin)就會自動(dong)刪除(chu)！
DROP INDEX idx_parcel_history_tracking_number;
-- completed in 400 ms
創建新分區
在 parcel_history ?數據表上面，創建 parcel_history_2026 ?新分區
CREATE TABLE parcel_history_2026 PARTITION OF parcel_history
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
掛載分區
流程介紹
掛載(zai)分(fen)(fen)(fen)區(qu)的(de)操作和(he)(he)創建新分(fen)(fen)(fen)區(qu)來(lai)對比，復雜了很多！因為(wei)創建新分(fen)(fen)(fen)區(qu)是生成(cheng)一張(zhang)全(quan)新的(de)數據表，PostgreSQL 只需要(yao)維(wei)護對應的(de)元(yuan)數據（字段、索引、分(fen)(fen)(fen)區(qu)約束和(he)(he)主鍵(jian)等等），而(er)掛載(zai)新分(fen)(fen)(fen)區(qu)的(de)時候，因為(wei)待掛載(zai)的(de)分(fen)(fen)(fen)區(qu)已(yi)經存在(zai)大量的(de)數據了，在(zai)掛載(zai)到分(fen)(fen)(fen)區(qu)表之前，PostgreSQL 需要(yao)做一堆數據校驗工作，以下是 PostgreSQL 文(wen)(wen)檔的(de)原文(wen)(wen)：
Note that when running the
ATTACH PARTITIONcommand, the table will be scanned to validate the partition constraint while holding anACCESS EXCLUSIVElock on that partition. As shown above, it is recommended to avoid this scan by creating aCHECKconstraint matching the expected partition constraint on the table prior to attaching it. Once theATTACH PARTITIONis complete, it is recommended to drop the now-redundantCHECK?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 suitableCHECKconstraint is encountered or the leaf partitions are reached.請注意，執行
ATTACH PARTITION命令時，將對分區表加 ?ACCESS EXCLUSIVE? 鎖，并掃描表內數據以驗證分區約束。如前所述，建議在掛載分區之前，在目標表上預先創建一個與預期分區約束相匹配的 ?CHECK? 約束，以規避此掃描操作。ATTACH PARTITION操作完成后，建議刪除此時已冗余的 ?CHECK? 約束。如果待掛載的表本身也是一個分區表，那么它的每個子分區都將被遞歸地加鎖并掃描，直到遇到匹配的 ?CHECK? 約束或到達葉子分區為止。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 PARTITIONhad been executed.對于目標表中的每個索引，系統將在被掛載的表中新建一個對應索引；或者，若該表上已存在結構等效的索引，則直接將該索引掛載至目標表的索引層級——該操作等同于自動執行了 ?
ALTER INDEX ATTACH PARTITION? 命令。
根(gen)據上面(mian)的官網信息，我(wo)們可以(yi)得知以(yi)下幾(ji)點：
-
?
ATTACH PARTITION?操作，會為數據表添加 ?ACCESS EXCLUSIVE?（訪問獨占鎖，阻(zu)塞該表的所有操(cao)作(zuo)），這個操(cao)作(zuo)將導致數據(ju)表鎖死，嚴重影響(xiang)業務系統的操(cao)作(zuo) -
?
ATTACH PARTITION?操作，有兩個(ge)(ge)比較耗時的操作，但是目前這兩個(ge)(ge)操作，官方都提供(gong)了解(jie)決方案！- 對待添加的分區表添加 ?
CHECK? ?約束校驗，校驗改分區內的所有數據，是否都滿足分區鍵的約束！ - 校驗待添加的分區表中，是否存在和父表的等效索引，以維護父表的索引在子表中的傳遞！
- 對待添加的分區表添加 ?
下面的流程圖，是 DeepSeek ?對 1000w 的數據表執行 ATTACH PARTITION ?大致流程：
?
由流程圖可以得知，假如我們控制好約束和索引，則 ATTACH PARTITION ?基本上可(ke)以在秒級執行（這(zhe)點我(wo)已經做過測試）！
實際操作-hot 表
目前我們需要將一張 4000w 的 parcel_hot ?掛載到 parcel ?數據表。
parcel ?表目前有(you)如(ru)下(xia)特征：
- 索引：有一個
tracking_number?的索引，idx_parcel_tracking_number? - 主鍵：主鍵為
id, archived, created_at，主鍵名稱為parcel_pkey?
?parcel_hot ?有如下特征：
- 索引：無任何索引
- 主鍵：有一個
id, created_at?主鍵，主鍵名稱為parcel_hot_pkey?
分區校驗
首先，我們需要提前執行好 parcel_hot ?的分區約束，避免執行 ATTACH PARTITION ?時，鎖表太長時間。parcel_hot ?分區約束比較簡單：archived=FALSE。下面是約(yue)束(shu)執行(xing)的(de)詳細 SQL：
-- 添加 parcel_hot_archived_false 約束，并且只對新數據執行，老數據不執行校驗
ALTER TABLE parcel_hot
ADD CONSTRAINT parcel_hot_archived_false
CHECK (archived = FALSE) NOT VALID;
-- completed in 244 ms
-- 校驗 parcel_hot_archived_false 老數據校驗
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，而這主鍵(jian)(jian)不一致，因此無法進行掛載，所以需要我(wo)們(men)手(shou)動(dong)更(geng)換主鍵(jian)(jian)，對齊兩張數據表的主鍵(jian)(jian)！下面是更(geng)換主鍵(jian)(jian)的 SQL 操(cao)作：
-- 增加新的主鍵
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;
-- 此事務 0.5s 左右
等效索引
目前 parcel ?有一個 tracking_number ?索引，而 parcel_hot ?無任何索引，因此需要在 parcel_hot ?也添加 tracking_number ?索引(yin)(yin)。下面是添加索引(yin)(yin)的 SQL：
CREATE INDEX CONCURRENTLY idx_parcel_hot_tracking_number
ON parcel_hot (tracking_number);
-- completed in 53 s 704 ms
掛載分區
以上的(de)操作鈞執行完成后，現在就可(ke)以執行掛載分區的(de)操作了！
ALTER TABLE parcel ATTACH PARTITION parcel_hot
FOR VALUES IN (FALSE)
-- Time: 0.277s
刪除分區校驗
ALTER TABLE parcel_hot
DROP CONSTRAINT parcel_hot_archived_false;
實際操作-history
目前我們需要將一張 4000w 的 parcel_history_2023 ?掛載到 parcel_history ?數據表。
?parcel_history ?表目(mu)前(qian)有如下特征：
- 索引：有一個
tracking_number?的索引，idx_parcel_history_tracking_number? - 主鍵：主鍵為
id, archived, created_at，主鍵名稱為parcel_history_pkey?
?parcel_history_2023 ?有如下特征：
- 索引：無任何索引
- 主鍵：有一個
id, created_at?主鍵，主鍵名稱為parcel_history_2023_pkey?
分區校驗
首先，我們需要提前執行好 parcel_history_2023 ?的分區約束，避免執行 ATTACH PARTITION ?時，鎖表太長時間。parcel_history_2023 ?分區約束比較簡單：archived=FALSE AND created_at >= '2023-01-01' AND created_at < '2024-01-01'。
注意：一定不能夠將分區條件寫為下面這樣：
archived=FALSE AND created_at BETWEEN '2023-01-01' AND '2024-01-01'?因為
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01')?的時間區間為：['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]?當寫成
archived=FALSE AND created_at BETWEEN '2023-01-01' AND '2024-01-01'?這樣(yang)，依(yi)舊會執行分區校驗(yan)的 SQL，導致鎖表時間大大的加長(chang)了！
下(xia)面(mian)是約(yue)束執行的詳細 SQL：
-- 添加 parcel_hot_archived_false 約束，并且只對新數據執行，老數據不執行校驗
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 老數據校驗
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，而這(zhe)主(zhu)(zhu)鍵不一致，因此無法(fa)進行掛載，所以需要我們手動(dong)更換主(zhu)(zhu)鍵，對齊兩張數據表的(de)主(zhu)(zhu)鍵！下(xia)面是(shi)更換主(zhu)(zhu)鍵的(de) 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;
-- 此事務 0.5s 左右
等效索引
目前 parcel_history ?有一個 tracking_number ?索引，而 parcel_history_2023 ?無任何索引，因此需要在 parcel_history_2023 ?也添加 tracking_number ?索引。下(xia)面是添(tian)加索引的 SQL：
CREATE INDEX CONCURRENTLY idx_parcel_history_2023_tracking_number
ON parcel_history_2023 (tracking_number);
-- completed in 53 s 704 ms
掛載分區
以上的(de)操(cao)作鈞執行完成后，現在就(jiu)可以執行掛載分(fen)區(qu)的(de)操(cao)作了！
ALTER TABLE parcel_history ATTACH PARTITION parcel_history_2023
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')
-- Time: 0.277s
刪除分區校驗
ALTER TABLE parcel_history_2023
DROP CONSTRAINT parcel_history_2023_archived_true_created_at;
卸載分區
卸(xie)載分區(qu)一般速(su)度都(dou)比較(jiao)快(kuai)，所以相對來說比較(jiao)安全。卸(xie)載分區(qu)后，卸(xie)載的分區(qu)將以獨立的數據表存在，且不再與主(zhu)表有任何(he)關聯。
將 parcel_history_2023 ?從 parcel_history ?分區卸載：
鎖(suo)表卸(xie)載分區（鎖(suo)的時間很(hen)短）：
ALTER TABLE parcel_history DETACH PARTITION parcel_history_2023;
并(bing)發(fa)卸(xie)載分區（不鎖表）：
ALTER TABLE parcel_history DETACH PARTITION parcel_history_2023 CONCURRENTLY;
?
?
參考文檔
?
?
