数据布局和访问模式的最佳实践

某些数据访问模式，尤其是数据更新，会加剧数据库的压力。如果可能，请避免使用这些模式。

本文列出了一些应避免的模式，并提供了替代方案的建议。

高频更新，尤其是对同一行的更新#

避免单个数据库行同时被多个事务更新。

• 如果多个进程同时尝试更新同一行，它们会排队，因为每个事务都会锁定该行进行写入。这可能会显著增加事务时间，导致 Rails 连接池饱和，进而导致整个应用程序停机。
• 对于每次行更新，PostgreSQL 都会插入一个新的行版本并删除旧版本。在高负载场景下，这种方法可能会导致 vacuum 和 WAL（预写日志）压力，降低数据库性能。

当为每个请求计算聚合的成本过高时，通常会出现这种模式，因此会在数据库中保留一个运行计数。如果需要这样的聚合，请考虑在单个行中保留一个运行总计，以及一个小型的最近添加数据的工作集（例如各个增量）：

• 引入新数据时，将其添加到工作集。这些插入不会导致锁争用。
• 计算聚合时，将运行总计与工作集中的实时聚合相结合，从而提供最新结果。
• 添加一个定期任务，将工作集合并到运行总计中，并在事务中清除它，从而限制读取器所需的工作量。

宽表#

PostgreSQL 将行组织到 8 KB 的页中，并一次操作一个页。通过最小化表中行的宽度，我们可以改善以下方面：

• 顺序扫描和位图索引扫描性能，因为如果每页包含更多行，则需要扫描的页就更少。
• vacuum 性能，因为 vacuum 可以处理每页中的更多行。
• 更新性能，因为在（非 HOT）更新期间，每次行更新都必须更新每个索引。

缓解宽表问题是数据库团队 100 GB 表计划 的一部分，因为更宽的表在 100 GB 中可以容纳的行数更少。

向表中添加列时，请考虑是否打算单独访问新列中的数据，并与该表的其他列保持一对一关系。如果是这样，这些新列可能是拆分到新表的良好候选。

有几张表已经以这种方式拆分。例如：

• search_data 从 issues 中拆分出来。
• project_pages_metadata 从 projects 中拆分出来。
• merge_request_diff_details 从 merge_request_diffs 中拆分出来。

数据模型权衡#

某些表，如 users、namespaces 和 projects，可能会变得非常宽。这些表通常是应用程序的核心，并且使用非常频繁。

为什么这是一个问题？

• 这些列中的许多列都包含在索引中，这会导致索引写入放大。当表上的索引数量超过 16 个时，会影响查询计划，并可能导致 轻量级锁（LWLock）争用。
• PostgreSQL 中的更新是通过删除和插入的组合来实现的。这意味着每一列，即使很少使用，在每次更新时都会被反复复制。这会影响生成的预写日志（WAL）量。
• 当存在频繁更新的列时，每次更新都会导致所有表列被复制。同样，这会导致生成的 WAL 增加，并为自动 vacuum 带来更多工作。
• PostgreSQL 将数据存储为页中的行或元组。宽行会减少每页的元组数量，从而影响读取性能。

解决这个问题的一个可能方案是只在主表上保留最重要的列，将其余列提取到不同的表中，并与主表保持一对一关系。良好的候选列是更新非常频繁的列（例如 last_activity_at），或者是很少更新和/或使用的列（如激活令牌）。

这种提取带来的权衡是，索引唯一扫描不再可能。取而代之的是，应用程序必须要么连接新表，要么执行额外的查询。应该权衡其性能影响与垂直表拆分的好处。

PostgresFM 播客中有一集非常好的关于这个主题的节目，其中 PostgresAI 的 @NikolayS 和 PgMustard 的 @michristofides 更深入地讨论了这个话题 - https://postgres.fm/episodes/data-model-trade-offs。

示例#

让我们看看 users 表，在撰写本文时它有 75 列。我们可以看到有几组列符合上述标准，并且是提取的良好候选：

• OTP 相关列，如 encrypted_otp_secret、otp_secret_expires_at 等。这些列很少，而且一旦填充后，不应该经常更新（如果更新的话）。
• 与电子邮件确认相关的列 - confirmation_token、confirmation_sent_at 和 confirmed_at。一旦填充，这些列很可能永远不会更新。
• 时间戳，如 password_expires_at、last_credential_check_at 和 admin_email_unsubscribed_at。此类列要么更新非常频繁，要么根本不更新。将它们放在单独的表中会更好。
• 各种令牌（及与之相关的列），如 unlock_token、incoming_email_token 和 feed_token。

让我们关注 users.incoming_email_token —— JihuLab.com 上的每个用户都有一个设置，并且此令牌很少更新。

为了将其从 users 提取到新表中，我们必须执行以下操作：

1. 发布 M 示例
   • 创建表（发布 M）
   • 更新应用程序以从新表读取，并在尚无数据时回退到原始列。
   • 开始回填新表
2. 发布 N 示例
   • 完成执行回填的后台迁移。这应该在 必需停止 之后的下一个发布中完成。
3. 发布 N + 1 示例
   • 更新应用程序以仅从新表读取和写入。
   • 忽略原始列。这启动了安全删除数据库列的过程，如我们的 指南 中所述。
4. 发布 N + 2 示例
   • 删除原始列。
5. 发布 N + 3 示例
   • 移除针对原始列的忽略规则。

虽然这是一个漫长的过程，但为了在不中断应用程序的情况下进行提取，这是必要的。完成后，原始列和相关索引将不再存在于 users 表中，这将提高性能。