2 hours ago, piojo said:

Postgres can run on Aurora

 

No, Aurora is a separate database entirely. You can run Postgres or Mongo on Amazon Web Services, and Aurora is another option they give you which they created and specialise in.

The main benefit of Postgres is that it's an old and well-known piece of software with many people who fully understand it and a development team that has worked hard to make it reliable, robust, and standards-compliant. Mongo, by comparison, is a very new piece of software, designed for a typical web use case of frequent reads and infrequent writes where JSON is the desired output format and where stale data is not a problem. (I have no opinion on Aurora.) I brought up Postgres specifically because it can meet your 'schemaless' use case via the JSONB columns, but is also able (when properly configured) to give you strong guarantees against giving you stale data, even across multiple instances. (But, it is NOT trivial. https://www.postgresql.org/docs/current/static/high-availability.html)

The relative speed of SQL and so-called 'NoSQL' databases is not something that can be compared in a vacuum. MongoDB will usually give you better read speed with its defaults than Postgres might for similar information, but Postgres won't return out of date values if you update during your speed test! And you can adjust both databases to be more or less lenient on this aspect, so it only makes sense to compare speed once you have set them up for equivalent levels of consistency.

It really is a good idea to start off with your data model first, normalized/denormalized. Run through creating/updating scenarios, and identify critical areas. If strong consistency and complex joins are critical to your game, then you should go the way of RDBMS. To my knowledge, social games that do not require active player-player interactions (e.g Clash of Clans, etc) can safely use NoSQL. I have seen people put the entire player data into a big JSON object and just send that object back and forth between server/client. MongoDB or other key-value NoSQL databases are okay with this type of usage.

Once you start having more complex relationship between players, or you need to do joins based on certain player data like "give item X to all players level 20 and above that only has a chainmail in the inventory and nothing better", that sounds like you are going in the direction of RDBMS. Although there are techniques you can do with kv databases to accomplish the same task, they are typically more like hacks than actual solutions.

7 hours ago, alnite said:

It really is a good idea to start off with your data model first, normalized/denormalized

That's the sort of thing a database analyst would say.

I've never worked on a game that had any idea what its data structure would end up looking like even a few months down the line, never mind at release time. Of course a degree of planning can give you the basic outline and should indicate some key objects but schema and data migrations are going to happen, regularly. Planning for migration is sensible and having worked with both relational and document-style DBs for this, I can confidently say the latter is less hassle as a developer, even if DBAs hate you for it.

9 hours ago, Kylotan said:

That's the sort of thing a database analyst would say.

I've never worked on a game that had any idea what its data structure would end up looking like even a few months down the line, never mind at release time. Of course a degree of planning can give you the basic outline and should indicate some key objects but schema and data migrations are going to happen, regularly. Planning for migration is sensible and having worked with both relational and document-style DBs for this, I can confidently say the latter is less hassle as a developer, even if DBAs hate you for it.

Ha. I am not a DBA, nor do I want to be one. I prefer schemaless document-style DBs personally, and I am also in the middle of researching a good reliable NoSQL database for my own project. Unfortunately, every single one of them seems to be designed for specific cases, and using it for other means can usually lead to headaches down the road. That's why I recommend to start off with the data model and how the data is going to be used, at least some rough idea, because that can narrow it down. Even as simple as knowing the maximum number of items in a player's inventory or do you allow trades between players can determine whether you want to normalize or denormalize your data.

Then hardware requirements can matter. NoSQL tend to have higher hardware requirements. They can claim 100M+ reads/writes per minute or whatever, but if they achieve that with a cluster of high end machines, then it's not really a number to rely on when you are on a budget. Team size can also affect the decision. I don't quite like Cassandra because it seems to be a high-maintenance type of DB, the kind you'd need a designated DBA for. It's highly configurable so most likely suits all kind of needs, but that also means someone needs to know all about it.

I am pretty sure piojo should be okay with MongoDB, or even Postgres if migration isn't much of a concern.