5 ms·

Even if counts could be made faster, at scale you'd probably still want to avoid counting anything that can be pre-calculated. We use something similar to the

Even if counts could be made faster, at scale you'd probably still want to avoid counting anything that can be pre-calculated. We use something similar to the trigger-based method they describe, tho have found that a lot of updates to count table inevitably ends with deadlocks. So instead of updating a count value, we always insert a new count of 1 or -1, and use summing to calculate the total count as needed. A background task is responsible for continually squashing the count values.

eveningcoffee 10y ago
This is very elegant solution.
oelmekki 10y ago
Great solution, thanks for sharing. At this point, I wonder: with a huge dataset, wouldn't you have better time leaving the count field out of postgres altogether and use something like redis with its INCR/DECR instructions instead? This would prevent having deadlocks as well. EDIT: that is, if you don't need to use the count field in other queries.
- rowanseymour 10y ago
  We've found that Redis works great for counting some things, but when you need a count which is guaranteed to match what is in a db table, it's preferable to manage counts at a db level. Often we're making counts based on the state of a record, and it's easy for the application level view of things to end up stale.
brightball 10y ago
Solid solution. I've done similar by just writing counts to Memcached and then periodically persisting them to the database. If I you don't have queries that actually need the counts the persist time can be stretched out to much longer windows too.
pgaddict 10y ago
Right, I was going to point out that one of the main issues with the per-table tally (trigger-based) is limited concurrency, as all inserts/deletes have to modify the same row. Deadlocks (due to updating counters for different tables) are an extreme case of this. The solution (or rather a mitigation reducing the chance of a deadlocks) is having multiple counters for each table (say, 32), and updating only one of them (e.g. based on backed PID). The trouble is that this increases the size of row_counts table. Another issue with the global tally is that the row_counts table bloats quite a bit, because the trigger is executed per-row and the update creates a copy of the row (so the next invocation has to walk all the previous MVCC copies, causing the long INSERT time). I wonder whether statement-level triggers might be used here, somehow (I don't think so).
mey 10y ago
The middle ground to this sounds like a skip-list, but I haven't seen that implemented inside a SQL system. Maybe a trigger on inserts to the count row to randomly insert the skip list count. Problem would be, this would essentially require ordering and a transaction, so maybe could be calculated offline in an async way in some way.

eveningcoffee 10y ago

This is very elegant solution.

oelmekki 10y ago

Great solution, thanks for sharing. At this point, I wonder: with a huge dataset, wouldn't you have better time leaving the count field out of postgres altogether and use something like redis with its INCR/DECR instructions instead? This would prevent having deadlocks as well. EDIT: that is, if you don't need to use the count field in other queries.

rowanseymour 10y ago
We've found that Redis works great for counting some things, but when you need a count which is guaranteed to match what is in a db table, it's preferable to manage counts at a db level. Often we're making counts based on the state of a record, and it's easy for the application level view of things to end up stale.

brightball 10y ago

Solid solution. I've done similar by just writing counts to Memcached and then periodically persisting them to the database. If I you don't have queries that actually need the counts the persist time can be stretched out to much longer windows too.

pgaddict 10y ago

Right, I was going to point out that one of the main issues with the per-table tally (trigger-based) is limited concurrency, as all inserts/deletes have to modify the same row. Deadlocks (due to updating counters for different tables) are an extreme case of this. The solution (or rather a mitigation reducing the chance of a deadlocks) is having multiple counters for each table (say, 32), and updating only one of them (e.g. based on backed PID). The trouble is that this increases the size of row_counts table. Another issue with the global tally is that the row_counts table bloats quite a bit, because the trigger is executed per-row and the update creates a copy of the row (so the next invocation has to walk all the previous MVCC copies, causing the long INSERT time). I wonder whether statement-level triggers might be used here, somehow (I don't think so).

mey 10y ago

The middle ground to this sounds like a skip-list, but I haven't seen that implemented inside a SQL system. Maybe a trigger on inserts to the count row to randomly insert the skip list count. Problem would be, this would essentially require ordering and a transaction, so maybe could be calculated offline in an async way in some way.