> There’s no reason higher level frameworks or ORMs couldn't handle the migration process.
> ...this is a manual painful process today, but theres no reason this can’t be fully handled by PostgreSQL or directly within an ORM .
You mean like EF already does [1]?
> Add-Migration will scaffold the next migration based on changes you have made to your model since the last migration was created
For what it's worth, I don't use that feature. I prefer to use SQL Server Data Tools [2] to maintain a model of the database and use its schema and data diff tools to generate upgrade scripts. This is more due to the database pre-dating EF migrations but as well the schema is fairly complex so having SSDT (with its knowledge of nearly all SQL Server object types) do diffs against the actual database model is better than EF diffing its own abstract model.
The Haskell database-mapping library [1] "persistent" does auto-migrations as well. You declare your data types in a DSL reminiscent of SQL CREATE TABLE statements. If you add a new data type, it creates the table; if you add a field, it adds the column; if you make a field optional, it changes the column to allow nulls.
It will only do that for non-destructive changes, so if you remove a column (which would lose data), or make an optional field non-optional (which is ill-defined if you had NULLs), it bails out with a message suggesting the SQL you should run to migrate manually.
[1] I can't really call it an ORM, since it maps to Haskell record types rather than _O_bjects, and it works with NoSQL databases as well as _R_elational databases.
In my experience, these tools never handle complex migrations well. Renaming a column? You might drop the production data, and add a column initialized to its defaults.
My approach is to maintain a stack of diffs to the schema in DDL that are hand-crafted and checked into source control, so that when it inevitably fails during QA there isn't a 2MB SQL diff to hunt through to figure out why.
I am not an expert on RDBMSs, but isn't the issue of distributed computing and the CAP theorem a far more important driving force behind the NoSQL movement than any of the reasons in the article?
People who cite the CAP theorem as the reason they went with MongoDB etc for their tiny new project deserve some kind of premature optimization medal. The CAP theorem starts to matter when you are dealing with highly distributed systems, and even then there is almost certainly a long series of simpler workarounds to keep things usable than redoing your data architecture. Some downtime, too, is of course undesirable but acceptable up to a certain point, which is also something people tend to gloss over when raising concerns about CAP.
The biggest hangup other than the UX issues the author talks about is the fear of having to manually shard your data. There are a lot of promises about automatically sharding your data from NoSQL vendors but I have no idea if they actually are able to deliver on this. If you are just doing a K-V store, then of course sharding is easy, regardless of the database you are using. I don't think there is really a silver bullet here, just maybe better tooling and removing redundant manual steps. You still need to think carefully about data access paths and queries and so on. Any additional sharding-centric features of NoSQL databases missing in mainstream databases could presumably be added, if they don't exist already with poor UX.
For example PostgreSQL replication, wal log archiving, etc works great but it's still a bit tougher to set up than the more magical cluster auto discovery/configuration/sync stuff you see in things like elasticsearch. It would be interesting if the PostgreSQL people made a real push for a release to tighten up the operational UX of the product to be more human friendly instead of adding on more core features. (Not that I'm complaining, it's great!)
> The biggest hangup other than the UX issues the author talks about is the fear of having to manually shard your data. There are a lot of promises about automatically sharding your data from NoSQL vendors but I have no idea if they actually are able to deliver on this. If you are just doing a K-V store, then of course sharding is easy, regardless of the database you are using. I don't think there is really a silver bullet here, just maybe better tooling and removing redundant manual steps. You still need to think carefully about data access paths and queries and so on. Any additional sharding-centric features of NoSQL databases missing in mainstream databases could presumably be added, if they don't exist already with poor UX.
Original author here. Yes this is definitely another real hangup, though I semi-intentionally left this one out as magical sharding is still a bit unclear of how well it works depending on who you talk to.
> For example PostgreSQL replication, wal log archiving, etc works great but it's still a bit tougher to set up than the more magical cluster auto discovery/configuration/sync stuff you see in things like elasticsearch. It would be interesting if the PostgreSQL people made a real push for a release to tighten up the operational UX of the product to be more human friendly instead of adding on more core features. (Not that I'm complaining, it's great!)
A big plus one on all this as well, and we're doing what we can to push it forward.
NoSQL databases are not a magic solution for CAP, they just make different trade-offs, so nothing stops you from using a RDBMS and choosing your own trade-offs too. For instance, you could choose to lose the C and have a multi-master PostgreSQL cluster by taking some measures, like using uuids and appending-only. In practice, you always have to solve some things on the application layer (e.g., distributed queries), so that's why out-of-the-box solutions are popular.
this. I don't know if it would be appropriate to suggest author try RethinkDB
if so... Please try RethinkDB, I think it's fantastic. If offers some interesting promises, and is by far the best noSQL DB that I have worked with (they actually fix problems with it, too, constantly).
I think they're really up front with what they promise, instead of people just saying it's "web scale", and I think of them as an iteration after Mongo. Then again, I haven't done a super large amount of work in Mongo, so...
Absolutely agree with this. RethinkDB is the NoSQL db that got me to use NoSQL again. Currently use it in tandem with Postgres for most of my projects.
Currently using MongoDB on a project and boy do I just hate it. We have highly relational data that is a nightmare to reason with and query. Guess what? We also have migrations! It's hard to explain why, but I just get a bad feeling in my tummy every time I have to interact with the database. Just not enjoyable to work with at all. The only thing I've seen it do that actually impresses me is the aggregation framework/pipeline. That's actually pretty neat, and I feel like it's a good fit for the data we're querying with it (analytics data). Other than that, man, I wish I was using PostgreSQL...
After a few months dealing with a system that's built on Mongo ad the data store, I've come to the conclusion that MongoDB only use cases are:
1. A persistent cache, or a compute-ahead read-only data store.
2. A write-only store for high volume but non-critical data, such as comments, log events, etc.
MongoDB should never be the primary source of truth data store. Nothing can replace a relational DB for that purpose, yet.
I may be in the minority, but I rather like the rigidity of a fixed schema. Yes, it's a bit more "painful" for initial setup (you have to actually create the tables) and yes, it's a bit more work during migrations (you have to actually add the columns), but I don't see either of those as getting in the way enough to give it up. It's just too useful.
Data structures are meant to last much longer than application code. Anyone that has worked on a long running system can attest to that; well defined data structures and table layouts will outlive any application code.
When I'm designing a system, I think I spend orders of magnitude more time thinking about data structures then actually implementing the CREATE/ALTER TABLE code for them. Planned properly, you can even do the ALTERs/CREATEs necessary to add columns in advance of any actual app usage (ie. "two stage" app deployment).
There is a place for "flex fields" or storing generic "documents" but legit use cases are pretty rare. When they are necessary, a single JSON column is usually enough. The example I generally use is an audit trail: The who (FK to user), what (event enum), and when (timestamp) are all strongly typed but you may want a JSON field for event specific data.
Oh and if anybody has every tried to do a data migration with a schema-less database ... well have fun with that. Either you bite the bullet and convert everything or you end up with a lot if/then/else logic littered through your app that will bite you down the road.
I agree. Early on in my career I had a general distaste for relational databases but after finding my way back to using them I now realize that most of the issues I had were really just limitations of the software (and to some degree the hardware) at that time that caused large amounts of design-time analysis paralysis.
eg. WILL THIS VARCHAR COLUMN EVER BE BIGGER THAN N? How should I size it? I don't want to size it too small and then have to rewrite the column... But bytes are precious... Oh, dear!
Now I just make it a TEXT in Postgres and don't worry about it. Obviously you might have good reasons to limit the size of a text column for other reasons (security, interoperability... YMMV depending upon lots of factors), but it is nice to not have to think about it if you otherwise have no reason to think about it.
The actual relational bits of the relational database never bothered me and in fact I quite liked the abstraction they provided, but I really hated how rigid the systems were at the column level, which is now pretty much solved.
After years of using relational databases, I've been learning MongoDB a bit recently, just out of curiosity. I can imagine the flexible structure being useful for quick prototyping, or for knowledge representation research, or other particular instances where you really don't know exactly what you're going to need to store, and you don't need much in the way of the "relational" properties of a relational database.
But for most real-world work... I find it hard to imagine preferring a document database.
I'd put it more strongly. If your apps are anything more than dumb opaque storage, if they ever process the data they write, they have a schema. There are certain fields and values your code is relying on to work as intended. "Schemaless" merely means your schema is not written down anywhere, and you might not have any tests that could detect whether any version of your apps ever wrote data that violates it. I say "apps", plural, because in my experience if the data store has been there for more than about a year, anyone who thinks there's just one piece of code that uses it is in for a very unpleasant surprise.
On data migration, I don't think the if/then/else version is even feasible. If there have been n versions of your apps, there are 2^n possible states any particular record could be in depending on which versions of your apps did and did not update it. I've seen Notes documents after a few years of this that are in such weird states that not even the dev team could say just what the hell happened to that doc or what correct (well, least bad) behavior of the apps would be, much less what the current versions of the apps would probably do. You can kind of get partway there with apps that have existed and been actively maintained for as long as any of your data has been there, but trying to write anything like new analytics over old non-migrated data is hopeless.
To me it seems like using NoSQL simply to avoid the hassle of creating a schema is not really a good reason to choose it over a relational DB. Each solution brings it's own pros and cons. It's helpful to understand what kind of tradeoffs you're making when you choose one over the other.
That's easy. Renaming, splitting, changing data types, or altering character encodings... on in-use production data? That's harder, and you may need to take downtime.
On the other hand, document-oriented databases allow in-stream data model changes. More code, of course, but with care you can spread the cost of the upgrade over time.
I'm not a huge advocate for document-oriented databases, but there are trade-offs.
It tries to be like english, but it is anything but. It has a complicated irregular grammar. It has an implicit structure that is hard to guess if you don't already know. People write huge bloated ORMs to try to run away from it. Your application needs to have code in it to do a bunch of string concatenation if you don't use one.
It is a bad language.
EDIT: It is completely preposterous that SQL injection is even a thing.
Every once in a while, though, it does something really cool. The declarativeness is cool, IMO; I always appreciate inline conditionals. That's something I would want to be carried over to a replacement.
What happens when you have to deal with a crazy pop star with a " in his (real, legal, you-are-the-unreasonable-one-for-demanding-something-else) name? (This is ignoring the myriad of issues that come with parameterizing names into "first name" and "last name" in the first place, but that's a separate thing.)
This is what binding variables is for, but to use them you're either writing for specific platforms (PSQL, Oracle SQL, etc), or you're using middleware that hides the raw SQL from you.
This is a problem with the string domain, not with SQL. You'd have it no matter what data persistence method you use. The answer is to put logic between that function and your form data puller.
I have to say, I strongly disagree on this point. I am a coder who works in a variety of languages build on many paradigms, of which SQL is one of my most enjoyable. I agree that the creation of schemas can be painful, but SQL's almost Prolog level of declaration can be beautiful in certain problem spaces. It is unfortunate that the current state of query optimizers leads to leaky abstractions where one must rearrange queries to allow for efficient execution. Still, I have had a great deal of success introducing SQL to otherwise nontechnical people, and they also love the fact that one can describe the solution you desire as opposed to instructing the computer on how to get there. These people particularly benefit from the almost English nature of the language, and I feel I have no right to complain about any syntax with a `use Perl;' background on my desktop.
Thats interesting considering Cassandra moved to CQL (their SQL-like language) and theres a ton of work in the Hadoop stack on SQL-like languages (Hive, Pig, Presto)
You would not want a Framework/ORM that abstracts alter statements from you when working with large tables in a relational db. Alter statements can have real performance interruption and is something that an engineer has to be aware of. It's a case where a framework might help 70% of the use cases but really hurt the other 30% with medium-larger sized tables.
Craig - great post. I think you missed a few huge benefits that drive the "NoSQL movement." Specifically, sharding and aggregation.
Most devs don't need the power of sharding, which is why that benefit can never be felt. But the reality is this is probably the #1 characteristics (huge benefit) of NoSQL databases. Google and Amazon definitely paved the way for this movement, primarily b/c they dealt with tons of data. Its simply cheaper to scale out (distributed) than scale up.
You can't aggregate data with a relational database. But you can aggregate with (most) NoSQL databases (exception is graph dbs). Instead of building relationships, with NoSQL you're building composites. The huge benefit here is enabling sharding while, having your data all in one place.
Lastly, a relationship db is made up of tuples and sets of tuples. With a NoSQL DB you can have complex data structures. I think this is the point you we're trying to make re: Documents.
I still love relational databases. It's cool to have options though. Before, relational was the only way.
The question is how do we determine which db to use (or use more than one)? Ah, the beauty of polygot persistance...
> Having Rails/Django/(Framework of your choice) automatically notice the need for a column to exist and make appropriate modifications you could work with it the same way you would managing a document relation in your code.
Hibernate can do this (IIRC, it's been awhile), which is great for development mode, but I would never trust a framework/ORM to "auto-migrate" my production database.
I find the whole "schema-less" thing to be a side-effect of NoSQL marketed as a feature. Unless you are about prototyping a small feature quickly (which MongoDB is great for) - on the large scale of things, 5,6 months out in development time, you will find that the time saved on "not having to do migrations" may very will be nil. In most cases, if you want to keep your hair you end up having a schema anyways. At the very best MongoDB and friends make it easy add columns, but removing and changing datatypes might as well be migrations. Also, as the article mentions, Postgres now has a JSON data type making the schema less advantage kind of useless.
When it comes to limits of relational databases some of the real limits are actually sharding, replication and high availability, which are all relatively more difficult to do on the popular YesSQL databases.
In any case, its pretty tiring to see NoSQL only refer to MongoDB and other document stores. Redis, Cassandra, HBase and Hive are all NoSQL engines ranked before the next document store, and IMO have a lot harsher performance penalties for incorrect usage than Postgres & friends. Given that Cassandra is a the second highest ranked NoSQL store which also (sort-of) enforces a schema, implying that the limits on relational databases are schemas is pretty bizarre.
45 comments
[ 4.9 ms ] story [ 84.0 ms ] thread> ...this is a manual painful process today, but theres no reason this can’t be fully handled by PostgreSQL or directly within an ORM .
You mean like EF already does [1]?
> Add-Migration will scaffold the next migration based on changes you have made to your model since the last migration was created
For what it's worth, I don't use that feature. I prefer to use SQL Server Data Tools [2] to maintain a model of the database and use its schema and data diff tools to generate upgrade scripts. This is more due to the database pre-dating EF migrations but as well the schema is fairly complex so having SSDT (with its knowledge of nearly all SQL Server object types) do diffs against the actual database model is better than EF diffing its own abstract model.
[1] http://msdn.microsoft.com/en-us/data/jj591621.aspx
[2] http://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).a...
It will only do that for non-destructive changes, so if you remove a column (which would lose data), or make an optional field non-optional (which is ill-defined if you had NULLs), it bails out with a message suggesting the SQL you should run to migrate manually.
[1] I can't really call it an ORM, since it maps to Haskell record types rather than _O_bjects, and it works with NoSQL databases as well as _R_elational databases.
My approach is to maintain a stack of diffs to the schema in DDL that are hand-crafted and checked into source control, so that when it inevitably fails during QA there isn't a 2MB SQL diff to hunt through to figure out why.
The biggest hangup other than the UX issues the author talks about is the fear of having to manually shard your data. There are a lot of promises about automatically sharding your data from NoSQL vendors but I have no idea if they actually are able to deliver on this. If you are just doing a K-V store, then of course sharding is easy, regardless of the database you are using. I don't think there is really a silver bullet here, just maybe better tooling and removing redundant manual steps. You still need to think carefully about data access paths and queries and so on. Any additional sharding-centric features of NoSQL databases missing in mainstream databases could presumably be added, if they don't exist already with poor UX.
For example PostgreSQL replication, wal log archiving, etc works great but it's still a bit tougher to set up than the more magical cluster auto discovery/configuration/sync stuff you see in things like elasticsearch. It would be interesting if the PostgreSQL people made a real push for a release to tighten up the operational UX of the product to be more human friendly instead of adding on more core features. (Not that I'm complaining, it's great!)
Original author here. Yes this is definitely another real hangup, though I semi-intentionally left this one out as magical sharding is still a bit unclear of how well it works depending on who you talk to.
> For example PostgreSQL replication, wal log archiving, etc works great but it's still a bit tougher to set up than the more magical cluster auto discovery/configuration/sync stuff you see in things like elasticsearch. It would be interesting if the PostgreSQL people made a real push for a release to tighten up the operational UX of the product to be more human friendly instead of adding on more core features. (Not that I'm complaining, it's great!)
A big plus one on all this as well, and we're doing what we can to push it forward.
if so... Please try RethinkDB, I think it's fantastic. If offers some interesting promises, and is by far the best noSQL DB that I have worked with (they actually fix problems with it, too, constantly).
Comparison from RethinkDB website (RethinkDB vs. Mongo/others): http://rethinkdb.com/docs/comparison-tables/
I think they're really up front with what they promise, instead of people just saying it's "web scale", and I think of them as an iteration after Mongo. Then again, I haven't done a super large amount of work in Mongo, so...
How did your project end up selecting MongoDB? That seems like exactly the sort of thing that it's not good for.
(I'm not just being rhetorical; were there other attributes of MongoDB that made it seem preferable to a relational database earlier in the project?)
EDIT: It's nice and quick for prototypes. Ironically, I'm building one with it now.
1. A persistent cache, or a compute-ahead read-only data store. 2. A write-only store for high volume but non-critical data, such as comments, log events, etc.
MongoDB should never be the primary source of truth data store. Nothing can replace a relational DB for that purpose, yet.
Even then, you're probably better off with Redis or memcached.
> 2. A write-only store for high volume but non-critical data, such as comments, log events, etc.
Just write to disk. It's very hard to beat flat file text for this use case and most of the time it's not worth it.
Definition? As in pre-aggregated data, i.e. materialized views or summary tables?
Data structures are meant to last much longer than application code. Anyone that has worked on a long running system can attest to that; well defined data structures and table layouts will outlive any application code.
When I'm designing a system, I think I spend orders of magnitude more time thinking about data structures then actually implementing the CREATE/ALTER TABLE code for them. Planned properly, you can even do the ALTERs/CREATEs necessary to add columns in advance of any actual app usage (ie. "two stage" app deployment).
There is a place for "flex fields" or storing generic "documents" but legit use cases are pretty rare. When they are necessary, a single JSON column is usually enough. The example I generally use is an audit trail: The who (FK to user), what (event enum), and when (timestamp) are all strongly typed but you may want a JSON field for event specific data.
Oh and if anybody has every tried to do a data migration with a schema-less database ... well have fun with that. Either you bite the bullet and convert everything or you end up with a lot if/then/else logic littered through your app that will bite you down the road.
eg. WILL THIS VARCHAR COLUMN EVER BE BIGGER THAN N? How should I size it? I don't want to size it too small and then have to rewrite the column... But bytes are precious... Oh, dear!
Now I just make it a TEXT in Postgres and don't worry about it. Obviously you might have good reasons to limit the size of a text column for other reasons (security, interoperability... YMMV depending upon lots of factors), but it is nice to not have to think about it if you otherwise have no reason to think about it.
The actual relational bits of the relational database never bothered me and in fact I quite liked the abstraction they provided, but I really hated how rigid the systems were at the column level, which is now pretty much solved.
But for most real-world work... I find it hard to imagine preferring a document database.
On data migration, I don't think the if/then/else version is even feasible. If there have been n versions of your apps, there are 2^n possible states any particular record could be in depending on which versions of your apps did and did not update it. I've seen Notes documents after a few years of this that are in such weird states that not even the dev team could say just what the hell happened to that doc or what correct (well, least bad) behavior of the apps would be, much less what the current versions of the apps would probably do. You can kind of get partway there with apps that have existed and been actively maintained for as long as any of your data has been there, but trying to write anything like new analytics over old non-migrated data is hopeless.
That's easy. Renaming, splitting, changing data types, or altering character encodings... on in-use production data? That's harder, and you may need to take downtime.
On the other hand, document-oriented databases allow in-stream data model changes. More code, of course, but with care you can spread the cost of the upgrade over time.
I'm not a huge advocate for document-oriented databases, but there are trade-offs.
It is a bad language.
EDIT: It is completely preposterous that SQL injection is even a thing.
This is what binding variables is for, but to use them you're either writing for specific platforms (PSQL, Oracle SQL, etc), or you're using middleware that hides the raw SQL from you.
Most devs don't need the power of sharding, which is why that benefit can never be felt. But the reality is this is probably the #1 characteristics (huge benefit) of NoSQL databases. Google and Amazon definitely paved the way for this movement, primarily b/c they dealt with tons of data. Its simply cheaper to scale out (distributed) than scale up.
You can't aggregate data with a relational database. But you can aggregate with (most) NoSQL databases (exception is graph dbs). Instead of building relationships, with NoSQL you're building composites. The huge benefit here is enabling sharding while, having your data all in one place.
Lastly, a relationship db is made up of tuples and sets of tuples. With a NoSQL DB you can have complex data structures. I think this is the point you we're trying to make re: Documents.
I still love relational databases. It's cool to have options though. Before, relational was the only way.
The question is how do we determine which db to use (or use more than one)? Ah, the beauty of polygot persistance...
Hibernate can do this (IIRC, it's been awhile), which is great for development mode, but I would never trust a framework/ORM to "auto-migrate" my production database.
When it comes to limits of relational databases some of the real limits are actually sharding, replication and high availability, which are all relatively more difficult to do on the popular YesSQL databases.
In any case, its pretty tiring to see NoSQL only refer to MongoDB and other document stores. Redis, Cassandra, HBase and Hive are all NoSQL engines ranked before the next document store, and IMO have a lot harsher performance penalties for incorrect usage than Postgres & friends. Given that Cassandra is a the second highest ranked NoSQL store which also (sort-of) enforces a schema, implying that the limits on relational databases are schemas is pretty bizarre.
http://db-engines.com/en/ranking