postgresql - 大表上的 Postgres 时间更新

Question

我在 AWS (RDS m4.large) 上处理一个非常大(59 亿行)的表，我在将数据获取到可以有效处理和查询的点时遇到了一些问题。

数据有一个从记录开始(类型:bigint)开始的时间变量(以秒为单位)，但我希望能够在每日级别(即 GROUP BY 天)聚合数据。

我通过简单地创建一个日列来开始这个过程，并更新表以通过将秒转换为天来填充日字段:

ALTER TABLE tbl
ADD COLUMN day INTEGER;

UPDATE tbl
SET day=tbl.eventtime/86400+1;

它在测试环境中运行良好(90 天记录的前 10 天)，但是，也许出乎意料的是，它在完整的 50 亿行数据集上运行得不太好。

在这个过程的几次尝试之后，我意识到我之前创建的索引大大减慢了这个过程(更新查询从未完成)。我已经删除了所有索引并再次尝试更新。 20 小时后，查询仍在运行，我想知道这种方法是否非常不适合我正在处理的数据量。

我意识到另一种方法是将以秒为单位的时间转换为某种 postgres 时间戳类型——但我不熟悉时间戳，不确定从哪里开始。

对这么大的表执行更新是否可行？是否有一种不同的方法可以更有效地使第二分辨率数据达到我可以按天汇总的程度？

如果需要任何其他信息，请告诉我。

Answer 1

索引大大减慢了数据修改的速度，但没有它们就无法有效地搜索大型表。

所以这里的艺术是尽可能少的索引。

你的增加一列的想法是错误的。它不仅会使已经很大的表因冗余数据而膨胀，而且除非您创建索引，否则它也不会加快搜索速度。

如果你需要搜索某一天的所有行，你可以在相应的表达式上创建一个索引:

CREATE INDEX ON tbl ((eventtime / 86400 + 1));

然后任何使用此表达式的搜索都可以使用索引，并且您不必在表中存储额外的数据。

这仅对查询数据有用。如果你想有效地聚合，索引对你帮助不大。在这种情况下，您应该使用物化 View ，也许是您通过触发器保持更新的 View 。