在PostgreSQL中存储和查询滚动数据

Question

我有大量的天气模型数据被放入一个PostgreSQL数据库中。该机器有8个核心和16 GB内存。我正在用PostgreSQL 2.1运行PostGIS 9.3。每个表都有不同的天气数据(温度、露点、风等)。每个表将有6-7列:纬度、经度、点几何学、高程、模型相关的日期时间，以及感兴趣的1-2个数据值。数据将主要根据时间和海拔来查询边框。每个表将有大约145,757,360行数据(将删除比现在更旧的数据)。我粗略估计没有索引的表的大小大约为10 GB。(即52字节的数据加上23字节的每行开销)。随着新模型数据的提供，数据将定期更新/插入。注意:新数据将包括覆盖表中的现有值。

所以我在看这两个计划：

1. 只需按(日期、时间、海拔)进行索引和聚类，并为点几何增加一个索引。运行一个常规的cron作业，删除旧行，运行真空/分析，并重新集群。
2. 按日期时间进行分区，然后根据每个表的标高对几何图形进行聚类和索引。运行一个常规的cron作业来添加新表，然后删除旧表。

此外，

• 所以，我知道删除一个表的效率要高得多，删除和清除也要有效得多。但如果不是这样的话，我会看到业绩的提升吗？
• 当所有的表都被均匀更新并被选中时，分区是否合适，直到删除时才被删除(文档表明，当只选择其中的几个表时，分区工作得最好)？

当传递数据时，选择会比聚集索引更快吗？如果同时提出多个请求，答案会改变吗？

谢谢。我希望我能提供所有需要的数据。如果不让我知道，我会加进去的。

Answer 1

考虑到所有因素，我将选择选项2。日期将被均匀地选择，但我将猜测，对于给定的查询，只涉及一个或两个日期分区。遗憾的是，您不能在地理位置上进行集群并在日期上进行分区，这将是理想的选择。无论如何，如果包围框足够小，高程往往与地理位置相关。

考虑到可用的选择，清洁的数据操作和避免日常真空是一件好事。

使用选项1，交付选择可能会更快，尽管我怀疑这可能是一个清洗。在选项1中，具有相同日期和高度的记录被放置在一个大的聚集索引中。使用选项2，具有相同日期和高度的记录在许多较小的聚集索引中彼此放置在一起。