数据库架构

如何设计您的 PostgreSQL 数据库：两个架构示例

How to Design Your PostgreSQL Database: Two Schema Examples—a developer's desk with a lot of data showing up on the screens.

作者 Dylan Paulus

我们设计和布置表格的方式，是在开发高效的 PostgreSQL 数据库中最重要的事情之一。数据库架构指的是数据库中数据的架构或结构。它定义了我们将要拥有的表格、列、数据类型、命名以及表格之间的关系。

数据库架构不应与PostgreSQL 架构混淆，后者是用于组织数据库的命名空间机制。

数据库架构是一个广义的概念，并非 PostgreSQL 特定。数据类型的选择、每个表格将具有的列以及表格之间的关系，会显著影响我们查询数据、写入数据以及维护数据库的速度。在本文中，我们将通过两个场景详细介绍不同的数据库架构设计，并介绍不同架构设计方法以及权衡取舍。

架构设计方法

在我们开始设计数据库架构之前，首先需要了解我们系统的需求。我们需要优化大量的写入操作吗？数据很少更改吗？我们可以优化繁重的读取操作吗？数据的读取、维护和访问方式将极大地改变我们对数据建模的方法以及我们愿意接受的权衡取舍。

在之前的文章中，我们探讨了使用宽表格、中表格和窄表格以及单表格和多表格的常见数据库架构设计模式。这两篇文章都概述了在设计数据库架构时不同的优点和缺点。

回顾

  • 窄表格：它们只有很少的“数据值列”，但可以包含许多元数据或分类列。窄表格非常适合拥有许多小型表格。

  • 宽表格：它们拥有许多数据值列。这些表格可以轻松拥有 200 多列，包含不同的数据点。宽表格通常会保存所有数据点，因此我们将拥有一个或几个表格。

  • 中表格：它们介于窄表格和宽表格之间，是一种折中的方案。

类似的技术也适用于数据库规范化。规范化是组织数据和关系以减少冗余和数据不一致的过程。遵循规范化对于降低复杂性和提高可维护性很重要，但有时我们也希望反规范化表格。

例如，在多个联接成本高昂且读取操作需要快速的情况下，拥有一个包含所有所需数据的宽表格，将比拥有许多规范化的窄表格更快。窄表格与宽表格、规范化与反规范化以及单表格与多表格，都是我们在设计数据库架构时可以使用的工具。

通过示例构建

我们将通过两个场景来巩固我们对数据库架构设计的理解。每个场景将具有不同的需求、解决方案和缺陷

1. 我将介绍我们将要建模的数据类型。

2. 我们将根据提供的背景信息创建数据库架构。

3. 场景将添加新的信息或“功能蔓延”，从而改变架构。

4. 我们将讨论这种方法的优缺点。

记录传感器数据

背景

我们有一个应用程序，它每分钟从世界各地的气象站读取温度数据。客户使用该应用程序获取给定城市的小时平均温度。由于是时间序列数据，因此数据将是追加式写入的。数据很少更新或删除。在这种情况下，气象站需要大量的投资来测量不同的数据点，因此架构很少会改变。

方法

根据背景信息，我们知道客户在查找平均温度时会按位置查询我们的系统。因此，位置将是主要搜索参数，可能需要索引。我们的架构也很少会改变。

我们事先知道每个气象站收集的数据（仅限温度），因此我们将使用宽表格、单表格来存储所有数据。单表格将比多个表格保持更快的查询速度，后者需要许多联接。考虑到这些细节，我们将创建一个名为temperatures 的表格，包含四列：created 用于记录测量的 timestamp，degrees 用于保存以摄氏度为单位的温度，以及city 和country 用于知道在哪里测量温度。

我们的 SQL 代码如下所示

CREATE TABLE temperatures (
    created  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    degrees  DOUBLE PRECISION,
    city     TEXT NOT NULL,
    country  TEXT NOT NULL
);


CREATE INDEX idx_location ON temperatures (country, city);

功能蔓延

在检查其中一个气象站时，工程师发现每个气象站也可以测量风速和风向。我们希望将这些信息添加到我们的应用程序中。为了便于使用，我们将继续使用宽表格、单表格方法，并将风速和风向包含在我们的温度读数中，因为我们的传感器架构保持相对一致。

让我们将temperatures 架构演变为包含wind_speed 和wind_direction 列

CREATE TABLE temperatures (
    created         TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    degrees         DOUBLE PRECISION,
    city            TEXT NOT NULL,
    country         TEXT NOT NULL
    wind_speed      DOUBLE PREVISION,
    wind_direction  TEXT -- N, NE, E, SE, S, SW, W, NW
);


CREATE INDEX idx_location ON temperatures (country, city);

这种架构设计的优缺点

与每种架构设计一样，我们的实现都有优点和缺点。在本例中，我们通过拥有包含所有数据的单表格来优先考虑快速读取和插入。单表格也更容易维护。

优点

  • 我们只有很少的表格需要维护，降低了系统复杂性，并确保查询速度快。

  • 非常容易关联给定城市的 wind 测量值和温度。

    • 例如，如果拥有单独的表格，我们需要谨慎地确保created timestamp 不同且在表格之间保持一致。

缺点

  • 当我们在temperatures 表格中添加数据时，表格的上下文或“模型”会发生变化——这是我们需要在架构发生变化时解决的问题。

    • 例如，表格名称temperatures 已经不再与表格中的数据匹配。

  • 如果数据库架构变得不稳定，例如我们需要经常添加或删除列，那么在单表格中维护它可能会变得难以管理。

网站分析

背景

作为首席工程师，您被分配了创建用于处理公司网站分析的系统的任务。数据被读入报告中，这些报告每天更新一次。

虽然对数据库的读取查询很少，但网站一直在写入分析信息，以监控并确保客户拥有流畅的体验。需求不断变化——利益相关者希望对网站的运行状况以及客户如何使用网站获得新的见解。

方法

在这个场景中需要关注的关键词是“需求不断变化”。我们知道数据库架构会迅速发展，因此我们希望数据库架构具有完全的灵活性。为此，我们将使用窄表格、多表格方法。规范化我们的表格将确保报告没有重复，插入数据速度快，我们可以根据需要轻松添加和删除新的指标。在没有更多关于如何生成报告的信息之前，我们不会向表格添加索引。过早地添加索引会导致表格膨胀，并给数据库操作增加开销。

✨ 了解如何在大型 PostgreSQL 表格中减少表格膨胀.

我们的 SQL 代码如下所示

CREATE TABLE pages (
    id     UUID NOT NULL DEFAULT gen_random_uuid(),
    name   TEXT,
);


CREATE TABLE page_load_speed (
    created   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    time_ms   DOUBLE PRECISION,
    page_id   UUID NOT NULL,
   CONSTRAINT fk_pages FOREIGN KEY(page_id) REFERENCES pages(id)
);


CREATE TABLE time_to_first_paint (
    created   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    time_ms   DOUBLE PRECISION,
    page_id   UUID NOT NULL,
    CONSTRAINT fk_pages FOREIGN KEY(page_id) REFERENCES pages(id)
);

功能蔓延

随着我们分析应用程序的成功，公司已决定开始将其作为服务提供给外部用户。我们需要在表中添加多租户功能来支持多个客户。为此，我们将更新每个分析表以包含一个customer_id 列，该列可以查询特定于客户的分析数据。

更新原始的CREATE TABLE 语句，新模式如下所示

CREATE TABLE customers (
    id    UUID NOT NULL DEFAULT gen_random_uuid(),
    name  TEXT,
);


CREATE TABLE pages (
    id           UUID NOT NULL DEFAULT gen_random_uuid(),
    name         TEXT,
    customer_id  UUID NOT NULL,
    CONSTRAINT fk_customer FOREIGN KEY(customer_id) REFERENCES customers(id)
);


CREATE TABLE page_load_speed (
    created      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    time_ms      DOUBLE PRECISION,
    page_id      UUID NOT NULL,
   customer_id  UUID NOT NULL,
    CONSTRAINT fk_pages FOREIGN KEY(page_id) REFERENCES pages(id),
    CONSTRAINT fk_customer FOREIGN KEY(customer_id) REFERENCES customers(id)
);

CREATE TABLE time_to_first_paint (
    created      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    time_ms      DOUBLE PRECISION,
    page_id      UUID NOT NULL,
    customer_id  UUID NOT NULL,
    CONSTRAINT fk_pages FOREIGN KEY(page_id) REFERENCES pages(id),
   CONSTRAINT fk_customer FOREIGN KEY(customer_id) REFERENCES customers(id)
);

优缺点

为了使我们的模式能够灵活地适应各种分析数据，我们选择使用许多窄表。正如我们在添加多租户功能时所见，许多窄表的一个缺点是在我们进行整体模式更新时。每个表都需要更新。如果我们拥有大型数据库，这会造成维护噩梦。

优点

  • 模式很灵活，我们可以通过创建新表轻松地添加新指标。

  • 数据已规范化，减少了数据集中数据的重复。

缺点

  • 如果我们需要加入许多表以收集指标（例如，笛卡尔积爆炸问题），查询可能会失控。

  • 拥有许多小表会增加系统的操作复杂性。

    • 在我们的示例中，如果page_load_speed、time_to_first_paint 和pages 表每个都有一百万行，则分别更新每个表以包含customer_id 将需要相当长的时间。

下一步

在本文中，我们查看了在设计数据库模式时遇到的两种不同场景和示例。我们还了解了如何应用宽模式与窄模式设计以及单表与多表设计。并非所有数据库模式都是完美的。系统不断发展，我们对数据库模式的方法也需要适应。

了解系统需求并权衡利弊至关重要。通过控制系统需求，我们可以创建适合今天需求并能够处理未来新需求的数据库模式。

要了解更多关于数据建模和设计数据库模式的信息，请继续阅读

如果您想尽快开始设计您的超表数据库模式，确保您获得时间序列数据的最佳性能和用户体验，同时实现惊人的压缩率，请创建一个免费的Timescale 帐户。