在数据库中存储大量数据

Question

我目前正在开发一个家庭自动化项目，该项目提供给用户查看其能源使用情况的可能性。目前我们每15分钟请求一次数据，并且我们预计在第一个大型试点中有约2000个用户。

我的老板要求我们至少存储半年的数据。快速计算估计需要大约3500万条记录。虽然这些记录很小（每个记录大约500字节），但我仍然想知道将它们存储在我们的数据库（Postgres）中是否是正确的决定。

有人有关于如何处理这么多信息的好参考资料和/或建议吗？

- Exelian

Answer 1

我们经常遇到这样的表格。显然，根据使用情况（读取或写入频率等），构建索引结构，并从一开始就考虑基于数据的高级分组进行表分区。

此外，您可以实现一个归档想法来保持实时表的瘦身。历史记录要么从不被触及，要么被报告，但在我看来，这些都不适用于实时表。

值得注意的是，我们有大约1亿条记录的表格，我们并没有感知到性能问题。许多这些性能改进可以在之后轻松实现，因此您始终可以从常识解决方案入手，并在性能证明不佳时进行调整。

- Adam Houldsworth

Answer 2

目前，每个0.5K的35M记录意味着37.5G的数据。这适用于您的试点数据库，但您还应考虑试点后的下一步。当试点取得巨大成功时，您告诉老板下个月无法将100,000个用户添加到系统中而不需要重新设计一切，老板肯定会不高兴。此外，对于VIP用户每分钟请求数据的新功能怎么办...这是一个复杂的问题，您所做的选择将限制软件的发展。

对于试点，尽可能简单，以便以最便宜的方式推出产品-适用于数据库。但是告诉您的老板，您不能像那样开放服务，必须在每周获得10,000个新用户之前更改某些事情。

下一个版本的一个要点：拥有许多数据存储库：一个用于经常更新的用户数据，一个用于查询/统计系统等...

您可以查看RRD以进行下一个版本。

请记住更新频率：2000个用户每15分钟更新一次数据意味着每秒2.2次更新 ->可以；100,000个用户每5分钟更新一次数据意味着每秒333.3次更新。我不确定一个简单的数据库能否跟得上这个速度，而单个Web服务服务器肯定不能。

- jfg956

---

速度也是硬件问题，尤其是存储。 - Frank Heikens

Answer 3

通过适当的索引来避免缓慢查询，我不会期望任何像样的关系型数据库管理系统在处理那种数据集时出现问题。许多人正在使用PostgreSQL来处理比那更多的数据。

这就是数据库存在的意义 :)

- David Precious

Answer 4

首先，我建议您进行性能测试 - 编写一个程序生成测试条目，这些条目对应于半年内将看到的条目数量，插入它们并检查结果以查看查询时间是否令人满意。如果不是，请尝试像其他答案建议的那样进行索引。顺便说一句，还值得尝试编写性能以确保您实际上可以在15分钟内插入您正在生成的数据量.. 15分钟或更短时间。

进行测试将避免所有问题的根源 - 假设 :-)

还要考虑生产性能 - 您的试点将拥有2000个用户 - 您的生产环境将在一两年内拥有4000个用户或200000个用户吗？

如果我们谈论的是一个非常大的环境，您需要考虑一种解决方案，该解决方案允许您通过添加更多节点来扩展，而不是依赖于始终能够向单个机器添加更多CPU、磁盘和内存。您可以通过跟踪多个数据库机器中哪个机器托管特定用户的详细信息，在应用程序中执行此操作，或者您可以使用其中一种Postgresql集群方法，或者您可以走完全不同的道路 - NoSQL方法，其中您完全放弃RDBMS并使用构建为水平扩展的系统。

有许多这样的系统。我只有Cassandra的个人经验。你必须从关系型数据库世界完全不同的角度去思考，这是一种挑战 - 更多地考虑如何访问数据，而不是如何存储它。对于你的例子，我认为将数据以用户ID作为键存储，然后添加一个列，其中列名称是时间戳，列值是该时间戳的数据，这样做是有意义的。然后，你可以请求这些列的片段，例如用于Web UI中的绘图结果 - Cassandra对于UI应用程序的响应时间足够好。

学习和使用nosql系统的好处是，当你需要更多空间时 - 你只需添加一个新节点。如果你需要更多的写入性能或读取性能也是一样的。

- Erik Forsberg

Answer 5

你不考虑不保留完整时间段的单个样本吗？你可以实现某种合并机制，将每周/每月的样本连接成一个记录，并按计划运行该合并。

你的决定必须取决于你需要在数据库上运行的查询类型。

- zeFrenchy

Answer 6

有很多处理这个问题的技巧。只有在您触及最少的记录数量时，您才能获得性能方面的提升。在您的情况下，您可以使用以下技巧。

1. 尝试将旧数据保留在单独的表中，您可以使用表分区或使用不同的方法，在文件系统中存储旧数据，并直接从应用程序提供服务，而无需连接到数据库，这样您的数据库将是自由的。我正在为我的一个项目做这个，它已经有超过50GB的数据，但运行非常顺畅。
2. 尝试对表列进行索引，但要小心，因为它会影响插入速度。
3. 尝试批处理插入或选择查询。您可以在此处非常巧妙地处理此问题。 例如：假设您每秒钟收到一次向任何表插入记录的请求，然后您制定一种机制，在其中以5条记录的批处理方式处理此请求，这样您将在5秒后访问数据库，这更好。是的，您可以让用户等待5秒钟等待他们的记录被插入，就像在Gmail中发送电子邮件并要求您等待/处理一样。对于选择，您可以定期将结果集放入文件系统中，并直接向用户提供服务，而无需触及数据库，就像大多数股票市场数据公司所做的那样。
4. 您还可以使用一些ORM，如Hibernate。它们将使用一些缓存技术来提高数据速度。

如有任何进一步的查询，请发送电子邮件至[e-mail]

- Ranjeet Rana