Optimizing writes over the same entity in relational DB

Question

I am developing a micro service that saves votes to a relational database. I have a use case with an entity that represents an aggregation of votes. I receive events from voters saying that they vote in favour of, or against X. This will increase values on the given entity, and these changes need to be persisted to DB at some point.

Given that the amount of voters can be high (~1k) and votes happen almost simultaneously (within 20 seconds) how can I batch some of the updates on the entity and periodically save to DB the accumulation of them, as to try to make it more efficient than flushing all of them to DB

Could hibernate second level cache be an option for this?

EDIT The systems will also need to handle 3-4 new polls every second, and this voting numbers apply to all of them

Answer 1

Although the frequency of updates per minute may not seem excessive, and your database should have no difficulty handling it, it may be worth considering implementing an in-memory buffer to keep track of new votes and periodically flushing it to the database, such as after every 100 new votes. While this will minimize the strain on the database, there is a risk of data loss in case of a service outage.

In case you are not tied to a specific database, you might also want to explore other options, such as Redis, which has built-in support for incremental operations (INCR op). However, given the relatively low number of updates you are expecting, it is probably sufficient to write each vote directly to the database.

Answer 2

Roll a new GUID for each voting event, and append a one-line entry to a text file of votes, perhaps in CSV format. Either fsync() the write immediately, or have a background thread issue fsync's every K milliseconds, which has the effect of batching each burst of voting activity so several closely-spaced votes are persisted together. (Oracle held a patent on this. It's called "group commit".)

Now your text file(s), one per thread / process / host / whatever, are your Source of Truth, describing the historic events that happened. Post process them as you wish. Send them to Redis. INSERT those rows into a relational database table, and do COUNT's. There's no more risk from bouncing servers, once the events have hit a persistent storage layer.

Answer 3

About performance

Your concern is legit, but I think you should start addressing the issue from a different angle first. Focus first on the connection pool size. Regarding Java's connection pool, it may interest you HikariCP. Once you are confident about the reliability and size of the connection pool. Perform load tests.

Simulate those +1K concurrent votes and track errors that might cause you to miss votes. If you have a way to assert the total count vs the expected one, make sure the assertion is checked in the end.

Once you have a complete report with successful and failed requests, locate (by error codes and logs) what caused the errors. Or what caused the large number of them¹.

Did you run out of connections? Make it larger. Did connections run out of time? Look for transaction locks, or set larger timeouts. Did you run out of memory? Look for memory leaks. Did you find none? Give a bit more room to the Heap. Make step increments tho (%5, 10%, 15%, etc.). Did you find vote computing getting slow over time? Try computing votes in parallel.

In other words, spot performance issues and tune up first with what you have. Find a good balance between resources and performance. Then scale or improve the design.

About design

Can you afford missing votes?

If not, then don't cache/buffer any. Make atomic transactions so that if one fails, the client is reported immediately and inform the user his vote doesn't count.

If yes, then it might interest you the write-through cache pattern.

I have implemented this pattern once to track users' last activity date and time. I used those values to check when an account became officially inactive and eligible for oblivion.

Every time I tracked user activity, I updated the cache with the date and time of the account causing the activity.

The flush strategy was as simple as

• After 1000 hits (to prevent the cache from growing too much)
• A cron job checking the cache every few minutes to prevent the cache from getting too old due to low activity or 0 activity. Also to prevent memory leaks.

The flush resulted in several transactions, all using the same DB connections, so not a problem at all. Maybe, you could solve the flush with a couple of transactions (upvotes and downvotes) or in a single one if only the overall result matters.

---

^{1: Load tests usually show behavioural patterns of the system under stress. Look for them}