PostgreSQL 表分区命令

在版本 17 以前，分区管理流程的操作仅限于创建、依附和分离分区。一旦我们设计了分区结构，我们就无法重新设计它。这适用于所有分区类型，无论我们使用的是RANGE、LIST还是HASH。

要将多个分区合并为一个分区，或者将单个分区“拆分”为多个分区，我们需要设计一个新的分区结构，然后将所有数据行迁移到该结构。这需要很多步骤！

从版本 17 开始，我们有了更多的选择。现在，我们可以对现有的单个分区执行SPLIT PARTITION操作，将其分成两个或多个新分区。

如果我们想做相反的事情，我们也同样可以。从两个或多个分区开始，我们可以执行MERGE PARTITIONS的操作，将它们合并为一个。

合并分区

下表有一个account_id列，但没有实际的数据列，因为我们只是想演示分区管理方面的操作。

id使用了生成的序列值，这意味着每一行在分区中都会有一个唯一的值。

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

假设我们有以下两个分区，分别对应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);

让我们为account_id为 1 插入 10 条记录，为account_id为 2 插入 100 条记录。我们总共会有 110 条记录，但它们分布在两个分区中。我们想把它们合并在一起。

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

现在我们使用MERGE PARTITIONS将它们合并在一起：

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

它将t_account_1和t_account_1合并为了一个分区，名为t_account_1_2，含有 110 条记录。

那拆分分区呢？它又是如何工作的？

拆分分区

我们已经了解了如何合并分区。我们还可以使用SPLIT PARTITIONS命令来拆分分区。

在此示例中，我们使用RANGE分区类型。

想象一下，我们决定为接收大量记录的一个“事件”类的表，创建一周数据的分区。我们在下面把表叫做t_events。

我们决定了以一周为限，但是发现表的数据量很大。我们希望改用每日分区，以让一天数据的表更小、更易于管理。

让我们看一下 SQL 命令，了解如何实现这一点。

拆分分区事件表

使用RANGE分区类型创建t_events表，最初使用每周分区来演示当前的配置。

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

以下是“上周”、“本周”和“下周”的分区。

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');

现在我们想对叫做t_events_next_week的“下周的分区”，把它分成 7 个每日分区，每天一个。

由于这是即将到来的一周，我们假设它没有数据，但是一个预先创建的分区。

在像这样设计自己的更改时，请记住，您提出的最终边界必须具有与当前配置等效的开始和结束边界。

如果边界对应不上，您会收到如下错误：

ERROR:  partition bound for relation "t_events_next_week" is null

下面是SPLIT PARTITION的 DDL 命令，用于将单周的分区拆分为 7 个每日分区：

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')
);

现在，如果我们运行\d+ t_events来查看t_events，我们将看到剩下的两个每周分区，以及新的 7 个每日分区。

有一个问题。执行此操作需要父表上的锁，该锁的持有时间可能会很长。

有解决方法吗？

分离、拆分、重新关联

只要表的结构保持不变，分区就可以分离和重新关联。

这些操作都可以通过使用CONCURRENTLY，以非阻塞的方式执行。

不幸的是，我们无法执行SPLIT PARTITION CONCURRENTLY，这会使这更加方便，因为我们不必担心在排他锁生效时阻止写入。

让我们来考虑一种解决方法。我们知道我们可以分离分区，在分离时拆分它们，然后重新关联它们。那行得通吗？

这需要很多操作，并且需要一个“新的假父表”（下面有它自己的名字）才能工作，因此这些步骤应该更多地被视为概念证明，而不是建议。目标是通过允许在分离的表层次结构上发生锁，来避免潜在的长锁阻塞写入。实质是“离线”的。

在没有父表的已分离的分区上，尝试运行SPLIT PARTITION是无法工作的。但是，我们可以临时添加一个“新的假父表”来代替。

下面是分离操作：

ALTER TABLE t_events
DETACH PARTITION t_events_next_week CONCURRENTLY;

下面是“假的”替身父表的定义。在创建它后，我们需要将已分离的分区关联到它，以执行拆分。

我们只会使用“假父表”进行拆分操作。完成后，我们将再次分离分区，然后以CONCURRENTLY的方式，将它们重新关联到原始父表。到这一步后，我们可以删除“假”父表了。

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')
);

由于表结构没有更改，并且由于我们没有引入任何重叠的分区约束，因此我们可以重新关联到原始父表。

替代方法

那么简单地创建新分区，以将数据行移动到其中，又会怎么样？

虽然创建新分区和移动数据行的工作量可能较少，但我们不能引入与任何现有分区的边界/约束重叠的新分区。PostgreSQL 会强制执行该规则的检查，并会阻止分区的创建。

为了避免重叠的限制，当我们的目标是像这样就地修改结构时，执行SPLIT PARTITION似乎是必要的。

但是，与上述解决方法类似，我们可以遵循相同的策略，分离重叠的分区以解决冲突。

使用这种方法，我们可以获得相同的最终结果，而不需要执行SPLIT PARTITION命令。