Re:PostgreSQL Count(*) Performance Improvements
by ants_a on Thursday April 11, 2019 @02:23AM
(#19631652)
Sharding 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.
Re:PostgreSQL Count(*) Performance Improvements
by stubish on Thursday April 11, 2019 @02:27AM
(#19631671)
Instead 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.
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?