postgresql - SQL最佳数据库结构: NOAA Data

Question

我正在尝试将大量的每日天气数据存储到 postgreSQL 数据库中。这可能看起来不是很多数据，但大约有 95,000 个站点的每日数据可追溯到 100 年前。这可能意味着数百万条记录 (95,000 * 365 * 100) = 3,467,500,000。虽然这是一个高估，但对我来说将所有日常数据存储在一个表中仍然是不切实际的，其中站点 ID 作为外键映射到另一个包含站点信息的表。构建此数据以按站点查询数据系列的最佳方法是什么？我应该为每个站创建一个表(将产生 95,000 个表)还是应该尝试更广泛的东西，比如为每个区域创建一个表？有什么优点和缺点？非常感谢任何帮助。

我的数据是这样的:

Stations
*ID
-longitude
-latitude
-elevation
-country
-state
-name
...

Weather
*Station ID
*Date
-Precipitation
-High Temp
-Low Temp

Answer 1

这还不够信息。

您要优化什么:查询性能、磁盘使用率、更新速度？

• 您正在运行哪些类型的查询？
• 您是否通常为某个站点获取所有 数据(似乎不太可能)？日期范围？
• 如果您按日期查询，通常的解决方案是什么:日、月、年？
• 这些是“天气”表中的所有字段，还是只是一个示例？
• 您通常会检索单个值还是多个不同的值？
• 您只是检索这些值，还是在数据库中进行聚合/分析？
• 您可接受的查询性能是多少？

根据您对这些问题的回答，“组合”您的数据可能有意义(每条记录存储超过一天；我假设“日期”表示它是一天，还是更细化？ ), 以减少总行数。 Postgres 的每行开销相对较高 - 据您估计，仅行标题将占用 ~75GB。

或者，您可能想调查这样的事情:https://github.com/citusdata/cstore_fdw

使用更多表的优点是索引大小更小和(可能)物理数据局部性。在每个 station_id 一个表的极端情况下(在您的情况下不实用)，您根本不需要 station_id 上的索引，并且查询可能最终是对数据的简单序列扫描你需要。

缺点是许多数据库操作涉及对所有表的线性扫描(尤其是在计划期间)并且管理数据库更加复杂。

典型的建议是将表的数量保持在几百到几千。也许。当然，除非您有一个非典型案例，并且您已经对其进行了测试并且它适用于您。