Modeling for Concurrency

We should have another modeling pass on the tweet.message table now. With what we learned about concurrency in PostgreSQL, it’s easy to see that we won’t get anywhere with the current model. Donald Knuth said:

“We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%.”

Database systems have been designed to handle concurrency so that your application’s code doesn’t have to. One part of the critical 3 percent is then related to concurrent operations, and the one that is impossible to implement in both a fast and correct way is the concurrent update on the same target row.

Handling concurrent activity

In our model here, given how the application works, we know that messages will get concurrent update activity for the favs and rts counters. So while the previous model looks correct with respect to normal forms—the counters are dependent on the message’s key—we know that concurrent activity is going to be hard to handle in production.

So here’s a smarter version of the activity parts of the database model: