Goodbye MongoDB, Hello PostgreSQL

http://developer.olery.com/blog/goodbye-mongodb-hello-postgresql/

by YorickPeterse • 11 years ago

802 371 11 years ago

developer.olery.com

super_sloth• 11 years ago

Is there anybody here who has run MongoDB at moderate scale with good results?

As in a few terabytes of data, >10k ops/second territory.

I've been really disappointed with its reliability and performance in situations where I've been around that.

Ixiaus4• 11 years ago

I came into a company that was attempting to use MongoDB for their soft-real time time-series data (I would consider it between low and medium scale) and it was atrocious, in too many ways.

It doesn't scale without tremendous effort and implicit schemas are a very dangerous thing to introduce into your application, they're insidious, and require enormous diligence in the application to codify the schemas (I would only feel comfortable using a loose document store with something like Haskell in which I can model the schema with strong types).

I've encountered MongoDB in three different companies / products and expended much effort to immediately move away from it in every case. In each case, the solution (which has been different each time) was far more appropriate to what was needed.

There's a sad inclination by developers to pick "one ring to rule them all" tools and MongoDB I believe even sells itself that way. It is not.

ak217• 11 years ago

What were the more appropriate solutions?

dkhenry• 11 years ago

Yes. When implemented correctly Mongo can scale very well.

quackerhacker• 11 years ago

I've been using mongo on 39M+ records (tracking financial tick data) across 73 assets (collections) and my queries take anywhere between 2-3mins depending on complexity.

You can always run db.currentOp() in the mongo shell to see what process is taking forever as well.

Let me clarify why though, there methods of optimizing a query by adding another field, but since I have to traverse my records with the sort() cursor my queries take that long.

Thiz• 11 years ago

Jesus Christ!

I remember querying databases with 100 tables and millions of records in foxpro a century ago and it took less than a second.

What has happened to the world while I was in cryogenic state? Take me back to the nitrogen pool!

kyberias• 11 years ago

I don't get this. If any relational database choked with that small number of rows, it would be thrown away immediately. That's a couple of orders of magnitude too slow.

nasalgoat• 11 years ago

I did, for nearly three years. MongoDB was consistently the number one reason for site downtime.

misiti3780• 11 years ago

i think foursquare is, unless they have migrated off and did not publicized it

kstirman• 11 years ago

High scale public references and links to their presentations are maintained here: http://www.mongodb.com/mongodb-scale

I think scaling any system takes careful planning.

IMHO, this is an area where MongoDB has improved a lot in the past two years, especially with 3.0, but there is still a lot of work to do.

pizza2342• 11 years ago

There is a mistake in the article, due to the OP not knowing an arguably basic notion about MySQL.

when defining a field as int(11) you can just happily insert textual data and MySQL will try to convert it.

this is dependent on the SQL Mode, which is quite flexible. for example, the STRICT_ALL_TABLES will prevent strings to be inserted in INT fields:

mysql> create table example ( `number` int(11) not null );

mysql> insert into example (number) values ('wat'); Query OK, 1 row affected, 1 warning (0,00 sec)

mysql> SET sql_mode = 'STRICT_ALL_TABLES';

mysql> insert into example (number) values ('wat'); ERROR 1366 (HY000): Incorrect integer value: 'wat' for column 'number' at row 1

There are certainly advantages in choosing PostgreSQL over MySQL... this is just not one of them :-)

vogan42• 11 years ago

Perhaps the author was aware, but found it unacceptable that clients could choose their own sanity ?

solutionyogi• 11 years ago

Personally, I would not use a database which is 'lax' by default instead of 'strict'. What other choices have they made which I need to learn OR it will bite me big in Production?

zrail2• 11 years ago

Example: Inserting strings into a varchar() that are longer than the limit will silently truncate them.

Example: The TIMESTAMP type defaults to the current time instead of null.

There are more, but those are the two that have bitten me in the past (and no I do not use MySQL at all if I can help it)

pnathan• 11 years ago

In a previous life, the lax by default approach of MySQL bit us hard on a regular basis; a number of discrepancies vs. reasonable expectation were had. At the time I left, they were working on migrating toward Postgres, and reports suggest they are extremely happy with pg.

pizza2342• 11 years ago

It's not so easy.

MySQL has a large legacy of being used as a very-immediate-although-somewhat-toy database at its roots.

For example, in absolute terms, I would find much more troubling the usage of non-transactional tables, justified by meaningless microbenchmarks, which has been somewhat common for some time.

Nowadays MySQL is definitely reliable, and it has a much more expert surrounding culture than the past, so if a person/company is willing to put time and knowledge, it's a reasonable choice.

I don't find automatic conversion so damaging that people should stay away just because it's default.

When you reach some level, you definitely need to have a relatively intimate knowledge of your tools, and at such point, one is far from the "defaults".

beering• 11 years ago

You can subtract two DATETIME columns in MySQL and it will not generate any warnings. It will give you an answer that probably is within the ballpark of the actual time difference but isn't the actual time difference.

spitfire• 11 years ago

If you feed MYSQL an invalid date for a date/timestamp field it will quietly insert 0000-00-00 and produce a warning, not an error.

imron• 11 years ago

utf8 is not really utf8 and doesn't support 4 byte characters that exist outside the BMP (you'll need the separate utf8mb4 type for that, assuming you're not running on a legacy version that doesn't support that either).

morgo3• 11 years ago

This is indeed the case. To clarify a little:

- STRICT_TRANS_TABLES is on by default for "new installations" starting from MySQL 5.6 (2013). What this means is that the bundled config files all turn it on.

- Starting from MySQL 5.7, it is a compiled default, along with several other more "strict" options. Effectively making it default to ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER.

I have some sample configuration files to make 5.6 behave like 5.7's strictness. For example: https://github.com/morgo/mysql-compatibility-config/blob/mas...

It does make upgrades harder, so some applications may also need to use a whitelist/blacklist approach to transition: http://www.tocker.ca/2014/09/01/suggestions-for-transitionin...

YorickPeterse10• 11 years ago

When buying a car, would you prefer for it to be safe by default or only after you've enabled half a dozen features such as "air bags" and "seatbelts"?

IgorPartola6• 11 years ago

Another way of handling this is defining a schema in your models. For example, Mongoid, a popular MongoDB ODM for Ruby, lets you do just that. However, when defining a schema using such tools one should wonder why they aren’t defining the schema in the database itself.

Bah. It's like they didn't know that schema-free data stores mean "there is no schema; different objects may have different fields". This is the whole point of MongoDB: you assume the responsibility of managing the schema. That's a "feature": you get greater flexibility by assuming more responsibility. Whether it's a useful feature, I won't say.

Their second reason is much more valid: MongoDB is not consistent/durable, all that. It's good for a cache, but not for long term data.

Here's a third reason I'd give against it as your primary data store: it's expensive. You have to keep your entire dataset in RAM, but that's not always necessary. My favorite example is from Foursquare. They had every single check-in ever in MongoDB in RAM. That's absolutely unnecessary, and quite silly to do so. Old check-ins are archived data. You don't need them. No user ever wants to know when/where they checked in three years ago. This is why at the time they were paying for 68 GB RAM Amazon boxes instead of 4-8 GB boxes. (I have no idea what they do now. I remember chatting with them on HN after a catastrophic out of memory failure when they filled up the entire 68 GB's).

colinhowe2• 11 years ago

We have a dataset much larger than RAM (600gb vs 60gb RAM). It's the working set that really matters. Accessing data outside the working set can be slow - unless you have SSDs :)

Regarding consistent/durable: during the past four years, we've not had any problems on this front that weren't caused by us in. We've had an issue that was a misconfiguration on our part where we allowed writes to a server because we pulled it out of a replica set. We also ran out of space on the logging volume once and that caused downtime - but, we didn't have log rotation or anything setup.

In general, we've found the failover very reliable and new primaries have come online without any problems.

That said, schemaless is both a blessing and a curse. Now that Postgres and MySQL have online alter built-in I'd possibly choose one of them if we were starting everything again.

IgorPartola6• 11 years ago

Well, SSD's aren't nearly as fast as RAM, even today.

I think we are in agreement about the working data vs all data. I am saying that in most applications your working dataset is much smaller than your total data set. So why pay for hardware capable of holding your entire dataset in RAM when you don't need it?

I am surprised you are able to do this with Mongo. Last I checked, it simply did not handle this case, and started failing miserably if it was not able to fit all data into RAM.

Re: durability: I am not talking about the server going down, coming back up. I am talking about whether there is an fsync() when writing data. Set up a test case where you are writing data very rapidly to MongoDB, then pull the plug on the box it's running on. It'll come back up, but the data it told you it just wrote won't be there because it didn't checkpoint. Did you check that all your writes succeeded when you had node failures in your cluster? Most applications don't have the machinery to do this because generally the state necessary to check this is stored in the database, yet it's the database you are testing. The only way to test this is to also write logs (also atomically), and then verify DB data against logs. Or, just use a database that guarantees durability.

Re: consistency: MongoDB doesn't support transactions [1]. That's enough to exclude it from a large number of applications. Anything to do with money, for example, is out since you really don't want double spending to be a thing.

[1] http://docs.mongodb.org/manual/core/write-operations-atomici...

jrochkind13• 11 years ago

This is the whole point of MongoDB: you assume the responsibility of managing the schema

Who/what is the 'you' there? Don't 'you' have the responsibility of managing the schema either way? It's a question of whether you want to manage the schema through an rdbms, or... just in your application logic, I guess?

IgorPartola6• 11 years ago

Manage as in write all the code that ensures adherence to the schema. If you say that the field called "score" is an int in a schema-ful DB, then insert an array, the DB will throw an error. If you do that in a schema-less DB, it will not unless you add a check yourself. If you are not using some type of unified DB access layer, you must perform this check every time you write a value. You must also perform the check every time you read a value, since someone else might have written something different to the DB while you weren't looking. This you take on doing much more work, yet gain flexibility and possibly some space savings (as in, you are not storing empty cells if you don't want to).

pico303• 11 years ago

I'm not a fan of MongoDB, but you don't know what you're talking about.

MongoDB is durable. While it doesn't quite support SQL transactions, it is durable. The data is journaled before being confirmed, and once confirmed will be written to disk. It can be consistent, but this sort of breaks the whole idea of scaling and distributing the load in Mongo, or causes massive performance problems, so that is something to consider when using it. Understanding and adopting eventual consistency is tough, but it's an issue with every distributed database, not just MongoDB.

And you don't "keep your entire data set in memory". You should make sure your indexes fit in memory. Your data can be as large as you like. Most people with terabytes of data in MongoDB don't keep terabytes of RAM in their servers.

Foursquare did NOT keep every single check-in in RAM. They kept an index of them in RAM, sure. But the problem was they had a sharded MongoDB deployment and one of the shards became unbalanced and exceeded the available RAM. If that happens (harder to do these days, but not impossible), it can be very difficult to recover.

MongoDB tries to bridge that gap between NoSQL and SQL. I think the MongoDB folks originally ignored decades of database research when developing MongoDB, but they've been forced to adopt it as the years have passed. Is it an Oracle killer? No. But it can be a useful and productive tool if you understand and apply it appropriately.

IgorPartola6• 11 years ago

Please read the first answer to http://stackoverflow.com/questions/18488209/does-mongodb-jou... before assuming that the MongoDB journal is durable.

It comes down to this: there is no way to tell MongoDB to commit a write and not return until it is durable. You can tell it to fsync() it within at most 33 ms or so from when it returns, but not immediately. There is no control in it to say "fsync() this value because it is important, then return".

That is not as durable as other database engines, which do this based on number of commits that have not been fsynced to the journal (this value can be set to 1 causing every commit to be fsynced).

Consistency is a large topic, and I am sorry I mentioned it vs something more specific, such as:

MongoDB doesn't have transactions, so you can't make multiple updates truly atomic. That's annoying at least, and unusable at most. Two phase commits let you get about 50% there, but with no rollback your application code has to implement rollbacks. In lots of scenarios your application is not smart enough to rollback a "transaction", so your data will be screwed.

Eventual consistency is starting to come under fire precisely because it avoids the hard problem of database design and is offloading it on the application developer, who is typically not as versed in how to create a system where temporary inconsistencies are OK. At best, it means that the developer is now wasting productivity on addressing consistency issues.

You may be right about Foursquare keeping only an index of all the check-ins, not the entire dataset. The main point remains: why keep essentially archived data in RAM? MongoDB does not let you keep a partial index.

MongoDB tries to give up basic datastore guarantees to gain speed and flexibility. Once again, I am not arguing that it's a useless tool. It is useful. As a cache. Or in cases where data loss is acceptable. It is much less useful in cases where dataloss is catastrophic, such as financial transactions. It will also be much more expensive at scale, whereas other datastores optimize for the conditions where RAM is expensive and disk is cheap. You can still run into this case when you are talking about a terabytes to petabytes of data.

bgentry3• 11 years ago

In the future we might also move our Rails applications over to Sequel, but considering Rails is so tightly coupled to ActiveRecord we’re not entirely sure yet if this is worth the time and effort.

Actually, with modern versions of Rails, using Sequel in place of ActiveRecord isn't bad at all. Nothing in Rails is really tied to ActiveRecord anymore. There are dependencies on ActiveModel, but you can easily make Sequel::Model objects conform to this interface. Sequel-rails helps with most of that: https://github.com/TalentBox/sequel-rails

It's really just the migration that's difficult, as that's tough to do piecemeal and requires good test coverage.

Here's a recent side project of mine that I switched from ActiveRecord to Sequel pretty quickly once I remembered how extremely limiting the querying capabilities of ActiveRecord can be: https://github.com/bgentry/portfolio-api

And if you really want to keep the option for schemaless data storage, Postgres can now do that with better performance than MongoDB, while keeping full indexing capabilities: http://blogs.enterprisedb.com/2014/09/24/postgres-outperform...

jmagoon• 11 years ago

I've switched about 50% of our internal applications and all of my side projects completely from ActiveRecord to Sequel and it's an absolute joy to to work with once it's fully migrated.

The main reason we started using it the first place was composite primary keys (not supported in AR, at all), and we've never looked back!

YorickPeterse10• 11 years ago

While certainly possible we simply haven't really evaluated it yet in depth. In between releasing a bunch of upcoming features and upgrading Rails from 3.2 to 4.2 I'd rather wait with _also_ moving from ActiveRecord to Sequel for the time being.

bgentry3• 11 years ago

yes, by all means upgrade yourself to the latest release of Rails before attempting to go "off the rails" :)

Tyguy7• 11 years ago

I did a similar migration last year. Loving postgres.

jgrahamc2• 11 years ago

As a greying developer I am most amused by people discovering that 'old' technologies like SQL databases work really well.

The only useful piece of advice I can give a younger developer is... be careful when drinking the newtech koolaid.

And one more thing:

    star = Sequel.lit('*')

    User.select(:locale)
      .select_append { count(star).as(:amount) }
      .select_append { ((count(star) / sum(count(star)).over) * 100.0).as(:percentage) }
      .group(:locale)
      .order(Sequel.desc(:percentage))

just makes me want to cry. Learn SQL rather than wrapping it.

serve_yay2• 11 years ago

Gosh, no kidding. (I sorta disagree about ORMs though -- if you're selecting by ID it's monkey work to write those queries, but anything complex, sure, use SQL)

macavity23• 11 years ago

Right. A good ORM should deal with all your common cases (if not, your schema probably needs improving), and it saves you writing a lot of boilerplate code.

For the edge cases where you need complex joins and/or extra performance, don't try and bother with the ORM, just drop to SQL.

silon3• 11 years ago

Agreed. Also it should be generating queries based on foreign keys/indexes (including joins).

Others should not be allowed in an interactive app anyway: developer should be warned/prevented using a "batch" query by accident.

JonnieCache2• 11 years ago

Sequel is the best ORM I've ever seen or used, but that is (probably IMO) not a good application of it.

It gives you total choice over what level of abstraction you want, so this is a particularly egregious use of it, being as nothing about that query is dynamic :)

You can use it purely to execute handwritten SQL queries loaded from files, or stored procedures, or any level of abstraction between raw SQL and the monstrosity you posted. The model layer is totally optional and is built cleanly on top of the query/relational layer, not inside of it.

The thing is a fucking work of art to be honest. The design shows impeccable taste, ruby's more exotic features are deployed only when really needed, everything is so clear, especially if youre used to activerecord.

BTW also the developer is super helpful and pops up everywhere to answer questions.

jonahx• 11 years ago

jeremy is incredible. he is more helpful and available (and certainly more knowledgeable) than most paid support people for premium products.

if you are learning sequel, stop by the irc channel and he'll probably answer your question.

yawniek• 11 years ago

sequel + postgresql is pretty much a dream team. i really miss it now that i am on a python stack.

Pxtl• 11 years ago

I use Entity Framework as an ORM and I have to say I like it. As much as there have been a lot of false-starts related to trying to reinvent SQL, I think Entity Framework hits many of the right notes for me:

1) All my code lives in Visual Studio with compile-time type-checking. No maintaining stored procedures outside of my main codebase, no mucking about with strings. And because Entity Framework puts the Select after the From clause, I even get good intellisense on column-names.

2) I can quickly shift code into being in-memory queries from database queries.

3) While it's a little muddy mixing SQL and C#, there are places where C#/EF's syntax is so much more brief and clean since it's hyper-aware of relationships. For example, instead of writing 2 joins to get the country of a user, I can say User.Centre.Country.

Of course, the Entity Framework is adding a layer of complexity to my work - you still have to know what's going on in SQL, and that mental overhead is a downside. But it pays off.

SQL is a brilliant language, and that's why it's stood the test of time in spite of its advanced age.

But seriously, it has a lot of bad flaws - it's often difficult to make reusable components in SQL like you can in other platforms.

The underlying relational algebra is brilliant. That's why I like ORMs - they admit that SQL is ideologically beautiful, but the SQL language itself could use some loving.

jgrahamc2• 11 years ago

Yes. I pretty much agree with you and am happy for people to use ORMs (or raw SQL if that's their thing and they are into maintaining it). It's actually the underlying 'throw away all this old SQL database stuff' that is the biggest loss for people because they miss out on the power and optimization that's gone into those databases.

snuxoll• 11 years ago

Entity Framework still generates pretty crappy looking SQL. It's nice to get a project going, but every time I try to look at why we are having performance issues in production and see the horrible query EF is generating I cry.

platz2• 11 years ago

EF has too much churn to be relied upon. They completely re-write it every few years. This time they are really taking an axe to the whole existing structure

emodendroket6• 11 years ago

I like EF well enough but if you don't know what you're doing it definitely makes it very easy to write code that works but runs really slowly when n is bigger than like, 10.

radicalbyte• 11 years ago

And because Entity Framework puts the Select after the From clause

I think that you're confusing LINQ with the ORM.

LINQ is a language feature, Language INtegrated Query. It operates on IEnumerable<T>. They introduced a new interface, IQueryable<T> which extends IEnumerable<T> with Expression. Basically it exposes your "query" as an Expression Tree - this can then be used by QueryProviders to do cool stuff like generate SQL.

A number of ORMs implement LINQ QueryProviders; Entity Framework, NHibernate, BLToolkit are all good examples.

EntityFramework, like a lot of fully-featured ORMs suffers from all of the traditional ORM problems: runtime performance is sub-optimal, it's query heavy and if you use the visual designer it encourages bad development practices.

On the other hand it allows people with very little knowledge to be productive; for small-scale projects the bang-for-buck is hard to compete against.

jv22222• 11 years ago

I agree, using ORMs and such hide the real SQL and make it much harder to optimize by using explains etc.

SnacksOnAPlane2• 11 years ago

Just have Rails dump the query and stick "EXPLAIN" before it.

ttty3• 11 years ago

I sincerely still prefer mongodb syntax because:

- Fits well with a programming language; {a: data.x} is better than 'WHERE A="' + data.x + '"' (sanitize?) or similar which are harder to read. SQL queries are good for direct input, mongodb queries styles are better to be used with a programming language;

aethertap2• 11 years ago

Your comment made me realize why I prefer to work in ORMs instead of raw SQL, even though I'm frequently frustrated by their limitations - it all comes down to composability.

The mongodb syntax looks like it supports composing statements much more readily than SQL. It makes me wonder if there's been any serious work done making a query language that's fully as general as SQL, but is designed to be safely and easily composable so that you can build queries up from parts.

Anyone know of such a thing? The mongodb approach of query-as-data-structure is as close as I've seen.

chc• 11 years ago

That is not how you do parameterized queries. With any civilized database library, it would be something along the lines of `"WHERE a = ? AND b = ?", data.x, data.y`, so that the parameters are like function parameters.

TylerE2• 11 years ago

If you're building queries in ANY language by concatenating strings you're doing it wrong.

In a sane language that query might look something like

cursor.execute("select field from tbl where long_obtuse_column_name=:foo and status != :status", foo=query, status='open')

All parameters are properly escaped by the api of course, so even if foo is "'; drop table audit; " you don't have any problems.

aikah• 11 years ago

If you really care about your data and you don't think a database should just be a bag where you put stuff but something that can be trusted , and efficient when it comes to storing data then you wouldn't be using MongoDB.

EpicEng• 11 years ago

Your users don't give two craps about the syntax. The technology choice is one of application requirements. If you chose Mongo over e.g. a SQL DB because you like your code to look pretty then I have to admit that I wouldn't trust your software at all.

coriny• 11 years ago

PostgreSQL is a very different beast to what it was 10 years ago.

So for a while it didn't support use cases as well as various NoSQL/NewSQL databases. However, PostgreSQL has also been built by great bunch of developers who are happy to adapt and implement the new, instead of harping on about the past.

So personally I adapt that advice to instead "use evidence to guide decisions".

baldfat• 11 years ago

The real issue isn't "newtech koolaid" it is the definition of the problem.

I find that people that don't understand the problem like MongoDB for the ability to be "flexible" in designing and modifying down the road. Though I like to argue you have flexibility in SQL also.

warble• 11 years ago

I agree, I've read a few 'screw Mongo, I'm going to SQL' type blogs, it really seems like they either didn't grasp how to architect Mongo correctly and tried to do things that don't work well with it, or they had a problem that was better solved by SQL in the first place.

SQL does many things very well that noSQL stuff won't. Also vice-versa.

theseoafs• 11 years ago

If the API surrounding the ORM is well-designed, using an SQL wrapper like this eliminates the possibility of SQL injection. There are arguments for not always doing things the same way.

DennisP• 11 years ago

Using sprocs or parameterized queries also prevents SQL injection.

api2• 11 years ago

Agreed, but it's important to understand what drove the adoption of NoSQL and schema-less stuff as well as the related trend of dynamic languages like Ruby and JavaScript.

(1) SQL server software itself was clunky, hard to scale, complex to deploy in a clustered fashion, and generally "old" in a bad way. This made NoSQL seem like a breath of fresh air.

(2) Startups! Ship now! MVP! Fail fast! The whole industry has been operating on this ship-fast-fail-fast way of doing things that prioritizes development speed and agility over correctness. That's because half the time what you are developing will be thrown away anyway -- it will fail, or you'll "pivot" and rip it all apart and almost start over. No point in static languages, SQL schemas, or other explicit design elements in that world.

#2 is I think the more significant of the two for things like schema-less MongoDB or dynamic languages. Once you get product/market fit, you typically end up taking the crap you wrote with no types and schemas and re-doing it in a "real language" with a "real database." But as they say in Startupistan, that's a "good problem to have."

pjmlp• 11 years ago

I imagine "whole industry" == Silicon Valley?

I am yet to do any project that doesn't use SQL servers for data storage.

dasil003• 11 years ago

Where this line of reason goes completely off the rails is in thinking that schema-less databases are a hedge for future uncertainty. Yes they let you churn out code slightly faster, but then your data becomes a ball of mud just as quickly as your code base, except the former is much much worse because when you pivot you still need your data even if you decide to chuck out all the code that goes with it.

In fact, a traditional RDBMS is designed to allow for any kind of ad-hoc querying you desire with reasonable performance, and the ability to normalize/denormalize, index and materialize views in order to optimize unforeseen use cases. The excuse of poor scalability is just a rationalization that some kids who didn't understand SQL used to justify using something that has a shiny marketing page that they ignorantly found more viscerally appealing. The tradeoff was all wrong, because 99.9% of projects will never need to scale beyond a single DB server, and for those that do, the flexibility of a well-defined schema and ad-hoc queryability will give them an early advantage in what kind of pivots are reasonable—if they are lucky enough to have to scale then they can replace the RDBMS with the context of knowing what exactly their use case that they must scale is. And at that point you'll presumably have the resources to do it, which is much more effective than prematurely attempting to design a scalable infrastructure and discovering that you didn't have the first clue what the real bottlenecks or even the full use case would be.

matwood• 11 years ago

I started with SQL in the 90s and completely agree with you. People who do not know SQL tend to reinvent it poorly in a language not designed to work with sets of data.

We have started using jOOQ and it strikes a great balance of raw sql/typed sql and result -> object mapping. I don't ever seen going back to a heavyweight ORM again. There is just too much magic and lost time spend figuring out performance issues for a minimal gain in ramp up for those who do not know SQL.

Sivart13• 11 years ago

I assure you that whoever wrote that code knows SQL.

The builder syntax is convenient because it allows queries to be composable without ugly string-smashing. For instance, you could take the object built out of that "User.select..." and pass it around into other functions that append extra 'where' clauses, etc.

joevandyk• 11 years ago

The advantage of this is that you can pass the relational dataset that's a result of this code around to other methods. Those methods can add additional filters, different ordering, etc. You can't do that with raw sql strings.

pdeuchler• 11 years ago

sigh

Not this argument again. I'm all for learning SQL (just like one should always learn foundational concepts), but for some reason there's this notion popping up that Real Programmers use Raw SQL, just like 20 years ago Real Programmers used Assembly. For the love of God, stop hating on basic software engineering principles like code-reuse and allow us to move up the abstraction hierarchy like we do in literally every other facet of software development. Here's a comment I made on the issue of ORMs vs Raw SQL a while back that goes into more detail: https://news.ycombinator.com/item?id=8134205

In the past year I've also grown very fond of type safety within my ORM coding as well, something that's hard to achieve at compile time with just raw SQL.

chousuke• 11 years ago

I may just be rehashing sibling arguments here, but to me that particular API looks very much what I think an SQL-wrapping library ought to be: a replacement for string concatenation and something that allows you to treat SQL queries as data. I don't know anything about Sequel, but that example still feels close enough to SQL.

My experience with ORMs has been that I eventually end up regretting using one if I try to model my data as objects because with ORMs it's easy to code yourself into a corner where you end up wishing that your design supported the relational model instead.

I do understand the desire for a good ORM, though. SQL is extremely powerful and a well-designed database is a joy to work with, but a sequence of tuples is often not the most convenient datastructure to process in most programming languages.

fennecfoxen• 11 years ago

In my experience, it's great to start with ORMs, and they help you go really fast, but if the project is important then sooner or later you're going to find a case where you need to go around it and write custom SQL (probably for performance reasons).

And that's fine, but it does mean that you should pick an ORM that plays nicely with that workflow (or otherwise design your ORM to be compatible with it). Back when I was working with Perl I found that you could do some pretty good stuff with RoseDB -- it could load objects from your custom queries and they'd still work like normal ORM objects (i.e. you can load related objects from them). I've missed that capability on several occasions since.

jjtheblunt• 11 years ago

Agreed. And one might note that there's a really good reason Oracle has dominated for decades, and Larry Ellison (who read the SQL research paper, and formed a company to implement it nearing 40 years ago) is a billionaire.

threeseed2• 11 years ago

Oracle's success is due to their aggressive expansion into business applications. Their database products have been taking a hammering as enterprises moved to data lakes and horizontally scaled databases like Casssandra.

Your argument would've made sense a few years ago but not now.

meistro• 11 years ago

You should learn SQL and understand relational databases. But using an abstraction (ORM) to cover 80% of the use cases is much better than writing tons of boilerplate SQL. http://java.dzone.com/articles/martin-fowler-orm-hate

clebio2• 11 years ago

Thank you. I searched to see if his article had been mentioned, and glad to find you did. The link he gives to Ted Neward's post is gone, but I found it in the Wayback Machine: https://web.archive.org/web/20141205230114/http://blogs.tedn...

Seems like this thread has gone down a very old, well worn rabbit hole.

evantahler• 11 years ago

Wait... does preferring to write SQL (and knowing how) make me old now?

Dang.

YorickPeterse10• 11 years ago

It's not so much about not wanting to write/understand SQL (both are still very much required), but about composability. If you want to re-use bits of a SQL query written as a string literal your only option is string concatention or using some kind of string builder/template system. In both cases there's little validation of the query's correctness (syntax wise) until you actually run it.

While I agree that many ORMs go too far or even worse, not implement certain powerful features, Sequel (and similar tools I imagine) strikes a nice balance. The particular examples I gave on their own are not super useful, but we already have quite a few queries that are composed/re-used without having to concat strings.

So tl;dr: it's about composability, not being "lazy" or "ignorant" to SQL.

bigpeopleareold5• 11 years ago

I once agreed with this, but now I don't. I just want to write SQL (dammit!).

I can never, ever remember the intricacies of the Sequel API or any one of these query builder APIs. I am always looking up something that is rather trivial because I am thinking in SQL, the language, and always have to convert back to Ruby or whatever language I am working in.

CTEs and SQL functions in PostgreSQL strike a good balance in the composibility department for me, while still being able to write SQL.

mbreese7• 11 years ago

How often do you really reuse bits of a SQL query? SQL is the language that an ORM will generate. The question is, how much work is done to avoid using SQL and is it worth it?

The only time that I've found ORMs useful for composing queries when I have to dynamically create a query at runtime based upon user input. And even in that case, today, I'd probably still just concatenate strings for a proper SQL statement.

jballanc2• 11 years ago

I'd argue it's less about composability, and more about laziness (in the computational sense). As pointed out elsewhere, if you have all of the information needed to generate a dynamic query, it's often not a huge leap from an ORM to composing strings (especially given how relatively flexible SQL syntax can be).

However, sometimes I want one part of my program to be responsible for one bit of a query, and a separate piece to be responsible for something else. To take a trivial example, say I want one object/function to be responsible for doing the right sorting, another to be responsible for any pagination, and yet another to be able to group results when needed. In that case, having a programmatic abstraction over a query (whether it be an object or a datatype, doesn't matter) can be very useful.

rikkus2• 11 years ago

Here's an example in C#. Imagine you're querying a database of products (here represented by integers). Users can enter filter parameters - you want to build your query dynamically based upon what they enter. With LINQ, you can do this kind of composing with no effort. You also get to run the same code on any kind of Queryable, so if you feel like doing some of the work in RAM and some using a DB, your query is usually going to be very similar, if not the same.

Yes, you still have to understand what you're querying against and how you should build your queries to make best use, but I'd much rather write this kind of code than try to concatenate SQL.

  IQueryable<int> Source()
  {
    return Enumerable.Range(0, int.MaxValue).AsQueryable();
  }

  class UserFilter
  {
    public bool? EvensOnly { get; set; }
    public int? Minimum { get; set; }
    public int? Maximum { get; set; }
  }

  IEnumerable<int> Search(IQueryable<int> source, int currentPage, int pageSize, UserFilter filter)
  {
    var result = source;

    if (filter.EvensOnly.HasValue && filter.EvensOnly.Value)
    {
      result = result.Where(i => i % 2 == 0);
    }
		
    if (filter.Minimum.HasValue)
    {
      result = result.Where(i => i >= filter.Minimum.Value);
    }
		
    if (filter.Maximum.HasValue)
    {
      result = result.Where(i => i <= filter.Maximum.Value);
    }
		
    return result.Skip(currentPage * pageSize).Take(pageSize);
  }
	
  void Main()
  {
    var currentPage = 4;
    var pageSize = 5;
		
    var filter = new UserFilter {
      EvensOnly = true,
      Minimum = 1000
    };
		
    var searchResults = Search(Source(), currentPage, pageSize, filter);
  }

Udik2• 11 years ago

The composability requirement only justifies itself when... your queries need composability.

I'm absolutely in favour of using a query builder when it avoids the need to fiddle with strings, but at the same time any decent programmer can write in half a day a query builder that satisfies the vast majority of the use cases.

So the question is: once you have covered those cases, do you need to use the query builder to write static queries? No. Do you need an extremely complex query builder to build extremely complex queries? Again, no. SQL is great for writing very readable and optimized static queries, which are almost all the SQL queries you need when you're not dealing with search forms.

Bottom line: use your favourite query builder or write a tiny one, but use it only when the resulting code is simpler than the plain SQL + string joining.

kiyoto• 11 years ago

I agree with this 100%. SQL works incredibly well for purely declarive, relatively simple operations. However, the moment you start doing "Do this, then that, then that", it starts to look (literally the appearance) daunting.

I myself come from the R/Python background, and my hypothesis is this difficulty around composability (especially for dplyr/pandas crowd) is what deters data scientists from writing more SQL.

gfody• 11 years ago

So tl;dr: it's about composability, not being "lazy" or "ignorant" to SQL.

If you want composability you should factor the common sql fragments out to parameterized views (table valued functions), or just regular views. Doing this across a large project can really improve performance as well.

frik• 11 years ago

Use an IDE like JetBrain's IDEA that checks the SQL syntax against the database.

bborud• 11 years ago

In both cases there's little validation of the query's correctness (syntax wise) until you actually run it.

Yes? So? When is this really a problem? Even if you do generate queries dynamically. When does this become such a big problem that it is worthwhile killing readability and bogging down the system with lots of extra complexity?

As for ORMs: I've done software for 25 years. I still have not seen a single example of ORMs resulting in better software. Not a single time.

stcredzero• 11 years ago

The lack of a schema may sound interesting, and in some cases it can certainly have its benefits. However, for many the usage of a schemaless storage engine leads to the problem of implicit schemas.

As another greying developer, one thing that was seen with ORMs were implicit schemas, often motivated as bureaucracy shortcuts.

So, you need a dictionary of configuration data for your user, but the bureaucratic overhead of adding that to your schema is too much? Why, then take your fancy OO serialization technology and save that little dictionary as a binary blob and stick it into just one new column in the database.

Problem solved...until years later when information in one of those blobs becomes tied (by a maintenance programmer who didn't entirely understand the system architecture) to what is effectively an implicit schema.

pbowyer• 11 years ago

And I thought I was the only one who'd encountered that...

igorgue• 11 years ago

The fact that you don't know what an ORM is is what makes me cry.

jakejake• 11 years ago

I think the problem is that NoSQL has been the cool new thing, which causes people to use it without really understanding why it is better/worse than other solutions.

Tools like MongoDB can do things that are extremely difficult or impossible with MySQL/PostgreSQL and they are a great choice for those situations. Using it simply out of laziness or misunderstanding, though, is probably going to create problems later.

scott_s2• 11 years ago

I agree with the commenters who argue for the value in a library that wraps strings that happen to be SQL statements, but none that I saw touched on this point: I think most users of such a library will have to know SQL. These libraries seem to have a one-to-one matching between concepts in them, and concepts in SQL. Hence, the point is not protecting developers from learning SQL (because you can't, for these needs), but in finding the least painful way of integrating SQL into the application logic.

drzaiusapelord• 11 years ago

As a greying developer

The problem with being a young person who wants to make his or her mark on the world is that if the people before you did an excellent job, you can only make things worse. In fact, I think this is one of our larger problems not only in technology but in society (see the recent move to extremism in many aspects of our political and religious life the people who grew up under more moderate times are pushing for).

Some things should be boring and stable. Build on top of those things. Redoing the foundation every six months is just sub-optimal. Accept that you may not make that big mark in the world, or if you do, it'll be in ways you completely did not expect, just like everyone else who made their mark.

JustSomeNobody2• 11 years ago

I agree with you. Also, we should be developing products that last. And be proud of that. Why does everything always have to be new?

izietto2• 11 years ago

This is a `negate` method in Arel (by Rails):

    class User < ActiveRecord::Base
      def self.negate(scope)
        where_values = scope.where_values

        scope.except(:where).where where_values.reduce(:and).not
      end

      def self.suspended
        where.not(confirmed: true).where(arel_table[:created_at].lt 2.weeks.ago)
      end
    end

    unsuspended_users = User.negate User.suspended

Do this in SQL. Composability is the real boost, and you have composabiliy when you don't have to build a string in order to interact with the db.

bgentry3• 11 years ago

unfortunately, where_values is also a private / unofficial API with no stability guarantees, so it can change at any time =/

bbcbasic• 11 years ago

In a compiled language like C#, "wrapping" SQL is a good idea, at the very least to catch bugs at compile time rather than runtime. Also makes it easier to switch from one DB provider to another if you need to.

You are less likely to suffer from SQL injection if you use at least some kind of wrapper, although a minimal wrapper that takes a SQL with placeholders and arguments would do for that.

My preferred way to wrap is to use EF with a dash of Linq, which is excellent and yields great readability and flexibility.

There is no way I am going to be willingly writing SQL statements in production code (unless I am forced to do so by some legacy problem). Worse case if the SQL is so complex that it is too much for Linq, then write a stored procedure.

Back in 2002 I had to write hand crafted SQL before mainstream ORMs and at the time I thought it sucked bad! I was looking for ways to generate the code from schema but didn't have time to go and back-fix all the legacy code ad-hoc SQL statments!

rjbwork• 11 years ago

I personally prefer SQL + Dapper instead of ORM stuff if it is Linq-level SQL, and sprocs w/ Dapper otherwise.

CWuestefeld2• 11 years ago

You are less likely to suffer from SQL injection if you use at least some kind of wrapper

Pretty much all of our coding guidelines on my team are just guidelines. The one absolute law is that all data going to the DB must be paramaterized, nothing goes in as string substitutions.

But virtually all of our DB access (like, at least 99%) is by stored proc anyway. We're just barely able to keep up with performance requirements by tuning things just right, and in my experience, EF isn't able to generate queries that are as efficient as we can by hand with some fiddling. I know that sounds like "I write in assembler, 'cause no compiler is as good as me", but that is what the reality seems like.

jgreen10• 11 years ago

SQL databases had their imminent death coming. They were stuck. However, PostgreSQL is under more active development than most other databases, SQL or NoSQL. It is new and cool technology.

In the end, whether you speak SQL or use NoSQL construct matters as much as whether you write a program in C or Pascal. You can have endless debates about form and function and you might want to see and identify with a winner, but in the end whether the tools and the community surrounding a technology fits people's needs is the only thing that really matters.

JustGotHere• 11 years ago

What I'd like to see is a universal SQL that can be translated to whatever dialect of SQL my current database is using. That way I won't have to relearn SQL every time I start a project with a different database engine.

tom9729• 11 years ago

You could use Teiid (https://github.com/teiid/teiid). The SQL dialect is similar to Postgres, and it has built-in translators to handle all of the popular relational databases (Postgres, Oracle, MySQL). Added bonus is that even some NoSQL databases are supported, and you can do things like join a table from a MySQL database against a collection in MongoDB. Full CRUD is supported for most translators.

If you're using Java (JDBC), there's an embedded kit so you don't have to run a standalone server. If you're not using Java, you can run the standalone server which also emulates the Postgres ODBC protocol so you can (for example) use `psql` to connect and run queries.

Animats• 11 years ago

Right. If you write SQL directly, you can also use commands such as EXPLAIN to see what the lookup strategy will be. If there's a full table scan of a large table involved, maybe you need a different query or a new index. If the SQL generation is hidden by some library, you can't do that.

Wikipedia runs on MySQL. They have replicated read-only copies of the database and ngnix caches which handle most read-type operations. Is your site busier than Wikipedia?

billrobertson42• 11 years ago

Relevant: https://twitter.com/stuartsierra/status/571386786238369796

dchest• 11 years ago

Stuart Sierra (@stuartsierra): "SQL is already a DSL"

mtanski2• 11 years ago

I would like to say lets step back and not conflate SQL and relational databases together. Clearly SQL as the language the primary way most people interact with relational database.

In my my mind SQL as a language is a huge PITA. First, parsing of complex statements is expensive (there's workloads where SQL parsing takes more time then processing the results).

Second, as SQL exists today (SQL2011) it's a large, complex language that's not implemented uniformly. So I understand why people want to build programmable interfaces for generating queries versus writing giant string statements that expand to SQL.

I personally would wish that there was an alternative language for interacting with relational databases that isn't SQL. Just expose the whole relational expression tree to users say ala S expressions. It's not like the relational engine is going to optimize / re-order it anyways.

I mean something along the lines of:

  [ GROUP_AGGREGATE,
    [ "name" ],
    [ [ SUM, "COUNT(*)" ],
      [ SUM, "cost" ]],
    [ JOIN, [ ==, "user_id", "customer_id"],
      [ FILTER, [
         [ &&, 
           [ >=, "age", "30" ],
           [ IN, "state", "NY", "CT", "NJ"]],
         [ TABLE, "customer" ]
      [ TABLE, "orders" ]]]]]

Is it more verbose, yes. But much easier to compose, parse and machine transform by software (code is data). Also, makes you think in terms of relational operations/expressions versus SQL the language.

jrapdx3• 11 years ago

Actually, some work on this has been done for CHICKEN Scheme in extensions "ssql" and "ssql-postgresql"[0]. I'm not too familiar with these particular eggs, but the flavor might be conveyed by this example:

    (ssql->sql #f '(select (columns (col actors name) 
                                    (count (col roles id)))
                     (from roles actors)
                     (where (= (col roles actor_id) (col actors id)))
                     (group (col actors name))))

translates to "SELECT actors.name, COUNT(roles.id) FROM roles, actors WHERE (roles.actor_id = actors.id) GROUP BY actors.name"

In the above sexpr, the SQL query is expressed as a Scheme/Lisp hierarchical list, which I think is a quite elegant notation. I'm sure other Scheme implementations provide variations on this theme of SQL generation. Using some macro magic, there would certainly be a lot of ways to organize it and probably it's not even all that hard to do.

[0] documentation at: http://wiki.call-cc.org/eggref/4/ssql

spydum• 11 years ago

i think there is a line to be drawn.. If the ORM is saving you no effort, I fully agree. However, I suspect most ORMs in the short term boost early productivity quite a bit.

It's like writing an app from scratch in assembly, versus C.. The compiler will do quite a bit of the dirty work for you, and if you look at it in a disassembler no doubt you could find tons of improvements to be made (errmmm depending on the compiler I suppose). Doesn't mean either one is the wrong way to go about it. Now if all you do is stuff ASM code in your C app, yeah.. Why bother?

collyw9• 11 years ago

Whenever I see someone shoehorning something into MongoDB or something else I ask the reasoning for not using a relational database. Its always something along the lines of "We don't do that now" or "schemaless" (which as the article points out means more work for the dev) something similar, with the "fast" added somewhere into the description.

The crazy thing is most of them are working on a single node, so no horizontal scaling. And they seem to think non-relational is the new way to do things.

serve_yay2• 11 years ago

It's very tiring to read things like this. And that is not meant as support for Mongo, quite the contrary in fact.

TheOtherHobbes• 11 years ago

It's interesting that if you search for MongoDB vs Postgres, you won't find any articles suggesting you should go from P to M.

Supposedly WiredTiger for M 3.0 will be more everything, including faster and better.

We'll see.

nmenglund• 11 years ago

Do anyone recognize the application used for the metrics screenshots?

infecto• 11 years ago

New Relic

YorickPeterse10• 11 years ago

Correct, this is taken from New Relic's APM service.

dcomartin2• 11 years ago

Beware of "The Problem Of Schemaless" can occur with a relational DB when you start defining columns as nullable. You end up with the same problem.

efuquen• 11 years ago

I wouldn't call it the same problem. Something nullable is similar to an Optional type (i.e. in scala Option[String] would map to a nullable varchar). That is not the same as being schemaless, you at least still have types that are being enforced, even if they might be empty.

I would agree that having too many nullable fields could indicate a problem with the schema, i.e. you should break things up into more tables.

dcomartin2• 11 years ago

Agree. I'll rephrase to "similar" problem.

hbbio• 11 years ago

Interesting to read both the article and comments. It's cool to hate Mongo nowadays but we use it successfully (for PEPS, open source: https://github.com/MLstate/PEPS).

The version of MongoDb used is not mentioned in the article. The changes between versions is a problem with Mongo but it improved much recently.

One problem clearly identified is the lack of model. There are solutions to this. For instance, with Opa (http://opalang.org) we use strong static typing to generate a database model and guarantee that the whole applications sticks to it. That leaves out model changes, but there are solutions for that.

Also, there is no need to have the whole collections in RAM, but clearly enough RAM for the "working set" helps.

In the end, Mongo is no magic so do SQL databases which have their share of problems too.

code_duck• 11 years ago

I had a business based on another company's API years ago, and they started changing a lot of their systems to work with mongo. The data lost a few useful points, like IDs and the speed and reliability didn't seem to be improved. My thought at the time it was, how about you guys just use postgresql? My comment on the mailing list about that seemed to be taken as an insult or naïveté. However, sure enough two or three years later they said they had all sorts of problems with Mongo and were switching to… Postgres.

neumino• 11 years ago

The problem of schemaless database mentioned in the article is then replaced with painful migrations.

The trend to go back to SQL databases now is fueled by the "same" poor reasons why people were going for NoSQL databases. You can't have everything...

bni• 11 years ago

I have found that the more schema you have, i.e. the more structure you have for your data, the easier migrations become, because there are no surprises.

limelight• 11 years ago

This post reflects an interesting technical narrative of companies switching off MongoDB to more traditional relational databases as they grow.

Importantly, I don't think that's an indictment of MongoDB. Instead, it highlights the key advantages of NoSQL: ease of use and rapid iteration.

When you're first working on a project, MongoDB is very easy to slap in. You don't even have to create tables/collections. As you iterate, you don't have to constantly be updating schemas as your product direction shifts. Thus, MongoDB is perfect for early startups.

As a company scales, of course it's warts start to show. The very attributes (like schemaless storage) which were an asset when just a few developers were hacking on the project become a drawback as the company grows in size. Reliability becomes more of a concern.

Naturally, people shift to a more traditional database. Personally, I'm almost ready to switch some of our core functionality from MongoDB to Postgres. This isn't an indictment of Mongo though.

MaggieL• 11 years ago

OP will be back in three years talking about migrating off of Ruby...if they make it that far :-)

YorickPeterse10• 11 years ago

Unlikely, we've been running on Ruby for about 5 years and it's the one thing we feel safe relying on for our serious internet business. Actually, our entire setup is Ruby (safe from some shell scripts here and there).

bau53• 11 years ago

No, this is bad. You shouldn't design a system that won't work from the outset. "Plan one to throw away" is about budgeting time, not about knowingly making big technical compromises when you write the first version.

matthewmacleod• 11 years ago

But these systems do work - it's just that they don't necessarily scal as well as others.

It's silly to expect that every single software project requires the same level of correctness or maintainability. A rapidly-developed speculative SaaS for example may be able to trade long-term maintainability or scalability for faster development time (often more important if you have no idea whether or not your idea will even work).

angersock• 11 years ago

Not quite--plan to throw away the internals, not the interface.

This lets you do things like writing shitty hyperlinear naive solutions to get all the pieces of a system in place, and then going back and optimizing each in turn. You don't spend a lot of time fussing over little details, and instead make rapid progress.

If you don't spend time on the interfaces between the pieces, though, you're absolutely screwed.

wainstead• 11 years ago

I had to look the quote up, because it didn't sound quite right.

"Plan to throw one away; you will anyway."

The quote is Fred Brooks from his book "Mythical Man Month." It seems to me the author's post reflects that quote. Granted they didn't throw the entirety of every application out, but arguably it's an entirely different application now. I would even wager there's months of refactorings ahead of them as they take advantage of Postgresql's features.

distantsounds• 11 years ago

Really, you can't spend the 10 minutes designing a table structure in a SQL database? And now you have to spend months re-inventing the wheel because you wanted an easy out?

This post reflects on developers being lazy, instead of doing it right the first time around. Oh no, you have to log in to the db and run a CREATE TABLE statement every few months when you need to scale. Cry some more.

And even then, 'lazy' is subjective. I don't find SQL that hard to implement. There's a library for practically every language you can think of. What's your excuse?

xyzzy123• 11 years ago

Actually, I think that's a great way to think about it: NoSQL is the "dynamic typing" of the database world.

Put another way, it's like "what? you couldn't spend 10 minutes declaring types everywhere?" - yeah, it's less robust, yet dynamically typed languages remain popular.

My excuse: When I'm just past the mock stage, and still playing with what UI functionality should be, sometimes I just want to get some JSON persisted. I'm changing the shape of the data a lot, discovering the schema as I build. The persistence is frankly a technical nuisance I wish I didn't have to think about.

Sometimes, the result is "good enough" and I don't need to go through the ceremony of glueing in an ORM.

ollysb• 11 years ago

You don't even need to design the schema, just add the bits you need as you go (updating the schema takes about 20 seconds with rails for instance).

AceJohnny2• 11 years ago

I think you're understating the cost of updating the structure of an in-use database.

rmrfrmrf2• 11 years ago

I guarantee you're not designing your databases correctly if they only take you 10 minutes and only a CREATE TABLE statement every few months.

aravan2• 11 years ago

Definitely not about spending 10 minutes on SQL Schema. It is all about maintenance. MongoDB is right use case, if you know what it will be there in Table.

rimantas• 11 years ago

In some cases you don't even have to log into anything, just type one line to create migration and another line to run it.

clay_to_n2• 11 years ago

I don't know about this, seems like a lot of technical debt to me.

Choosing mySQL over mongoDB to start with really can't add that much time to a project, especially if you're doing it in a language like Ruby. A couple hours extra here seems very worth it to avoid a multi-week migration process a couple years down the road.

imfletcher• 11 years ago

while i've not spent much time with the "nosql" products, its mostly because i've decided to stay SQL. The bottom line is that most humans think of data the same way SQL thinks about data. When you talk to people outside of engineering (i.e. mgmt or customers), they expect the product to be able to do things that SQL does naturally (normalized data for easy changes to things like 'user name', join and sort based on a variety of cross cutting properties, etc). You can always make both do the same thing eventually, but one will fight you more than the other....

buckbova• 11 years ago

The concept of moving from one database technology to another, especially something like NoSQL to Postgres, sounds like a huge task.

Aside from redesigning the schema/model and changing all the code, what about new backup/restore procedures? Scaling and performance best practices? Did you need to hire a postgres expert?

YorickPeterse10• 11 years ago

We didn't hire any experts, instead we educated ourselves on the matter. For example, one of the first steps we took was to run some rough benchmarks on Pg to see how it behaved compared to MySQL (https://github.com/olery/rds-shootout). Followed by this was mainly discussing PostgreSQL vs MySQL with those who used either one (or both) in production for a somewhat serious workload.

Backup/restoring is handled by Amazon RDS in our case, in the past we had a custom backup system for Mongo that backed data up to Amazon S3.

mtarnovan• 11 years ago

Will you publish the results of the benchmark?

lafar6502• 11 years ago

Isn't this a little premature to announce a victory? After all, they've been using MongoDB for 5 years and it delivered for most of the time. Who knows if this new shiny SQL thing will be sufficient for next 5 years? I'd say it's quite likely after few rounds of excited development the database will be slow and crappy again ;)

Fiahil• 11 years ago

I've been thinking of the same changes for a few months. We are running on MongoDB with a few M entries, and around 10GB of data. We use Scala and ReactiveMongo on the application side. So, my main fear is we're going to have a lot of overhead when trying to migrate to something like "ReactivePostgres".

esilverberg2• 11 years ago

The author's assertion that "Another problem with MySQL is that any table modification (e.g. adding a column) will result in the table being locked for both reading and writing. This means that any operation using such a table will have to wait until the modification has completed." is no longer correct as of Mysql 5.6:

http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-o...

If you specify ALGORITHM=INPLACE,LOCK=NONE you can alter table without blocking reads and writes. We have used this method successfully in Amazon RDS when updating schemas.

troels2• 11 years ago

It's not exactly a common operation either, so basing the choice of rdbms on it seems a bit arbitrary.

jeltz• 11 years ago

It happens a couple of times every month in our production databases. If it required locking tables we would have to schedule downtimes during the early mornings every time this happens would would have been a pain.

morgo3• 11 years ago

Small clarification:

If you specify ALGORITHM=INPLACE,LOCK=NONE you can alter table without blocking reads and writes. We have used this method successfully in Amazon RDS when updating schemas.

The use-case of ALGORITHM=INPLACE and LOCK=NONE is to produce an error if the modification you are attempting is not supported in this mode. i.e. even if you don't specify LOCK=NONE, that doesn't mean it will lock.

This is useful in preventing guessing games (i.e. you think its LOCK=NONE, but for some reason it's not compatible...)

barce• 11 years ago

The section, "The Problem with Schemaless," blames the technology instead of whoever put the data in there in the first place.

If the code has to handle both page.title and page_title, this is a feature of using a schemaless technology.

Also, lots of the issues the author had with MongoDB are also to be found in MySQL, e.g. taking hours to recover from a corrupt database/datastore.

acveilleux• 11 years ago

It's just that if you have a sizable team (or worse over a long time with low overlap) working on a product, you intrinsically get an accumulation of duplicates, deprecated, or both "keys" in your schema-less schema.

With a DB that enforces a schema, the overhead of modifying the schema tends to moderate that nature.

I once worked in a lab (Ph.D. students are atrocious programmers BTW) where I introduced a schema-less store (cheesy K/V store) to handle some mundane metadata caching on some medical imaging. It was intended to store 4-5 attributes per PK, I never touched it after setting it up for what I needed but showed it to colleagues.

Fast forward 10 years and there were something like 3000 attributes defined. Several hundreds of which were serialized blobs. Huge amount of overlap between the different attributes.

Almost all that because people didn't know what was already in there so they just did their own thing.

_pmf_• 11 years ago

Diving in a cesspit makes one appreciate clean air.

rdtsc3• 11 years ago

Wonder if they evaluated rethinkdb?

YorickPeterse10• 11 years ago

I briefly looked at it, but seeing how young it was (and still is) I'd prefer not to bet any money on it for now, I'd rather use something tried and proven.

tannerj5• 11 years ago

So let me ask a question. What should I use when I do need a schemaless database? Is NoSQL never the answer? I've got a project that needs to allow clients to create registration forms for different events that my company hosts. A lot of the registration data will have a defined shema ex: name, email, address. I feel like that stuff should go in a RDMS, but all the event specific stuff needs to be schemaless. I know I can do custom key/value tables in a RDMS, but that doesn't feel right either. Is MongoDB useless as a database, or are people being bitten for thinking it's a silver bullet and throwing it at every problem?

Ixiaus4• 11 years ago

Riak is amazing and actually scales. But I would only use Riak for high-volume data storage (similar to S3).

FoundationDB looks great, I haven't used it yet but they appear to have their heads on right.

PostgreSQL, the newer versions, have indexable BJSON data types so you can get the same exact behavior from Postgres as you do from Mongo but with a true RDBMS along with it, a dependable storage engine, etc...

Postgres is harder to scale horizontally though - if you have really high-volume data writes, you should be using something else for that.

I typically use PostgreSQL for all of my highly "structured" data and Riak for high-volume and "flatter" data (Postgres also often serves as an index into those objects).

tannerj5• 11 years ago

Awesome, Thanks for the reply. My use cause is barely out of the "toy app" range. We only do a handful of events each year and they only draw around 100 attendants. We're talking a very small amount of data. When you do the PostgreSQL and Riak combo is it ever on the same/related dataset. What are you using at the application layer? I'm building this in rails and I feel like it would be better to store the structured fields in MySQL and the variant data in something NoSQL. But I haven't read much into using active record with two different persistence layers. That's interesting about PostgreSQL with indexable BJSON. I've only ever used MySQL, I really need to check out Postgres.

digsmahler2• 11 years ago

MongoDB is a great database and has a really good set of client tools. It has a learning curve and it has not been without problems but I have loved it at a past startup and would absolutely use it again. If you try it out and find you like it, I'd really suggest getting to one of their MongoDB seminar days. They tend to have good speakers and for sure you'll learn something new about databases and MongoDB.

tannerj5• 11 years ago

Yea, from the good stuff I've read about MongoDB I really want to like it. There's just been so much more negative that I've read.

tstrimple2• 11 years ago

Mongo is rarely the answer for high performance, high transaction systems. I use it quite happily to prototype applications due to it's very low boilerplate overhead.

If you need schema-less data storage in a "real" database, use PostgreSQL's JSON type.

http://clarkdave.net/2013/06/what-can-you-do-with-postgresql...

tannerj5• 11 years ago

But what about low performance, low transaction? Realistically the stuff I'd use it for wouldn't see much traffic. The big factor for me is schemaless. I don't want to create a new table each time there is an event with similar, but not exactly matching data between events. I mean, is MongoDB so bad that in any production setting the reliability is not there? Thanks for the link. I didn't know about the JSON type. Maybe I'll finally give PostgreSQL a try.

pikzen• 11 years ago

Is MongoDB useless as a database, or are people being bitten for thinking it's a silver bullet and throwing it at every problem?

A bit of both really.

NoSQL databases allow for rapid prototyping, as do weakly and dynamically typed languages. It's amazing if you want to just get a product out of the door. NoSQL is the short term answer. And MongoDB is the answer if writing your data to /dev/null feels like a good idea to you..

However, strong typing and regular databases offer consistency. You have no way of going wrong because it would have refused to compile twenty times before you even think about pushing your (wrong) code in production. Sure, you can force yourself to get such consistency in MongoDB. But first, it's pretty taxing mentally, and secondly if you're doing that, why not go the way of a relational DB which offer you tools to enforce that and is faster ?

KV columns in databases feel wrong to me too, I feel dirty using PGSQL's json storage because I feel like I'm throwing normal forms away. But at the end of the day, what matters is that your product works.

tannerj5• 11 years ago

Yea, I'm going to check out PostgreSQL's json storage.

But at the end of the day, what matters is that your product works.

With my requirements, I could just write to a flat file and be fine... I seem to like complicating things just enough that I no longer understand how what I'm building works. LOL.

sfeng• 11 years ago

The addition of the json type to databases like Postgres has significantly limited the usefulness of JSON-datastores like MongoDB for me. It used to be that if you had to store truly schema-less information, and wanted to be able to query it, you needed Mongo. Now, Postgres can do that inside of an otherwise structured table.

That said, there are applications for other less-structured datastores like Redis. When you need to store data with an expiration quickly, and use common datastructures like sets, Redis can be fantastic.

zo12• 11 years ago

"I feel like that stuff should go in a RDMS, but all the event specific stuff needs to be schemaless."

Okay, you lost me there. Why does it need to be schema-less?

viraptor• 11 years ago

I imagine that because if you handle different types of events there is an infinite number of possible registration options. From preferred food type to breed of your dog... and many more. It could be stored as a huge (event, user, key, value) table, but in practice that's just how you choose to store a schemaless hash of event attributes.

clay_to_n2• 11 years ago

A related read, titled Why You Should Never Use MongoDB: https://news.ycombinator.com/item?id=6712703

tapirl• 11 years ago

There is only one NoSQL database satisfies all following requirements: 1. easy to use 2. reliable 3. transaction support 4. easy to scale 5. eays to build indexes

It is BigTable.

Generally, if your data is not very large, you should use a SQL database. NoSQL is mainly used for easy scaling, not for its schema-less feature.

bricss• 11 years ago

No comment.. (facepalm) and bad PR.

goshx• 11 years ago

Am I alone in thinking that if a programmer writes a code that allows a string to be sent to an integer field in the database the issue is not with the DBMS?

troels2• 11 years ago

Yeah. And since they use ActiveRecord, how would they even go about doing that in the first place? Over all the years I've used MySql, that has never been an issue for me. Sure it has its quirks, but show me a mature piece of software that doesn't.

DonnyV• 11 years ago

As soon as you have more then 1 app talking to your database its time to either create a common library that talks to your database that can be shared or wrap it in a service. This has nothing to do with NoSQL vs SQL. It has to do with architecture composition.

Plus your problem with checking if a field exists is because Ruby doesn't support property attributes. This is easily solved in C# using attributes. Which you can fake in Ruby. http://stackoverflow.com/questions/1085070/how-do-i-fake-c-s...

Data consistency is what your models are for. No data should be inserted before being assigned to a model.

Again it sounds like you needed a shared library or just a service wrapped around your database.

pmontra• 11 years ago

You probably had a replica set and maybe an off-site replica. What are you using now, a single PostgreSQL instance, a master-slave cluster or any other distributed setup? If positive, which one of the many psql distributed technologies are you using? Thanks.

YorickPeterse10• 11 years ago

Before (Mongo): 1 primary, 2 secondaries, 2 arbiters Now (Postgres): 1 primary

Most of our applications require write access in some shape or form, so at least the default replication of Amazon RDS doesn't cut it. Besides that we don't really need it so far, don't see the need for it in the coming months either.

bearhandsly• 11 years ago

Simple but sensible.

hugopeixoto• 11 years ago

One problem I have with postgres right now is that whenever I want to upgrade the cluster (say from 9.1 to 9.3) there's downtime while the cluster is upgraded.

Not even vanilla replication helps, right now. I think I'd have to use something like slony to replicate between different versions of postgresql, but I never tried it.

How does upgrading postgresql versions work in RDS?

neunhoef2• 11 years ago

Interesting article, did you consider any of the newer NoSQL solutions that offer a data model more similar to MongoDB but more, often configurable, consistency guarantees (like ArangoDB or RethinkDB)? I could imagine that the effort of a migration would have been considerably smaller...

michaelochurch• 11 years ago

My aversion to NoSQLs is derived from the readiness with which uninformed people dive into them, integrate them with their products, and create a web of complexity around something that should ideally be boring and reliable: the database.

There are so many things that I've heard you "can't do in SQL" that are false, at least pertaining to Postgres. Semi-structured data, full-text search, "web scale" programming, geographical indexes... all of these things, people say "you can't do in a relational database" and that's not true. Postgres is fucking powerful and can do a lot, very well.

Arguably, PostgreSQL isn't always a "relational database". You can use it as a key-value store. It just happens that you often want relational logic in a multi-purpose, long-lived data store. The relational database seems to be an attractor; the requirements that accrue to a typical in-house "we can do it better"/NIH non-relational database often converge on it.

NoSQLs have their place at very large scale (100+ TB) and there are plenty of specialized reasons to use alternative databases-- I doubt that Postgres's full-text search is competitive with Elasticsearch-- but I feel like most of the anti-SQL sentiment is against the language. And sure, it's an ugly and outmoded language, but the database is one place where I'd rather have an ugly language and rock-solid tech than the other way around.

Finally, fuck ORMs.

nawitus2• 11 years ago

Note that just because something is possible with PostgreSQL doesn't mean it's as easy to do with PostgreSQL than with an alternative database. The question could be rephrased as "why use PostgreSQL when you can do it in x?".

Cakez0r• 11 years ago

Agree with all of your points. The thing that perhaps annoys me the most is that people seem to assume that nosql is magically fast and that sql is slow... Sure, a key-value pair lookup runs like shit off a shovel, but as soon as you want to run anything more complex than that, it's likely faster with a relational database and a few indexes...

Usually the same people that think running an application on a cloud platform magically makes it fast, when in reality a VPS would be cheaper and faster.

pkolaczk• 11 years ago

NoSQL is not a well-defined term - there are huge differences between various solutions all commonly called "NoSQL", so the comparison to NoSQL is bogus. There are different NoSQL things designed with different use-cases in mind. On one side you'll have things like MongoDB, which is easy to set up, but doesn't really scale once your dataset grows out of memory, on the other side things like Cassandra which are unmatched by any RDBMS in terms of performance, scalability and availability on real-time, transactional workloads. Of course, your RDBMS of choice might be better at complex JOINs than Cassandra (which doesn't have JOINS at all, btw), but will it stand the chance at performace competition with Apache Spark or Hadoop? I don't think so.

curiously• 11 years ago

Finally, fuck ORMs.

Amen to that. Many times I wondered, geez I could've written this with a simple SQL query, here I am reading the docs.

nemothekid• 11 years ago

I think 100TB deployments is a very high bar you've set for postgres. I think the one place NoSQL databases continue to serve (and where My/Postgresql don't) are highly available, sharded deployments - and people are running into the issue at the 10s of TBs (I can't imagine you'd want 5TB+ of data served from a single node - AWS largest SSD instance would be 6.4TB in RAID0, and serving your database off a single large RAID0 already sounds like a bad idea).

Not to say people aren't running these types of Postgres clusters, but its something not easily done.

I think the anti-SQL sentiment is a very MongoDB-esque thing, in that MongoDB tried to replace everything. Most of the other popular NoSQL solutions have very clear and cut use cases, and are pretty upfront about selecting the right database.

st3fan• 11 years ago

Yes if you have one MongoDB database that uses `title` and another one that uses `post_title` then you have to adjust your code for that.

Guess what. Same thing applies to SQL.

freebullets• 11 years ago

In SQL you can't have a table whose title field is called either `title` or `post_title` depending on which record you're looking at.

dhatch387• 11 years ago

You easily could have this in SQL if you designed a poor schema or failed to migrate data from the previous field name.

jaequery• 11 years ago

Care to share your experience in terms of performance of Postgresql's JSONB vs Mongo? for both selects/inserts? and how about nested search queries?

YorickPeterse10• 11 years ago

Haven't looked into JSONB that much yet so I'm afraid I can't give any numbers.

wallflower• 11 years ago

In general, NoSQL databases are good for storing unstructured data. They start to fall apart when you want to query that unstructured data. You will hate yourself if you want to query something that is nested inside something, especially if one of the nesting levels is of the array JSON type. This is because indexes are required (just like in a normal database). Many times, Map-Reduces are required since the queries don't run in reasonable runtime. At some point, because you are storing the entire JSON document, relational databases start winning the space game (they don't store the name of the column with every document, like NoSQL does).

general_failure• 11 years ago

I think strict mode makes the warning an error

yawz• 11 years ago

... we value the following: Consistency

With all due respect, you should have had this list before selecting MongoDB. Consistency, as in "eventual consistency" and also in the way that you describe it is better supported outside the NoSQL group.

Also, I don't fully agree with the "schemaless" discussion. The moment you think about your data, you build a schema in your head, which, then, is translated into code etc. It's about how far you take this rigid model. Maybe you should see it as "flexible schema".

dkyc• 11 years ago

MongoDB was never about its benchmarking, scaling or sharding abilities. It was about two things:

#1: No schema. If you're prototyping, schemas just slow you down. Maybe that changes as your application matures.

#2: Great drivers. The amount of pain to get a basic CRUD app running with a SQL backend is just too high: push the data from the browser to the server in JSON or XML, validate, convert into database schema, sanitize using prepared statements or stored procedures, and send it off. Compare that with MongoDB: it's just JSON™.

The one thing that killed MongoDB was lack of reliability. Even when you're a startup, you may not lose data. I think MongoDB could have easily dropped 50% of performance & scalability for data security and it would be well off now. It was never intended to be the safe, sane choice for big enterprises.

timhon• 11 years ago

What system is he using to graph his queries response times?

bscanlan• 11 years ago

New Relic.

CurtMonash• 11 years ago

There are ever more use cases for which relational data models are a poor fit.

For everything else, there are relational DBMS.

harel• 11 years ago

I switched a new product in planning from MongoDB to PostgreSql 9.4. With the new JSONB columns you get the best of both worlds.

spdustin2• 11 years ago

I feel like I'm missing something. So many comments in the article and here along the lines of, "just issues a warning and not an error."

Am I the only one here who's thinking that this is correct behavior on the part of the DBMS? Three result codes from an operation: 1) everything is okay, 2) I'm sorry Dave, I can't do that (error) and 3) Okay, if you insist, but I'm going to change your data to make it work.

Am I the only one who thinks: the programmer should be aware of and respond appropriately to ALL THREE, not just 1 and 2.? That anything else is just laziness?

Or is that just me? Am I missing some subtle consideration here that results in my thought process being naive? If I'm being naive I do want to understand what I'm missing, because getting schemas right and having my code react when improper data types are being used is sometimes a pain, ORM or not. But I've always thought it was the right thing to do.

carlio• 11 years ago

Personally I feel like 3 should not be an option. Either things are okay or not okay, I don't like the "sort of okay maybe" option because that implies lots of fuzzy definitions and boundaries that the developer just has to learn.

Schema says int? Give it an int or fail. Not "it's sort of okay if you give it a thing which could be coerced into an int according to the database's ideas of coercability".

ahachete• 11 years ago

As the post explained, one of the big problems of migration off of MongoDB is finding out the "effective" schema of the data, and then migrating it (according to that schema). By "effective" I mean the resulting structure of the data stored in the database.

I think it would very helpful to use here ToroDB (https://github.com/torodb/torodb). While being MongoDB-compatible, it stores data structured into PostgreSQL tables, automatically identifying the schema of the data. Then, just by looking at the created tables, you very easily have the schema (and the data migrated to that schema). It would make migration easier.

Disclaimer: I am a ToroDB developer

atlih• 11 years ago

Not that im excusing mysql, but what I do when adding columns to a +10mil row tables is to create a new table with the new column and then insert into newtable from oldtable.

That changes those few hours into few seconds.

jbergens4• 11 years ago

They don't write if they looked at other databases. I think there are a lot of alternatives that might have worked better for them than MongoDb and even Mongo 3.0 might have worked better. Regarding the schemalessness for simple new properties they should probably have fixed that in the ORM layer, it can add default values if needed. That something is null can actually happen in sql also.

neunhoef2• 11 years ago

For some additional thoughts about schemaless vs. schema-enforcing datastores see

  https://gist.github.com/neunhoef/9b6749089775e472d44c

solidsnack90004• 11 years ago

For example, when defining a field as int(11) you can just happily insert textual data...

My eyes practically fell out of my head when reading this.

jqm2• 11 years ago

I haven't used MongoDB in production (the comments regarding reliability have been around for awhile), but playing around with it, I do like the json format and query structure.

The issue I have with SQL (MS sql in the case of work) is the amount of cleverness involved in some queries I have seen. Among the old timers, it seems almost a badge of honer to develop the longest, most clever SQL query that does everything in one step. Inevitably, there are problems, and people have trouble figuring out why because they can't debug parts of the statement in isolation. In this case, it's as if they have written an entire program in one line and can't test parts in isolation. No doubt this is abuse of the language... and admittedly my SQL skills are not world class, but I keep scratching my head and wondering why they do this to themselves. If a query is so complex you can't tell what it is doing, and it misbehaves, perhaps you would have been better off with some smaller queries you join in the program... nothing against joins... one or two or three of them... but really....

I keep asking myself if I'm missing something, but I sort of doubt it.

prottmann• 11 years ago

That is then not a problem of the SQL-Language, it is more a problem of the database design.

When you need really complex queries, you should think about a new db structure.

jqm2• 11 years ago

The largest database is vendor provided so we are stuck (for now).

Thanks for the insight.

alttab2• 11 years ago

This post outlines some of the most expensive parts of deploying, operating, and maintaining an application that operates on "NoSQL" databases like Mongo, and in my experience, DynamoDB:

1) Implicit Schemas. We avoid this completely by doing production migrations and re-indexes on every new field we add. Its expensive, and we have to write/test/run scripts in production. At times I wish I could just write a Rails migration on an RDS instance and call it a day. N-1 compatibility isn't hard to accomplish with good code reviews.

2) Search. Want to search and join like in the old days? Good luck. Using a NoSQL DB as the primary authoritative store of record is great, but you'll need a secondary indexes for any searches you want to do. If you need to look up an object on a new field (or even one that already exists in all of your data) if you haven't built an index for it you will have to.

3) Serving Clients. Because of the schemaless-blobby nature of writing clients, things get real messy the minute you have multiple services or applications reading or writing to a DB. To get around this, you have to put a service in front of it and serve the data from some RPC technology, which is an extra step and requires more development and maintenance.

4) Administration. There aren't a long history yet of robust toolsets and "science" behind different schemaless NoSQL databases. Meaning which one you choose has a huge impact on your ability to fine tune it, debug consistency or other expectation issues, and do things like proper failover, backups, restores, etc. Knowledge between similar NoSQL DBs doesn't transfer as well so your mileage will vary more so than on SQL databases (MySQL vs PostgresSQL, for instance).

NoSQL has its place and purpose, but it is rarely as the "one database that rules them all" that many businesses end up with. I'd be interested in counter stories.

aravan2• 11 years ago

The article is sort of weird, why Document, no sql, no schema DB to be a schema DB? One fundamental fact that you liked MongoDB in early days (first 5 years) because it was damn easy to handle anything you throw at, you learned to build the business around it. In 5 years, you learned enough, business model matured, not much changes in the data model. Now development progress is matured, you have got team to work on, your worries relies on consistency. You might want to back to SQL. Now you know what schema/table you need, string length, data type, constraint, relationship etc, because you learned that in 5 years time. You don't need to deal with new developers screwing the Document DB (like storing Object ID as string in few places)

Developers, who reads the article beware of reality, you can look back 5 years now, not looking forward 5 years ahead. Don't waste time in dealing with database columns and schemas, instead build the business faster, I found Mongo DB or any Document DB is good fit for agility.

1000's of business move to Document DB because it is schema-less.

While working on Document DB, you should be master in writing stand-alone scripts in Python/Perl/Ruby to run every-time you break the structure, or fix inconsistency.

edit: fixed typo errors

pacomerh• 11 years ago

It's very interesting to see the amount of negative articles about MongoDB, and at the same time see that its very popular on job boards. I'd honestly like to read an article about a success case

htsh• 11 years ago

The successes tend to be quiet. And folks who are happy don't take to the internet to defend their choices.

I've been on two straight projects where Mongo has been fine, no better or worse than an SQL database, but certainly nowhere as bad as one would assume reading all the negativity around here. I have experience with it being stable in two different contexts in production (social games, a very common use case, being one). A search for "social games mongo" should show you some success stories.

The thing that makes it a good choice for me when starting a new project is that you don't really think about it, especially if you're following lean startup methodology where the goal is to get a product into the hands of the users as quickly as possible & you anticipate changes to your models. Mongo is pretty fantastic for this. And when things stabilize, moving data really isn't that bad or expensive if you modeled based on this assumption (which you should). And now that PostgreSQL has a pretty good JSON datatype, it presents a pretty painless path if and when you outgrow Mongo.

jhoffner• 11 years ago

Something that I need to call out here. In talking about how to handle the lack of fixed schemas within Mongo, the OP refers to using if/else blocks to get around field name changes. This shows a severe lack of understanding of how to work with Mongo at such a fundamental level. If using Mongoid (which he is) you simply need to run `Post.all.rename(title: :post_title)` to do the schema change. This may seem like a nitpick to some people, but honestly if that concept was lost on the developer then its clear that there is little to no credibility to be had in the reporting of all the other Mongo issues.

I'm glad that the OP is growing as a developer and starting to understand the merits of SQL and why having defined schemas is important. SQL is awesome and its not going to be replaced by NoSQL (at least not fully). However please make sure that you are building the foundation of your understanding by fully grasping the technologies that you are already using.

snambi2• 11 years ago

Thank god for return of sanity.

cskakun• 11 years ago

I'm also moving back to Postgres. Postgres was the first DB I used when I learned to code PHP. MySQL came later. Just recently, around a year or two ago, I got introduced to Mongo and all its surrounding hype. Mongo is good for small small apps that need no references and have a low requirement for speed and data integrity.

yoanizer• 11 years ago

This maybe related:

https://news.ycombinator.com/item?id=6712703

onderkalaci• 11 years ago

Who doesn't want NoSQL with ACID properties? Can anyone tell me what is superior on MongoDB compared to PostgreSQL?

If anybody is going to talk about scalability, there are many good alternatives to scale PostgreSQL too.

remon• 11 years ago

I made a little bet with myself when I saw the topic of this post that a good 80% or more of the top comments would be of the "Shouldn't jump on the MongoDB/NoSQL bandwagon, nothing wrong with SQL" variety combined with various arguments to support that position or arguments against NoSQL in general or MongoDB specifically. I won.

Here's the thing; anyone that claims one is superior over the other without adding a specific use case or context is doing something akin to religious preaching. Databases are tools. Pick the tool you need for the job. If you don't understand exactly what a hammer is for and how it does what it does put effort into gaining that understanding. If after that research you think the tool has flaws that are important to you then don't use it.

xgil• 11 years ago

I also keep reading about how bad is MongoDB for lots of projects. I don't think MongoDB is worthless, but from my experience I also have concerns about it.

If so many people migrate to other technologies, why is MongoDB still so popular? Have a look at http://db-engines.com/en/ranking_trend, MongoDB has just passed PostgreSQL

Goodbye MongoDB, Hello PostgreSQL

Related Stories