数据库实施帮助:时序数据

Question

这是我的previous question的重新提交

我有一个有序的时间序列数据(股票分钟价格信息)的集合。我目前使用PostgreSQL的数据库结构如下：

symbol_table -保存以symbol_id作为主键的符号列表(序列号)。time_table, date_table -时间/日期值存储在那里。time_id/date_id是主键(串行/串行)。

我的主minute_table包含分钟价格信息，其中date_id|time_id|symbol_id是主键(也是来自相应表的外键)

使用这个主minute_table，我执行不同的统计分析，并将结果保存在一个单独的表中，如one_minute_std -其中保存了一分钟的标准偏差测量值。

每天晚上，我都会用上一天收盘价的当前价格信息来更新表格。

使用当前的实现，我的表包含了所有的符号，每个符号大约有5000万条记录。主键被编入索引。

如果我想查询all the symbols where closing price > x and one_minute_std >2 and one_minute_std < 4 for the specific date，搜索大约需要3-4分钟。

为了加快这一过程，我在考虑将每个符号分离到它自己的表中，但不是100%确定这是不是一种“合适”的方式。

你能建议我如何加快查询过程吗？

Answer 1

听起来你想要一种方法的组合。

首先，您应该了解表分区。这样可以跨多个存储单元(“文件”)存储单个表，但仍然可以灵活地使用单个表。(这里是postgres文档http://www.postgresql.org/docs/current/interactive/ddl-partitioning.html)。

您可能希望按天或按自动收录机符号进行分区。我的第一反应是按时间(天/周/月)，因为这是更新的单位。然而，如果你的分析只有一个滚动条，并且经常跨越多天，那么就有理由使用这种方法。

分区之后，您可能需要考虑索引。但是，我怀疑分区将解决您的性能问题。

由于你的更新是在晚上，你应该在你的摘要过程中与更新一起折叠。例如，应该在此过程中计算one_minute_std。您可能会发现最好将夜间数据加载到临时表中，执行诸如one_minute_std之类的摘要计算，然后将数据加载到最终的分区表方案中。

由于有如此多的行，而列却如此之少，因此使用良好的分区方案可能比使用索引方案更好。特别是，索引具有空间开销，并且每行中的记录越小，使用索引产生的开销就越大，这与扫描整个表的开销相当。