3 ms·

> Um, WTF? Can you flesh this out at all? Ha, sure. Let's say that you have a very-basic NoSql schema for a chat app: chat_app/person/<person id>/msgs/<chat

by er4hn 5y ago

> Um, WTF? Can you flesh this out at all? Ha, sure. Let's say that you have a very-basic NoSql schema for a chat app: chat_app/person/<person id>/msgs/<chat documents> so for each <person id> you stick chat messages in the <chat documents> separated by date or something. Conversations with different people end up in different <person id> paths. Chats have metadata like timestamps in addition to the message content You can also represent this as a SQL table with something like: Person_Id (uint) | Timestamp (timestamp) | Message (string) Now the classic argument for NoSql is that NoSql is the best representation for this since you don't really care about doing complicated queries over the chats. You just want to be able to index in and get a persons chats. Maybe sort by the most recent chats so the <chat documents> have the timestamp in the key. That's what I mean. Where I get frustrated with NoSql is that you inevitably end up needing to redo a lot of SQL logic anyways. A basic example is mapping the person_id -> visible name. The NoSql way is to have a meta file under <person id>, like chat_app/person/<person id>/<meta file> where you would have the user name under there. Then you can look up easily what to display when showing chats for <person id>. But then you add group chats. Now you need to also have a group chat section and you need to go back to the person_id path to resolve the visible names. Having a relational table with: Person Id | Visible Name seems like a more elegant solution to me. Once you need to start gathering stats on the data as well, relational tables become more easy to gather information on how often you are talking to various people or lengths of conversations with people. Most of the functionality for statistical gathering and other use cases can be replicated in NoSql but it involves extra effort that SQL just gives you.

lmm 5y ago
Sometimes data is relational, sure. But even in SQL-land you generally end up wanting to denormalize it for performance. I guess "optimized for not doing joins" is sort of true, but even on an SQL database that's something you optimize for in your live dataflow. Meanwhile not being able to have collection columns or sum types is a much bigger and more common data modelling problem in my experience - yes PostgreSQL has JSON columns but they're non-standard, not supported by all the drivers, not compatible with your in-memory test database, and ultimately still fairly clunky. > Once you need to start gathering stats on the data as well, relational tables become more easy to gather information on how often you are talking to various people or lengths of conversations with people. Most of the functionality for statistical gathering and other use cases can be replicated in NoSql but it involves extra effort that SQL just gives you. Now this I do sort of agree with - the one thing SQL databases are good at is semi-ad-hoc statistics/reporting/aggregation. Several systems I've worked on had an ETL pipeline that ended with dumping everything in an SQL or SQL-like datastore for the data folks to play around with. But you can't do that kind of querying in your primary live datastore once you have any sort of scale - at best you'll slow down your main system, quite possible you'll deadlock or worse. So even when I've worked on systems that were all-SQL, we ended up with much the same architecture of an ETL pipeline dumping data out of the primary datastore into a separate reporting datastore.

by er4hn 5y ago

lmm 5y ago

Sometimes data is relational, sure. But even in SQL-land you generally end up wanting to denormalize it for performance. I guess "optimized for not doing joins" is sort of true, but even on an SQL database that's something you optimize for in your live dataflow. Meanwhile not being able to have collection columns or sum types is a much bigger and more common data modelling problem in my experience - yes PostgreSQL has JSON columns but they're non-standard, not supported by all the drivers, not compatible with your in-memory test database, and ultimately still fairly clunky. > Once you need to start gathering stats on the data as well, relational tables become more easy to gather information on how often you are talking to various people or lengths of conversations with people. Most of the functionality for statistical gathering and other use cases can be replicated in NoSql but it involves extra effort that SQL just gives you. Now this I do sort of agree with - the one thing SQL databases are good at is semi-ad-hoc statistics/reporting/aggregation. Several systems I've worked on had an ETL pipeline that ended with dumping everything in an SQL or SQL-like datastore for the data folks to play around with. But you can't do that kind of querying in your primary live datastore once you have any sort of scale - at best you'll slow down your main system, quite possible you'll deadlock or worse. So even when I've worked on systems that were all-SQL, we ended up with much the same architecture of an ETL pipeline dumping data out of the primary datastore into a separate reporting datastore.