I'm storing a set of messages in a SQL table. Each message has a size and there's a column in the table which contains the size of the message. These messages are connected to accounts. When a new message arrives, I need to check that the current account size + the new message size is less than the quota for the account (which is just a "maxaccountsize" column in a row in the accounts table). If not, I need to report back to the sender that the message does not fit in the account.
To simplify:
Table messages:
ID int
AccountID int
Size int
Table accounts:
ID int
MaxSize int
To calculate the total size of each account, I execute statements similar to SELECT SUM(Size) from messages WHERE AccountID = 12345.
In a large user databases where there's hundreds of thousands of messages in accounts, this operation is heavy and becomes a big bottleneck when receiving a message. My software use both Microsoft SQL Server, MySQL and PostgreSQL as backend.
To solve this, I've added some in-memory caching of the value. This is cumbersome to me since I need to implement thread-safe updates of the cache, and I need to make sure that the cache is always up to date. Also, it doesn't work if someone manually edits the database.
An alternative solution would be to store the current account size in the accounts table. However, this would mean I have somewhat redundant data (of course, one can say that this is already the case today with my in-memory cache). If I choose this solution, I need to make sure I always update the account size when creating or deleting messages. This is also a bit cumbersome and I can bet that there will be times when the sum(size) does not equal to the CurrentAccountSize value in Accounts row. With the in-memory cache, at least it will be reset to its correct value when the server is restarted.
Does anyone have an opinion on what should be done in situations like these?