PostgreSQL 表分區(qū)命令

在版本 17 以前，分區(qū)管理流程的操作僅限于創(chuàng)建、依附和分離分區(qū)。一旦我們設(shè)計了分區(qū)結(jié)構(gòu)，我們就無法重新設(shè)計它。這適用于所有分區(qū)類型，無論我們使用的是RANGE、LIST還是HASH。

要將多個分區(qū)合并為一個分區(qū)，或者將單個分區(qū)“拆分”為多個分區(qū)，我們需要設(shè)計一個新的分區(qū)結(jié)構(gòu)，然后將所有數(shù)據(jù)行遷移到該結(jié)構(gòu)。這需要很多步驟！

從版本 17 開始，我們有了更多的選擇。現(xiàn)在，我們可以對現(xiàn)有的單個分區(qū)執(zhí)行SPLIT PARTITION操作，將其分成兩個或多個新分區(qū)。

如果我們想做相反的事情，我們也同樣可以。從兩個或多個分區(qū)開始，我們可以執(zhí)行MERGE PARTITIONS的操作，將它們合并為一個。

合并分區(qū)

下表有一個account_id列，但沒有實際的數(shù)據(jù)列，因為我們只是想演示分區(qū)管理方面的操作。

id使用了生成的序列值，這意味著每一行在分區(qū)中都會有一個唯一的值。

CREATE TABLE t (
  id INT GENERATED ALWAYS AS IDENTITY,
  account_id INT NOT NULL
) PARTITION BY LIST (account_id);

假設(shè)我們有以下兩個分區(qū)，分別對應(yīng)account_id為 1 和account_id為 2。

CREATE TABLE t_account_1 PARTITION OF t FOR VALUES IN (1);
CREATE TABLE t_account_2 PARTITION OF t FOR VALUES IN (2);

讓我們?yōu)?code style="margin: 0px; padding: 3px 5px; overflow-wrap: break-word !important; line-height: 1.5em; color: rgb(221, 17, 68); background: rgba(27, 31, 35, 0.05); border-radius: 4px; word-break: break-all;">account_id為 1 插入 10 條記錄，為account_id為 2 插入 100 條記錄。我們總共會有 110 條記錄，但它們分布在兩個分區(qū)中。我們想把它們合并在一起。

INSERT INTO t (account_id) SELECT 1 FROM GENERATE_SERIES(1,10);
INSERT INTO t (account_id) SELECT 2 FROM GENERATE_SERIES(1,100);

現(xiàn)在我們使用MERGE PARTITIONS將它們合并在一起：

ALTER TABLE t
MERGE PARTITIONS (t_account_1, t_account_2)
INTO t_account_1_2;

它將t_account_1和t_account_1合并為了一個分區(qū)，名為t_account_1_2，含有 110 條記錄。

那拆分分區(qū)呢？它又是如何工作的？

拆分分區(qū)

我們已經(jīng)了解了如何合并分區(qū)。我們還可以使用SPLIT PARTITIONS命令來拆分分區(qū)。

在此示例中，我們使用RANGE分區(qū)類型。

想象一下，我們決定為接收大量記錄的一個“事件”類的表，創(chuàng)建一周數(shù)據(jù)的分區(qū)。我們在下面把表叫做t_events。

我們決定了以一周為限，但是發(fā)現(xiàn)表的數(shù)據(jù)量很大。我們希望改用每日分區(qū)，以讓一天數(shù)據(jù)的表更小、更易于管理。

讓我們看一下 SQL 命令，了解如何實現(xiàn)這一點。

拆分分區(qū)事件表

使用RANGE分區(qū)類型創(chuàng)建t_events表，最初使用每周分區(qū)來演示當前的配置。

CREATE TABLE t_events (
  id INT GENERATED ALWAYS AS IDENTITY,
  event_at TIMESTAMP WITHOUT TIME ZONE NOT NULL
) PARTITION BY RANGE (event_at);

以下是“上周”、“本周”和“下周”的分區(qū)。

CREATE TABLE t_events_last_week PARTITIONOF t_events
FORVALUESFROM('2024-04-08 00:00:00')TO('2024-04-15 00:00:00');

CREATETABLE t_events_this_week PARTITIONOF t_events
FORVALUESFROM('2024-04-15 00:00:00')TO('2024-04-22 00:00:00');

CREATETABLE t_events_next_week PARTITIONOF t_events
FORVALUESFROM('2024-04-22 00:00:00')TO('2024-04-29 00:00:00');

現(xiàn)在我們想對叫做t_events_next_week的“下周的分區(qū)”，把它分成 7 個每日分區(qū)，每天一個。

由于這是即將到來的一周，我們假設(shè)它沒有數(shù)據(jù)，但是一個預(yù)先創(chuàng)建的分區(qū)。

在像這樣設(shè)計自己的更改時，請記住，您提出的最終邊界必須具有與當前配置等效的開始和結(jié)束邊界。

如果邊界對應(yīng)不上，您會收到如下錯誤：

ERROR:  partition bound for relation "t_events_next_week" is null

下面是SPLIT PARTITION的 DDL 命令，用于將單周的分區(qū)拆分為 7 個每日分區(qū)：

ALTER TABLE t_events SPLIT PARTITION t_events_next_week INTO(
PARTITION t_events_day_1 FORVALUESFROM('2024-04-22 00:00:00')TO('2024-04-23 00:00:00'),
PARTITION t_events_day_2 FORVALUESFROM('2024-04-23 00:00:00')TO('2024-04-24 00:00:00'),
PARTITION t_events_day_3 FORVALUESFROM('2024-04-24 00:00:00')TO('2024-04-25 00:00:00'),
PARTITION t_events_day_4 FORVALUESFROM('2024-04-25 00:00:00')TO('2024-04-26 00:00:00'),
PARTITION t_events_day_5 FORVALUESFROM('2024-04-26 00:00:00')TO('2024-04-27 00:00:00'),
PARTITION t_events_day_6 FORVALUESFROM('2024-04-27 00:00:00')TO('2024-04-28 00:00:00'),
PARTITION t_events_day_7 FORVALUESFROM('2024-04-28 00:00:00')TO('2024-04-29 00:00:00')
);

現(xiàn)在，如果我們運行\d+ t_events來查看t_events，我們將看到剩下的兩個每周分區(qū)，以及新的 7 個每日分區(qū)。

有一個問題。執(zhí)行此操作需要父表上的鎖，該鎖的持有時間可能會很長。

有解決方法嗎？

分離、拆分、重新關(guān)聯(lián)

只要表的結(jié)構(gòu)保持不變，分區(qū)就可以分離和重新關(guān)聯(lián)。

這些操作都可以通過使用CONCURRENTLY，以非阻塞的方式執(zhí)行。

不幸的是，我們無法執(zhí)行SPLIT PARTITION CONCURRENTLY，這會使這更加方便，因為我們不必擔心在排他鎖生效時阻止寫入。

讓我們來考慮一種解決方法。我們知道我們可以分離分區(qū)，在分離時拆分它們，然后重新關(guān)聯(lián)它們。那行得通嗎？

這需要很多操作，并且需要一個“新的假父表”（下面有它自己的名字）才能工作，因此這些步驟應(yīng)該更多地被視為概念證明，而不是建議。目標是通過允許在分離的表層次結(jié)構(gòu)上發(fā)生鎖，來避免潛在的長鎖阻塞寫入。實質(zhì)是“離線”的。

在沒有父表的已分離的分區(qū)上，嘗試運行SPLIT PARTITION是無法工作的。但是，我們可以臨時添加一個“新的假父表”來代替。

下面是分離操作：

ALTER TABLE t_events
DETACH PARTITION t_events_next_week CONCURRENTLY;

下面是“假的”替身父表的定義。在創(chuàng)建它后，我們需要將已分離的分區(qū)關(guān)聯(lián)到它，以執(zhí)行拆分。

我們只會使用“假父表”進行拆分操作。完成后，我們將再次分離分區(qū)，然后以CONCURRENTLY的方式，將它們重新關(guān)聯(lián)到原始父表。到這一步后，我們可以刪除“假”父表了。

CREATE TABLE t_events_fake_new (
  id INT GENERATED ALWAYS AS IDENTITY,
  event_at TIMESTAMP WITHOUT TIME ZONE NOT NULL
) PARTITION BY RANGE (event_at);

在單獨的父表上運行SPLIT PARTITION，可以避免在原始父表上長時間持有鎖，因為它是一個完全獨立的表。

ALTER TABLE t_events_fake_new SPLIT PARTITION t_events_next_week INTO(
PARTITION t_events_day_1 FORVALUESFROM('2024-04-22 00:00:00')TO('2024-04-23 00:00:00'),
PARTITION t_events_day_2 FORVALUESFROM('2024-04-23 00:00:00')TO('2024-04-24 00:00:00'),
PARTITION t_events_day_3 FORVALUESFROM('2024-04-24 00:00:00')TO('2024-04-25 00:00:00'),
PARTITION t_events_day_4 FORVALUESFROM('2024-04-25 00:00:00')TO('2024-04-26 00:00:00'),
PARTITION t_events_day_5 FORVALUESFROM('2024-04-26 00:00:00')TO('2024-04-27 00:00:00'),
PARTITION t_events_day_6 FORVALUESFROM('2024-04-27 00:00:00')TO('2024-04-28 00:00:00'),
PARTITION t_events_day_7 FORVALUESFROM('2024-04-28 00:00:00')TO('2024-04-29 00:00:00')
);

由于表結(jié)構(gòu)沒有更改，并且由于我們沒有引入任何重疊的分區(qū)約束，因此我們可以重新關(guān)聯(lián)到原始父表。

替代方法

那么簡單地創(chuàng)建新分區(qū)，以將數(shù)據(jù)行移動到其中，又會怎么樣？

雖然創(chuàng)建新分區(qū)和移動數(shù)據(jù)行的工作量可能較少，但我們不能引入與任何現(xiàn)有分區(qū)的邊界/約束重疊的新分區(qū)。PostgreSQL 會強制執(zhí)行該規(guī)則的檢查，并會阻止分區(qū)的創(chuàng)建。

為了避免重疊的限制，當我們的目標是像這樣就地修改結(jié)構(gòu)時，執(zhí)行SPLIT PARTITION似乎是必要的。

但是，與上述解決方法類似，我們可以遵循相同的策略，分離重疊的分區(qū)以解決沖突。

使用這種方法，我們可以獲得相同的最終結(jié)果，而不需要執(zhí)行SPLIT PARTITION命令。