Fixing the Behavior

While it’s easy to maintain cache in an event-driven fashion thanks to PostgreSQL and its trigger support. Turning an insert into an update with contention on a single row is never a good idea. It’s even a classic anti-pattern.

Script for triggers

Here’s a modern way to fix the problem with the previous trigger implementation, this time applied to a per-message counter of Retweet and favorite actions: