Hacker News Reader

Comment by metheus | Hacker News Reader

> 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/aggregati...

This has been the case since MongoDB 2.0, in 2012.

View on HN

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.

>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.

> 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.

> 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;

Am I the only one that finds it ridiculous that the way to query for data in SQL is by passing a string?

Reply on news.ycombinator.com