0

I haven't started building my back end yet but I need to now.

My needs are the following:

  • Database needs to be holding millions of game information (just info that can be stored in a typical db like match_id, win or loss, hero id #, item id # etc...)
  • Needs to be able to perform a basic division calculation based on certain parameters, like games that contain a hero id # and this needs to be as fast as possible since I don't want my user to wait like 5 minutes for a db query.
  • Once a day needs to call the api and update the db with thousands to millions of new games that were played in the last 24 hours.

Those are the most important aspects of my back end. Based on this, how would you build it?

I was planning on using MySQL but some people I talked to suggested Postgres. Some people also suggested using Heroku for this back end need, what about firebase? I know that there are a lot of options available for me. I think my BIGGEST NEED is speed. I need to have the queries done FAST AS POSSIBLE so that my front end isn't super slow waiting for information.

edit: more information about this.

This is just a simple DB holding basic data about the game of Dota 2. I am getting match information from the steam api and putting that into my db. I then make a simple division calculation based on some parameters like games that have a particular hero in them. Its just basic information not like an actual game. Just ID #s and stuff like that.

  • 1
    This question is too broad. Can you elaborate the nature of these games? – Chamindu Jul 18 '16 at 7:56
  • @Chamindu i edited my post to add more information. Its just GAME DATA not an actual game or games. And based on some parameters I am getting win % of the games that have been played – alber Jul 18 '16 at 8:03
  • 1
    May want to look into Data Warehouse concepts and products. – JimmyB Jul 18 '16 at 9:03
  • @JimmyB What Jimmy said. This looks like standard data warehousing solution. Both in use case and in scale. – Euphoric Jul 18 '16 at 9:33
  • 1
    "and this needs to be as fast as possible since I don't want my user to wait like 5 minutes for a db query". "I need to have the queries done FAST AS POSSIBLE so that my front end isn't super slow waiting for information." -- Spoken as if any piece of software is EITHER the fastest thing ever OR far too slow to use. Of no use in deciding what you need. Make a list of probable queries, the maximum amount of milliseconds those query should use, and the $$$ value you think reaching those limits is worth. – RemcoGerlich Jul 18 '16 at 10:25
1

PostgreSQL would be slightly better, because its design leans more towards analytical workload, unlike MySQL which was designed for transactional workload. That is if you want to do the calculations directly in the database. Getting player statistics is analysis of the data.

The obvious drawback (while not really that important) of PostgreSQL is its lesser popularity, ie. it's more difficult to find a community where you could discuss a PostgreSQL specific issues.

You should know that Heroku is a cloud application platforms, but that's it. Using Heroku alone will not suddenly improve your architecture and make your application scalable.

What you are really looking for is caching. After performing statistical analysis on the data you currently have, you should store the results into cache so they do not need to be recalculated each time. During the API call this cache should then be freed, statistics should be recalculated and reinserted into the cache.

But even then, you do not know yet what the biggest bottleneck is going to be. What I suggest you to do is to create a dummy database, fill it with millions of dummy records and try to run a query against it. The query you are likely to use. This way you can benchmark the database without actually having the application yet.

Another approach is to update the data sequentially. Call the API multiple times a day and update (cache invalidate and reinsert) only the statistics for players which are affected by the recieved batch.

  • awesome thank you! I agree that caching is probably a very significant factor in this design. I am assuming that every db would have some sort of caching? – alber Jul 18 '16 at 8:20
  • @alber Yes, databases internally have caches, but the cache I am talking about is a whole new layer in your application, cache like Redis or memcached. This cache would be a simple key-value store where key might be an id of a player and value it's calculated stats. – Andy Jul 18 '16 at 8:32
  • Re: Postgres community... It's less important than you suggest. When was the last time you called Microsoft over a Visual Studio problem? If you wanted that kind of hand-holding, you could probably get it from Stack Overflow. – Robert Harvey Jul 18 '16 at 13:30
  • @RobertHarvey Good point. – Andy Jul 18 '16 at 14:12
0

Rather than doing the calculations by hand it might be beneficial (depending on the complexity of the queries of course) to use an OLAP server. Some OLAP servers support creating MOLAP or HOLAP cubes that have pre-calculated aggregates that will improve your query performance significantly (E.g http://kylin.apache.org/).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.