3 ms·
It's an interesting debate. While I also feel that developers should know the underlying SQL, however all that stuff like joins, indexes etc. are actually very
by EGreg 13y ago
It's an interesting debate. While I also feel that developers should know the underlying SQL, however all that stuff like joins, indexes etc. are actually very hard to scale beyond one machine. MySQL cluster does attempt to do it automatically, but even it has limits, and places most stuff in memory. In short, if I was looking for developers to do sharding, I would actually prefer to AVOID queries with joins, non-pk lookups etc.
Having said that, I have discovered a heuristic over the years: that if you are using an ORM, you probably don't want a relational database. You should learn something like Riak and let it handle the distribution and provide all the partitioning and availability for you. The CAP theorem shows that you can't get it all, and most likely you want to use one of those data stores instead of a relational one.
For regular sites that won't have millions of users constantly using it, though, a relational db is fine.
- acdha 13y ago“that if you are using an ORM, you probably don't want a relational database. You should learn something like Riak and let it handle the distribution and provide all the partitioning and availability for you” These are not the same concept: a relational database makes sense when your application relies on relations between records. If you need to do lots of joins across many records, Riak is going to perform horribly because it's designed for a different problem. CAP says nothing whatsoever about whether you want a relational or non-relational database, merely what tradeoffs you'll have to make to satisfy your business needs. Using an ORM doesn't factor into this discussion at all other than for providing a convenient place to implement whatever system you devise to meet those needs.
- EGreg 13y agoAs I said, it's a heuristic. If you find that you are telling your developers to use your ORM, then you probably should have gone with a NoSQL database like Riak. You can still do joins, etc. but it's in the context of things like map-reduce, and it makes sure that you can scale despite the joins. MySQL way: SELECT * FROM a JOIN b ON x WHERE y NoSQL way: 1) SELECT * FROM a WHERE x 2) Perform join in app layer or stored procedure. Like it or not, when you scale you will lose one of the CAP, and NoSQL databases do the hard task of delivering an eventually consistent data store to you and letting you express yourself in the RIGHT context, which is not SQL.
- acdha 13y agoYou're still conflating different parts of the stack: an ORM has nothing to do with CAP. > You can still do joins, etc. but it's in the context of things like map-reduce, and it makes sure that you can scale despite the joins. Either of your examples are commonly implemented in SQL databases, too: this is a routine MySQL optimization to avoid subselects and, amusingly, one which an ORM makes significantly easier to implement: SELECT * FROM a WHERE x; SELECT * FROM b WHERE pk IN (…list of IDs from first query…); Again, the SQL vs. NoSQL question is about your data model and access patterns, not whether you use an ORM or magical thinking about CAP. The line between the two has become quite blurry since there are things like MySQL-backed key-value stores or Postgres extensions which allow it to handle document-store workloads without losing performance or giving up the ability to do flexible queries. This isn't a question of religion: it's just looking at your business, assessing how well you know the access patterns (SQL systems are generally more flexible) and performance requirements and picking the best solution. Anyone claiming to have a right answer for everyone is wrong. > Like it or not, when you scale you will lose one of the CAP, and NoSQL databases do the hard task of delivering an eventually consistent data store to you and letting you express yourself in the RIGHT context, which is not SQL. You've now gone from wrong to very dangerously wrong: there is no scale which is immune to CAP and NoSQL has no magic for avoiding this. Eventual consistency is only appropriate for some problems and, as above, can be implemented on either system. No matter what storage system you choose you're still going to have to make careful decisions about business priorities and test carefully.