3 ms·
Note the trigger approach mentioned in the article would be terrible for performance in a concurrent environment, since only one transaction could modify the wh
by zeroimpl 7y ago
Note the trigger approach mentioned in the article would be terrible for performance in a concurrent environment, since only one transaction could modify the whole table at a time.
- simonw 7y agoI wonder if there's a way to "shard" these counters to avoid this problem. If you had 10 different counters (maybe in ten different tables) and a mechanism for round-robin or randomly selecting which counter gets incremented/decremented would that allow ten concurrent transactions at once? The query to return the total count would then need to sum the 10 individual counters, which should be extremely fast. Or is the concurrency limitation here caused by the trigger on the counted table itself, not the writes performed by the trigger?
- ht85 7y agoEven though only 1 / 10 counters would be locked, you'd still have to read all 10 to get the count, which would be blocked until the concurrent transaction ended.
- ants_a 7y agoWith MVCC writers don't block readers.
- ants_a 7y agoSharding the counters would help, but with MVCC the typical solution is a delta table containing +1 and -1 records that is periodically compacted into the main count. With some cleverness about how to perform the compaction it's possible to make that very efficient.
- stubish 7y agoInstead of maintaining a single row in a table for the count, you maintain a table containing several rows. Instead of updating the single row, you insert a new row containing a 1 or -1. To get the count, you sum() the table. And you have a process to rollup the rows occasionally. This way you avoid the locks, except for the rollup.