PostgreSQL：count（）还是保留一个计数器？ [英] PostgreSQL: count() or keep a counter?

问题描述

我有两个表，一对多。假设表 foo 中的每一行，表 bar 中可以有0或更多行在 foo 中。

I have two tables in a one-to-many relationship. Let's say that for each row in table foo, there can be 0 or more rows in table bar that reference the row in foo.

客户端想知道栏中的行数为 foo 中的所有行引用 foo 中的一行。

The client wants to know how many rows in bar reference a row in foo, for all the rows in foo.

我可以使用以下查询来完成此操作：

I can accomplish this with the following query:

SELECT count(bar_id) FROM bar WHERE bar.foo_id = foo.foo_id;

但是，如果表 foo 和 bar 大吗？假设 foo 有100万行，而 bar 有1000万行。还要说， foo 中99％的行的引用计数少于1000个 bar 行。假设客户通常一次请求大约100行 foo 。

However, what if the tables foo and bar were large? Say foo has 1 million rows, and bar has 10 million rows. Let's also say that 99% of rows in foo would have a count of less than 1,000 bar rows referencing it. Let's say that the client typically asks for around 100 rows of foo at a time.

我应该将朴素的count（）查询与外键上的索引一起使用，还是最好保留一个计数器？甚至有可能保持反击吗？通过使用 bar 上的触发器通过原子增量和减量来更新计数器，我相信这是可能的，但我可能是错的。

Should I use the naive count() query with an index on the foreign key, or would it be better to keep a counter? Is it even possible to keep a counter? By updating the counter with atomic increments and decrements using a trigger on bar, I believe it's possible, but I could be wrong.

推荐答案

也许与直觉相反，您可能会发现，简单的 count 方法会更快，除非您的工作负载有很大偏见

Perhaps counter-intuitively, you'll probably find that the simple count approach is faster unless your workload is very biased towards reads.

原因是计数器表的作用是序列化更新，因此只有一个事务正在更新给定的 foo 可以在任何给定时间飞行。这是因为用于更新计数器的触发器的更新将锁定计数器表中的 foo 条目，并且在事务回滚或删除之前不会释放它。

The reason for this is that the effect of the counter table will be to serialize updates, so only one transaction that's updating a given foo can be in flight at any given time. That's because the update for the trigger that updates the counter will take a lock on that foo's entry in the counter table and won't release it until the transaction rolls back or commits.

更糟糕的是，如果您的交易影响一个以上的 foo ，那么另一个交易也会影响您，

Worse, if your transaction affects more than one foo and so does another one, you have a high chance of one of the transactions being aborted due to a deadlock.

简单地计数一次，直到您有充分的理由进行更改为止。

Stick to a simple count until you have a good reason to change it.

这篇关于PostgreSQL：count（）还是保留一个计数器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！