概述
分區(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）的值自動路由到目標(biāo)分區(qū)。若更新某行的分區(qū)鍵值導(dǎo)致其超出原分區(qū)的邊界，該行將被遷移至新的分區(qū)。
按照上面的解釋，我們可以得出以下的一些結(jié)論：
數(shù)據(jù)存儲：分區(qū)表的主表是一張邏輯表（虛擬表），它不負(fù)責(zé)存儲數(shù)據(jù)，只負(fù)責(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ā)到指定的子表。
查詢條件中帶有分區(qū)字段且能夠定位到指定的一個分區(qū)：直接查詢該分區(qū)的數(shù)據(jù)，然后返回。
查詢條件中無分區(qū)字段或分區(qū)字段的值只能定位到一個模糊的分區(qū)范圍：查詢定位到的 N 個分區(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ù)插入&刪除：根據(jù)分區(qū)鍵的分區(qū)策略，將操作數(shù)據(jù)自動路由到指定子分區(qū)。
數(shù)據(jù)更新：
數(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)化 | ?? 精準(zhǔn)匹配查詢快
?? 等值查詢優(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è)計復(fù)雜性
功能限制
性能陷阱
分區(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ū)操作
下面以這張 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ū)：
二級分區(qū)是再根據(jù) created_at 等時間字段，在 ord_parcel_history 的基礎(chǔ)上再進行劃分分區(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)建索引，此時新建索引會被標(biāo)記為無效狀態(tài)，且不會自動應(yīng)用到現(xiàn)有分區(qū)。隨后可執(zhí)行以下操作：
在每個分區(qū)上使用 CONCURRENTLY 分別創(chuàng)建索引
通過 ALTER INDEX ... ATTACH PARTITION 將分區(qū)索引掛載至父表的索引
當(dāng)所有分區(qū)索引都完成掛載后，父級索引將自動標(biāo)記為生效狀態(tài)。
由上面的官方文檔我們可以得知：
因此，我們創(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 索引是否標(biāo)記為有效：
-- 當(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 索引標(biāo)記是否有效：
-- 當(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ū)之前，在目標(biāo)表上預(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.
對于目標(biāo)表中的每個索引，系統(tǒng)將在被掛載的表中新建一個對應(yīng)索引；或者，若該表上已存在結(jié)構(gòu)等效的索引，則直接將該索引掛載至目標(biāo)表的索引層級——該操作等同于自動執(zhí)行了 ALTER INDEX ATTACH PARTITION 命令。
根據(jù)上面的官網(wǎng)信息，我們可以得知以下幾點：
下面的流程圖，是 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 有如下特征：
分區(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 有如下特征：
分區(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;
?
?轉(zhuǎn)自https://www.cnblogs.com/booleandev/p/19012821
400 186 1886
