分布式 PostgreSQL 集群(Citus)，分布式表中的分布列选择优秀实践_PostgreSQL

确定应用程序类型

在 Citus 集群上运行高效查询要求数据在机器之间正确分布。这因应用程序类型及其查询模式而异。

大致上有两种应用程序在 Citus 上运行良好。数据建模的第一步是确定哪些应用程序类型更接近您的应用程序。

概览

多租户应用	实时应用
有时 `schema` 中有几十个或数百个表	表数量少
一次与一个租户（公司/商店）相关的查询	具有聚合的相对简单的分析查询
用于服务 `Web` 客户端的 `OLTP` 工作负载	摄取大量几乎不可变的数据
为每个租户分析查询提供服务的 `OLAP` 工作负载	通常围绕着一个大的事件表

示例和特征

多租户应用

这些通常是为其他公司、帐户或组织服务的 SaaS 应用程序。大多数 SaaS 应用程序本质上是关系型的。它们具有跨节点分布数据的自然维度：只需按 tenant_id 分片。

Citus 使您能够将数据库扩展到数百万租户，而无需重新构建应用程序。您可以保留所需的关系语义，例如联接、外键约束、事务、ACID 和一致性。

示例：为其他企业托管店面的网站，例如数字营销解决方案或销售自动化工具。
特征：与单个租户相关的查询，而不是跨租户加入信息。这包括为 Web 客户端提供服务的 OLTP 工作负载，以及为每个租户提供分析查询的 OLAP 工作负载。在您的数据库模式中拥有数十或数百个表也是多租户数据模型的一个指标。

使用 Citus 扩展多租户应用程序还需要对应用程序代码进行最少的更改。我们支持流行的框架，如 Ruby on Rails 和 Django。

实时分析应用

需要大规模并行性、协调数百个内核以快速获得数值、统计或计数查询结果的应用程序。通过跨多个节点对 SQL 查询进行分片和并行化，Citus 可以在一秒钟内对数十亿条记录执行实时查询。

示例: 需要亚秒级响应时间的面向客户的分析仪表板。

特征: 几张表，通常以设备、站点或用户事件的大表为中心，并且需要大量摄取大部分不可变的数据。涉及多个聚合和 GROUP BY 的相对简单(但计算量大)的分析查询。

如果您的情况类似于上述任何一种情况，那么下一步就是决定如何在 Citus 集群中对数据进行分片。如概念部分所述，Citus 根据表分布列的哈希值将表行分配给分片。数据库管理员对分布列的选择需要与典型查询的访问模式相匹配，以确保性能。

选择分布列

Citus 使用分布式表中的分布列将表行分配给分片。为每个表选择分布列是最重要的建模决策之一，因为它决定了数据如何跨节点分布。

如果正确选择了分布列，那么相关数据将在相同的物理节点上组合在一起，从而使查询快速并添加对所有 SQL 功能的支持。如果列选择不正确，系统将不必要地缓慢运行，并且无法支持跨节点的所有 SQL 功能。

本节提供两种最常见的 Citus 方案的分布列提示。最后，它深入探讨了共置(co-location)，即节点上理想的数据分组。

多租户应用

多租户架构使用一种分层数据库建模形式在分布式集群中的节点之间分布查询。数据层次结构的顶部称为 tenant id，需要存储在每个表的列中。Citus 检查查询以查看它们涉及的 tenant id，并将查询路由到单个 worker 节点进行处理，特别是保存与 tenant id 关联的数据分片的节点。运行将所有相关数据放置在同一节点上的查询称为 Table Co-Location。

下图说明了多租户数据模型中的共置(co-location)。它包含两个表，Accounts 和 Campaigns，每个表都由 account_id 分配。阴影框代表分片，每个分片的颜色代表哪个 worker 节点包含它。绿色分片一起存储在一个 worker 节点上，蓝色分片存储在另一个节点上。请注意，当将两个表限制为相同的 account_id 时，Accounts 和 Campaigns 之间的 join 查询如何将所有必要的数据放在一个节点上。

分布式 PostgreSQL 集群(Citus)，分布式表中的分布列选择优秀实践

要在您自己的 schema 中应用此设计，第一步是确定在您的应用程序中构成租户的内容。常见实例包括公司(company)、帐户(account)、组织(organization)或客户(customer)。列名称类似于 company_id 或 customer_id。检查您的每个查询并问自己：如果它有额外的 WHERE 子句将所有涉及的表限制为具有相同 tenant id 的行，它会起作用吗?多租户模型中的查询通常以租户为范围，例如销售或库存查询将在某个商店内进行。

最佳实践

按公共 tenant_id 列对分布式表进行分区。例如，在租户是公司的 SaaS 应用程序中，tenant_id 可能是 company_id。

将小型跨租户表转换为引用表。当多个租户共享一个小信息表时，将其作为参考表分布。

限制按 tenant_id 过滤所有应用程序查询。每个查询应一次请求一个租户的信息。

阅读多租户应用程序指南，了解构建此类应用程序的详细示例。

实时应用

虽然多租户架构引入了分层结构并使用数据共置(data co-location)来路由每个租户的查询，但实时架构依赖于其数据的特定分布属性来实现高度并行处理。

我们在实时模型中使 “entity id” 作为分布列的术语，而不是多租户模型中的租户 ID。典型的实体是用户(users)、主机(hosts)或设备(devices)。

实时查询通常要求按日期(date)或类别(category)分组的数字聚合。Citus 将这些查询发送到每个分片以获得部分结果，并在 coordinator 节点上组装最终答案。当尽可能多的节点做出贡献并且没有单个节点必须做不成比例的工作时，查询运行速度最快。

最佳实践

选择具有高基数的列作为分布列。为了比较，订单表上的 status 字段具有新(new)、已付款(paid) 和已发货(shipped) 值，是分布列的一个糟糕选择，因为它只假设这几个值。不同值的数量限制了可以保存数据的分片数量以及可以处理数据的节点数量。在具有高基数的列中，最好另外选择那些经常用于 group-by 子句或作为 join 键的列。
选择分布均匀的列。如果您将表分布在偏向某些常见值的列上，则表中的数据将倾向于在某些分片中累积。持有这些分片的节点最终会比其他节点做更多的工作。
将事实表和维度表分布在它们的公共列上。您的事实表只能有一个分布 key。在另一个 key 上 join 的表不会与事实表位于同一位置。根据 join 的频率和 join 行的大小，选择一个维度来共同定位。
将一些维度表更改为引用表。如果维度表不能与事实表共存，您可以通过将维度表的副本以引用表的形式分发到所有节点来提高查询性能。

阅读实时仪表板指南，了解构建此类应用程序的详细示例。

时间序列数据

在时间序列工作负载中，应用程序在归档旧信息的同时查询最近的信息。

在 Citus 中建模时间序列信息的最常见错误是将时间戳本身用作分布列。基于时间的散列分布将看似随机的时间分布到不同的分片中，而不是将时间范围保持在分片中。但是，涉及时间的查询通常会参考时间范围(例如最近的数据)，因此这样的哈希分布会导致网络开销。

最佳实践

不要选择时间戳作为分布列。选择不同的分布列。在多租户应用程序中，使用租户 ID，或在实时应用程序中使用实体 ID。

改为使用 PostgreSQL 表分区。使用表分区将一个按时间排序的数据大表分解为多个继承表，每个表包含不同的时间范围。在 Citus 中分发 Postgres 分区的表会为继承的表创建分片。

阅读 Timeseries Data 指南，了解构建此类应用程序的详细示例。

表共置

关系数据库因其巨大的灵活性和可靠性而成为许多应用程序的首选数据存储。从历史上看，对关系数据库的一个批评是它们只能在一台机器上运行，当数据存储需要超过服务器改进时，这会产生固有的限制。快速扩展数据库的解决方案是分发它们，但这会产生其自身的性能问题：join 等关系操作需要跨越网络边界。共置(Co-location) 是一种策略性地划分数据的做法，将相关信息保存在同一台机器上以实现高效的关系操作，但利用整个数据集的水平可扩展性。

数据共存的原理是数据库中的所有表都有一个共同的分布列，并以相同的方式跨机器分片，使得具有相同分布列值的行总是在同一台机器上，即使跨不同的表也是如此。只要分布列提供了有意义的数据分组，就可以在组内执行关系操作。

Citus 中用于 hash 分布表的数据共存

PostgreSQL 的 Citus 扩展在能够形成数据库的分布式数据库方面是独一无二的。Citus 集群中的每个节点都是一个功能齐全的 PostgreSQL 数据库，Citus 在顶部添加了单个同构数据库的体验。虽然它没有以分布式方式提供 PostgreSQL 的全部功能，但在许多情况下，它可以通过托管在单台机器上充分利用 PostgreSQL 提供的功能，包括完整的 SQL 支持、事务和外键。

在 Citus 中，如果分布列中值的哈希值落在分片的哈希范围内，则将一行存储在分片中。为了确保共置，即使在重新平衡操作之后，具有相同哈希范围的分片也始终放置在同一个节点上，这样相等的分布列值始终位于跨表的同一个节点上。

分布式 PostgreSQL 集群(Citus)，分布式表中的分布列选择优秀实践

我们发现在实践中运行良好的分布列是多租户应用程序中的租户 ID。例如，SaaS 应用程序通常有许多租户，但它们所做的每个查询都是特定于特定租户的。虽然一种选择是为每个租户提供 database 或 schema，但它通常成本高昂且不切实际，因为可能有许多跨用户的操作(数据加载、迁移、聚合、分析、schema 更改、备份等)。随着租户数量的增加，这变得更难管理。

共置的实际示例

考虑以下表格，这些表格可能是多租户 Web 分析SaaS 的一部分：

CREATE TABLE event (
  tenant_id int,
  event_id bigint,
  page_id int,
  payload jsonb,
  primary key (tenant_id, event_id)
);

CREATE TABLE page (
  tenant_id int,
  page_id int,
  path text,
  primary key (tenant_id, page_id)
);

现在我们要回答可能由面向客户的仪表板发出的查询，例如：“返回租户六中所有以‘/blog’开头的页面在过去一周的访问次数。”

使用常规 PostgreSQL 表

如果我们的数据位于单个 PostgreSQL 节点中，我们可以使用 SQL 提供的丰富的关系操作集轻松地表达我们的查询：

SELECT page_id, count(event_id)
FROM
  page
LEFT JOIN  (
  SELECT * FROM event
  WHERE (payload->>'time')::timestamptz >= now() - interval '1 week'
) recent
USING (tenant_id, page_id)
WHERE tenant_id = 6 AND path LIKE '/blog%'
GROUP BY page_id;

只要此查询的工作集适合内存，这是许多应用程序的合适解决方案，因为它提供了最大的灵活性。但是，即使您还不需要扩展，考虑扩展数据模型的影响也会很有用。

按 ID 分布表

随着租户数量和为每个租户存储的数据的增长，查询时间通常会增加，因为工作集不再适合内存或 CPU 成为瓶颈。在这种情况下，我们可以使用 Citus 跨多个节点分片数据。分片时我们需要做出的第一个也是最重要的选择是分布列。让我们从一个天真的选择开始，将 event_id 用于事件表，将 page_id 用于页表：

-- naively use event_id and page_id as distribution columns

SELECT create_distributed_table('event', 'event_id');
SELECT create_distributed_table('page', 'page_id');

鉴于数据分散在不同的 worker 中，我们不能像在单个 PostgreSQL 节点上那样简单地执行 join。相反，我们需要发出两个查询：

跨页表的所有分片(Q1)：

SELECT page_id FROM page WHERE path LIKE '/blog%' AND tenant_id = 6;

跨事件表的所有分片(Q2)：

SELECT page_id, count(*) AS count
FROM event
WHERE page_id IN (/*…page IDs from first query…*/)
  AND tenant_id = 6
  AND (payload->>'time')::date >= now() - interval '1 week'
GROUP BY page_id ORDER BY count DESC LIMIT 10;

之后，应用程序需要组合这两个步骤的结果。

回答查询所需的数据分散在不同节点上的分片中，每个分片都需要被查询：

分布式 PostgreSQL 集群(Citus)，分布式表中的分布列选择优秀实践

在这种情况下，数据分布会产生很大的缺陷：

查询每个分片的开销，运行多个查询
Q1 的开销返回许多行给客户端
Q2 变得非常大
需要在多个步骤中编写查询，组合结果，需要在应用程序中进行更改

相关数据分散的一个潜在好处是查询可以并行化，Citus 会这样做。但是，这只有在查询的工作量远远大于查询许多分片的开销时才有用。通常最好避免直接从应用程序中进行如此繁重的工作，例如通过预先聚合数据。

按租户分布表

再次查看我们的查询，我们可以看到查询需要的所有行都有一个共同的维度：tenant_id。仪表板只会查询租户自己的数据。这意味着，如果同一租户的数据始终位于单个 PostgreSQL 节点上，那么我们的原始查询可以由该节点通过对 tenant_id 和 page_id 执行 join 来一次性回答。

在 Citus 中，具有相同分布列值的行保证在同一个节点上。分布式表中的每个分片实际上都有一组来自其他分布式表的位于同一位置的分片，这些分片包含相同的分布列值(同一租户的数据)。从头开始，我们可以创建以 tenant_id 作为分布列的表。

-- co-locate tables by using a common distribution column
SELECT create_distributed_table('event', 'tenant_id');
SELECT create_distributed_table('page', 'tenant_id', colocate_with => 'event');

在这种情况下，Citus 可以回答您将在单个 PostgreSQL 节点上运行而无需修改 (Q1) 的相同查询：

SELECT page_id, count(event_id)
FROM
  page
LEFT JOIN  (
  SELECT * FROM event
  WHERE (payload->>'time')::timestamptz >= now() - interval '1 week'
) recent
USING (tenant_id, page_id)
WHERE tenant_id = 6 AND path LIKE '/blog%'
GROUP BY page_id;

由于使用了 tenantid 过滤器和 tenantid 上的 join，Citus 知道可以使用包含特定租户数据的一组位于同一位置的分片来回答整个查询，而 PostgreSQL 节点可以在一个步骤中回答该查询，从而支持完整的 SQL 支持。

分布式 PostgreSQL 集群(Citus)，分布式表中的分布列选择优秀实践

在某些情况下，查询和表 schema 需要进行少量修改，以确保 tenant_id 始终包含在唯一约束和 join 条件中。但是，这通常是一个简单的更改，并且避免了在没有共置的情况下所需的大量重写。

虽然上面的示例只查询一个节点，因为有一个特定的 tenant_id = 6 过滤器，但共置还允许我们在所有节点上有效地执行对 tenant_id 的分布式 join，尽管存在 SQL 限制。

共置意味着更好的功能支持

Citus 通过共置解锁的功能的完整列表如下：

对一组位于同一位置的分片上的查询的完整 SQL 支持
多语句事务支持对一组位于同一位置的分片进行修改
通过 INSERT..SELECT 聚合
外键
分布式外部联接(outer join)
Pushdown CTEs(要求 PostgreSQL >=12 )

数据共置是一种强大的技术，可以为关系数据模型提供水平扩展和支持。使用分布式数据库迁移或构建应用程序的成本(通过共置实现关系操作)通常大大低于迁移到限制性数据模型(例如 NoSQL)的成本，并且与单节点数据库不同，它可以随着规模的大小而横向扩展您的业务。有关迁移现有数据库的更多信息，请参阅过渡到多租户数据模型。

查询性能

Citus 通过将传入查询分解为多个在工作分片上并行运行的片段查询(“任务”)来并行化传入查询。这使 Citus 可以利用集群中所有节点的处理能力以及每个节点上的单个核心的处理能力来进行每个查询。由于这种并行化，您可以获得集群中所有核心的计算能力的累积性能，与单个服务器上的 PostgreSQL 相比，查询时间显着减少。

Citus 在规划 SQL 查询时采用了两阶段优化器。第一阶段涉及将 SQL 查询转换为它们的交换和关联形式，以便它们可以下推并在工作线程上并行运行。如前几节所述，选择正确的分布列和分布方法允许分布式查询规划器对查询应用多种优化。由于网络 I/O 减少，这会对查询性能产生重大影响。

Citus 的分布式执行器然后将这些单独的查询片段发送到 PostgreSQL worker 实例。分布式规划器和执行器都有几个方面可以调整以提高性能。当这些单独的查询片段被发送给 worker 时，查询优化的第二阶段就开始了。worker 只是运行扩展的 PostgreSQL 服务器，他们应用 PostgreSQL 的标准计划和执行逻辑来运行这些片段 SQL 查询。因此，任何有助于 PostgreSQL 的优化也有助于 Citus。PostgreSQL 默认带有保守的资源设置;因此优化这些配置设置可以显着缩短查询时间。

我们在文档的查询性能调优部分讨论了相关的性能调优步骤。