Modeling for Concurrency
Explore techniques to model PostgreSQL databases for handling concurrency effectively using isolation and locking principles. Understand how to redesign schemas to support concurrent updates without heavy locking, enabling more scalable transactional operations.
We'll cover the following...
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.