Quote:
Originally Posted by microbet
So, you just keep the number of likes in the table with the tweet and separately keep the user and the tweets they liked and programmatically ensure as best you can that those two things remain in sync.
Right?
For this approach I'd probably create another table, with id_tweet and total_likes rather than add column to the tweets table. Neater, and allows for storage flexibility.
You could use a transaction to make sure the count table gets updated when a tweet is liked / like removed, then one can't happen without the other.
You could use triggers.
You could maybe use a functional index (index on expression)
Here's an interesting article that compares numerous approaches including your idea (though as usual, trying to sell things)
https://www.citusdata.com/blog/2016/...t-performance/
Like you said, if implementing a caching system of your own design, the actual liked_tweets table would be a source of truth so you could periodically check and fix if required. Perhaps you could do that triggered by querying the likes, but only once every so often.
Also, remember perhaps YAGNI