作者：Emery Mizero 和 Carlota Soto
到目前为止，在本 PostgreSQL 性能调优博客系列中，我们已经介绍了 如何确定数据库大小、 需要优化的关键参数 以及 数据库索引。现在，我们再介绍一个难题——模式设计。
作为 Timescale 的一名支持工程师，我最常被问到的一个问题是如何设计数据库模式：本质上，数据库框架定义了数据如何存储在关系数据库中。优化模式设计是扩展 PostgreSQL 数据库的基础。良好的模式将帮助您实现最佳查询性能，最大程度地减少 I/O 操作，确保后端进程高效利用 CPU 和内存，甚至减少存储需求。
在我们之前的一些文章中，我们已经讨论了 PostgreSQL 表的常见设计选择（窄表与中表与宽表）以及整体数据库设计的注意事项（单表与多表）。我们分享的最重要的收获之一是，要在窄表/中表/宽表或单表/多表之间做出决定，您必须确定对您来说什么是最重要的（例如，易用性、前期设计成本、可扩展性、对多租户的需求），并根据您的优先级选择最具潜力的设计。
今天，我们将继续围绕模式设计主题展开讨论，介绍在扩展 PostgreSQL 时优化查询性能和存储效率的具体实现注意事项。
扩展数据库就像一个活生生的产品：在完成模式设计并将其发送到制造车间之前，您必须就如何收集数据、如何提取数据以及最终下游消费者如何访问数据进行讨论。这将确保您的产品（又称数据库）能够胜任预期工作负载，避免在生产环境中启动并运行后难以解决的故障和意外瓶颈。
让我们直接进入正题。
在 Timescale，我们是 时间序列数据 （或按时间索引的数据）方面的专家，因此我们非常熟悉传统上描述的仅追加工作负载。例如，在时间序列数据的情况下，常见的时间序列类型包括指标、事件、物联网数据和股票价格。每条数据都捕获了某个时间点有关某些有用事物的信息；因此，它不会经常更新。
提取仅追加数据将为您提供最佳性能，尤其是在按时间顺序（或添加到最近创建的分区）添加数据时。 仅追加提取涉及将新记录添加到表末尾，而无需更新或删除现有行。这种模式最大程度地减少了与更新现有数据和维护索引相关的开销：由于未修改现有数据，因此系统的争用和工作量更少。
作为见过许多不同用例的支持工程师，我们建议始终尝试 写入最近的分区（或 Timescale 术语中的块）并使它们适合内存。以这种方式写入数据可确保一致且可预测的部分数据（最近的数据）保持活动状态。
如果您能设计您的模式以适应这种摄取模式，您的插入性能将达到最佳。如果您正在使用分区（正如超表那样），确定正确的分区大小将是实现这一目标的关键。有关分区大小的建议，请查看我们最近关于该主题的博客文章。
如果并非所有数据都按时间顺序摄取（例如，如果您要迁移现有数据集并且需要回填），会发生什么情况？带回填的摄取通常意味着性能下降，具体取决于传入数据涵盖的范围以及这些插入所针对的分区数量。
回填场景中可能出现的问题是内存驱逐：如果您的数据不适合内存或其数据访问模式针对许多不同的分区，您将遇到频繁的内存驱逐，这会导致数据摄取的摄取速度显著下降。
为了缓解回填带来的这个问题，请考虑以下最佳实践
尝试在数据库负载最低的时候回填数据。这可以确保密集的回填过程对正常操作的影响最小。
不要一次插入一条记录，而是将数据批量处理。也就是说，每次插入数百或数千行，这有助于减少事务开销并提高数据摄取的效率。
如果要回填的数据可以并行处理，请考虑使用并行插入进程，针对不同的分区。这有助于更有效地利用系统资源。如果您使用的是 Timescale，则可以通过timescaledb_parallel_copy()来实现。
如果您有对当前操作不重要的索引，请考虑在回填之前删除它们，并在之后重新创建它们。每个索引更新都会减慢数据插入速度，正如我们在本系列的前一篇文章中所述。
考虑在回填过程中临时增加shared_buffers。这可能有助于减少内存驱逐，但请考虑有关
shared_buffers的总体建议（并且记住在完成回填后恢复到默认值）。
您还可以临时调整work_mem。同样，请务必在考虑潜在缺点的同时谨慎操作。
您还可以研究一下预写日志 (WAL)。在对数据进行任何修改期间，PostgreSQL 首先会将这些更改写入 WAL，确保即使在发生崩溃的情况下也可以使用此日志恢复数据库。但是，当您回填大量数据时，默认的 WAL 配置可能不是最佳的，并且会成为瓶颈。您可以考虑增加wal_buffers，为缓冲 WAL 数据分配更多内存，以便在需要刷新之前可以在内存中保存更多事务更改。commit_delay 是另一个可以调整的有趣参数，它允许将更多事务组合在一起。
最后，让我们简要介绍另一种情况：带有更新的摄取。这通常伴随着ON CONFLICT子句，并且需要在每次插入时都必须检查唯一性约束。就像回填一样，时间窗口和数据插入顺序将决定您是对最近的数据进行积极的内存驱逐，还是对已经同步到磁盘的旧数据进行更理想的内存驱逐。
我们一篇永恒的博客文章中的第三条建议 -13 条提高 PostgreSQL 插入性能的技巧- 阐述了为什么在摄取过程中避免数据唯一性检查可以提高性能。
查询模式对 PostgreSQL 中的模式设计有深远的影响。在设计模式时，必须了解数据库将执行的最常见和最关键的操作。我在从非常大的 PostgreSQL 数据库查询数据方面拥有多年经验，并且我一直依赖以下准则
利用高度选择性的索引。正如我们在本系列的前一篇文章中所述，索引是一种有效的方法，可以加快数据库中的数据检索操作。高度选择性的索引是指过滤掉表中大部分行的索引，从而大大减少查询的搜索空间，并允许 PostgreSQL 快速定位和访问相关行。
返回合理的结果集（通常低于 10,000 条记录）。查询返回的数据量不仅会影响检索数据所需的时间，还会影响服务器端和客户端上的带宽、内存和处理资源。通过将结果集限制为合理的数量，您可以确保数据库不会过载。您可以通过使用“LIMIT”子句或通过使用LIMIT 和OFFSET实现分页来将其付诸实践。这可能也值得从您的应用程序层进行优化，例如，通过鼓励用户在运行查询时提供特定的过滤条件（例如，在获取指标时提示他们输入特定的日期范围）。
使查询处理期间的中间数据量尽可能接近最终结果集中的记录数量。每个 SQL 查询在得出最终结果之前都要经过多个处理阶段。如果中间阶段处理大量数据，则会导致 I/O 操作增加、内存使用量增加以及处理时间延长。努力以这样一种方式设计查询：中间数据量与最终结果集的大小紧密一致。这可以通过在查询的早期过滤数据或明智地使用连接和子查询来实现。
在必要时缓存计算出的数据。重复计算数据可能会占用大量资源，尤其是对于复杂的计算或聚合。与其每次查询数据时都执行这些计算，不如考虑缓存计算出的结果。
如果您使用的是 Timescale，则上述准则可以转化为以下非常具体的最佳实践
查询超表以获取最新数据或特定时间窗口。访问最新数据或特定短时间窗口的查询在超表中将表现最佳- 您可以直接查询它们。
针对较大时间窗口或大部分历史数据的分析查询，查询连续聚合。对于需要查看较长时间间隔（例如，月平均值）的查询，请使用连续聚合。它们允许您预先聚合数据，从而更快、更高效地检索历史数据的分析结果。如果需要节省存储空间，它们还允许您删除父超表中的原始原始数据。
利用用户定义的操作进行复杂计算。通过这样做，您可以将复杂计算卸载到数据传入时执行，将它们存储在单独的缓存或结果表中，以便可以一次在较小的数据集中完成。这些计算可能涉及复杂的连接、数学运算，甚至是机器学习预测。 存储计算结果后，您的应用程序可以查询缓存表以获得快速响应。
如果不提及分区，我们就无法为大型 PostgreSQL 表的模式设计提供建议。
自成立以来，Timescale 一直致力于解决开发人员在处理时间序列数据时往往会遇到的挑战，包括如何扩展 PostgreSQL 以处理海量数据的摄取和保留。 数据库分区是 PostgreSQL 用户解决变得迟钝的超大表问题的常用方法。本文深入探讨了分区的主题以及何时应该考虑使用分区。
在如何为大型 PostgreSQL 表实施分区而不使您的生活复杂化方面，超表是答案。当您创建超表（看起来就像一个普通的 PostgreSQL 表）时，分区将在您插入数据时自动创建，并在您设置保留策略后的一段时间后透明地删除。
超表可以帮助您显著提高摄取和查询性能（特别是当您处理大型表和/或高摄取率时），并且还可以为您节省大量数据管理时间。
在 PostgreSQL 中，由于对齐填充，列顺序会影响存储空间的有效使用，而对齐填充由列类型的大小决定。例如，一个八字节的 bigint 类型将从地址为 8 的倍数的位置开始存储。同样，一个四字节的 integer 类型将从地址为 4 的倍数的位置开始存储。这种模式与两字节的 smallint 和一字节的 char 类型一致。
考虑这样一种情况：一个 smallint （两字节）紧挨着一个 bigint （八字节）放置，从地址 0 开始。 smallint 占用地址 0 和 1。然而，后面的 bigint 不会从地址 2 开始，因为它不是 8 字节对齐的。相反，它从地址 8 开始，跨越到地址 15，导致地址 2 到 7 未被使用。这些空闲的地址本可以容纳像 integer、 smallint 或 char 这样的类型。
为了优化存储并最大程度地减少由于对齐填充造成的空间浪费，建议在表定义中按数据类型从大到小的顺序排列列。
在下面的示例中，“bad_column_order”超级表每行数据使用额外的 8 个字节。与“good_column_order”超级表相比，它可能会节省大约 14% 的空间。
create table bad_column_order (
id bigint not null,
val_a smallint not null,
val_b integer not null,
val_c smallint not null,
time timestamp with time zone not null
);
create index bad_column_order_time_idx on bad_column_order(time desc);
select create_hypertable('bad_column_order', 'time', chunk_time_interval => INTERVAL '1 day');insert into bad_column_order (id, val_a, val_b, val_c, time)
values (1, 15, 123456789, 50, now());
select pg_column_size(bad_column_order.*) from bad_column_order
pg_column_size
----------------
56create table good_column_order (
id bigint not null,
time timestamp with time zone not null,
val_b integer not null,
val_a smallint not null,
val_c smallint not null
);create index good_column_order_time_idx on good_column_order(time desc);
select create_hypertable('good_column_order', 'time', chunk_time_interval => INTERVAL '1 day');insert into good_column_order (id, val_a, val_b, val_c, time)
values (1, 15, 123456789, 50, now());
select pg_column_size(good_column_order.*) from good_column_order;
pg_column_size
----------------
48单个表通常足以处理多租户场景；我们经常建议在应用程序级别处理数据分离。但是，可能存在操作要求和法规，可能要求将这种数据分离推送到数据库对象（甚至完全不同的数据库），从而将数据分离到许多表中。
有关单表与多表架构设计选择的注意事项，请查看我们之前关于该主题的文章。如果您选择使用多个表或超级表，请考虑以下因素
小心真空
如果您对多个表或超级表进行了大量更新/删除操作，那么您可能需要注意优化您的 vacuum 策略。对数百个（如果不是数千个）超级表进行真空通常会导致持续的 CPU 利用率增加，这可能会严重损害您的整体数据库性能。 您不希望发生这种情况。
这个主题值得单独写一篇文章，敬请期待。同时，我们希望这有所帮助：您可以 构建一个 BPFtrace 程序来监控您的 PostgreSQL 真空操作。
不要耗尽后台工作进程
如果您是 Timescale 用户，并且利用了 Timescale 提供的大部分功能，那么每个超级表都将伴随许多连续聚合和保留策略。运行这些进程中的每一个都会消耗一个已分配的工作进程，这些进程在“timescaledb.max_background_workers”下配置，而“timescaledb.max_background_workers”又是“max_worker_processes”的子集（我们在本系列的第二部分中介绍过的一个参数）。
如果您有许多超级表，因此也有更多策略/作业，则多个策略同时运行的可能性会增加，这可能会耗尽您的后台工作进程并导致某些策略失败。为了解决这个问题，我们在 TimescaleDB 2.9.0 中发布了一个功能，允许您创建具有固定计划的策略，并将它们错开足够的时间，以确保同时运行的作业数量永远不会超过 timescaledb.max_background_workers 配置值。
也就是说，您可能会受到运行作业所需时间和作业计划间隔的限制。例如，假设您有多个策略，其中每个策略需要 5 秒钟才能完成，并且计划每分钟运行一次。如果您在任何给定时间只能消耗两个后台工作进程或两个正在运行的作业，那么您最多只能定义 24 个策略。作业的完成时间可能会有所不同，但直接的交错实现将基于每个作业的预期最长完成时间。
要继续阅读有关如何配置 timescaledb.max_background_workers 与 max_parallel_workers 和 max_worker_processes 的关系，请 查看我们关于该主题的文档 和 我们之前的文章。
在您可以采取的所有措施中，充分设计数据库架构是提高 PostgreSQL 性能最有效的方法之一，但也是最令人怀疑的方法之一。作为一名支持工程师，我希望我已经阐明了您应该遵循的最佳实践，包括如果您是 Timescale 用户，如何利用 Timescale 功能的技巧。
如果您有任何问题，并且碰巧是 Timescale 用户，请 记住，您随时可以向我们寻求建议。请发送电子邮件至 [email protected]，包括如果您 是试用用户。