postgresql - 使用物化视图更快地从大型表中检索数据

我们有一个表来存储用户登录的日志user_login_log。每次用户登录时，此表都会收到一条记录。

我们有一个用例来获取每个用户的最后登录时间戳，并且将经常获取此数据。要求是应快速加载此数据。

要求

日志表可以有数万条记录，因此每次查询它们将是一个缓慢的过程。我们提出了两种快速检索数据的方法。

方法1：我们最初的想法是使用物化视图。

这种方法的问题是，随着user_login_log表中数据的增加，刷新物化视图的查询会变慢，从而使 INSERT、UPDATE 和 DELETE 操作变慢。

方法2：另一个想法是使用专用的快照表来存储最新的登录数据。

然后将直接从快照表中检索数据。这种方法的问题是我们将在两个表中维护相同的数据，而触发器函数将负责维护数据完整性。

这些方法中哪一种更适合我们的用例？是否有任何其他方法可以更有效地过滤和检索所需数据？

标签： postgresqlmaterialized-views

您的第一种方法没有多大意义。为什么要构建一个触发器，它具有您需要的确切信息，只是为了丢弃这些信息并让它从头开始重建整个表？是的，当然这会很慢。

在您的第二种方法中，为什么要创建第二张表来保存一列信息？你不是已经有一个可以添加列的“app_user”表吗？

这种方法的问题是我们将在两个表中维护相同的数据，而触发器函数将负责维护数据完整性。

我看不出这有什么问题。这不是触发器的用途吗？只需正确编码，并确保用户登录事件在其自己的事务中，以避免将记录锁定过多的时间。

另一种选择是编写递归 CTE 代码，对现有的 user_login_log 表进行松散索引扫描。这可能足够快以避免在写入时必须做任何特殊的事情。