Just for fun: does anyone have any stories about initially using a SQL database for a project, later hitting problems, then switching to (or augmenting with) a NoSQL solution that solved those problems?
At project onset, usually only redis or memcache are in use.
It's a model that has served me well, apart from bugs that cause divergence between redis and relational. These are usually the class of bugs that would corrupt data were I relying on redis alone, so I just think of them as a lesser evil.
There's an obvious bottleneck on writes to the relational DB but I've so far managed to keep from hitting that ceiling.
I'm really becoming a big fan of CQRS/event sourcing where we have two read models - one NoSql for live data and one relational for BI/Reporting. Events are stored separately in JSON because that's how they come in anyway through the API.
Do you read them back? Does your code expect property "X" to be on a "Player" object? Do you put a check everywhere for when it doesn't? Do you have default value for objects that were created before new property was added?
All that work could've been done by your database (which is probably has some kind of JSON implementation anyways), but somehow it's a "no-brainer" to implement data consistency at app-level. Okay.
Why would you put checks everywhere a properly written code base would have one module/service that handles the player object and every consumer would use that one module.
e.g. you add a new field and an application level default, then later that default is changed. Now there's no notion of which default the docs that never had the property should be using. Sure, you just need to patch the old docs to set the property where it's missing. But the GPs point was that by not keeping a consistent schema you end up moving the definition of the data into the application, and you need to be very aware of the trade-off.
Personally I'm really happy using postgres and dropping into jsonb when it's the correct model to use. It's the best of both worlds as far as I'm concerned. I've built and worked on systems using nosql solutions that have been migrated back to sql and the data inconsistencies we found have made me very wary of having a primary datastore that doesn't enforce strict constraints.
Though, everybody's use case is different.
Why wouldn't the defaults be in the application? Why wouldn't it be treated like any other piece of business logic in code that should be unit tested?
That being said, I only caught the NoSQL religion about six months ago when I started using Mongo. But SQL Server 2016 has such great support for JSON natively, as long as I'm spending other people's money, I prefer having the best of both worlds.
How about we check our data in the place where it lives given how very robust and mature products that do that were already written, tested, and used in production by literally thousands of people for decades.
So by "checking data" can we check all of the business constraints?
Definetly stuff like presence of fields and their basic sanity (string not being empty or within a certain length), date intervals, uniqueness of certain fields or combinations, etc.
You can go as far as you want, really; but like with most cases, when there's a range of possibilities, both extremes are probably a wrong answer.
if (player.hasSomeProp) {
doStuff(player);
}
With "no-brainer" I mean it doesn't take any effort when developing since the representation of the player object in runtime is exactly the same as it's stored in the database.
If you just want things as JS objects, you could have a Player table with a column for playerID and a column for the JSON string.
I'm not saying you shouldn't use Mongo/NoSQL, I just think SQLite is an interesting potential choice. (e.g. https://sqlite.org/appfileformat.html)
There are also excellent out-of-the-box support for arrays, enums, custom types, and JS procedures if you so desire. All that comes with consistent syntax, great aggregation functions, JOINs; and all those 43 years of advancement mentioned in general.
Read the docs, you'll be amazed with what you're missing :)
General overview https://www.postgresql.org/docs/current/static/tutorial.html
JSON support specifically https://www.postgresql.org/docs/current/static/datatype-json...
That'd be forever extensible and if you made the primary key a combination of playerId and key, you'd offload duplicate handling to the database.
Seems simple to me.
I have done it before, once. While some folks will tell you it's just wrong, like NoSQL I think it's sometimes the best fit, even for the entire life of the app.
For 99% of things, though, I use SQL tables, normalized to the Third Normal Form, foreign keys, the whole shebang.
However, data production is still a mix of batch and sql systems.
Later when we needed a bit more control over search, we started using Elasticsearch. The way we do it now is to perform joins and get a nice serialized object with all the possible search/filter/sort fields, and put it in ES. All the data still remains in postgres, though.
Zuckerberg may have had to rely on a typical LAMP stack but FB's newer user-facing features still use SQL. For example, the Timeline feature released in 2012 was built on MySQL/InnoDB: https://www.facebook.com/note.php?note_id=10150468255628920
And last year Facebook released MyRocks, which is a space/write optimized replacement for InnoDB, and is being used for their "user database tier" https://code.facebook.com/posts/190251048047090/myrocks-a-sp...
I guess the things I've read about FB using NoSQL [1] is for other parts of their infrastructure, particularly Messages. It sounds like they had considered using MySQL, even though Cassandra was built for the Inbox feature. They ended up building a new system for Messages [2].
Now that I've spent a little time in that rabbit hole, it looks like one answer to your question is Facebook's Inbox Search, which used MySQL originally to store inbox data (7TB for over 100M users, which seems laughably small with respect to Facebook's scale today):
http://docs.datastax.com/en/articles/cassandra/cassandrathen...
> Before launching the Inbox Search application we had to index 7TB of inbox data for over 100M users, then stored in our MySQL[1] infrastructure, and load it into the Cassandra system. The whole process involved running Map/Reduce[7] jobs against the MySQL data files, indexing them and then storing the reverse-index in Cassandra. The M/R process actually behaves as the client of Cassandra. We exposed some background channels for the M/R process to aggregate the reverse index per user and send over the serialized data over to the Cassandra instance, to avoid the serialization/deserialization overhead. This way the Cassandra instance is only bottlenecked by network bandwidth.
The acknowledgments section makes it more clear that the MySQL data was indeed migrated over to Cassandra:
> Cassandra system has benefited greatly from feedback from many individuals within Facebook. In addition we thank Karthik Ranganathan who indexed all the existing data in MySQL and moved it into Cassandra for our first production deployment.
[1] https://news.ycombinator.com/item?id=7891316
[2] https://www.facebook.com/notes/facebook-engineering/the-unde...
Not only is it fairly fast, but it lets us tie JSON blobs that really don't need schema enforcement to be still tagged with the rest of the data and laid out relationally to still allow complex queries.