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

某些数据访问模式，特别是数据更新模式，可能会加剧数据库的负担。如果可能，应避免这些模式。

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

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

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

• 如果许多进程尝试同时更新同一行，它们会排队等待，因为每个事务都会锁定该行进行写入。这会显著增加事务时间，导致 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，并给 auto-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 - GitLab.com 上的每个用户都有一个，并且这个令牌很少更新。

为了将其从 users 表中提取到新表，我们需要执行以下步骤：

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

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