Why I Migrated Away From MongoDB • Hacker News Board

Re: Why I Migrated Away From MongoDB

#51

Post by bthomas » Mon, Sep 17, 2012, 5:53 PM UTC

aneth4 wrote: post #23

stevencorona wrote: post #10

The downside, or challenge, with NoSQL (generally speaking) is that you need to handle your aggregations ahead of time - you need to know what queries you'll want to run in the future when you store your data. If you have some new aggregation you want to keep, you'll need to re-process the data (with Hadoop or something else). It's the trade-off of being able to scale reads and writes horizontally. And unless you nee…

This is the opposite of "agile." It is difficult to know where your product will be in 2 months let alone 12, so it seems the advice to use SQL first is sound - unless you enjoy long distractions to solve simple JOINs.

If you are refactoring your app enough to need a different class of joins, you'll probably need schema and data migrations too.

I think no-schema fits agile quite well. For rapid prototyping, I prefer Mongo to even sqlite.

Re: Why I Migrated Away From MongoDB

#52

Post by gaius » Mon, Sep 17, 2012, 5:55 PM UTC

rbranson wrote: post #46

stickfigure wrote: post #43

Earlier quoted context omitted.

it's best just to start with a SQL database and go from there. This is bad advice. It's best to understand your problem domain and use the tools that are most appropriate. You see a lot of two types of posts on HN: * "I picked a NoSQL database for a problem domain with a better relational fit." Those posts look like this one. * "I picked an RDBMS for a problem domain with a better NoSQL fit." Those posts are usually…

Given the context of the OP, a brand new project or startup, my advice still stands true.

There's maths behind RDBMS - the relational algebra and calculus. These give you enormous power when you don't know how you want to query your data in future (something you'd think the "agile" crowd would be all over). But instead, the prevalent thinking is "just stuff it all in and worry about it later". Experienced DB hands call this "painting yourself into a corner".

Re: Why I Migrated Away From MongoDB

#53

Post by sureshv » Mon, Sep 17, 2012, 5:55 PM UTC

ericcholis wrote: post #37

lucasjans wrote: post #29

Earlier quoted context omitted.

Do you know why are case-insensitive searches not recommended? What's the realistic work-around?

I've found that a simple {"lastname":/cholis/i} works great. However, trying to do the same thing for a multi-key search isn't ideal. Specifically, searching for 3 words in a title using $and with multiple regex queries on a collection with 100,000+ documents took about 520 ms. The mongodb documentation suggests that you could have an array with your keywords, generated from the field you wish to search. Using indexe…

Building over a full text search engine that indexes your db is going to scale much better in the long run. Plus you get stemming and other niceties to boot as long as consistency is not of the highest priority.

Re: Why I Migrated Away From MongoDB

#54

Post by AlisdairO » Mon, Sep 17, 2012, 5:57 PM UTC

gregjor wrote: post #44

bunderbunder wrote: post #25

Earlier quoted context omitted.

Don't forget to read a book that's specific to your particular RDBMS. Because SQL databases are only trivially interchangeable for trivial cases. There have been more than a couple times when I was ready to blame the relational model, but further investigation revealed that the real root of the problem was that the existing schema or query used an approach that was optimized for one DBMS but performed terribly on the…

Wrong. SQL databases (except for SQLite) are almost completely interchangeable because they are all based on the same relational model and they all implement the ANSI SQL standard with only minor deviations. If you have a lot of stored procedures and triggers -- executable code embedded in the database -- you will have to rewrite that. Oracle is in a world of its own in a lot of ways, but if you are moving to or from…

Just in case you weren't already aware, the count(*) issue should be significantly improved for some queries in postgres 9.2, thanks to the addition of covering indexes.

Re: Why I Migrated Away From MongoDB

#55

Post by bunderbunder » Mon, Sep 17, 2012, 5:58 PM UTC

gregjor wrote: post #44

bunderbunder wrote: post #25

Earlier quoted context omitted.

Don't forget to read a book that's specific to your particular RDBMS. Because SQL databases are only trivially interchangeable for trivial cases. There have been more than a couple times when I was ready to blame the relational model, but further investigation revealed that the real root of the problem was that the existing schema or query used an approach that was optimized for one DBMS but performed terribly on the…

Wrong. SQL databases (except for SQLite) are almost completely interchangeable because they are all based on the same relational model and they all implement the ANSI SQL standard with only minor deviations. If you have a lot of stored procedures and triggers -- executable code embedded in the database -- you will have to rewrite that. Oracle is in a world of its own in a lot of ways, but if you are moving to or from…

SQL databases (except for SQLite) are almost completely interchangeable because they are all based on the same relational model and they all implement the ANSI SQL standard with only minor deviations.

They only all implement the ANSI SQL standard for certain values of "ANSI SQL standard." Those values being SQL-89 or perhaps SQL-99.

Beyond that it's a mess. SQL Server didn't get around to doing a really good job with window functions until this year. Last I checked, MySQL still doesn't do any number of useful things, such as WITH or FULL JOIN. Upserts and bulk inserting are different for every DBMS on which I've learned how to do them. And so on and so on. There are any number of ways you can get yourself coupled to a particular DBMS if you go beyond the core set of basic SQL features.

In other words, SQL databases are only trivially interchangeable for trivial cases.

Re: Why I Migrated Away From MongoDB

#56

Post by gaius » Mon, Sep 17, 2012, 5:59 PM UTC

gregjor wrote: post #44

bunderbunder wrote: post #25

Earlier quoted context omitted.

Don't forget to read a book that's specific to your particular RDBMS. Because SQL databases are only trivially interchangeable for trivial cases. There have been more than a couple times when I was ready to blame the relational model, but further investigation revealed that the real root of the problem was that the existing schema or query used an approach that was optimized for one DBMS but performed terribly on the…

Wrong. SQL databases (except for SQLite) are almost completely interchangeable because they are all based on the same relational model and they all implement the ANSI SQL standard with only minor deviations. If you have a lot of stored procedures and triggers -- executable code embedded in the database -- you will have to rewrite that. Oracle is in a world of its own in a lot of ways, but if you are moving to or from…

Not wrong. As a DBA with 15+ year experience, I can tell at a glance what an application was first developed against (Oracle, SQL Server, etc) or even where a dev started their career. You might as well say any OS is interchangeable as they're all just a kernel running processes.

Re: Why I Migrated Away From MongoDB

#57

Post by datasage » Mon, Sep 17, 2012, 6:01 PM UTC

jaimebuelta wrote: post #12

Mmm, not sure about some of the complains... - You can make case insensitive searches on the DB using regexes ( http://www.mongodb.org/display/DOCS/Advanced+Queries#Advance... ). A simple case-insensitive regex is not very bad performance-wise, but in general, case-insensitive searches should be avoided for search purposes (you can normalize to set everything to lower case or other equivalent trick) - The proper way…

- The proper way of doing an audit (and search later) is to make an independent collection with a reference to the other(s) document in a different collection. Then you can index by user, date, or any other field and leave the main collection alone. The described embedded access collection doesn't look very scalable.

I think this point is very important even in the RDBMS side. There are cases, even with relational datastores that would preform better if the dataset was built to the query.

The difficulty comes into play when you are trying to keep the denormalized data up to date based on changes within the base dataset.

Re: Why I Migrated Away From MongoDB

#58

Post by jt2190 » Mon, Sep 17, 2012, 6:01 PM UTC

gregjor wrote: post #47

stickfigure wrote: post #43

Earlier quoted context omitted.

it's best just to start with a SQL database and go from there. This is bad advice. It's best to understand your problem domain and use the tools that are most appropriate. You see a lot of two types of posts on HN: * "I picked a NoSQL database for a problem domain with a better relational fit." Those posts look like this one. * "I picked an RDBMS for a problem domain with a better NoSQL fit." Those posts are usually…

This advice -- the most frequent reply in this thread -- is the same as saying you should be able to accurately predict the future. Database management is a big and complicated topic. Especially with emerging tools like Mongo there is no way reading a book is going to give you the expertise and experience you need to choose the right tool. You do your best and maybe make the wrong decision. It's not stupid to go down…

  > This advice [Use the right tool for the right job]...
  > is the same as saying you should be able to accurately 
  > predict the future.

The future that's easy to predict here is the one where a person who doesn't know anything about the tools will need extra time to learn about them, and possibly extra time to switch tools once they've become more knowledgeable.

Re: Why I Migrated Away From MongoDB

#59

Post by sureshv » Mon, Sep 17, 2012, 6:03 PM UTC

lucasjans wrote: post #29

zemo wrote: post #26

Earlier quoted context omitted.

case-insensitive regex searches are supported.

Do you know why are case-insensitive searches not recommended? What's the realistic work-around?

The documentation mentions that prefix searches will be much faster than other searches. Most likely reason is that the index is mapped with some sort of prefix tree, perhaps a case-sensitive prefix tree.

Re: Why I Migrated Away From MongoDB

#60

Post by jamesli » Mon, Sep 17, 2012, 6:05 PM UTC

rbranson wrote: post #14

I'm no fan of MongoDB, but this same advice goes for any NoSQL data store. I am an Apache Cassandra contributor and community MVP, but my advice stays the same: it's best just to start with a SQL database and go from there. Read some books and learn it well: the "SQL Cookbook" from O'Reilly is great, and so is "The Art of SQL." Premature optimization continues to be the root of all evil.

I agree that relational databases are a safer bet after you study the business domains, consider the pros and cons of relational databases vs NoSQL, and there are no clear winners.

Disagree to the books recommended. SQL is only a query language, not the database itself. It definitely should be part of the consideration. Understanding how database engines works under the hood is more important in terms of performance in high-concurrency, high load scenarios.