5 ms·

Mongodb can be quite a nightmare once you start requiring anything more than a 1:1 relationship, which is pretty much any kind of app that is doing anything mea

Mongodb can be quite a nightmare once you start requiring anything more than a 1:1 relationship, which is pretty much any kind of app that is doing anything meaningful. Having to resort to doing things like map/reduce for a simple group by / order is not the way to go IMO. I think you later truly realize the beauty of SQL once you get far down that rabbit hole. Initially I rode on the Mongo's NoSQL bandwagon when I saw that you can just save a JSON hash and thought that's the coolest thing in the world. But ever since I tried out Postgres's JSONB, I just can't go back to Mongo anymore. With Postgres, I have the best of both worlds, performance, relational data, and reliability. I don't have to sacrifice any of it. Also, I don't know who codes using raw SQL, it's been years languages have had ORMs that made queries look just like a Mongo query. Also, for anything else, like a super simple requirement of saving data (JSON), Firebase have fit that role perfectly. Mongo is starting to look like it's out of place in the eco system.

metheus 9y ago
> Having to resort to doing things like map/reduce for a simple group by / order is not the way to go IMO. Perhaps you are unaware, but that is a straw-man. That’s not how you’re supposed to with MongoDB. You’d use the aggregation framework: db.sales.aggregate( [ { $group : { _id : null, totalPrice: { $sum: { $multiply: [ "$price", "$quantity" ] } }, averageQuantity: { $avg: "$quantity" }, count: { $sum: 1 } } } ] ) https://docs.mongodb.com/manual/reference/operator/aggregation/group/ https://docs.mongodb.com/manual/reference/operator/aggregati... https://docs.mongodb.com/manual/reference/operator/aggregation/sort/#pipe._S_sort https://docs.mongodb.com/manual/reference/operator/aggregati... This has been the case since MongoDB 2.0, in 2012.
- spamizbad 9y ago
  Aggregates carry with them a memory limit. We currently use MongoDB 3.4. It's definitely much improved. The replication protocol that came along with 3.4 has been very reassuring. But aggregate queries really do not give you the power of SQL. They are great for transforming stuff for a report, but I would avoid using them for anything else unless 1) it can be cached (and therefor properly invalidated) and 2) doesn't need to be "real time" Being a document, schema-less database, your "quality of life" as you scale with MongoDB is going to be heavily dependent on how you structure your documents and the types of their fields. Are you treating your collections like SQL tables? Have any many-to-many relationships in hot code paths? Welcome to hell. Its type system is also limited compared to modern SQL DBs. Storing IP addresses, and want to query them based on a given CIDR range? Postgres makes this eas, MongoDB has you writing code that does sub-queries.
  - metheus 9y ago
    >Aggregates carry with them a memory limit. I think you are referring to the 100MB RAM limit, but that’s not a hard limit, it’s more of a bad default. The `allowDiskUse` option lets MongoDB write intermediate results to the disk (which is exactly what SQL databases are doing). > But aggregate queries really do not give you the power of SQL. They are great for transforming stuff for a report, but I would avoid using them for anything else unless 1) it can be cached (and therefor properly invalidated) and 2) doesn't need to be "real time" I really don’t see a difference between what you can do with MongoDB and SQL. I can’t say much more without knowing specifically what impediments you have in mind, but I would certainly like to hear more. For example, why do you cite results caching and lack of real-time requirements? > Being a document, schema-less database, I guess if you’re on 3.4 you can’t take advantage of JSON Schema yet, but keep that in mind as a part of your upgrade plans. In the meanwhile you can still use document validation? > your "quality of life" as you scale with MongoDB is going to be heavily dependent on how you structure your documents and the types of their fields. This is completely true, but couldn’t we say that just as much about any database? I’d put money on there being way more grief out there over bad tabular schema than over bad document schema. I mean, who’s worked on large-scale systems that hasn’t put off implementing great ideas, or had to hack up app code to compensate for a restrictive schema, because you can’t take the pain of ALTER TABLE? > Are you treating your collections like SQL tables? Ouch. Please don't! > Have any many-to-many relationships in hot code paths? Welcome to hell. That's probably fair, but if you're in hell to a vastly greater than you would be with Postgres, I'm pretty sure that's a modeling problem. Again, can you tell me more about the particular example? > Its type system is also limited compared to modern SQL DBs. Storing IP addresses, and want to query them based on a given CIDR range? Postgres makes this eas, MongoDB has you writing code that does sub-queries That's 100% legit. MongoDB needs to do a ton better with that... types rule.
    - spamizbad 9y ago
      > I think you are referring to the 100MB RAM limit, but that’s not a hard limit, it’s more of a bad default. The `allowDiskUse` option lets MongoDB write intermediate results to the disk (which is exactly what SQL databases are doing). While technically true, this isn't an apples-to-apples comparison. MongoDB's memory limit, as best I can tell -- and please correct me if I'm wrong -- is based on the contents of all documents and operations in the pipeline. Documents in MongoDB tend to be larger, so you can run into that limit faster than one might anticipate. In Postgres, you have the equivalent of "work_mem". It defaults to 4MB, but most production installation will bump this up. Regardless, this limit is per operation (a join, a sort, etc), not per query. And often times the operation is against a specific fields, as opposed to the entirety of the record contents. > I really don’t see a difference between what you can do with MongoDB and SQL. I can’t say much more without knowing specifically what impediments you have in mind, but I would certainly like to hear more. For example, why do you cite results caching and lack of real-time requirements? This might be my own hangups or just us fighting our own specific problems, but I've never been happy with the latency I see come out of the aggregate pipelines we've created. I also don't like what they do to the server's memory.
- takeda 9y ago
  > db.sales.aggregate( [ { $group : { _id : null, totalPrice: { $sum: { $multiply: [ "$price", "$quantity" ] } }, averageQuantity: { $avg: "$quantity" }, count: { $sum: 1 } } } ] ) Now look at SQL version of it and tell me which one is easier: SELECT SUM(price * quantity), AVG(quantity), COUNT(*) FROM sales;
  - sethgecko 9y ago
    Am I the only one that finds it ridiculous that the way to query for data in SQL is by passing a string?
nugator 9y ago
Do you have an ORM that can translate to good PG queries using JSON/JSONB functions? I would LOVE that in Java/on the JVM.
overcast 9y ago
"Also, I don't know who codes using raw SQL, it's been years languages have had ORMs that made queries look just like a Mongo query." Baloney. Anything relatively complex will definitely require you to optimize in raw SQL. Abstracting all that out in an ORM is horrible.

metheus 9y ago

> Having to resort to doing things like map/reduce for a simple group by / order is not the way to go IMO. Perhaps you are unaware, but that is a straw-man. That’s not how you’re supposed to with MongoDB. You’d use the aggregation framework: db.sales.aggregate( [ { $group : { _id : null, totalPrice: { $sum: { $multiply: [ "$price", "$quantity" ] } }, averageQuantity: { $avg: "$quantity" }, count: { $sum: 1 } } } ] ) https://docs.mongodb.com/manual/reference/operator/aggregation/group/ https://docs.mongodb.com/manual/reference/operator/aggregati... https://docs.mongodb.com/manual/reference/operator/aggregation/sort/#pipe._S_sort https://docs.mongodb.com/manual/reference/operator/aggregati... This has been the case since MongoDB 2.0, in 2012.

spamizbad 9y ago
Aggregates carry with them a memory limit. We currently use MongoDB 3.4. It's definitely much improved. The replication protocol that came along with 3.4 has been very reassuring. But aggregate queries really do not give you the power of SQL. They are great for transforming stuff for a report, but I would avoid using them for anything else unless 1) it can be cached (and therefor properly invalidated) and 2) doesn't need to be "real time" Being a document, schema-less database, your "quality of life" as you scale with MongoDB is going to be heavily dependent on how you structure your documents and the types of their fields. Are you treating your collections like SQL tables? Have any many-to-many relationships in hot code paths? Welcome to hell. Its type system is also limited compared to modern SQL DBs. Storing IP addresses, and want to query them based on a given CIDR range? Postgres makes this eas, MongoDB has you writing code that does sub-queries.
- metheus 9y ago
  >Aggregates carry with them a memory limit. I think you are referring to the 100MB RAM limit, but that’s not a hard limit, it’s more of a bad default. The `allowDiskUse` option lets MongoDB write intermediate results to the disk (which is exactly what SQL databases are doing). > But aggregate queries really do not give you the power of SQL. They are great for transforming stuff for a report, but I would avoid using them for anything else unless 1) it can be cached (and therefor properly invalidated) and 2) doesn't need to be "real time" I really don’t see a difference between what you can do with MongoDB and SQL. I can’t say much more without knowing specifically what impediments you have in mind, but I would certainly like to hear more. For example, why do you cite results caching and lack of real-time requirements? > Being a document, schema-less database, I guess if you’re on 3.4 you can’t take advantage of JSON Schema yet, but keep that in mind as a part of your upgrade plans. In the meanwhile you can still use document validation? > your "quality of life" as you scale with MongoDB is going to be heavily dependent on how you structure your documents and the types of their fields. This is completely true, but couldn’t we say that just as much about any database? I’d put money on there being way more grief out there over bad tabular schema than over bad document schema. I mean, who’s worked on large-scale systems that hasn’t put off implementing great ideas, or had to hack up app code to compensate for a restrictive schema, because you can’t take the pain of ALTER TABLE? > Are you treating your collections like SQL tables? Ouch. Please don't! > Have any many-to-many relationships in hot code paths? Welcome to hell. That's probably fair, but if you're in hell to a vastly greater than you would be with Postgres, I'm pretty sure that's a modeling problem. Again, can you tell me more about the particular example? > Its type system is also limited compared to modern SQL DBs. Storing IP addresses, and want to query them based on a given CIDR range? Postgres makes this eas, MongoDB has you writing code that does sub-queries That's 100% legit. MongoDB needs to do a ton better with that... types rule.
  - spamizbad 9y ago
    > I think you are referring to the 100MB RAM limit, but that’s not a hard limit, it’s more of a bad default. The `allowDiskUse` option lets MongoDB write intermediate results to the disk (which is exactly what SQL databases are doing). While technically true, this isn't an apples-to-apples comparison. MongoDB's memory limit, as best I can tell -- and please correct me if I'm wrong -- is based on the contents of all documents and operations in the pipeline. Documents in MongoDB tend to be larger, so you can run into that limit faster than one might anticipate. In Postgres, you have the equivalent of "work_mem". It defaults to 4MB, but most production installation will bump this up. Regardless, this limit is per operation (a join, a sort, etc), not per query. And often times the operation is against a specific fields, as opposed to the entirety of the record contents. > I really don’t see a difference between what you can do with MongoDB and SQL. I can’t say much more without knowing specifically what impediments you have in mind, but I would certainly like to hear more. For example, why do you cite results caching and lack of real-time requirements? This might be my own hangups or just us fighting our own specific problems, but I've never been happy with the latency I see come out of the aggregate pipelines we've created. I also don't like what they do to the server's memory.
takeda 9y ago
> db.sales.aggregate( [ { $group : { _id : null, totalPrice: { $sum: { $multiply: [ "$price", "$quantity" ] } }, averageQuantity: { $avg: "$quantity" }, count: { $sum: 1 } } } ] ) Now look at SQL version of it and tell me which one is easier: SELECT SUM(price * quantity), AVG(quantity), COUNT(*) FROM sales;
- sethgecko 9y ago
  Am I the only one that finds it ridiculous that the way to query for data in SQL is by passing a string?

nugator 9y ago

Do you have an ORM that can translate to good PG queries using JSON/JSONB functions? I would LOVE that in Java/on the JVM.

overcast 9y ago

"Also, I don't know who codes using raw SQL, it's been years languages have had ORMs that made queries look just like a Mongo query." Baloney. Anything relatively complex will definitely require you to optimize in raw SQL. Abstracting all that out in an ORM is horrible.