sql - 存储事件/非事件时间戳

Question

我正在使用 PostgreSQL 后端运行 Ruby on Rails 应用程序，我想知道存储一系列时间戳的标准方法是什么。情况是我有一个带有 bool status 属性的模型。我需要跟踪此属性更改的日期/时间，因为我需要跟踪 status 设置为 true 的时间段。我严重缺乏解决这类问题的经验，而且我不确定以可扩展的方式做这类事情的最佳实践是什么。我考虑过的两种方法是:

1) JSON化文本。我正在考虑采用一个可能如下所示的数组:

[
  { 
      start: "20150131103045",
      end: "20150228103045"
  },
  { 
      start: "20150531103045",
      end: "20150628103045"
  },
]

然后我会将这个数组 JSON 化并将其存储在 text 列中。

2) 创建一个单独的表，其中包含 model_id、status 和 time_recorded 属性列，然后简单地创建一个条目更新模型的 status 属性的时间。

以下哪种方法更可靠？这里需要考虑的是，这些数据可能不会被非常频繁地读取——95% 的时间，它只是写入数据库的新数据。

选项 1) 对我来说似乎不那么严厉，但在我需要时读取数据并对其进行排序也会带来更大的痛苦。选项 2) 将以更易于使用的方式存储该数据，并且永远不需要读取/更新预先存在的数据，但该表可能会变得非常大，非常快。更重要的是，我必须在我的应用程序中对多个模型执行此操作，因此仓促做出决定并从一开始就实现糟糕的架构可能会在以后处理起来相当烦人。

这两种方法的优缺点是什么？这些中的任何一个显然比另一个更好的解决方案吗？还是有其他我没有想到的更好的选择？

Answer 1

选项 1:

您必须考虑在连续更新时，Postgres 将在后台创建该行的另一个版本，其中包含新数据，并在该表中完成 vacuum 时标记旧版本以供重用。如您所见，这将导致大量“真空工作”(将其理解为高磁盘/IO 工作)。如果该行的数据不适合单个数据页，情况会更糟。在这种情况下，还有一个“toast”表也需要清理。

---

选项 2:

在我看来这是更好的方法，因为一旦插入该行，您将永远不会更新它。并且数据看起来足够小以适合单个数据页(不需要 toast 表)。如果您使用正确的索引，您将不会遇到性能问题。 Postgres 可以轻松处理数百万条记录。

此外，您还可以使用 patitioning table技术。如果您的表有数十亿条历史记录，您可以将它“拆分”到其他几个表中(例如，每个月一个)，其中每个表的索引非常小并且运行速度非常快。一切都在后台完成，因此您的应用程序只会看到“主”表，而 Postgres 将自动处理所有其他拼接表。

如果您需要以 JSON 格式检索数据，这也很容易。您可以只创建一个 View (如果您愿意，也可以创建一个函数)，其中带有“开始/结束”时间的列将以 JSON 数组格式(使用 postgres 9.3 及更高版本)挂载。