Best practice to create a Summary Table for an ever-growing table in MySQL

Question

I have a table called transactions with ~20 million records. This table grows every second.

I calculate users current balance with:

SELECT sum(`amount`) FROM `transactions` WHERE `user_id` = 1000;

I'm showing user current balance in top bar of my web application and user can see how much balance he/she has!

Obviously every time a user browses my web app pages, the above query must be executed to calculate the current user balance!

I want to create a Summary Table to obtain the current user balance without querying on that transactions table with ~20 million records!

Be aware that in our workflow it's so common that a user may have multiple transactions simultaneously (a user may even have multiple transactions in just one second).

I think we have two approaches here:

The First Approach

Creating a Summary Table with One-To-One relationship as below:

ID  |  user_id  |  current_balance
1   |  1000     |      8590
2   |  1001     |      235
3   |  1002     |      3780
... |  ...      |      ...

And every time a new record inserted into the transactions table we trigger a stored procedure to update the user current_balance in the Summary Table.

I don't know if this approach breaks MySQL consistency or not!

The Second Approach

Creating a Summary Table with One-To-Many relationship as below:

ID  |  user_id  |  amount
1   |  1000     |   8590    <--- it's the initial user balance
2   |  1001     |   235     <--- it's the initial user balance
3   |  1002     |   3780    <--- it's the initial user balance
4   |  1000     |   50
5   |  1000     |   -30
6   |  1001     |   10
7   |  1002     |   60
8   |  1000     |   -45

We clear out our Summary Table nightly (for example at 00:00 AM) and recalculate the current balance for all users from transactions table and insert them into the Summary Table. To determine a user's current balance we just need to do this:

SELECT sum(`amount`) FROM `users_balance` WHERE `user_id` = 1000;

But there is something that worries me about this approach. What if some users do transactions exactly at the time we are recalculating the users current balance and putting them into the Summary Table! ( exactly at 00:00 AM)

Does this approach break consistency?

---

Please tell me if you know any better practice for this workflow.

P.S.

Our web app is an SMS panel via which users can send/receive/etc. SMS through the panel directly or an API. We have some users who send 1 million or more SMS in a day!

Every time an SMS is sent, a new record must be inserted in the transactions table.

I know 20 million records is not a big deal and we can achieve good performance with indexes, but as I mentioned above it's an ever-growing table. I'm pretty sure next year we'll have hundreds of millions records in transactions table.

Answer 1

You're maintaining a balance for each user, as you have explained.

Your best bet is to write application code that carries out two queries, perhaps in a transaction, but probably not.

One query:

      UPDATE balances
         SET current_balance = current_balance - 1 
       WHERE user_id = 1000

That query, in itself, maintains consistency without any need for a transaction.

(Edit) It looks for the row of the balances table with user_id=1000 and then subtracts one from the value of current_balance in that row, reading, modifying, then writing the row. You can do this kind of arithmetic with column values in INSERT and UPDATE queries as needed.

The other query

      INSERT INTO transactions (columns) VALUES (values)

The way you have explained your application, it sounds like the integrity of your business depends upon the table I'm calling balances in my first query. The transactions table is a log of user activity, and serves to explain how a customer balance got to be what it is. So, if you get your application to perform the two queries I propose in order, you will have excellent balances values and good-enough logging. That's a good way to structure a transactional database.

Why should your balances be maintained separately from your transaction log? What if you want to give a customer 100 free messages? What if you want to start charging extra for messages at a certain time of day? What if a customer demands a credit for a batch of messages that were, according to her, handled incorrectly? If you make your balances from your transactions table, you're going to have to put all sorts of bizarre stuff into that table to handle your evolving business rules.

Would I bury the update of the balances table in a trigger if I were you? No, I would not. I'd make it part of your application. Easier to see, easier to debug, etc.