This is bad advice. It's best to understand your problem domain and use the tools that are most appropriate. You see a lot of two types of posts on HN:
* "I picked a NoSQL database for a problem domain with a better relational fit." Those posts look like this one.
* "I picked an RDBMS for a problem domain with a better NoSQL fit." Those posts are usually titled something like "How I scaled Postgres to XYZ qps" and describe an insane amount of re-engineering and operational hell. Oddly, these posts are usually proud of the accomplishment rather than embarrassed that they picked the wrong tools in the first place.
There are upsides and downsides to RDBMSes. From my experience, you should be leaning towards NoSQL systems (of which there are many, each suited to different use cases) when you have very large scaling needs (in terms of dataset and qps), heavily polymorphic data, or data that has ambiguous structure.
It's been stated many times already: Use the right tool for the right job.
I think the advice is right on and I see NoSQL databases as very much useful adjuncts to the relational systems, not as replacements.
> This advice [Use the right tool for the right job]...
> is the same as saying you should be able to accurately
> predict the future.
It is a lot easier to start strict and organized and back down to less strictness with looser rules where you need it, than to do the opposite.
The problem is the old cliche that if all you have is a hammer, everything looks like a nail. "Just start with a hammer" is bad advice because with enough effort and willingness to cause collateral damage, you can almost always get a screw in with a hammer. If you've never seen a screwdriver, you're doomed to posting HN stories about "how I spent the last three weeks rebuilding my application architecture to deal with the fact that hammered-in screws tend to fall out a lot."
Polymorphic structures are a PITA to model in relational tables, but they fit very well in schemaless, document-oriented NoSQL systems. This has nothing to do with data validation.
Rapidly changing schemas are a PITA in RDBMSes, especially with a large cluster of servers. It's usually very easy to alter schemas on-the-fly with NoSQL systems. This has nothing to do with data validation.
On a related point, the database is a crappy place to put data validation anyways. It can only do the most primitive forms of validation (is this an integer? is the FK reference valid?). Application level-validation gives you the full power of a modern programming language and all the data you could possibly want to fetch. Yeah, "senior discount" is a float... but it should only be given to people over age 65.
The fact that the OP managed to insert "111" into a column instead of 111 means he is using crappy tools. My datastore api guarantees that integer fields in my (statically typed) classes are written as integers. I never have this problem, nor have I ever taken down my GAE (NoSQL) applications for maintenance.
> Polymorphic structures are a PITA to model in relational tables, but they fit very well in schemaless, document-oriented NoSQL systems. This has nothing to do with data validation.
JSON and XML types in PostgreSQL rock. If you can limit the areas where the data is polymorphic, you can push the rest into those types. Many other RDBMS's support XML types as well.
> Rapidly changing schemas are a PITA in RDBMSes, especially with a large cluster of servers. It's usually very easy to alter schemas on-the-fly with NoSQL systems. This has nothing to do with data validation.
This again trades one set of problems for another. You are allowing old schema and new schema to co-exist. How many old schemas does your application have to support? It seems to me this would likely ossify things down the road a bit because if your schema is that rapidly changing, you may never get caught up and so backward compatibility is going to be a bitch. In other words, the less guarantees you can make regarding information stored, the fewer guarantees you can make about data output. That's a big deal as your product matures.
And don't get me started on BASE.....
A schemaless datastore does not mean a schemaless app. Furthermore, the application layer is a far better place to do validation simply because it has more knowledge of the real world. An RDBMS can constraint an age field to be integer; the application can constrain it to be positive and within the probable lifespan of a human.
How many old schemas does your application have to support? Only as many as I want to; reprocessing removes the old, which I tend to do fairly quickly. I have done a lot of schema migration over the last three years on GAE and the process is vastly easier than it is in RDBMS-land. There's no need to stop the world.
Hey, I love Postgres. I spent most of my (almost two-decade-long) career building apps on many different RDBMSes. But there are many applications that are better suited to alternatives, especially with large-scale consumer-facing web apps. These threads annoy me; we should all be looking at new tools saying "wow, I wonder what this is good for" rather than smugly burning all the heretics that turn away from to the One True Idol.
My tools elegantly map database fields to my objects; doing this with serialization formats is incredibly crude and brittle.
Build tools to do it.
A schemaless datastore does not mean a schemaless app.
That's actually a problem in most cases.
But there are many applications that are better suited to alternatives, especially with large-scale consumer-facing web apps. These threads annoy me; we should all be looking at new tools saying "wow, I wonder what this is good for" rather than smugly burning all the heretics that turn away from to the One True Idol.
Certainly that last part is true, but the best uses I have seen, including uses put forth by NoSQL proponents, have been as adjuncts to a relational approach, for example handling data transformation on the way in or out. Otherwise you end up with two fundamental problems:
1) How many other apps can your application support? (see https://news.ycombinator.com/item?id=4495377) Where is your public API? Basically you can do this in your db layer and/or your app layer, but you get a lot more flexibility by doing it in the db. With a schemaless app, you can't do it there without intimate knowledge of innards...
2) Strict schemas with validated input and a declarative query language buys you flexible output. Rigid schemas are thus an investment in the future. If your data is sufficiently polymorphic that output can't be flexible no matter how you do it, then I suppose that's not a huge issue. However for many cases where NoSQL is chosen wrongly it is a huge issue.
Good luck.
In certain times and places. However, I am not a fan of the idea that all db access goes through stored procedures. LedgerSMB will eventually do everything through stored procs but the reason is to make sure that all the logic is available to other apps, not to require that everyone else do all access through the stored procs.
Also our stored procs are designed to be discoverable by the application and therefore allow for looser coupling between the app and sproc than is typical.
The problem with that argument is that things change, and it's not always obvious what problems you will need to solve when you start a project.
RDBMSs are backed by extensive theory as well as decades of product hardening, development of expectations and best practices, and battle-tested in production environments. They are explicitly designed as general-purpose systems. That means that when your needs change and you need to adapt, an RDBMS likely provides features that make that transition work smoothly and avoid breaking applications.
At this time, those things are not true of MongoDB, so it is somewhat more specialized. That's OK, but it means that you need to be more careful about using it than, say, postgres. Trying to put MongoDB on equal footing with postgres and say "choose the right tool" is an oversimplification.
I think it's perfectly reasonable advice to start with SQL unless you have specific reasons that you think something else will be a better fit.
Sure. The big problem though is that the problem domain of RDBMS's is ad hoc reporting which is pretty universal.
rom my experience, you should be leaning towards NoSQL systems (of which there are many, each suited to different use cases) when you have very large scaling needs (in terms of dataset and qps), heavily polymorphic data, or data that has ambiguous structure.
On the other hand, you should choose an RDBMS if you are pretty sure reporting needs will change or you will need to accommodate new metrics on a frequent basis.
The problem is there is a huge overlap between those two. NoSQL means telling your customers "no, we can't do that new report you'd like right now." That's a huge tradeoff for any customer-facing app.
After all the basic tradeoff is between flexible input and flexible output.
RDBMSes were designed for an era of spinning platters, data volumes in the megabytes-to-gigabytes, and small volumes of long-running ad-hoc queries. It's remarkable how far we've been able to push a general-purpose design but it's starting to burst the seams.
Do you want to design for easy reporting or easy scaling? There is no universal right answer because it's different for every app. There are times when I really wish I could run a SQL query. On the other hand, I spend zero time maintaining my (appengine) datastore and it automatically scales to any data or traffic volume. And I have a heavily polymorphic system which would be a nightmare in SQL.
I don't think that overlap is as big as you do.
But mdolap is far easier to work with if your feed is from a relational model. Otherwise you are left with a lot of effort getting the data transformed into an appropriate model.
Relational math + fixed schemas means flexible output.
I don't think that overlap is as big as you do.
Any line of business app had better start with a relational model then, as would any case where you are selling business intelligence. The other data stores work best as adjuncts to, rather than replacements for, a relational store.
Edit: Also it occurs to me that MDOLAP is only partly ad hoc. You have to set up your cubes ahead of time and that means deciding on what you are going to report. I imagine that adding a new reporting dimension to a large data set would be painful.
There have been more than a couple times when I was ready to blame the relational model, but further investigation revealed that the real root of the problem was that the existing schema or query used an approach that was optimized for one DBMS but performed terribly on the one we were actually using.
Oracle is in a world of its own in a lot of ways, but if you are moving to or from Oracle you should have the required resources and expertise in your budget.
If you are referring to the well-known cost of COUNT(*) in PostgreSQL (or MySQL with InnoDB, for that matter), or different ways to handle full-text searching, I agree that there are differences you have to deal with, but they are not usually a big deal.
Contrast the fairly easy and routine process of migrating between MySQL, PostgreSQL, or SQL Server with the huge amount of work involved changing your application code from MongoDB to anything else.
To speed up a lot, I think you'd need to be able to scan an index in physical order which is not currently supported.
Now if you are using SSD's yeah, you could tweak the planner settings enough to cause it to make an index scan perhaps, and maybe you'd see a modest performance increase, but even there it appears the operating system's prefetch logic really does come to the rescue of a sequential scan of a table in physical order.
They only all implement the ANSI SQL standard for certain values of "ANSI SQL standard." Those values being SQL-89 or perhaps SQL-99.
Beyond that it's a mess. SQL Server didn't get around to doing a really good job with window functions until this year. Last I checked, MySQL still doesn't do any number of useful things, such as WITH or FULL JOIN. Upserts and bulk inserting are different for every DBMS on which I've learned how to do them. And so on and so on. There are any number of ways you can get yourself coupled to a particular DBMS if you go beyond the core set of basic SQL features.
In other words, SQL databases are only trivially interchangeable for trivial cases.
If this is the case, then, it's only because database application developers go out of their way to write compatible code. Things like upserts are not trivially compatible between vendors, and so are avoided by people trying to write portable applications.
Similarly, let's look at SQL 2003 structure data type inheritance and how this is implemented in Informix, Oracle, DB2, and PostgreSQL. Ok, so it isn't in PostgreSQL, and Informix allows tables to be inherited as well as types, and will even happily return result sets with different numbers of columns per row (google "Informix jagged rows")... And Oracle and DB2 look pretty close in this way until you get under the hood.
I also disputed the notion that reading a book first would enable someone to choose the right tool the first time. There's no substitute for experience and real expertise, and you only get that by trying things and making mistakes, or at least learning from other people's mistakes.
Keeping the punctuation but replacing the words with symbols:
"X." Not X: X, but tangential comparison.
Wrong again. Here's a very trivial example. To index or not to index? On MySQL InnoDB you usually get a major performance benefit out of indexing just about everything you want to search on later. On PostgreSQL you usually get a major performance benefit out of indexing columns only as needed later. This is because of internal design differences, sequential scans through a table are much cheaper on PostgreSQL (in MySQL it has to scan a table in key order, not physical order which means no OS prefetch). Also non-PK index scans are faster on PostgreSQL though PK index scans are slightly faster on InnoDB (basically the table is contained in the Primary Key index there).
Contrast the fairly easy and routine process of migrating between MySQL, PostgreSQL, or SQL Server with the huge amount of work involved changing your application code from MongoDB to anything else.
So suppose I want to move my database which uses PostGIS and pgcrypto to MySQL or SQL Server. How would I do that?
Disagree to the books recommended. SQL is only a query language, not the database itself. It definitely should be part of the consideration. Understanding how database engines works under the hood is more important in terms of performance in high-concurrency, high load scenarios.
What this means is that optimizations appropriate for InnoDB (index everything you search on) are very poor for PostgreSQL and vice versa. In PostgreSQL you index only a few columns, typically join conditions and a few select search columns, and the combination of the operating system and database will do the rest. In short, InnoDB only does index scans (and the table is a special index). This makes some things perform better (primary key lookups), but many other things perform worse. Moreover optimizing for one does not mean optimizing for the other....
The same problem exists with OLAP databases.
People trying to do MOLAP without building ROLAP. They are thinking that MS SASS, ESSBASE, Cognos or Qlikview will do some "magic" that eliminate the need to carefully think about data