PostgreSQL 表分區(qū)命令

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

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

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

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

合并分區(qū)

下表有一個(gè)account_id列，但沒有實(shí)際的數(shù)據(jù)列，因?yàn)槲覀冎皇窍胙菔痉謪^(qū)管理方面的操作。

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

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

假設(shè)我們有以下兩個(gè)分區(qū)，分別對(duì)應(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 條記錄。我們總共會(huì)有 110 條記錄，但它們分布在兩個(gè)分區(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合并為了一個(gè)分區(qū)，名為t_account_1_2，含有 110 條記錄。

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

拆分分區(qū)

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

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

想象一下，我們決定為接收大量記錄的一個(gè)“事件”類的表，創(chuàng)建一周數(shù)據(jù)的分區(qū)。我們?cè)谙旅姘驯斫凶?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;">t_events。

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

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

拆分分區(qū)事件表

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

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)在我們想對(duì)叫做t_events_next_week的“下周的分區(qū)”，把它分成 7 個(gè)每日分區(qū)，每天一個(gè)。

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

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

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

ERROR:  partition bound for relation "t_events_next_week" is null

下面是SPLIT PARTITION的 DDL 命令，用于將單周的分區(qū)拆分為 7 個(gè)每日分區(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)在，如果我們運(yùn)行\d+ t_events來(lái)查看t_events，我們將看到剩下的兩個(gè)每周分區(qū)，以及新的 7 個(gè)每日分區(qū)。

有一個(gè)問(wèn)題。執(zhí)行此操作需要父表上的鎖，該鎖的持有時(shí)間可能會(huì)很長(zhǎng)。

有解決方法嗎？

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

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

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

不幸的是，我們無(wú)法執(zhí)行SPLIT PARTITION CONCURRENTLY，這會(huì)使這更加方便，因?yàn)槲覀儾槐負(fù)?dān)心在排他鎖生效時(shí)阻止寫入。

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

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

在沒有父表的已分離的分區(qū)上，嘗試運(yùn)行SPLIT PARTITION是無(wú)法工作的。但是，我們可以臨時(shí)添加一個(gè)“新的假父表”來(lái)代替。

下面是分離操作：

ALTER TABLE t_events
DETACH PARTITION t_events_next_week CONCURRENTLY;

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

我們只會(huì)使用“假父表”進(jìn)行拆分操作。完成后，我們將再次分離分區(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);

在單獨(dú)的父表上運(yùn)行SPLIT PARTITION，可以避免在原始父表上長(zhǎng)時(shí)間持有鎖，因?yàn)樗且粋€(gè)完全獨(dú)立的表。

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)到原始父表。

替代方法

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

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

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

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

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