案例：Postgres中构建客户数据仓库


在 Tembo（，我们希望拥有一个客户数据仓库来跟踪和了解客户的使用情况和行为。我们希望快速回答​​诸如

  • “我们部署了多少个 Postgres 实例？”、
  • “谁是我们最活跃的客户？”之类的问题。以及
  • “截至目前我们有多少注册用户？”。

为了做到这一点，我们需要将多个来源的数据转移到一个位置并保持最新，以便我们可以构建仪表板。

通常，此过程需要多种编排工具和技术，最终结果是一个高度复杂的数据生态系统。然而，我们通过使用外部数据包装器和其他 Postgres 扩展，完全在 Postgres 上构建了我们的客户数据仓库，从而提高了效率并简化了流程。

从多个来源加载数据
为了在 Tembo 构建数据仓库，我们首先需要将运营数据从多个外部来源拉入 Postgres，即：

  • Postgres - 我们运行专用的 Postgres 集群（称为“控制平面”）来存储客户 Tembo 实例的所有元数据。该数据库包含实例的 cpu、内存和存储规格、实例创建时间、实例名称、所属组织等信息。我们还使用pgmq在 Postgres 中运行消息队列，其中包含来自系统。
  • Prometheus - Prometheus 存储我们的使用指标，例如 CPU 和内存使用情况，这些指标是从我们的基础设施（包括 Postgres 和 Kubernetes）导出的。
  • Clerk.dev - 我们与 Clerk 合作，为所有客户提供身份验证和授权，以及用户组织的管理。因此，Clerk 拥有我们所有的用户元数据和组织信息。

第一个任务是将所有这些数据集中到一个位置，以便我们可以将数据连接在一起、分析数据并将其呈现在仪表板中。

将 Postgres 连接到源
现在，我们如何将所有这些来源的数据获取到 Postgres 中？我们已经知道可以使用postgres_fdw连接数据仓库和控制平面 Postgres 实例。但是，我们不确定如何对 Prometheus 和 Clerk 中的数据执行相同的操作。

Prometheus 已经有几个可用的项目，但没有一个适合我们的用例。 Clerk.dev 没有任何现有的 Postgres 扩展，因此我们决定构建自己的.

外来数据封装器
外来数据封装器（FDW）是一类 Postgres 扩展，为您提供一个简单的接口，将 Postgres 连接到另一个数据源。如果你用过 Kafka，这就类似于 "连接器"。有许多不同的外来数据封装器可用，你甚至可以编写自己的封装器。此外，Wrappers 框架让用 Rust 开发 FDW 变得非常容易。

因此，我们使用 Wrappers 框架构建了两个新的 FDW 来连接这些源；clerk_fdw 和 prometheus_fdw。

使用 FDW 是一种相当一致的体验。我们将详细介绍如何设置 clerk_fdw，而 prometheus_fdw 和 postgres_fdw 的设置过程也大同小异。

详细点击标题

使用 pg_cron 安排更新
新用户每天都会注册 Tembo 并创建新实例，因此我们需要确保数据仓库中的数据保持最新。为此，我们使用流行的作业调度扩展pg_cron。如果您熟悉 unix 实用程序“cron”，那么 pg_cron 与它完全相同，但都在 Postgres 中。

我们创建一个函数来刷新数据源，然后告诉 pg_cron 按计划调用该函数。使用 pg_cron 非常直观：我们只需提供作业的名称、计划和要执行的命令。这很像创建一个作业来执行您使用 Apache Airflow 或 Dagster 编写的某些代码。

使用 pg_partman 进行分区以提高性能并轻松过期
分区是 Postgres 的一项本机功能，它将一个表逻辑拆分为更小的物理表。我们的数据仓库中的一些表（但不是全部）已经变得相当大。最大的是我们的指标，这提出了两个必须解决的问题；性能和存储。我们的大多数仪表板查询都会随着时间的推移聚合数据，最常见的是每天一次。因此，我们可以按天对表进行分区，并且只查询回答问题所需的分区。这极大地提高了这些查询的性能，使我们的仪表板变得非常敏捷。

Postgres 中的分区是一种无需电池的体验，这意味着您需要自己处理分区的创建、更新和删除。也就是说，除非您使用pg_partman扩展。

我们的利益相关者不需要对整个指标数据进行可视化，事实上他们通常最多只关心 30 天。因此，我们可以通过设置保留策略并回收该存储来自动删除超过 30 天的分区。删除分区比从表中删除行要快得多，并且还可以跳过处理膨胀的问题。正如我们稍后将看到的，如果您使用 pg_partman（这是我个人最喜欢的 Postgres 扩展），那么在 Postgres 上配置分区是很简单的。

CREATE TABLE public.metric_values (
    id int8 NOT NULL,
    "time" int8 NULL,
    value float8 NOT NULL,
    CONSTRAINT metric_values_unique UNIQUE (id, "time")
)
PARTITION BY RANGE ("time");
CREATE INDEX metric_values_time_idx ON ONLY public.metric_values USING btree ("time" DESC);

接下来，我们设置 pg_partman。我们将分区表传递到create_parent().

SELECT create_parent('public.metric_values', 'time', 'native', 'daily');

总结：

  • 使用外部数据包装器将 Postgres 连接到外部源，
  • 使用 pg_cron 作为调度程序来保持数据最新，
  • 使用 pg_partman 来提高性能并自动化我们的保留策略。

您可以使用任何可视化工具来创建仪表板，因为大多数工具都支持 Postgres。我们选择了 Preset。

我们建立的数据仓库易于维护，易于推理，并能快速启用新的工程师。

最终，我们的利益相关者获得了做出业务决策所需的仪表盘。