PostgreSQL数据库中的表分区

xcvxvxc
636 阅读14分钟

作者后 台工程师Ruslan Shakirov，后台工程师Denis Lifanov，以及Evil Martians的技术编辑Travis Turner

在任何成功的项目中，流量的激增，伴随着必须存储和处理的数据量的增加，都是不可避免的。因此，数据库本身通常是业务快速增长期间的主要瓶颈之一，因为数据量直接影响到查询执行速度。

你可以试着暂时容忍这种速度的下降，或者你可以尝试通过额外的资源来扩展你的系统（虽然，让我们在这里直截了当地说--这不是解决这个问题的最实惠的方法）。此外，还有一个更便宜、更有效的解决方案：分区。

分区本身当然不是一个放之四海而皆准的解决方案。它只有在大表中的数据可以根据某些标准被分成几组的情况下才真正有意义。也就是说，你会经常遇到这样的情况，因为表经常包含允许轻松分组的数据字段（一个典型的例子是时间戳）。因此，在很多情况下你都可以使用分区。

这篇文章是为Ruby on Rails的开发者准备的，但各种类型的后台工程师也可以自由阅读

分区的介绍

分区涉及到根据一些属性（如时间范围、区域、甚至用户ID组）将大表分割成小表。这是一个合乎逻辑的步骤，它可以显著提高你的性能。但是，一个错误的实施可能会产生意想不到的效果，从而失去潜在的好处，并使你与数据库的工作完全变成一场噩梦（后面会有更多的介绍）。

分区的好处是什么？嗯，首先，对于我们这些数据库用户和Ruby开发者来说，几乎没有什么变化--在分区之前，我们是在使用和引用一个大表来读写。之后，我们仍然在工作并引用一个大的（现在技术上是虚拟的）表，但是在它里面有许多小的表（分区）。这就导致了最主要的好处：速度!当写数据时，尽管我们引用所有的分区，就像我们引用一张表一样，但新的记录只被插入到一个分区的表中。另外，读取数据也变得更快，但只有当我们从一个或几个分区中读取数据时（否则，在一个未分区的表中进行搜索可能会更快）。

在PostgreSQL中，有2种分区的方法。

  1. 老方法，通过继承（在分区得到PostgreSQL官方支持之前，工程师们就在使用这种技术）。
  2. 声明式分区，我们在PostgreSQL 10中得到了这种分区。这是一种创建和管理分区的方便方法，由数据库管理系统（DBMS）本身提供，尽管它在引擎盖下使用相同的继承机制。

我们推荐使用声明式分区，因为这种方法提供了官方的支持，而且DMBS的开发者在每个新版本中都会继续扩展其功能。也就是说，如果由于任何原因，你需要使用过时的方法实现分区，你可以阅读这篇文章。分区和征服。(但请记住，这种方法在Rails环境下实现起来比较麻烦，而且现在被认为是一种传统的方法）。

一些开始的提示

让我们快速介绍一下我们的工作范围的背景，我们将设置一个例子，我们将与分区的主题相关联。在Evil Martians，我们是运行eBaymag项目的核心技术团队，时间超过了7年。在工程方面，该产品是完全围绕着事件处理而建立的。这意味着我们一直在与事件和处理程序打交道，以处理每一个事件。我们在数据库中存储事件，在后台使用Sidekiq处理它们，并将它们标记为 "完成"。

每周我们都会收到并处理超过3000万个事件。当然，当它们过期时，我们会将它们清理掉，但有些事件必须存储很长时间，因为它们是用户在这个电子商务平台上的产品发生的日志（最常见的是涉及销售或数量变化）。一旦该表膨胀到3亿条记录，查询开始运行得更慢，性能开始下降。该怎么做呢？好吧，我们用分区的方式解决了这个问题。

让我们来看看!因此，有2个由uuid连接的表--event_store_messages （有原始事件数据）和event_store_completions （关于事件处理的数据）。

这里是event_store_messages 的模式。

The event store messages schema displayed.

以及event_store_completions 的模式。

The event store completions schema displayed.

由于这些表是相互连接的，而且都包含大量的数据，我们将对它们进行分区。

但我们将如何做到这一点呢？考虑到我们的工作是与事件相关的，使用时间（通过时间列）作为分割表的标准会非常方便。例如，按周划分将加快查询的执行速度，一般来说，使事件处理更加方便，因为我们需要最近的事件来进行SQL操作。随着时间的推移，旧的条目会失去其相关性，可以被删除。因此，我们将选择按范围划分。

首先，我们需要创建一个分区表：其模式将与我们的原始模式表相同，原始表将成为第一个分区。为什么呢？这是因为不可能把一个普通的表变成一个分区表（反之亦然）。

我们将创建我们的分区表，event_store_messages_partitioned 。

CREATE TABLE event_store_messages_partitioned
(
  uuid        uuid DEFAULT gen_random_uuid() NOT NULL,
  kind        character varying              NOT NULL,
  data        jsonb,
  "time"      timestamp WITHOUT TIME ZONE    NOT NULL,
  parent_uuid uuid
) PARTITION BY RANGE ("time");

现在我们将添加主键，并注意它必须包含 "时间 "分区键（这是PostgreSQL对分区表的要求之一，见：声明式分区限制）。

📝 注意：主复合键允许我们创建具有相同uuid的条目。同时，由于唯一约束必须包括一个分区键，我们不能确保uuid是唯一的。

添加主键。

ALTER TABLE event_store_messages_partitioned
  ADD CONSTRAINT event_store_messages_partitioned_pkey PRIMARY KEY (uuid, "time”);

现在我们要创建索引。

CREATE INDEX index_event_store_messages_partitioned_on_parent_uuid
  ON public.event_store_messages_partitioned USING btree(parent_uuid) INCLUDE (uuid) WHERE ( parent_uuid IS NOT NULL );
CREATE INDEX index_event_store_messages_partitioned_on_time
  ON public.event_store_messages_partitioned USING btree("time");
CREATE INDEX index_event_store_messages_partitioned_on_kind_and_time
  ON public.event_store_messages_partitioned USING btree(kind, "time") INCLUDE (uuid);

并且让我们重复上面的过程来创建event_store_completions_partitioned 表。

CREATE TABLE public.event_store_completions_partitioned
(
  uuid         uuid DEFAULT gen_random_uuid() NOT NULL,
  message_uuid uuid                           NOT NULL,
  handler      character varying              NOT NULL,
  created_at   timestamp WITHOUT TIME ZONE    NOT NULL,
  updated_at   timestamp WITHOUT TIME ZONE    NOT NULL,
  "time"       timestamp WITHOUT TIME ZONE    NOT NULL
) PARTITION BY RANGE ("time");

我们应该强调，我们要保持数据的完整性，所以为此，我们不会摆脱外键。但是，外键又必须指向一些含有分区键的唯一约束的字段。因此，为了给event_store_completions 和event_store_messages 表创建所需的外键，我们需要在event_store_completions 表中存储 "时间 "分区键。

ALTER TABLE public.event_store_completions_partitioned ADD CONSTRAINT event_store_completions_partitioned_pkey PRIMARY KEY (uuid, "time");

ALTER TABLE public.event_store_completions_partitioned
  ADD CONSTRAINT fk_event_store_completions_partitioned_message_uuid FOREIGN KEY (message_uuid, "time")
    REFERENCES public.event_store_messages(uuid, "time") ON UPDATE CASCADE ON DELETE CASCADE;

然后让我们创建索引。

CREATE INDEX event_store_completions_p_handler_message_uuid_idx ON public.event_store_completions_partitioned USING btree(handler) INCLUDE (message_uuid);
CREATE UNIQUE INDEX idx_escp_on_message_uuid_time_and_handler ON public.event_store_completions_partitioned USING btree(message_uuid, handler, "time");

表已经被创建了!然而，还有更多的事情要做：在连接我们的原始表之前，我们需要改变它们的主键。如果我们只是用一个简单的ADD PRIMARY KEY 语句来做这件事，那么PostgreSQL将使用最强的锁--ACCESS EXCLUSIVE--可能会确保你的应用程序将停止运行一段不受欢迎的时间。我们不想让这种情况发生，所以我们将使用一种不同的方法来改变主键--我们将以非阻塞的方式事先准备好唯一的索引。

CREATE UNIQUE INDEX CONCURRENTLY event_store_messages_unique_idx
ON event_store_messages (uuid, "time");

CREATE UNIQUE INDEX CONCURRENTLY event_store_completions_unique_idx
ON event_store_completions (uuid, "time");

奖励：为分区表添加索引

我们不能为整个分区表并发地创建索引，但有一个解决方案：我们可以为每个分区并发地添加一个索引。这样做之后，我们将为分区表添加一个带有ONLY 标志的索引。最后，我们将通过为每个分区索引运行ATTACH PARTITION 命令，将分区索引与分区表的索引联系起来。

就这样，我们完成了所有必要的准备工作：让我们真正地创建一个分区；old_event_store_messages 将是我们的第一个分区。

ALTER TABLE public.event_store_messages
  RENAME TO old_event_store_messages;
ALTER TABLE public.event_store_messages_partitioned
  RENAME TO event_store_messages;
ALTER TABLE public.event_store_completions
  DROP CONSTRAINT fk_event_store_completions_message_uuid;
ALTER TABLE public.old_event_store_messages
  DROP CONSTRAINT event_store_messages_pkey;

由于我们已经创建了一个唯一的索引，我们可以在几秒钟内轻松地创建一个基于它的主键！一个表只能有一个主键。一个表只能有一个主键，所以我们先删除它，然后创建一个新的。

ALTER TABLE old_event_store_messages
  ADD CONSTRAINT event_store_messages_pkey PRIMARY KEY USING INDEX event_store_messages_unique_idx;

现在一切都准备好了，我们可以简单地将old_event_store_messages 到event_store_completions_partitioned 。

或者我们可以吗？

这里还有一个细微的差别需要考虑，不注意这一点会让你付出额外的停机时间：当我们用数据附加一个分区时，PostgreSQL会检查数据是否与分区约束一致。CHECK 我们可以通过在附件之前创建一个NOT VALID 选项来避免这种情况。这允许我们跳过数据检查。也就是说，你应该只在确定你的数据是有效的情况下这样做。

ALTER TABLE old_event_store_messages
  ADD CONSTRAINT first_partition_integrity CHECK ( "time" BETWEEN ( '-infinity' ) AND ( DATE_TRUNC('week', CURRENT_DATE::timestamp) + '7 days'::interval )) NOT VALID;

在附加了一个分区之后，我们就不再需要这个约束了，所以我们可以删除它。

ALTER TABLE event_store_messages
  ATTACH PARTITION old_event_store_messages FOR VALUES FROM ('-infinity') TO (DATE_TRUNC('week', CURRENT_DATE::timestamp) + '7 days'::interval);
ALTER TABLE old_event_store_messages
  DROP CONSTRAINT first_partition_integrity;

我们将为第二个表运行同样的过程。

ALTER TABLE public.event_store_completions
  RENAME TO old_event_store_completions;
ALTER TABLE public.event_store_completions_partitioned
  RENAME TO event_store_completions;
ALTER TABLE public.old_event_store_completions
  DROP CONSTRAINT event_store_completions_pkey;
ALTER TABLE public.old_event_store_completions
  ADD CONSTRAINT event_store_completions_pkey PRIMARY KEY USING INDEX event_store_completions_unique_idx;
ALTER TABLE public.old_event_store_completions
  ADD CONSTRAINT first_partition_integrity CHECK ( "time" BETWEEN ( '-infinity' ) AND ( DATE_TRUNC('week', CURRENT_DATE::timestamp) + '7 days'::interval )) NOT VALID;
ALTER TABLE public.event_store_completions
  ATTACH PARTITION public.old_event_store_completions FOR VALUES FROM ('-infinity') TO (DATE_TRUNC('week', CURRENT_DATE::timestamp) + '7 days'::interval);
ALTER TABLE public.old_event_store_completions
  DROP CONSTRAINT first_partition_integrity;

然后......我们的表的分区就完成了。万岁!🥳

奖励：如何创建分区

如果我们只有一个分区，那么分区是没有用的。但是，手动创建新的分区往往是令人厌烦和危险的（因为人可能会简单地忘记它）。因此，我们必须自动创建分区，这里有几种方法可以做到这一点。

  1. 使用cron作业。例如，如果你按周进行分区（就像我们这里的事件表一样），那么你可以事先准备好分区。尽管这不是最安全的选择，因为如果你在某种程度上不能创建一个新的分区，所有使用该分区的SQL查询都会失败。
  2. 使用触发器。下面是一个例子，当你想分区的表有一个user_id 字段，它引用了public.users 表的id 字段。在下面的代码中，我们为每1000个用户创建一个新的分区。

Using triggers to create partitions for every 1000 users

考察一些可能的分区隐患

我们上面描述的分区过程在理论上非常漂亮，但在现实生活中的Rails场景中工作时，几乎总是需要采取一些额外的步骤。

陷阱#1：Rails本身

首先，Rails不能使用复合主键。但是，尽管如此，你还是需要使用它们，原因有二。

分区表已经使用了一个复合主键（其中包括分区键）。当然，你仍然可以使用没有分区键的查询，并为UPDATE 和其他一些事情手动编写你的SQL。但是......也有关联要处理。此外，相关的表往往是分区的；相应地，你需要一个方便的工具来处理它们。

幸运的是，我们有一些现成的解决方案：例如，复合主键宝石。然而，最好记住，这个 gem 解决了支持不同 ORM 的复合主键的问题。因此，在某些情况下，由该宝石收集的查询可能是非常复杂的（有许多层次的嵌套）。在这里，你必须决定：是牺牲性能来换取漂亮的代码，还是通过手动编写一部分SQL查询来避免额外的复杂性？

📝 注意：由于查询嵌套和排序的原因，有些查询（截至gem 12版本）可能是无效的。例如，像这样的结构...

scope = EventStore::Message.where("time > ?", 1.day.ago).order(:time)
scope.update_all("...")

...会给我们以下的回应（注意两个ORDER BY 子句；第二个子句显然是多余的）。

UPDATE "event_store_messages" SET ... WHERE ("event_store_messages"."uuid", "event_store_messages"."time") IN (SELECT "event_store_messages"."uuid", "event_store_messages"."time" FROM "event_store_messages" WHERE (time > '2022-01-19 14:33:16.239683') ORDER BY "event_store_messages"."time" ASC) ORDER BY "event_store_messages"."time" ASC

陷阱#2：锁

Ruby on Rails（仍然）将分区表接受为一个单一的表（这很好！），但我们要处理的是几个小表。当我们只有十几张的时候，这还可以，但是面对一百张，甚至几百张的时候，该怎么办？休斯顿，我们有一个问题。🧑🚀

想象一下，你有一个大表，例如，你运行一个简单的查询，根据一些条件选择N条记录，相应地，PostgreSQL在这个表中创建了ACCESS SHARE 锁，以及它的序列和索引。但是如果你有100个分区，而你的查询没有指定应该使用的分区范围，那么将为每个分区创建锁。一般来说，(N + 1) * M，其中N是分区的数量（不要忘记分区表！），M是PostgreSQL想要锁定的一个表中的实体数量。结果，一个通常需要10个锁的查询，在80个分区的情况下，需要超过800个。

让我们在这个过程中加入事务。假设我们有一个典型的服务，在分区表和相关表（其中一些也可能是分区的）中处理记录。在一个事务中创建的锁将不会被释放，直到事务的最后--也就是说，直到COMMIT/ROLLBACK 。这意味着，如果你在单位时间内有许多类似的事务操作，PostgreSQL将不可避免地报告。out of shared memory，而且它将伴随着这个通知，紧急要求增加每个事务的最大锁。

这就是为什么在分区之前，你必须检查所有（实际上是）所有的查询，看它们是否使用分区键。因为即使是一个无害的find_by_id ，也会让你损失停机时间，并导致以后的神经烧坏。

这里有一个例子，解决了同样的挑战，在飞行中的查询（但在实施分区后）。

Solving the challenge with queries on the fly graph result displayed.

陷阱#3：索引

当你在处理大量的数据时（正是你需要分区的那种情况），关键是要明白你创建的索引应该加速你对表的工作，而不是减慢一切。例如，一个（时间，uuid）类型的主键会降低在大范围的时间内按uuid选择事件的效率（对于一个或两个分区）。

Index Scan using event_store_messages_08112021_pkey on event_store_messages_08112021  (cost=0.43..38480.54 rows=1 width=337) (actual time=166.344..166.345 rows=0 loops=1)
   Index Cond: (("time" > '2021-11-08 00:00:00'::timestamp without time zone) AND (uuid = '53400a2c-3654-411b-9247-288247406087'::uuid))
 Planning Time: 0.549 ms
 Execution Time: 166.366 ms
(4 rows)

为了比较，让我们看看用（uuid，时间）进行的相同操作。

Index Scan using event_store_messages_pkey on event_store_messages_08112021  (cost=0.43..2.65 rows=1 width=337) (actual time=0.008..0.008 rows=0 loops=1)
   Index Cond: ((uuid = '53400a2c-3654-411b-9247-288247406087'::uuid) AND ("time" > '2021-11-08 00:00:00'::timestamp without time zone))
 Planning Time: 0.425 ms
 Execution Time: 0.017 ms
(4 rows)

如果时间样本对你来说比较关键，这可能是一个合理的折中方案；在这种情况下，你可以为按uuid的选择创建一个附加索引。但是，在任何情况下，如果你在计划分区方案时总是考虑到这些细微的差别，将会很有帮助。

压轴大戏

对于工程师来说，分区已经变得更加方便。你可以（而且有时必须）进行分区。例如，在数据量妨碍你工作的情况下（或在将来可能会使工作复杂化的情况下），以及在数据可以很容易地被分成几组的情况下。但是，尽管这个方法很简单，分区还是有很多细微的差别，可能会导致相反的预期效果，所以要小心!请随意使用这篇文章作为参考，并在可能出现任何问题时，保持文档的便利性。

而且，如果你在后端、前端或更远的地方遇到问题，邪恶的火星人已经准备好提供帮助了！我们将检测它，分析它。我们将检测它，分析它，并将它消灭掉！（或者，如果你错过了这个问题，你将会发现它。(或者，如果你错过了什么，我们会把它消灭掉！）请给我们留言

avatar
文章
阅读
粉丝