作者：Chris Engelbert 和 Carlota Soto
在设置 PostgreSQL 数据库时，甚至在开始提取数据之前，您就会面临一些关于模式设计的重要设计决策。确定数据库模式类似于艺术家在开始绘画之前选择画布：画布的形状，或者在本例中为表格布局，将极大地影响数据库的成型方式，影响性能、可用性和可维护性。
PostgreSQL 数据库有两种主要的表设计选项：窄表和宽表。在本博文中，我们将介绍这两种方法，并解释为何以及如何使用它们，同时还介绍了第三种方法（中等布局），作为传统宽表和窄表设计之间的折衷方案。
让我们先描述一下数据库模式中窄表和宽表之间的区别，然后讨论每种方法的优缺点。
在模式设计中，窄表的特点是数据值列很少，这意味着对于表中的每一行，存储的信息类型并不多。例如，窄表可能只存储时间戳和单个测量值，例如温度。
但是，必须了解的是，“数据值列少”并不等同于“列总数少”。窄表仍然可以包含多个用于其他目的的列。例如，可以存在用于对每一行进行分类或标记的列，将其与提供数据的特定设备、正在测量的指标相关联，或将其分配给特定的时序。因此，虽然窄表中的核心数据可能很简单，但该表仍然可以进行结构化，以便为每个数据点提供上下文或分类。
以下是窄表的示例
CREATE TABLE narrow_table (
created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
point_id UUID NOT NULL DEFAULT gen_random_uuid(),
device_id UUID NOT NULL,
metric TEXT NOT NULL,
value DOUBLE PRECISION NOT NULL
);-- Example insert statement for the given data:
INSERT INTO narrow_table (created, point_id, device_id, metric, value)
VALUES ('2022-01-01 00:00:00.0+00', '123', '10', 'temp', 24);如您所见，该行只有一个用于保存值的列：在本例中为十进制值。此示例还使用一列表示指标名称（在本例中为“temp”，表示温度）。它也可以是任何存储为十进制值的内容。
如果要使用其他数据类型值，则可以创建另一个表来存储文本字符串或任何其他类型。或者，您经常会看到 JSONB 作为值列的数据类型。这样，您就可以在该列中存储任何有效的 JSON 值。
CREATE TABLE narrow_table (
created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
point_id UUID NOT NULL DEFAULT gen_random_uuid(),
device_id UUID NOT NULL,
metric TEXT NOT NULL,
value JSONB NOT NULL
);宽表布局与窄表布局完全相反，这意味着您最终会得到很多列。通常，每个可用指标对应一列。根据您拥有的指标数量，宽表可能会（顾名思义）变得非常宽，速度非常快。在一个表中看到 200 个或更多列是很常见的。
一个简单的宽表示例可能如下所示
CREATE TABLE measurement_table (
created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
point_id UUID NOT NULL DEFAULT gen_random_uuid(),
device_id UUID NOT NULL,
temp DOUBLE PRECISION,
hum DOUBLE PRECISION,
co2 INTEGER,
wind_speed INTEGER,
wind_direction INTEGER
);
-- Example insert statement for the given data:
INSERT INTO measurement_table (created, point_id, device_id, temp, hum, co2, wind_speed, wind_direction)
VALUES ('2022-01-01 00:00:00.0+00', '123', '10', 24.7, 57.1, 271, NULL, NULL);
根据您的用例，值列可以为空，也可以不为空。这主要取决于数据的交付方式。例如，如果数据是同时提供的，并且可以存储在一行中，或者如果数据是在不同的时间点提供的，并且每次只使用一个或多个列。
在上面的示例中，温度、湿度和 CO2 这三个值一起提供，而风速和风向将在单独的数据包中，因此值为 NULL。
在设计数据库模式时，如果您有少量的指标并且您预先知道所有指标及其数据类型，则窄表布局非常有用。如果您不知道这一点，您可以删除作为列的指标名称，并为每种指标类型创建一个表，以某种方式将指标名称添加到表名中，如下所示
Table: temperature
| point_id | device_id | value |
| 123 | 10 | 24.7 |
Table: humidity
| point_id | device_id | value |
| 123 | 10 | 57.1 |
此示例包含用于温度和湿度指标的独立表。这样，您就可以独立考虑每个指标的数据类型，并且表设计变得更加容易。
使用其他指标扩展布局很容易：创建与指标的数据类型匹配的附加超表，就完成了。
然而，之前的优势也可能变成劣势。如果您有大量不同的指标，则管理单个表的数量可能会变得很麻烦。虽然 PostgreSQL 对许多表没有问题，但人类通常会有。
如果您想进一步拆分这些表，例如，每个客户有一个温度表（也称为多租户），则可能会增加额外的复杂性。您可以通过修改名称（<<customer_id>>_temperature）或使用查找表来处理此问题，该查找表将客户和指标映射到生成的表名。不过，这可能会变得很乏味。
降低复杂性的另一种方法是使用 JSONB 作为值列的数据类型。这允许您将每个客户的所有指标统一到一个表中，添加回指标列。请记住，您仍然需要了解查询时该值的将是什么数据类型，因为 JSONB 可能会强制您将结果值转换为您的应用程序期望的数据类型（例如，JSON 不使用真正的整数，而只使用IEEE 754 标准中的数字）。
最后但并非最不重要的一点是，同时查询许多不同的指标可能意味着您需要使用大量的JOIN 或UNIONs，特别是当每个指标都有自己的表时。虽然这不一定会降低查询速度，但编写或生成查询可能会很乏味。此外，在准备结果时，每个 JOIN 操作都会增加系统的输入/输出 (I/O) 负载。当指标的数量不断增加时，所有这些都变得更有可能发生。
如果您预先知道所有潜在的指标，则宽表是您进行模式设计的绝佳选择。对于已经投入生产很长时间并且数据将被迁移的系统来说，情况通常如此。如果只需要以较低的速率添加新列，则这可能是一个可行的选择，但添加列可能是一项繁重的操作。
除此之外，支持多租户也很简单，只需为每个客户添加一个新的宽表即可。（不过请记住，在添加新列时，您可能还必须将其添加到所有客户的表中，这可能是一个乏味的过程。）
查询数据很简单：我们不必转换数据，因为它们已经以其本机数据类型存储，并且不需要查找要查询的列，因为每个指标都有一列。此外，查询列的子集非常有效，尤其是在将块压缩为列式表示形式时。
从多个客户查询数据很快。只需将不同的中间查询 JOIN 和 UNION 在一起，就完成了。
虽然宽表布局是初学者的常见选择，但它可能是随着时间的推移最难管理和扩展的。
宽表最大的问题是添加更多指标列。在不断增长的新指标的系统中跟踪事物可能具有挑战性。
此外，虽然删除不再需要的指标列是一项立即操作（该列将仅对查询引擎隐藏），但它不会立即从磁盘中删除内容。
每当更新该行时，这些行都将被重写，这在时间序列数据中很可能永远不会发生，从而使悬空数据永远保留。添加 NULLABLE 列很简单，与 DEFAULT 值相同。未更新的行将在查询时进行评估。
窄表布局的优点
适用于已知指标和数据类型数量较少的情况。
允许为每个指标单独考虑数据类型。
通过为每个指标使用独立的表来简化表设计。
通过添加新表即可轻松扩展新指标。
窄表布局的缺点
管理大量用于不同指标的表可能具有挑战性。
多租户（例如，每个客户一个表）增加了复杂性。
使用 JSONB 需要转换值，从而影响数据类型清晰度。
查询多个指标涉及多个 JOIN 或 UNION，从而使查询编写复杂化。
由于 JOIN 操作，这意味着 I/O 负载增加，尤其是在指标数量不断增加的情况下。
宽表布局的优点
在预先知道所有潜在指标的情况下效果很好。
通过为每个客户使用新表来简化多租户。
本机数据类型无需在查询期间进行转换。
高效查询列子集，尤其是在使用列式压缩的情况下。
使用 JOIN 和 UNION 可以在多个客户之间进行更快的查询。
宽表布局的缺点
随着时间的推移，管理和扩展具有挑战性，尤其是对于初学者而言。
在动态系统中添加新的指标列可能很困难。
删除列不会立即释放磁盘空间，从而留下未使用的数据。
在查询时使用 DEFAULT 值评估未更新的行，从而影响性能。
在设计数据库模式时，窄模型和宽模型之间存在一种折衷方案：中等表布局。
基本设计原则是为每种需要的数据类型创建一个列：
CREATE TABLE medium_table (
created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
point_id UUID NOT NULL DEFAULT gen_random_uuid(),
device_id UUID NOT NULL,
metric TEXT NOT NULL,
int_value INTEGER,
float_value DOUBLE PRECISION
);
-- Example insert statement for the given data:
INSERT INTO medium_table (point_id, device_id, metric, int_value, float_value)
VALUES ('123', '10', 'temp', NULL, 2);
窄表通常只有一列或很少的几列表示数据值（例如，指标和值）。
宽表有多列，每列代表一个不同的指标或数据点。如果我们在前面的表中有用于温度、湿度、压力等的单独列，那就是一个宽表布局。
上面的中等表位于窄布局和宽布局之间。它具有表示不同数据类型或指标类别的列，但保留了一定程度的通用结构。在提供的表中，int_value 和 float_value 之间存在分隔，但存储的实际指标（例如，温度、湿度）仍由 metric 列标识。这比窄表提供了更大的灵活性，但不像宽表那样为每个指标使用不同的列。
如果需要更多数据类型，只需添加更多列即可。如果您知道所有预期的数据类型，那么这是一个非常方便的设置，与“预先知道所有可能的指标”相比，这相当容易。但是，您必须知道哪种指标类型将在哪个特定数据类型列中找到。
中等表布局具有一行，其中每种需要的数据类型都有一列。这意味着您只需要一列来定义存储在该特定行中的数据类型。这可能需要一个额外的查找表，不仅定义指标，还定义需要为每个指标查询哪个数据类型列。对于数量少且静态的指标，您可以在应用程序层处理此问题。
只要我们不想引入新的数据类型，此布局就提供了窄设计的绝大多数优点，可以轻松地使用其他指标进行扩展。但即使这样，您也只需要在表布局中添加一个新列即可。这取决于超表中现有行的数量，以及这样的操作有多繁重。
如果您确定将来可能出现的不同数据类型，但还不确定所有潜在的指标，则中等布局是一种非常实用的设计选择。这在物联网用例中很常见，在这些用例中，您会经常添加新型指标。虽然指标的数量会增加，并且您必须将它们添加到查找机制（指标到数据类型列）中，但表的结构不会改变，您可以继续添加新指标而无需修改布局。
通过为每个客户创建一个超表并将所有相应数据存储在该特定表中，可以轻松实现多租户。如果您需要查询多个客户的数据，可以将所有数据 JOIN 和 UNION ALL 在一起，并获得单个结果集。
同时查询多个指标也很简单，因为所有数据都驻留在同一个表中。但是，如果您想从多个不同的数据类型中检索数据，您最终可能会使用CASE WHEN 子句，并在特定指标没有可用值但其他查询的指标有可用值时用 NULL 填充结果列。这需要在应用程序层中处理。
在做出决定时，有很多因素需要考虑。虽然可以从一种布局迁移到另一种布局，但该过程可能非常乏味，尤其是在存储的记录数量不断增加的情况下。下表快速概述了每种选择的优缺点：
窄表布局 | 中等表布局 | 宽表布局 | |
|---|---|---|---|
易用性 | 简单 | 简单 | 比较简单 |
前期设计成本 | 低 | 比较低 | 比较高 |
可扩展性 | 简单 | 比较简单 | 比较复杂 |
多租户 | 比较复杂 | 简单 | 简单 |
让我们分解一下。
如果适用以下情况，请考虑在您的数据库模式中使用窄表
您的指标基数较低。当您预先了解指标及其数据类型时，窄表会很有用。它更易于管理并提供清晰的布局。
您的指标在不断发展。如果您不确定可能处理的指标范围，您可能会喜欢为每种指标类型创建单独的表的灵活性。
您重视可扩展性。随着系统的发展和对新指标的需求，您可以引入专为该指标数据类型量身定制的新表。
但是，如果出现以下情况，请谨慎：
您预计指标会激增。管理许多表可能会很麻烦。
您需要复杂的多租户。为每个指标-客户组合创建一个唯一的表可能会变得很繁琐。
您将经常跨多个指标进行查询。创建涉及多个指标的查询可能需要多个 JOIN 或 UNION，这可能会使您的 SQL 变得复杂并给系统带来更大压力。
如果出现以下情况，请考虑使用宽表：
您的指标已确定。如果您清楚地了解您将需要的所有指标，则宽表可以提供结构化且可预测的布局。
您需要简化的多租户。通过每个客户一张表，处理跨多个租户的数据变得简单明了。
您的查询是针对特定列的。由于每个指标都有自己的列，因此查询是直接且高效的。
与之前类似，如果出现以下情况，请谨慎：
您预计新指标会经常出现。不断添加列可能具有挑战性，尤其是在不断增长的系统中。
您的系统将经历频繁的变化。定期向所有客户表添加列可能会成为维护难题。
最后，请考虑在以下情况下使用中等表：
您想要一种平衡的方法。如果您正在寻找介于窄表的粒度和宽表的结构之间的东西，那么中等表是一个不错的选择。
您确定数据类型。如果您对数据类型有清晰的了解，但不确定指标，则中等表可以提供灵活性。
您优先考虑简单的多租户。
让我们快速了解一下 Timescale 的压缩 如何与不同的表布局配合使用。压缩是 Timescale 的基本功能之一，可以将数据库大小减少多达 10 倍。
附： 如果您不知道 Timescale 是什么，请查看这篇文章。 它通过向大型表添加自动分区、查询计划改进以及许多其他功能来提高 PostgreSQL 的速度。
一般来说，使用 Timescale 压缩时，完全支持所有布局。
窄表布局易于管理：由于在以后的某个时间点不应期望任何列（假设您很早就确定了是要使用指标列还是表名设置），因此压缩方面无需特殊考虑。您还可以预期非常好的压缩率，因为记录非常均匀，因此压缩效果非常好。
借助 Timescale 压缩的 DML 功能，中等和宽表布局也可以很好地与压缩配合使用。如果您必须添加新列，可以直接添加它们。
最后要注意的是，包含 NULL 的列压缩效果非常好：许多人经常担心的是，中等或宽布局中的 NULL 值数量会在超表中造成大量不必要的膨胀。但由于压缩块的设计方式，NULL 值几乎可以压缩到零。
您的用例特征和您自己的优先级将决定您最终为架构设计选择的表布局。没有“一刀切”的布局。查看您的特定用例，并确定不同布局方面的优缺点。
也许您有不同的用例，使用不同的布局会有不同的好处。例如，您可能希望以不同于基础设施可观察性数据的方式存储来自传感器的物联网指标。
虽然在实际应用中，中等表布局可能是最不常见的一种，但它通常是在易于理解的设计、未来可扩展性和快速启动之间取得的良好折 compromises。需要一些查找机制来了解哪些指标将在哪些数据类型列中可用。
如果您知道要存储的确切指标，则宽表布局可能是一个不错的选择。它提供了快速设置、出色的查询速度（尤其是在启用压缩的情况下）和常见查询，基本上类似于典型的关系数据库查询。最后，不需要查找表。
如果您想要最直接的设计选择和不同类型指标的最高隔离度，则窄表布局是您的选择。鉴于表的布局预计不会发生变化，因此它是最容易设计的，因为您可能确切地知道特定指标（或一组共享相同数据类型的指标）的数据类型是什么样子的。对于高基数指标，可能需要一种查找机制来了解要查询哪个表（尤其是在名称修改的情况下）。
无论您做什么，请记住一件事：可以从一种布局切换到另一种布局，但迁移并不简单。如果您不确定未来，请考虑其中一种更简单的可扩展布局选项。