用于审计日志事件表的 PostgreSQL 分区

为什么事件表和审计表会成为问题

事件表和审计表看起来相似，但存在目的不同。

事件表记录发生的事情：页面浏览、发送的邮件、Webhook 调用、作业运行。审计表记录谁在何时更改了什么：状态变更、权限更新、支付批准，通常带有“变更前/变更后”的细节。

两者都会快速增长，因为它们是追加写入的。你很少删除单行，且新行每分钟都会到来。一旦包含后台作业和集成，即便是一个小产品也能在几周内产生数百万条日志行。

痛点会在日常工作中显现。管理面板需要快速的筛选，如“昨天的错误”或“某用户的操作”。随着表的增长，这些基本界面会开始变慢。

你通常会先注意到一些症状：

• 即使是窄的时间范围，筛选也需要几秒钟（或超时）。
• 索引变得非常大，插入变慢且存储成本上升。
• VACUUM 和 autovacuum 需要更长时间，维护工作明显增多。
• 保留变得有风险：删除旧行很慢并导致膨胀（bloat）。

分区是解决这些问题的一种方法。通俗地说，它把一个大表拆成许多更小的表（分区），这些分区共享一个逻辑表名。PostgreSQL 根据规则（通常是时间）把每一行路由到正确的分区。

这就是为什么团队会考虑对事件表做 PostgreSQL 分区：它能把近期数据保存在更小的块中，当查询只需要一个时间窗口时，PostgreSQL 可以跳过整个不相关的分区。

分区不是魔法开关。它对“最近 7 天”之类的查询帮助很大，并且让保留更简单（丢弃旧分区很快）。但它也可能带来新问题：

• 不使用分区键的查询可能不得不检查很多分区。
• 更多分区意味着更多对象需要管理，也更容易配置错误。
• 一些唯一约束和索引在跨分区强制时变得更困难。

如果你的管理面板严重依赖日期筛选和可预测的保留规则，分区可能是真正的好选择。如果大多数查询是“查找用户 X 的所有历史操作”，除非你小心设计 UI 和索引，否则它可能带来麻烦。

日志和审计的典型访问模式

事件和审计表按一个方向增长：向上（追加）。它们有稳定的写入流，几乎没有更新。大多数行写入一次，之后在支持、事件回顾或合规检查时被读取。

这种“追加写入”形态很重要。写性能是持续关注点，因为插入全天发生，而读性能则在突发时段变得重要（当支持或运维需要快速响应时）。

大多数读取是筛选，而非随机查找。在管理面板中，用户通常先做一个广泛的筛选（最近 24 小时），然后再缩小到某个用户、实体或操作。

常见筛选包括：

• 时间范围
• 执行者（用户 ID、服务账号、IP 地址）
• 目标（实体类型 + 实体 ID，例如订单 #1234）
• 操作类型（创建、更新、删除、登录失败）
• 状态或严重性（成功/错误）

时间范围是自然的“第一步”筛选，因为它几乎总是存在。这就是对事件表进行 PostgreSQL 分区的关键洞见：许多查询需要一个时间切片，其它条件是在该切片内的次级筛选。

保留是另一个常量。日志很少永久保存。团队通常会把高详情事件保留 30 或 90 天，然后删除或归档。审计日志的要求可能更长（365 天或更久），但即便如此，你通常也希望有一种可预测的方法来移除旧数据而不会阻塞数据库。

审计记录还有额外期望。通常希望历史是不可变的、每条记录可追踪（谁/什么/何时 加上请求或会话上下文），并且访问受到控制（并非所有人都应看到安全相关事件）。

这些模式直接体现在 UI 设计中。用户默认期望的筛选——日期选择器、用户选择、实体搜索、操作下拉——正是如果你希望管理体验在数据量增长时保持快速，表和索引需要支持的筛选。

如何判断是否值得分区

分区并不是审计日志的默认最佳实践。当一个表变得足够大以致于日常查询和常规维护互相影响时，它才有价值。

一个简单的规模提示：当事件表达到数千万行时，值得开始度量。当表和其索引增长到几十 GB 时，即使是“简单”的时间范围搜索也可能变慢或变得不可预测，因为需要从磁盘读取更多数据页，索引维护也变得昂贵。

最明确的查询信号是：你经常请求一个小的时间切片（最近一天、最近一周），但 PostgreSQL 仍然触及表的大部分。你会看到“最近活动”界面变慢，或按日期加用户、操作类型或实体 ID 筛选的审计变慢。如果查询计划显示大量扫描或缓冲区读取持续偏高，你就在为不必要的数据付出代价。

维护信号同样重要：

• VACUUM 和 autovacuum 比以前慢得多。
• Autovacuum 跟不上，死元组（bloat）堆积。
• 索引增长速度比预期快，尤其是多列索引。
• 当维护与正常流量重叠时，锁争用明显增加。

运营成本是推动团队走向分区的慢性因素。备份和恢复随着某个大表的增长变慢、存储费用上升、保留任务变得昂贵，因为大规模 DELETE 会产生膨胀并增加额外的 vacuum 工作。

如果你的主要目标是实现清晰的保留策略和加速“近期时间段”查询，分区通常值得认真考虑。如果表规模适中且在良好索引下查询已经足够快，分区会增加复杂性而无法带来明显回报。

适合事件和审计表的分区选项

对于大多数审计和事件数据，最简单的选择是按时间范围分区。日志按时间顺序到达，查询常常关注“最近 24 小时”或“最近 30 天”，且保留通常基于时间。使用时间分区时，删除旧数据可以像移除旧分区一样简单，而不是运行会导致膨胀的大量 DELETE。

时间范围分区也使索引更小、更聚焦。每个分区有自己的索引，因此查询最近一周的数据不需要遍历覆盖多年历史的巨大索引。

其他分区方式存在，但适用的日志和审计场景较少：

• 列表分区（按租户或客户）在你有少数非常大的租户且查询通常局限于单个租户时有效。当有数百或数千租户时会变得痛苦。
• 哈希分区（均匀写分布）在你没有时间窗口查询且希望写入均匀分布时有用。对于审计日志不太常见，因为它让保留和基于时间的浏览更困难。
• 子分区（时间加租户）可以很强大，但复杂度迅速增加。主要适用于具有严格租户隔离需求的超高流量系统。

如果选择时间范围，请选一个与浏览和保留策略匹配的分区大小。对于写入非常高或保留严格的表，按天分区是合理的。中等流量下按月分区更易管理。

一个实用示例：如果管理团队每天早上检查失败登录并按最近 7 天筛选，按天或按周分区意味着查询只需访问最近的几个分区，PostgreSQL 可以忽略其余部分。

无论选择哪种方法，都要为那些乏味但重要的事情做计划：创建未来分区、处理晚到事件、以及在每个边界（天末、月末）如何处理。分区在这些例行工作保持简单时才会带来价值。

如何选择正确的分区键

一个好的分区键应当与读取表的方式匹配，而不是图表上的数据样子。

对于事件和审计日志，从你的管理面板开始：人们首先使用什么筛选，几乎每次都用？对大多数团队来说是时间范围（最近 24 小时、最近 7 天、自定义日期）。如果情况属实，基于时间的分区通常带来最大且最可预测的收益，因为 PostgreSQL 可以跳过分区之外的所有数据。

把分区键当作长期承诺。你是在为未来多年的常驻查询进行优化。

从人们用的“第一个筛选”开始

大多数管理界面遵循一个模式：时间范围加可选的用户、操作、状态或资源。按能在早期并持续缩小结果的字段分区。

一个快速现实检查：

• 如果默认视图是“近期事件”，按时间戳分区。
• 如果默认视图是“某租户/账户的事件”，tenant_id 可能有意义，但前提是租户足够大值得这样做。
• 如果第一步总是“选择一个用户”，按 user_id 分区听上去诱人，但通常会产生太多分区而难以管理。

避免高基数键

分区在每个分区都是有意义的数据块时效果最好。像 user_id、session_id、request_id 或 device_id 这类键会导致成千上万的分区，增加元数据开销、复杂化维护，通常会减慢查询规划。

基于时间的分区能保持分区数量可预测。你可以根据量选择按日、按周或按月。分区太少（每年一个）帮助不大；太多（每小时一个）会迅速增加开销。

选择正确的时间戳：created_at 还是 occurred_at

明确时间字段代表什么：

• occurred_at：事件在产品中真实发生的时间。
• created_at：数据库记录该事件的时间。

对于审计，管理员通常关心“发生时间”。但延迟到达（离线移动客户端、重试、队列）意味着 occurred_at 可能会晚到。如果经常有延迟到达，按 created_at 分区并为 occurred_at 建索引以用于筛选，操作上会更稳定。另一种选择是定义清晰的回填策略，接受偶尔向旧分区写入晚到事件。

还要决定如何存储时间。使用一致的类型（通常是 timestamptz）并把 UTC 作为事实标准。在 UI 中再根据查看者的时区格式化显示。这能保持分区边界稳定并避免夏令时问题。

逐步：规划并推出分区

把分区当作一个小的迁移项目而非快速调整，这样会更容易。目标是写入简单、读取可预测并且保留成为例行操作。

实用的落地计划

1. 选择与流量匹配的分区大小。 月度分区在每月数十万行时通常足够。如果你每月插入数千万行，按周或按日分区能保持索引更小并把 vacuum 工作限制在更小范围内。

2. 为分区表设计键和约束。 在 PostgreSQL 中，唯一约束必须包含分区键（或用其他方式强制）。常见模式是 (created_at, id)，其中 id 是生成的，created_at 是分区键。这能避免后来发现一些期望的约束无法生效的惊讶。

3. 提前创建未来分区。 不要等到插入失败才去创建分区。决定提前创建的时间范围（例如提前 2–3 个月）并把它做成例行任务。

4. 保持每个分区的索引精简且有意。 分区并不使索引免费。大多数事件表需要分区键加一到两个能匹配真实管理筛选的索引，例如 actor_id、entity_id 或 event_type。跳过“以防万一”的索引。你可以在新分区上添加索引并在需要时回填旧分区。

5. 围绕丢弃分区来规划保留，而不是删除行。 如果你保留 180 天日志，丢弃旧分区既快又能避免长时间删除和膨胀。把保留规则写下来，说明谁来执行以及如何验证它生效。

小示例

如果审计表每周产生 500 万行，那么以 created_at 做周分区是一个合理的起点。提前创建 8 周的分区并在每个分区保留两个索引：一个用于常见的 actor_id 查询，另一个用于 entity_id。当保留期结束时，丢弃最旧的周分区，而不是删除数百万行。

如果你在使用 AppMaster 构建内部工具，提前决定分区键和约束很有帮助，这样数据模型和生成的代码会从一开始就遵循相同的假设。

分区对管理面板筛选的影响

一旦你对日志表分区，管理面板筛选不再只是 UI 的事。它们成为决定查询是触及少数分区还是扫描数月数据的关键因素。

最重要的实际变化：时间变得不能再是可选的。如果用户可以运行无界搜索（没有日期范围，仅“显示用户 X 的全部”），PostgreSQL 可能需要检查每个分区。即使每次检查都很快，打开许多分区也会增加开销并让页面显得缓慢。

一个行之有效的规则是：对日志和审计搜索要求时间范围并把默认设置为合理值（例如最近 24 小时）。如果有人确实需要“全部时间”，把它作为一个有意的选择并提示结果可能较慢。

让筛选匹配分区裁剪

只有在 WHERE 子句包含分区键且形式为 PostgreSQL 能利用的形式时，分区裁剪才会生效。像 created_at BETWEEN X AND Y 这样的筛选能干净地触发裁剪。经常破坏裁剪的模式包括把时间戳转换成日期、在列上包函数，或主要筛选与分区键不同的时间字段。

在每个分区内部，索引应当匹配人们实际的筛选方式。实际上，经常重要的组合是时间加另一个条件：租户/工作区、用户、操作类型、实体 ID 或状态。

排序与分页：保持浅分页

分区本身不会解决深度分页的慢问题。如果管理面板按最新排序且用户跳到第 5000 页，深度 OFFSET 分页仍然会迫使 PostgreSQL 跳过大量行。

游标式分页通常对日志表现更好：比如“加载早于此 (timestamp, id) 的事件”。它让数据库使用索引而不是跳过大量偏移。

预设也有帮助。提供几个常用选项通常足够：最近 24 小时、最近 7 天、今天、昨天、自定义范围。预设减少了意外的“扫描全部”搜索并让管理体验更可预测。

常见错误与陷阱

大多数分区项目因简单原因失败：分区生效了，但查询和管理 UI 没有匹配它。如果想让分区发挥作用，请围绕真实的筛选和真实的保留来设计。

1) 在错误的时间列上分区

仅当 WHERE 子句与分区键匹配时才会发生分区裁剪。常见错误是按 created_at 分区，而管理面板按 event_time 筛选（或反过来）。如果支持团队总是问“在 10:00 到 10:15 之间发生了什么”，但表按摄取时间分区，你仍可能接触比预期更多的数据。

2) 创建过多过小的分区

按小时（或更小）分区看起来整洁，但它们增加了开销：更多对象需要管理、查询规划器的工作更多、且更容易遗漏索引或权限设置。

除非你有极高的写入量和严格的保留，否则按日或按月分区通常更容易操作。

3) 假设“全局唯一性”仍然可用

分区表有约束：某些唯一索引必须包含分区键，否则 PostgreSQL 无法跨所有分区强制执行。

这常常让期望 event_id 永远唯一的团队感到惊讶。如果你需要唯一标识，使用 UUID 并把唯一性与时间键一起考虑，或在应用层强制全局唯一。

4) 让管理 UI 运行不受限制的宽泛搜索

管理面板常会带一个友好的搜索框可以在没有筛选的情况下运行。在分区日志表上，这可能意味着扫描每个分区。

对消息负载进行全文搜索尤其危险。添加保护措施：要求时间范围、限制默认范围，并把“全部时间”作为有意选择。

5) 没有保留计划（以及针对分区的计划）

分区并不会自动解决保留问题。没有策略的话，你最终会有一堆旧分区、混乱的存储和更慢的维护。

一套简单的操作规则通常能避免这些问题：定义原始事件的保留期限、自动创建未来分区并丢弃旧分区、一致地应用索引、监控分区数量和边界日期，并在真实数据量下测试最慢的管理筛选。

在承诺之前的快速检查表

分区对审计日志可能是巨大的收益，但它增加了例行工作。在更改模式前，用健康的怀疑检验人们实际如何使用该表。

如果你的主要痛点是在有人打开“最近 24 小时”或“本周”时管理页面超时，你就很接近分区适合的情况。如果大多数查询是“跨全部历史的用户 ID”，除非你也改变 UI 的搜索引导方式，否则分区的帮助可能有限。

以下简短清单能让团队保持冷静：

• 时间范围是默认筛选。 大多数管理查询包含清晰的窗口（从/到）。如果开放式搜索常见，分区裁剪的帮助会小很多。
• 通过丢弃分区而非删除行来强制保留。 你能接受丢弃旧分区并有明确的数据保留规则。
• 分区数量保持在可接受范围内。 估算每年的分区数（按日、按周、按月）。分区过小会增加开销，过大又降低收益。
• 索引匹配人们实际使用的筛选。 除了分区键外，你仍然需要为常用筛选和排序顺序在每个分区上建立适当的索引。
• 分区被自动创建并受监控。 有例行任务创建未来分区，并能在失败时报警。

一个实用测试：查看支持或运维团队最常用的三项筛选。如果其中两项通常由“时间范围 + 另一个条件”满足，那么对事件表做 PostgreSQL 分区通常值得认真考虑。

一个现实的示例和实用后续步骤

支持团队全天保持两个界面：“登录事件”（成功与失败登录）和“安全审计”（密码重置、角色变更、API 密钥更新）。当客户报告可疑活动时，团队按用户筛选、检查最近几小时并导出简短报告。

分区前：所有数据都在一个巨大的 events 表中。它快速增长，即使简单搜索也会变慢，因为数据库需要处理大量旧行。保留也很痛苦：每晚删除旧行，但大规模删除耗时、产生膨胀并与正常流量竞争。

分区后（按月，用事件时间分区）：工作流得到改善。管理面板要求时间筛选，大多数查询只触及一到两个分区。页面加载更快，因为 PostgreSQL 可以忽略所选范围外的分区。保留变为常规操作：不再删除数百万行，而是丢弃旧分区。

仍然棘手的一点是：跨“全部时间”的全文搜索。如果有人在没有日期限制的情况下搜索 IP 地址或模糊短语，分区无法让这类查询变便宜。解决办法通常在产品行为上：把搜索默认限制在时间窗口，并把“最近 24 小时 / 7 天 / 30 天”设为明显的路径。

通常有效的后续步骤：

• 先映射你的管理面板筛选。写下人们使用的字段以及必须要的字段。
• 选择与浏览方式匹配的分区。月度分区通常是良好的起点；只有在流量迫使时才改为周度或日度。
• 把时间范围作为一等公民的筛选。如果 UI 允许“无日期”，就预期会有慢页面出现。
• 让索引与真实筛选对齐。当时间总是在时，基于时间优先的索引策略通常是合适的基线。
• 设定与分区边界对齐的保留规则（例如保留 13 个月并丢弃更旧的分区）。

如果你在用 AppMaster (appmaster.io) 构建内部管理面板，尽早把这些假设建模进来：把时间有界的筛选视为数据模型的一部分，而不仅仅是 UI 的选项。这一小小的决定能在日志量增长时保护查询性能。