一直以来，PostgreSQL 的用户都很羡慕 Oracle 中的间隔自动分区。实际上，这在 PostgreSQL 中也不难做到。

介绍

假设有一个 PostgreSQL 用户，他在单个表中存储了 5 亿条聊天消息。随着表的增长，他们的查询速度变慢了。

该场景的简单的解决方案是表分区。难点在于创建分区和迁移数据，不阻塞查询和中断业务。本文探讨了我们用来解决这个问题的方法：动态表分区。

为什么要对数据分区？大表问题

当小型项目变得庞大时，表中数百万的行会迅速增长到数十亿行。当在单个表中存储数据时，这带来了几个挑战：

• • SELECT 会随着索引的增长而变慢。PostgreSQL 中最常见的索引类型是 btree，它是一个类似树的结构。要查找一行，必须从该树的根节点“遍历”到存在记录值的相应叶节点。索引越大，需要遍历的节点就越多。
• • INSERT 会随着索引的增长而变慢。由于和 SELECT 变慢相同的许多原因，必须同样“遍历”索引以查找需要插入值的叶节点。如果叶节点已满，则必须添加新节点。表越大，此过程花费的时间就越长。
• • 数据的相关性可能会变差。实际业务中的数据，它的效用通常会随着时间的推移而降低。例如，聊天消息在发送时最有用，但随着时间的推移，您作为用户阅读它们的频率会降低。旧的聊天消息会占用表中的空间，因此查询新的聊天消息会受到影响。
• • 大表混合了不相关的数据。通常，不相关的数据存储在一起，因此 SELECT 语句需要扫描许多不相关的行。在我们聊天消息的示例中：也许一个组织的用户与另一个组织聊天是不寻常的。如果每个组织的数据都被拆分为单独的存储块，那就更好了。
• • 大表会阻止 VACUUM 操作完成。VACUUM 是 PostgreSQL 最神秘的一个方面。由于 Postgres 的并发模型（MVCC），更新或删除的行不会立即从存储中删除，而是标记为已删除。在后台，Postgres 偶尔会删除这些“死”行和回收它们的存储空间，以用于更新或插入的新行。表越大，这个清理的过程所需的时间就越长。如果您的表经常更新，则可能会阻止清理过程成功完成。
• • 大表意味着需要移动的数据更多。通常，要测试新功能，您不需要所有生产数据，只需要其中的一小部分。也许您只需要一个月的分析数据或一天的聊天消息。通过将数据分区为较小的部分，您可以选择加载刚好足以运行所需测试的负载。这种模式称为增量数据加载。
• • 在定义完善的分区中，要归档较老的数据会更容易。增量数据加载问题的另一面是增量归档。老数据通常会变得无关紧要，以至于将其保存在实时数据库表中的成本高得令人望而却步。通过使用动态分区，可以将较老的分区分离和归档到更便宜的 “冷存储” 中，然后仅在需要时重新加载和附加分区。

设置基础的数据模型

小型初创公司常见的一个成长痛点是，他们以非常简单明了的方式开始他们的数据库模型，但随后他们的受欢迎程度激增，他们需要存储的数据量也爆炸式增长。Postgres 擅长在单个表中处理数百万行，但系统中通常只有那么一个大表，在以每天的速度增长到数亿行。

一个简单的解决方案是表分区，但对于 Postgres，这通常需要在创建表的同时设置好分区。挑战在于要弄清楚在数据库有数百万行以后如何创建分区，以及如何以最少的停机时间，将这些现有的行迁移到新的分区方案中。

让我们来探索一个工作示例，演示如何使用动态表分区来摆脱这种情况。我们将设置一个模拟的 “聊天应用程序”，它每天必须存储可能到数百万条的聊天消息。第一步是设置一个假的有问题的 “大表”，以模拟一个已经超出 Postgres 支持在一个表中处理所有内容的数据集。首先，我们将为示例的聊天应用程序添加几个新表：

create table chats (
  id bigserial,
  created_at timestamptz not null default now(),
  primary key (id)
);

create table chat_messages (
  id bigserial,
  created_at timestamptz not null,
  chat_id bigint not null,
  chat_created_at timestamptz not null,
  message text not null,
  primary key (id),
  foreign key (chat_id) references chats (id)
);

第一个表chats，包含了应用程序中各方之间的对话，第二个表chat_messages，包含了聊天中来回发送的单个消息。请注意，在此示例中，为简单起见，我们没有包含任何列来描述这些聊天的发送方或接收方，该问题留给您来解决，但还是涉及了从chats表到应用程序中的用户表的某种外键关联。在这里，我们只对存储消息本身的结构感兴趣。

接下来我们必须合成一些假数据。我们会插入一段时间内的一些聊天行，然后插入一些聊天消息到这些聊天中。您可以通过调整下面的 “开始” 和 “结束” 时间戳，来调整创建的数据量。我们只设置了一个月的数据，但您可以增加时间跨度或者将聊天之间的一小时间隔减小，以获得更多行。

INSERT INTO chats (created_at)
SELECT generate_series(
  '2022-01-01'::timestamptz,
  '2022-01-30 23:00:00'::timestamptz,
  interval '1 hour'
);

INSERT INTO chat_messages (
 created_at,
 chat_id,
 chat_created_at,
 message
)
SELECT
  mca,
  chats.id,
  chats.created_at,
  (SELECT ($$[0:2]={'hello','goodbye','I would like a sandwich please'}$$::text[])[trunc(random() * 3)::int])
FROM chats
CROSS JOIN LATERAL (
    SELECT generate_series(
        chats.created_at,
        chats.created_at + interval '1 day',
        interval '1 minute'
  ) AS mca
) b;

CREATE INDEX ON chats (created_at);
CREATE INDEX ON chat_messages (created_at);

使用 PL/pgSQL 进行动态分区

接下来，我们将展示一个简单的分区策略，使用上面的 “大表” 中的伪数据，并使用上面介绍中描述的一些分区模式，为您自己的分区方案提供一个起点。

首先，我们来给 Postgres 表分区稍作一下解释。分区表可以看作是一个 “父” 表，其中有一堆 “附加” 到它的 “子” 表。通过将一个大表分解为多个较小的表，可以获得许多性能和管理的优势。当您查询一个分区表时，您可以像查询任何其他 Postgres 表一样查询父表，但在内部，Postgres 知道如何以最佳方式将这些查询委托给子表。

子表按它们存储的数据行中的某个键列进行分区。在我们的 “聊天” 示例中，这个键将是created_at时间戳。我们会将表划分为 “每日” 分区，以便每个子表保存一天的聊天消息。当您创建新的子分区表时，您必须告诉 Postgres 该表要保存的数据的日期范围，这也是 Postgres 知道在分区中存储和查找聊天消息的位置的方式。在下面的示例中，您将看到我们的子分区表名称会包含分区保存数据的日期，chats_2023-08-03表也就是一个 2023 年 8 月 3 日的子分区表。

如果您查询父表，但没有在WHERE子句中提供要查找的创建日期或日期范围的值，则 Postgres 必须查看所有子表，这对于大量的数据可能会需要一些时间，因此请确保您始终尽量将查询限制在较小的时间范围内，以便 Postgres 可以通过查看最小数量的必要子分区，来优化查找该数据的效率。

在下面的示例中，我们将从一个大表中复制大量现有的聊天消息到我们将要“动态”创建的新分区中，因此我们必须确保我们插入到每个新的子分区中的行位于正确的日期时间范围内。每个子分区的这个时间范围称为分区约束。当我们将数据复制到新表中时，我们将使用一个技巧，首先创建一个检查约束，该约束将强制 Postgres 确保该约束对每一行都有效，如果您尝试放入子分区约束范围以外的行，它将会引发错误。

在我们用复制的行预加载进一个新的子表后，并使用检查约束验证了这些行有效之后，我们可以使用ATTACH PARTITION ...命令将分区附加到父表。这样将使得新的子表中的数据可用于父表上的查询。

在最近的版本中，Postgres 分区明显变得更加易用了。Postgres 添加了声明式分区的新语法，通过向CREATE TABLE语句添加其他详细信息，可以轻松创建新分区：

CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);

但是，在许多情况下，如上面讨论的那样，如果您想先将数据预加载到分区中，再将其附加到父表，那么简单的分区存在一些问题，最终可能会阻塞查询。所以一般有两种方法，可以在创建表时声明一个新的子表，但它必须是空的。

本文中描述的方法是创建一个独立的表，填充进数据，然后“附加”到父表，但是如果不仔细操作，该方法会有一个问题：如果新填充的表没有正确设置，Postgres 将会使用一个阻塞查询的EXCLUSIVE ACCESS锁锁定父表，因为 Postgres 要确保新分区仅包含属于分区数据范围内（这叫做分区约束）的数据。为了避免这种锁定，必须首先将此约束添加到独立的表中，以便 Postgres 不需要先扫描它。

例如，如果您每天对数据进行分区，对于将要附加的表，Postgres 必须确保新表中没有行超出给定的日期范围的边界。本文提供了一个简单的方法，通过一些 PL/pgSQL 函数来解决这个问题，您可以快速轻松地达成您的需求。

没有一种放之四海而皆准的分区方案，因此您可能需要修改此示例，以满足您的需要。请阅读官方的表分区文档，以便了解所涉及的所有问题。分区可能是一个复杂的主题，但希望此代码能帮助您开始走上应对超大数据集的高扩展性之路！

创建父表

此处显示的示例模型是一个 “聊天” 应用程序，其中有一个 “chats” 表，充当一组聊天消息的容器。

由于用户的表示是一个高度特定于应用程序的概念，所以此处将它留下作为您的一个练习，以练习如何将这些消息关联到任何类型的用户系统。一种可能的方法是，添加外键引用到包含聊天各方 ID 的chats表中。对于单人对单人的简单聊天，这可能就是简单的两个列，对于多方聊天，您必须给出一个将聊天与参与者相关联的多对多连接表。

BEGIN;
CREATE SCHEMA app;

CREATE TABLE app.chats(
    id bigserial,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id, created_at)  -- the partition column must be part of pk
    ) PARTITION BY RANGE (created_at);

CREATE INDEX "chats_created_at" ON app.chats (created_at);

CREATE TABLE app.chat_messages(
    id bigserial,
    created_at timestamptz NOT NULL,
    chat_id bigint NOT NULL,
    chat_created_at timestamptz NOT NULL,
    message text NOT NULL,
    PRIMARY KEY (id, created_at),
    FOREIGN KEY (chat_id, chat_created_at)   -- multicolumn fk to ensure
        REFERENCES app.chats(id, created_at)
    ) PARTITION BY RANGE (created_at);

CREATE INDEX "chat_messages_created_at" ON app.chat_messages (created_at);
--
-- need this index on the fk source to lookup messages by parent
--
CREATE INDEX "chat_messages_chat_id_chat_created_at"
    ON app.chat_messages (chat_id, chat_created_at);

在这个模型中，聊天消息按其创建日期进行分区，这可以在PARTITION BY RANGE子句中看到。我们将为每天创建一个新分区，这意味着每天都会为 “当日的” 聊天消息创建一个新表。如果不进行分区，每天对表的查询会越来越慢，但通过分区，查询时间将保持一致。chats和chat_messages表都会按天分区。这使得模型相当简单，但有一个问题，聊天可能会持续多天，因此如果您想保持数据的一致性，请记住这一点。

另一个细节是chats和chat_messages表的主键有两列。这称为 “复合” 主键。这很重要，因为主键的工作是唯一标识行，对于分区表，它还必须唯一标识分区。因此，分区列created_at必须是主键的一部分。这是分区一个重要的结构要求，它允许 Postgres 快速处理分区数据，因为 Postgres 始终知道任何给定的主键将存储到哪个分区表，它不必在每次操作时检查所有分区，这样会非常慢。

请注意，这个复合主键意味着引用它的任何外键也必须是复合的。这可以从用于chat_messages的CREATE TABLE语句的FOREIGN KEY子句中看出来。这可能很棘手，因为即使它是一种完全正常且常见的 SQL 模式，在 Postgres 和其他数据库中已经存在了几十年，但各种语言中的许多类似 ORM 的工具仍然不理解或不支持组合键的概念。如果您遇到这种情况，您可能需要重新考虑，要不要使用一种不理解这类标准 SQL 模式的工具。像 sqlalchemy 这样强大的 ORM 对组合键没有问题，但即使是像 Django 这样的流行工具也不支持它们。

最后，请注意有一个CREATE INDEX语句，在chat_messages表的外键关系上创建了一个索引。这是为了确保根据chats中的一行能快速查找到chat_messages中的行，否则，必须扫描chat_messages表，而这是绝对要避免的事情！

创建动态的子表

现在我们已经创建了父表，是时候创建一些分区了！此过程有两个步骤，因此我们为每个表将它们分成两个PROCEDURE对象。Postgres 的存储过程类似于函数，但它们不能在许多可以使用函数的上下文中使用，例如 RLS 策略、计算索引、默认表达式等。它们只能使用CALL procedure_name(args)语法调用，以避免意外的误用。过程也可以进行事务控制，我们将在本文后面介绍。

下面显示了chats和chat_messages表的完整代码示例，因此我只解释chats表的分区代码，它们都非常相似，因此您可以毫无问题地将该模式应用到数据库中的一个或多个表。

为了创建新分区，我们需要知道创建表的日期，这是存储过程的唯一参数partition_day。此外，我们将以该日期作为子分区表名称的一部分来创建新表，因此我们需要执行 “动态的” SQL。这是使用执行 SQL 代码字符串的EXECUTE关键字来完成的。因为我们必须格式化该字符串，所以我们使用了format()函数来构造要执行的 SQL 字符串。

请注意子分区是使用LIKE子句来创建的，这让它们可以获得和父表一样的结构，其中的INCLUDING DEFAULTS INCLUDING CONSTRAINTS子句意味着子表会获得和父表一样的默认值和约束（如外键）。

--
-- Function creates a chats partition for the given day argument
--
CREATE OR REPLACE PROCEDURE app.create_chats_partition(partition_day date)
    LANGUAGE plpgsql AS
$$
BEGIN
    EXECUTE format(
    $i$
        CREATE TABLE IF NOT EXISTS app."chats_%1$s"
        (LIKE app.chats INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
    $i$, partition_day);
END;
$$;

在完整的代码清单中，有一个类似的函数create_chat_messages_partition()，对chat_messages表执行相同的操作。

在使用create_chats_partition()过程创建子分区表后，就可以将这些子表建立索引，再将子表附加到父表了。

下面有一些重要的细节，主键是在加载数据后添加的，这样可以尽快地为这些键创建好索引。此外，如上所述，向子表添加了一个CHECK约束，以确保它们不会违反父表PARTITION BY RANGE的约束，从而避免在附加到父表时对父表进行任何不必要的EXCLUSIVE ACCESS锁定。这种类型的锁定会导致业务中断，因为它会阻塞对表的所有访问，包括SELECT查询。对于可能需要几个小时才能完成复制的数亿行的数据量，您承受不了应用程序长时间的中断。

降低这种锁定的时长是一项重要的优化，可确保您从大表进行迁移时，不会因阻塞对父表的查询而导致停机。由于检查约束与分区约束完全匹配，因此 Postgres 知道它可以附加该表，而无需锁定表并扫描其有效性。检查约束已经证明了新的子分区中数据的有效性，因此 Postgres 不必在附加时重新验证它，并且可以在不阻塞任何其他查询的情况下附加新的子分区。

加载数据后，您还可以创建应用程序所需的任何其他索引，例如我们之前讨论的chat_messages表上的外键索引。在将数据批量加载到表中后创建索引，始终会比在加载数据之前先创建索引更快，因为索引对每次插入的影响，比扫描表和创建索引所需的总时间要慢。这是另一个加快复制过程的重要优化点。

最后，使用ATTACH PARTITION ... FOR VALUES FROM TO 将新的子分区表附加到父表。由于已知所有约束都使用了主键和CHECK约束进行过验证，因此这可以在不用锁定的情况下完成。接下来，新创建的索引也用ALTER INDEX ... ATTACH PARTITION附加到了父表。最后，对避免表锁定非常有用的CHECK约束现在不再需要了，因此可以使用ALTER TABLE ... DROP CONSTRAINT将它们删除。

CREATE OR REPLACE PROCEDURE app.index_and_attach_chats_partition(partition_day date)
    LANGUAGE plpgsql AS
$$
BEGIN
    EXECUTE format(
    $i$
        -- now that any bulk data is loaded, setup the new partition table's pks
        ALTER TABLE app."chats_%1$s" ADD PRIMARY KEY (id, created_at);

        -- adding these check constraints means postgres can
        -- attach partitions without locking and having to scan them.
        ALTER TABLE app."chats_%1$s" ADD CONSTRAINT
               "chats_partition_by_range_check_%1$s"
           CHECK ( created_at >= DATE %1$L AND created_at < DATE %2$L );

        -- add more partition indexes here if necessary
        CREATE INDEX "chats_%1$s_created_at"
            ON app."chats_%1$s"
            USING btree(created_at)
            WITH (fillfactor=100);

        -- by "attaching" the new tables and indexes *after* the pk,
        -- indexing and check constraints verify all rows,
        -- no scan checks or locks are necessary, attachment is very fast,
        -- and queries to parent are not blocked.
        ALTER TABLE app.chats
            ATTACH PARTITION app."chats_%1$s"
        FOR VALUES FROM (%1$L) TO (%2$L);

        -- You now also "attach" any indexes you made at this point
        ALTER INDEX app."chats_created_at"
            ATTACH PARTITION app."chats_%1$s_created_at";

        -- Dropping the now unnecessary check constraint they were just needed
        -- to prevent the attachment from forcing a scan to do the same check
        ALTER TABLE app."chats_%1$s" DROP CONSTRAINT
            "chats_partition_by_range_check_%1$s";
    $i$,
    partition_day, (partition_day + interval '1 day')::date);
END;
$$;

同样，下面有一个完整的代码列表，其中显示了一个类似的过程index_and_attach_chat_messages_partition。

逐步从大表复制数据

现在分区创建和附加的代码已经就位，是时候创建最后一组存储过程，将数据从旧的 “大表” 复制到新的分区方案了。下面是一个存储过程，它几乎只是SELECT旧数据，将查出来的新数据进行简单的INSERT。

值得注意的是，查出来的数据会按created_at列进行ORDER BY排序。这意味着，插入到新的分区中的所有行会自然地按其创建日期排序，这往往会让最近的消息放在相同的数据库块文件中，从而提高缓存的效率：

--
-- Function copies one day's worth of chats rows from old "large"
-- table new partition.  Note that the copied data is ordered by
-- created_at, this improves block cache density.
--
CREATE OR REPLACE PROCEDURE app.copy_chats_partition(partition_day date)
    LANGUAGE plpgsql AS
$$
DECLARE
    num_copied bigint = 0;
BEGIN
    EXECUTE format(
    $i$
        INSERT INTO app."chats_%1$s" (id, created_at)
        SELECT id, created_at FROM chats
        WHERE created_at::date >= %1$L::date AND created_at::date < (%1$L::date + interval '1 day')
        ORDER BY created_at
    $i$, partition_day);
    GET DIAGNOSTICS num_copied = ROW_COUNT;
    RAISE NOTICE 'Copied % rows to %', num_copied, format('app."chats_%1$s"', partition_day);
END;
$$;

整合所有内容：创建、复制、索引、附加

现在，我们还需要的最后一个存储过程是一个包装器，它要将所有内容放在一起，创建一个分区，将旧数据复制到新表中，为新表构建索引，然后将其附加到父表。

其工作方式是，一个存储过程将创建一系列的每日分区，并逐天复制，每天之间调用COMMIT，以便新表在不阻塞或锁定其他数据库会话的情况下增量增长。请注意，这个存储过程是从最新的聊天到最早的聊天（这是由generate_series()的interval '-1 day'参数实现）。这样，即使您仍在加载旧数据，新表也可以立即发挥作用。和上面一样，我们只展示load_chats_partitions()的存储过程示例。

--
-- Wrapper function to create, copy, index and attach a given day.
--
CREATE OR REPLACE PROCEDURE app.load_chats_partition(i date)
    LANGUAGE plpgsql AS
$$
BEGIN
    CALL app.create_chats_partition(i);
    CALL app.copy_chats_partition(i);
    CALL app.index_and_attach_chats_partition(i);
END;
$$;

--
-- This procedure loops over all days in the old table, copying each day
-- and then committing the transaction.
--
CREATE OR REPLACE PROCEDURE app.load_chats_partitions()
    LANGUAGE plpgsql AS
$$
DECLARE
    start_date date;
    end_date date;
    i date;
BEGIN
    SELECT min(created_at)::date INTO start_date FROM chats;
    SELECT max(created_at)::date INTO end_date FROM chats;
    FOR i IN SELECT * FROM generate_series(end_date, start_date, interval '-1 day') LOOP
        CALL app.load_chats_partition(i);
        COMMIT;
    END LOOP;
END;
$$;

请注意，在每次调用 create、copy 和 attach 的存储过程之后，COMMIT语句是如何在循环内发生的。这允许其他会话在提交新数据后看到新数据，这样即使在加载旧数据时也可以使用新表。

最后，是时候通过调用load_存储过程来启动整个过程了：

CALL app.load_chats_partitions();
CALL app.load_chat_messages_partitions();

设置每日的定时任务来创建分区

通过 cron 这个老派但简单的工具，可以在每晚 23:00（午夜前 1 小时）运行预定的 “作业” 来创建第二天的分区。当然您也可以提前创建好一周的分区，这样可以避免在 cron 作业碰巧失败时停机，给你几天的时间来解决问题，而不是一个小时。

--
-- This procedure will be used by pg_cron to create both new
-- partitions for "today".
--
CREATE OR REPLACE PROCEDURE app.create_daily_partition(today date)
    LANGUAGE plpgsql AS
$$
BEGIN
    CALL app.create_chats_partition(today);
    CALL app.create_chat_messages_partition(today);
END;
$$;

SELECT cron.schedule('new-chat-partition', '0 23 * * *',
                     'CALL app.create_daily_partition(now()::date + "interval 1 day")');
COMMIT;

完整的示例代码

这就是完整的方案了！虽然存在一些复杂性，但我希望此示例能为您提供一些关于如何对数据进行分区的想法，以实现最佳的查询性能。根据您的需要，有很多方法可以解决这个问题，虽然我展示了一种按日期范围对时间序列数据进行分区的简单常用方法，但还有很多其他方法，我建议您查看官方 Postgres 表分区文档，了解如何针对您的情况应用其他的模式。

阅读原文：原文链接