概述
分區(qū)的本質(zhì)是將一張大的物理表從邏輯上拆分，為 N 個(gè)較小的物理表。
分區(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ū)表本體作為 「虛擬表」 存在，自身不持有實(shí)際存儲(chǔ)空間。其物理存儲(chǔ)由關(guān)聯(lián)的分區(qū)（即普通物理表，子表）承擔(dān)，每個(gè)分區(qū)通過(guò)預(yù)定義的分區(qū)邊界（Partition Bounds）存儲(chǔ)對(duì)應(yīng)的數(shù)據(jù)子集。所有插入操作將依據(jù)分區(qū)鍵列（Partition Key）的值自動(dòng)路由到目標(biāo)分區(qū)。若更新某行的分區(qū)鍵值導(dǎo)致其超出原分區(qū)的邊界，該行將被遷移至新的分區(qū)。
按照上面的解釋?zhuān)覀兛梢缘贸鲆韵碌囊恍┙Y(jié)論：
數(shù)據(jù)存儲(chǔ)：分區(qū)表的主表是一張邏輯表（虛擬表），它不負(fù)責(zé)存儲(chǔ)數(shù)據(jù)，只負(fù)責(zé)數(shù)據(jù)的分發(fā)，所有的數(shù)據(jù)都是存儲(chǔ)在子表中。因此，主表的數(shù)據(jù)操作是不會(huì)產(chǎn)生 WAL 日志，他的 WAL 日志會(huì)由基礎(chǔ)的子表產(chǎn)生。所以，我們的數(shù)據(jù)實(shí)時(shí)同步，應(yīng)該監(jiān)聽(tīng)的是子表，才能獲取到 WAL 日志。
數(shù)據(jù)分發(fā)：當(dāng)對(duì)主表的任何一個(gè)操作，PG 會(huì)經(jīng)過(guò)處理轉(zhuǎn)化下發(fā)到指定的子表。
查詢(xún)條件中帶有分區(qū)字段且能夠定位到指定的一個(gè)分區(qū)：直接查詢(xún)?cè)摲謪^(qū)的數(shù)據(jù)，然后返回。
查詢(xún)條件中無(wú)分區(qū)字段或分區(qū)字段的值只能定位到一個(gè)模糊的分區(qū)范圍：查詢(xún)定位到的 N 個(gè)分區(qū)，然后再把查詢(xún)出來(lái)的數(shù)據(jù)，進(jìn)行二次處理，返回。
路由查找：數(shù)據(jù)更新的 WHERE 語(yǔ)句中包含分區(qū)鍵，則會(huì)自動(dòng)到路由到指定的子表。如果沒(méi)有，則會(huì)將此 UPDATE 路由到所有的分區(qū)，找到待更新的數(shù)據(jù)。
數(shù)據(jù)更新中帶有分區(qū)字段：假如數(shù)據(jù)更新中，更新了分區(qū)字段，且此分區(qū)字段的值改變了此條數(shù)據(jù)的所屬分區(qū)，則會(huì)執(zhí)行兩個(gè)操作：
現(xiàn)所屬分區(qū)刪除數(shù)據(jù)：因?yàn)榇藬?shù)據(jù)已不屬于該分區(qū)，所以此條更新的數(shù)據(jù)將從此分區(qū)刪除。
新分區(qū)插入數(shù)據(jù)：將 UPDATE 后的數(shù)據(jù) INSERT 到新分區(qū)。
數(shù)據(jù)插入&刪除：根據(jù)分區(qū)鍵的分區(qū)策略，將操作數(shù)據(jù)自動(dòng)路由到指定子分區(qū)。
數(shù)據(jù)更新：
數(shù)據(jù)查詢(xún)：
DDL 操作：在父表上面的任何 DDL 操作，都會(huì)經(jīng)過(guò)處理，分配到每個(gè)子表上面。
分區(qū)方式
PostgreSQL 提供了以下的分區(qū)方式
范圍分區(qū)（Range Partitioning)
基于分區(qū)鍵列（單列或多列）劃分連續(xù)且互斥的數(shù)值區(qū)間。例如按日期范圍（如 2023-Q1）或業(yè)務(wù) ID 區(qū)間劃分。邊界規(guī)則：包含下限值，不包含上限值（即左閉右開(kāi)）。
示例：分區(qū) A 范圍[1,10)，分區(qū) B 范圍[10,20)，數(shù)值 10 歸屬分區(qū) B。
列表分區(qū) (List Partitioning)
通過(guò)顯式枚舉分區(qū)鍵值定義分區(qū)。每個(gè)分區(qū)存儲(chǔ)指定的離散值集合。
示例：按地區(qū)分區(qū)，華東分區(qū)包含('上海','江蘇','浙江')。
哈希分區(qū) (Hash Partitioning)
通過(guò)取模運(yùn)算分配數(shù)據(jù)：指定模數(shù)（modulus）和余數(shù)（remainder），分區(qū)鍵哈希值取模后匹配余數(shù)的行存入對(duì)應(yīng)分區(qū)。
示例：模數(shù)=4，余數(shù)=0 的分區(qū)存儲(chǔ)哈希值 mod 4 = 0 的數(shù)據(jù)行。
對(duì)比
| 維度 | 范圍分區(qū) (Range) | 列表分區(qū) (List) | 哈希分區(qū) (Hash) |
|---|
| 分區(qū)邏輯 | 連續(xù)區(qū)間（數(shù)值/日期等） | 離散值枚舉（地區(qū)/狀態(tài)等） | 哈希取模運(yùn)算 |
| 邊界定義 | FROM A TO B（左閉右開(kāi)） | IN (v1, v2...) | WITH (MODULUS N, REMAINDER M) |
| 數(shù)據(jù)分布 | 可能不均勻（如歷史數(shù)據(jù)集中） | 人工指定，靈活但需預(yù)定義 | 強(qiáng)制均勻分布 |
| 查詢(xún)優(yōu)化 | ?? 高效支持范圍查詢(xún)
?? 分區(qū)剪枝優(yōu)化 | ?? 精準(zhǔn)匹配查詢(xún)快
?? 等值查詢(xún)優(yōu)化 | ?? 等值查詢(xún)快
?? 并行掃描均衡 |
| 典型場(chǎng)景 | 時(shí)間序列（日志、銷(xiāo)售記錄） | 業(yè)務(wù)分類(lèi)（地區(qū)、產(chǎn)品線(xiàn)） | 分布式存儲(chǔ)（用戶(hù) ID、隨機(jī)鍵） |
| 邊界管理 | 需防區(qū)間重疊 | 需防值重復(fù) | 余數(shù)需覆蓋 0 到(modulus-1) |
| 縮容成本 | 高（需重組相鄰分區(qū)） | 中（修改枚舉列表） | 極高（需重分布所有數(shù)據(jù)） |
| 擴(kuò)容成本 | 低（增加新分區(qū)即可） | 低（增加新分區(qū)即可） | 極高（需重分布所有數(shù)據(jù)） |
| 子分區(qū)支持 | ? 多級(jí)分區(qū)（如年 → 月） | ? 多級(jí)分區(qū)（如國(guó)家 → 城市） | ?? 僅單層 |
優(yōu)缺點(diǎn)
優(yōu)點(diǎn)
查詢(xún)性能優(yōu)化
分區(qū)剪枝：自動(dòng)跳過(guò)無(wú)關(guān)分區(qū)（如 WHERE date > '2023-01-01' 僅掃描新分區(qū)）
局部索引：高頻分區(qū)索引常駐內(nèi)存，減少 I/O
并行掃描：不同分區(qū)可由多個(gè) Worker 同時(shí)讀取
數(shù)據(jù)管理高效
運(yùn)維靈活性
滾動(dòng)維護(hù)：分區(qū)級(jí) VACUUM 不鎖全表
動(dòng)態(tài)掛載：ATTACH/DETACH PARTITION 實(shí)現(xiàn)數(shù)據(jù)秒級(jí)切換
避免出現(xiàn)超級(jí)大表：超級(jí)大表的維護(hù)會(huì)異常的困難（例如添加索引、字段和修復(fù)數(shù)據(jù)等操作），消耗的性能和花費(fèi)是時(shí)間都會(huì)讓表的維護(hù)異常的困難！
缺點(diǎn)
設(shè)計(jì)復(fù)雜性
功能限制
性能陷阱
分區(qū)鍵更新代價(jià)高：觸發(fā)行遷移（等效 DELETE + INSERT）
規(guī)劃器超時(shí)風(fēng)險(xiǎn)：超過(guò) 1000 個(gè)分區(qū)時(shí)查詢(xún)計(jì)劃生成延遲顯著增加
元數(shù)據(jù)內(nèi)存膨脹：每個(gè)會(huì)話(huà)緩存分區(qū)樹(shù)，消耗額外 RAM
運(yùn)維成本
統(tǒng)計(jì)信息收集繁瑣：需對(duì)每個(gè)分區(qū)單獨(dú) ANALYZE
工具鏈兼容性差：部分 ORM/備份工具無(wú)法正確處理分區(qū)表
版本升級(jí)風(fēng)險(xiǎn)：PG 10-13 的分區(qū)管理性能遠(yuǎn)低于 PG 14+
對(duì)開(kāi)發(fā)要求更高：
?
分區(qū)操作
下面以這張 parcel 表來(lái)示例，我們是如何合理的進(jìn)行分區(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ī)則如下：
一級(jí)分區(qū)主要根據(jù) archived 字段分區(qū)：
二級(jí)分區(qū)是再根據(jù) created_at 等時(shí)間字段，在 ord_parcel_history 的基礎(chǔ)上再進(jìn)行劃分分區(qū)
簡(jiǎn)單來(lái)說(shuō)，archived 控制是否在 hot 表，還是在 history 表，created_at 控制在那張 history 表
history 分區(qū)范圍不一定要是按照年分區(qū)，假如數(shù)據(jù)量比較大，則也可以改為半年或季度分區(qū)，這個(gè)主要取決于數(shù)據(jù)量大小，建議單個(gè) 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 主表
要點(diǎn)：
主鍵為 (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)建二級(jí)分區(qū)的主表 parcel_history，掛載在 parcel 數(shù)據(jù)表下面
-- 當(dāng) archived = TRUE，則分配至 history 表,且此表再根據(jù) created_at 的 RANGE 分區(qū)方式，再進(jìn)行分區(qū)
CREATE TABLE parcel_history PARTITION OF parcel
FOR VALUES IN (TRUE)
PARTITION BY RANGE (created_at);
創(chuàng)建 parcel_history 下面的子表，均掛載到 parcel_history 二級(jí)分區(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)建！
索引維護(hù)
官方文檔：
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)建索引時(shí)可使其自動(dòng)應(yīng)用于整個(gè)分區(qū)層次結(jié)構(gòu)。這種方式非常便捷——不僅所有現(xiàn)有分區(qū)會(huì)建立索引，未來(lái)新增的分區(qū)也將自動(dòng)同步創(chuàng)建。但需要注意，分區(qū)表創(chuàng)建新索引時(shí)存在一項(xiàng)限制：無(wú)法使用 CONCURRENTLY 修飾符，這可能導(dǎo)致長(zhǎng)時(shí)間鎖定表。
為避免此問(wèn)題，可采用 CREATE INDEX ... ONLY 語(yǔ)法在分區(qū)表上創(chuàng)建索引，此時(shí)新建索引會(huì)被標(biāo)記為無(wú)效狀態(tài)，且不會(huì)自動(dòng)應(yīng)用到現(xiàn)有分區(qū)。隨后可執(zhí)行以下操作：
在每個(gè)分區(qū)上使用 CONCURRENTLY 分別創(chuàng)建索引
通過(guò) ALTER INDEX ... ATTACH PARTITION 將分區(qū)索引掛載至父表的索引
當(dāng)所有分區(qū)索引都完成掛載后，父級(jí)索引將自動(dòng)標(biāo)記為生效狀態(tài)。
由上面的官方文檔我們可以得知：
因此，我們創(chuàng)建索引有兩種方式：父表創(chuàng)建索引和子表創(chuàng)建索引，兩種創(chuàng)建索引的對(duì)比：
| 特性 | 父表（Partitioned Table）創(chuàng)建索引 | 子表（Partition）創(chuàng)建索引 |
|---|
| 索引定義方式 | CREATE INDEX idx_parent ON parent_table (key);
（自動(dòng)級(jí)聯(lián)到所有子表） | 需在每個(gè)子表單獨(dú)創(chuàng)建：
CREATE INDEX idx_child1 ON child1 (key); |
| 索引物理存儲(chǔ) | 虛擬索引（無(wú)實(shí)際數(shù)據(jù)），實(shí)際數(shù)據(jù)在各子表的本地索引 | 獨(dú)立的物理索引 |
| 查詢(xún)優(yōu)化器行為 | 自動(dòng)識(shí)別分區(qū)剪枝，僅掃描相關(guān)分區(qū)的本地索引 | 需手動(dòng)確保所有子表有索引，否則未索引分區(qū)全表掃描 |
| 新增分區(qū)支持 | 自動(dòng)為新分區(qū)創(chuàng)建索引 | 需手動(dòng)為新分區(qū)創(chuàng)建索引 |
| 索引類(lèi)型限制 | 不支持表達(dá)式索引/部分索引（需在子表單獨(dú)創(chuàng)建） | 支持任意索引類(lèi)型 |
| 唯一約束實(shí)現(xiàn) | 必須包含分區(qū)鍵（全局唯一性難保障） | 可創(chuàng)建子表局部唯一索引（但無(wú)法跨分區(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 查詢(xún)當(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';
在對(duì)應(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);
-- ...
將新加的索引，通過(guò) 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;
待所有子表都添加完索引后，校驗(yàn)父表 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';
此步可忽略：校驗(yàn)階段，添加一個(gè)新的分區(qū)表，查看新分區(qū)表是否添加了對(duì)應(yīng)的索引：
CREATE TABLE parcel_history_2026 PARTITION OF parcel_history
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
索引刪除
假如子表的索引是由父表進(jìn)行維護(hù)，則當(dāng)通過(guò)子表去刪除索引的時(shí)候，這個(gè)操作是不允許的，PostgreSQL 會(huì)直接拒絕掉這個(gè)操作：
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.
所以，假如需要某個(gè)分區(qū)表的索引，則一定需要?jiǎng)h除父表索引，子表的索引就會(huì)自動(dòng)刪除！
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ū)來(lái)對(duì)比，復(fù)雜了很多！因?yàn)閯?chuàng)建新分區(qū)是生成一張全新的數(shù)據(jù)表，PostgreSQL 只需要維護(hù)對(duì)應(yīng)的元數(shù)據(jù)（字段、索引、分區(qū)約束和主鍵等等），而掛載新分區(qū)的時(shí)候，因?yàn)榇龗燧d的分區(qū)已經(jīng)存在大量的數(shù)據(jù)了，在掛載到分區(qū)表之前，PostgreSQL 需要做一堆數(shù)據(jù)校驗(yàn)工作，以下是 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.
請(qǐng)注意，執(zhí)行 ATTACH PARTITION 命令時(shí)，將對(duì)分區(qū)表加 ACCESS EXCLUSIVE 鎖，并掃描表內(nèi)數(shù)據(jù)以驗(yàn)證分區(qū)約束。如前所述，建議在掛載分區(qū)之前，在目標(biāo)表上預(yù)先創(chuàng)建一個(gè)與預(yù)期分區(qū)約束相匹配的 CHECK 約束，以規(guī)避此掃描操作。ATTACH PARTITION 操作完成后，建議刪除此時(shí)已冗余的 CHECK 約束。如果待掛載的表本身也是一個(gè)分區(qū)表，那么它的每個(gè)子分區(qū)都將被遞歸地加鎖并掃描，直到遇到匹配的 CHECK 約束或到達(dá)葉子分區(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.
對(duì)于目標(biāo)表中的每個(gè)索引，系統(tǒng)將在被掛載的表中新建一個(gè)對(duì)應(yīng)索引；或者，若該表上已存在結(jié)構(gòu)等效的索引，則直接將該索引掛載至目標(biāo)表的索引層級(jí)——該操作等同于自動(dòng)執(zhí)行了 ALTER INDEX ATTACH PARTITION 命令。
根據(jù)上面的官網(wǎng)信息，我們可以得知以下幾點(diǎn)：
ATTACH PARTITION 操作，會(huì)為數(shù)據(jù)表添加 ACCESS EXCLUSIVE（訪(fǎng)問(wèn)獨(dú)占鎖，阻塞該表的所有操作），這個(gè)操作將導(dǎo)致數(shù)據(jù)表鎖死，嚴(yán)重影響業(yè)務(wù)系統(tǒng)的操作
ATTACH PARTITION 操作，有兩個(gè)比較耗時(shí)的操作，但是目前這兩個(gè)操作，官方都提供了解決方案！
下面的流程圖，是 DeepSeek 對(duì) 1000w 的數(shù)據(jù)表執(zhí)行 ATTACH PARTITION 大致流程：
?
graph TD
A[開(kāi)始ATTACH PARTITION] --> B[立即請(qǐng)求 ACCESS EXCLUSIVE 鎖]
B --> C{是否預(yù)創(chuàng)建已驗(yàn)證的 CHECK 約束？}
C -- 是 --> D[跳過(guò)全表掃描<br>直接信任約束]
C -- 否 --> E[在鎖保護(hù)下掃描數(shù)據(jù)<br>SSD:20-50min]
D --> F{新分區(qū)是否有等效索引？}
E --> F
F -- 無(wú)索引 --> G[在鎖保護(hù)下創(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 基本上可以在秒級(jí)執(zhí)行（這點(diǎn)我已經(jīng)做過(guò)測(cè)試）！
實(shí)際操作-hot 表
目前我們需要將一張 4000w 的 parcel_hot 掛載到 parcel 數(shù)據(jù)表。
parcel 表目前有如下特征：
索引：有一個(gè) tracking_number 的索引，idx_parcel_tracking_number
主鍵：主鍵為 id, archived, created_at，主鍵名稱(chēng)為 parcel_pkey
parcel_hot 有如下特征：
分區(qū)校驗(yàn)
首先，我們需要提前執(zhí)行好 parcel_hot 的分區(qū)約束，避免執(zhí)行 ATTACH PARTITION 時(shí)，鎖表太長(zhǎng)時(shí)間。parcel_hot 分區(qū)約束比較簡(jiǎn)單：archived=FALSE。下面是約束執(zhí)行的詳細(xì) SQL：
-- 添加 parcel_hot_archived_false 約束，并且只對(duì)新數(shù)據(jù)執(zhí)行，老數(shù)據(jù)不執(zhí)行校驗(yàn)
ALTER TABLE parcel_hot
ADD CONSTRAINT parcel_hot_archived_false
CHECK (archived = FALSE) NOT VALID;
-- completed in 244 ms
-- 校驗(yàn) parcel_hot_archived_false 老數(shù)據(jù)校驗(yàn)
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，而這主鍵不一致，因此無(wú)法進(jìn)行掛載，所以需要我們手動(dòng)更換主鍵，對(duì)齊兩張數(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 有一個(gè) tracking_number 索引，而 parcel_hot 無(wú)任何索引，因此需要在 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ū)校驗(yàn)
ALTER TABLE parcel_hot
DROP CONSTRAINT parcel_hot_archived_false;
實(shí)際操作-history
目前我們需要將一張 4000w 的 parcel_history_2023 掛載到 parcel_history 數(shù)據(jù)表。
parcel_history 表目前有如下特征：
索引：有一個(gè) tracking_number 的索引，idx_parcel_history_tracking_number
主鍵：主鍵為 id, archived, created_at，主鍵名稱(chēng)為 parcel_history_pkey
parcel_history_2023 有如下特征：
分區(qū)校驗(yàn)
首先，我們需要提前執(zhí)行好 parcel_history_2023 的分區(qū)約束，避免執(zhí)行 ATTACH PARTITION 時(shí)，鎖表太長(zhǎng)時(shí)間。parcel_history_2023 分區(qū)約束比較簡(jiǎn)單：archived=FALSE AND created_at >= '2023-01-01' AND created_at < '2024-01-01'。
注意：一定不能夠?qū)⒎謪^(qū)條件寫(xiě)為下面這樣： archived=FALSE AND created_at BETWEEN '2023-01-01' AND '2024-01-01'
因?yàn)?FOR VALUES FROM ('2026-01-01') TO ('2027-01-01') 的時(shí)間區(qū)間為：['2023-01-01 00:00:00', '2024-01-01 00:00:00)
而 BETWEEN '2023-01-01' AND '2024-01-01' 的時(shí)間取件為：['2023-01-01 00:00:00', '2024-12-01 00:00:00]
當(dāng)寫(xiě)成 archived=FALSE AND created_at BETWEEN '2023-01-01' AND '2024-01-01' 這樣，依舊會(huì)執(zhí)行分區(qū)校驗(yàn)的 SQL，導(dǎo)致鎖表時(shí)間大大的加長(zhǎng)了！
下面是約束執(zhí)行的詳細(xì) SQL：
-- 添加 parcel_hot_archived_false 約束，并且只對(duì)新數(shù)據(jù)執(zhí)行，老數(shù)據(jù)不執(zhí)行校驗(yàn)
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
-- 校驗(yàn) parcel_hot_archived_false 老數(shù)據(jù)校驗(yàn)
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，而這主鍵不一致，因此無(wú)法進(jìn)行掛載，所以需要我們手動(dòng)更換主鍵，對(duì)齊兩張數(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 有一個(gè) tracking_number 索引，而 parcel_history_2023 無(wú)任何索引，因此需要在 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ū)校驗(yàn)
ALTER TABLE parcel_history_2023
DROP CONSTRAINT parcel_history_2023_archived_true_created_at;
卸載分區(qū)
卸載分區(qū)一般速度都比較快，所以相對(duì)來(lái)說(shuō)比較安全。卸載分區(qū)后，卸載的分區(qū)將以獨(dú)立的數(shù)據(jù)表存在，且不再與主表有任何關(guān)聯(lián)。
將 parcel_history_2023 從 parcel_history 分區(qū)卸載：
鎖表卸載分區(qū)（鎖的時(shí)間很短）：
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
