Hacker News Reader

Comment by scarface74 | Hacker News Reader

I’m a big fan of Mongo for the use case you described - searching by ID and all information in one document.

But people don’t seem to understand that there are plenty of scenarios where you really either don’t know the schemes in advance and/or the “schema” is defined by an external source.

I worked for a company that sold software that allowed users to create forms that could be filled out either on the web or via a mobile app.

The user created the form and the schema and the indexes were created on the fly - one collection per type of form. What would an RDMS have bought us?

View on HN

PostgreSQL's JSON columns are pretty powerful

And if you create a table with a single ID column and a single JSON column you’ve essentially re-invented a NoSQL database. But I guess you can pretend it isn’t.

And it’s a lot worse and the tooling isn’t as robust...

And the tooling around it? For instance the Mongo C# Linq driver can translate LINQ to MongoQuery.

I haven't used myself but have heard good things: http://jasperfx.github.io/marten/documentation/documents/que...

I've been using this in production for around 1 year - it's an absolute dream to use!

For context, I've previous experience with NHibernate, EF, EF Core, Dapper and some others from yesteryear - Marten is probably the best dev experience I've had from an ORM.

They added Json fields to mssql too, didn't make it a mongo killer.

There’s nothing stopping you from creating tables and indexes on the fly using SQL but it requires explicit commands.

> What would an RDBMS have brought us?

It would probably work using a forms table, fields table, submissions table, and values table.

And then what happens when they add a field to the form and the table already has a million rows? What happens when they decide that the numeric field should have strings?

It would probably work using a forms table, fields table, submissions table, and values table.

I didn’t ask “would it have worked”, I asked “what would have bought us”.

> And then what happens when they add a field to the form and the table already had a million rows?

Maybe I don’t follow. You would insert a row into the fields table. Millions of rows should be fine.

> I asked “what would have bought us”.

I don’t know. None of this matters really, as long as the service works.

You said doing a “create table on the fly”. So if they needed to add or modify a field, you would have to do an alter table.

Alter table is generally no big deal for any of the use cases that MongoDB is also able to handle.

On any good RDBMS, adding a nullable column to an existing table is an O(1) operation. This is the only option that's comparable to what's available in MongoDB, and it has the same performance characteristics.

On the great ones, adding a non-nullable column with a default value to an existing table is also an O(1) operation. The good-but-not-great ones, it's also O(N). (As always, you get what you pay for.) For MongoDB, wanting to do this would be unusual, but you would have the option of back-filling every record. It would be an O(N) operation, too. So, for this case, the characteristics of the RDBMS are no worse, and possibly better.

Adding a non-nullable column with no default is always O(N), but the fact that you're suggesting a document store as an alternative implies even more strongly that this is not the use case you're trying to cover. That said, if you did do it, it would also be O(N).

Converting a numeric column to a string column is always going to be O(N), yes. Whether or not that's the better option is something that's got to be decided in context. Basically, do you want to pay the cost of datatype conversion in one lump sum and then be done with it forevermore, or do you want to pay a small fee for datatype coalescing every time you access that field? There are good reasons to choose both options. However, all too often, the 2nd option is chosen for a very bad reason: Simply assuming that it's zero cost.

I don’t think we’re on the same page here. It’s all good.

EAV.

JSONB > EAV

What happens when you need to do something like "Select browser user agent from all users who filled forms for a particular set of clients after a given date." ?

This would fit in a single SQL query which is expected to perform reasonably well, with an unstructured database optimizing this query will take months of work.

    db.formEvent.aggregate([
        { $match: { createdAt: {$gte: ISODate("whenever")}}},
        { $group: { _id: "$custId", userAgents: { $addToSet: "$metadata.userAgent" } } },
        { $sort: { createdAt: -1 } }
    ])

Something like this? I'm not sure why this couldn't be an optimized query in mongo, but I'm also not sure why a query like this one needs to be optimized? This would run fast enough without needing indexes, and really fast with an index on a couple fields, but is a query like this run so frequently you need to have it be extremely optimized?

How so? In our case, meta data like the userid, browser agent, date entered, etc was always added to the object before it was stored and those fields were indexed. They are just name value pairs.

The point is that the query I mentioned requires joins. You can of course get the same information from key value pairs, it will just require a number of scans over all your data, which doesn't scale if you need the queries to be fast. On the RDBMS side, there has been more than three decades of research on optimizing patterns like this. You don't want to try and reinvent that.

If you can know for sure from the start that you'll never need queries like this, then of course something like Mongo will be awesome. But requirements change, hence this article.

You saw the part where I said that all the forms had different schemas and were in different collections? The RDMS equivalent would be all of the different types of forms would be in different tables and each user would have their own database. You would still have the same issue where you would have to query the database’s metadata to get all of the tables and programmatically join the data.

At another company where I worked where we used Postgres, we had a multitenant set up where each of our (large) customers had their own database. The issue would have been the same.

You would no more “scan over all of your data” with Mongo with indexed fields than you would with an RDMS with indexes.

MongoDB has supported some types of joins for years now, you can do quite a bit with MongoDB aggregation pipelines. https://docs.mongodb.com/manual/reference/operator/aggregati...

As requirements change, you do need to migrate your data into a schema that makes sense and that's regardless of whether you're using SQL or MongoDB.

Yes Mongo supports joins. But, I wouldn’t use them. Application servers scale much easier than database servers. You’re not getting any efficiency gains from doing server side joins over just reading documents from the left side and doing an “in” query with the ids from the right side. Assuming you are doing the equivalent of a left outer join.

In fact, if you are using C#. You could use the same LINQ syntax either way.

The OP just said it would be hard to do that query (regardless of performance) and I'm saying it's totally capable.

Reply on news.ycombinator.com