现在我正在寻找软件替代方案来支持这种操作。
将这些数据放在单个表中会导致灾难性情况的发生,因为由于一年内存储的数据量太大,我将无法使用这些数据。
我正在使用Postgres,数据库分区似乎不是一个答案,因为我需要按月份或更精细的方法进行表分区,例如按天计算。
我考虑使用SQLite的解决方案。每个设备将拥有自己的SQLite数据库,这样信息就足够细粒度化,便于维护和快速插入和查询。
您认为如何?
仅记录设备位置的变化 - 大部分时间内,任何设备都不会移动 - 汽车将被停放,人将坐着或睡觉,手机将在不动的人身上或充电等等 - 这将使您需要存储的数据量大幅减少。
即使没有实现第一个建议,您每年最多也只会生成约1TB的数据,这并不是很大的数据量。这意味着大约30MB/s的数据,单个SATA驱动器可以处理。
即使是一个简单的未分区的Postgres数据库,只要硬件不太大,也应该能够处理这个数据量。唯一的问题可能是当您需要查询或备份时,可以使用Hot Standby镜像,使用流复制 - 这是即将发布的PostgreSQL 9.0中的新功能。只需针对/备份镜像进行查询 - 如果它繁忙,它会暂时自动排队更改,并稍后赶上。
当您确实需要分区时,请例如按照device_id模256进行分区,而不是按时间。这样,您的写入就会分布在每个分区上。如果按时间分区,任何时候只有一个分区很忙,其他分区则处于空闲状态。Postgres支持此种方式的分区非常好。然后,您还可以使用tablespaces将负载分散到几个存储设备上,这也在Postgres中得到了良好的支持。
数据库分区管理可以自动化;基于时间的数据分区是应对这种问题的标准方法,我不确定为什么不能在PostgreSQL中执行此操作。
假设每天有大约72m行 - 假设一个设备ID、日期戳和两个坐标浮点值,每行将占用(比如)16-20字节加上一些较小的页面元数据开销。一个简单的容量规划建议每天约1-1.5GB的数据,或者每年400-500GB,如果需要还要包括索引。
如果您可以接受定期刷新数据(即不完全实时),则可以构建一个单独的报告表,并使用ETL过程定期更新该表。如果该表存储在单独的物理磁盘卷上,则可以查询该表而不会显着影响交易数据的性能。
一个单独的用于历史数据的报告数据库还可以通过删除较旧的分区来修整您的运营表,这可能有助于提高应用程序性能。您还可以为报告表创建索引和摘要表以优化报告性能。你提出的问题有点模糊。我认为你面临的不是数据库软件的选择,而是架构问题。
以下是一些考虑因素:
基本上,你的空间分区的想法是一个好主意。如果必要的话,这并不排除时间分区的可能性。你是在postgres还是sqlite中实现这个想法取决于其他因素,比如处理能力和可用库。
另一个考虑因素是你的设备是否足够可靠和强大来处理你的查询。否则,你可能需要使用集中式数据库集群,仍然可以并行查询。