http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660Hi @Chris Wright I think this is going to be hard to explain what is actually possible and what I mean when I say 'in most cases nothing needs to be done' without an example. So I've followed the theme in this article and used this Blog example and show you what you can expect with schema-less designs: [code.google.com/.../MigrationsUsingSchemalessNoSql](http://code.google.com/p/servicestack/wiki/MigrationsUsingSchemalessNoSql) Essentially, in nearly all cases data migrations (if they even need to be done) are much less painful in schema-less datastores. There is no need for an 'intermediate language' (e.g. DDL) to define your custom application's schema in the datastore, it's just your code -> and your datastore, so life's easy. Redis and most NoSQL datastores keep this theme and design their functionality to be non-invasive, e.g. you can store a 'BlogPost' into a list that doesn't even exist, it will just create it on the fly, likewise when you query from a non existing list of BlogPost's you will get the same result as if it was an empty list, i.e. 0 results. http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment15http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment15Thu, 29 Apr 2010 00:11:39 GMTOkay, so if you're going to explicitly plan for compatibility, you can do that with SQL just as well as with NoSQL, using the same mechanisms. Version A depends on a field; version B writes to it; version C ignores it entirely (sets the column to nullable, or adds a DEFAULT constraint); version D eliminates it. How would this work with NoSQL? Pretty much the same way. SQL allows you to define constraints, but your application has the same constraints, so it doesn't help you to eliminate SQL. Unless, of course, you're just looking to reduce the friction for setup and adding code. Or if you're actually storing documents and your application is treating the bulk of the documents as a blob. http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment14http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment14Fri, 23 Apr 2010 14:46:15 GMT@Chris Wright >>You need to schedule downtime or explicitly plan for compatibility in any case. Or are you going to let your application crash because multiple versions are running, some of which expect a field to have one name, some of which expect it to have a different name? Do we let our app crash?? Obviously that's insane. We code defensively, maintain backwards compatibility and our app only accesses web services that are not tightly-coupled to the db-schema. The typical use-case is that we add new fields which means additional functionality that the latest version of the client app can take advantage of. We have an automated CI process which includes a 1-click deployment to all our load balanced app servers. IIS can handle a redeployment where it's still serving old requests off the old App domain while we're deploying a newer version of the software - which means it is always serving requests during deployment, old or new. Now one of our back-end services is built on a sharded architecture and we can't change the table schema when there are active connections, it is this *reason* we have downtime. Adding new fields in text-blobs doesn't exhibit this behaviour and like I said requires 'no down-time'. Schema migration is mitigated as best as it can be where we have a tool to generate the required DDL statements to migrate the old schema to the new ones. We literary take IIS down run the migration script, redeploy the services and after that we can finally bring the system back on line. So it's not down for a long time but it still goes off-line which causes client disruption. Now I'm not advocating to not use RDBMS because of this reason because as far as I'm concerned it is a necessary evil, when your data/requirements best fit into a RDBMS it is best to leave it there. But schema migration is one of the benefits of *schema-less designs*. http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment13http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment13Fri, 23 Apr 2010 08:35:40 GMT@Demis Bellot You need to schedule downtime or explicitly plan for compatibility in any case. Or are you going to let your application crash because multiple versions are running, some of which expect a field to have one name, some of which expect it to have a different name? Now in SQL, I can change a column name in one line with sp_rename, or even if I don't have that convenience, in three lines (create column, update, drop old column; maybe need to alter the new column to disallow nulls, or drop a foreign key constraint). And I only need to write the table name, the old column name, and the new column name. Only three chances to mess up, and it's pretty hard to mess up by forgetting something. Whereas (if I'm interpreting the examples correctly) RavenDB will force me to list all fields to rename one, which means I can drop one by forgetting about it. Not to mention, that's implicitly creating a schema. If I don't have a schema, I'll be unable to predict what fields will exist, so I'll never be able to run an update query without removing fields from some of the stored objects. Even assuming you want and can deal with a schema, this system makes it harder. At work, I have tables with 40 columns. The examples indicate to me that I simply wouldn't be able to rename or drop fields, without a tool to write the queries for me. Even then, I'd be quite hesitant to do so. This could be fixed if there were a special variable "the_rest" (or some such) that got interpreted to mean "any fields that I haven't explicitly named". http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment12http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment12Fri, 23 Apr 2010 03:03:03 GMT@Chris Wright I don't actually think its that bad, as we are talking about renaming a field (i.e. putting the value of one field into another) and it looks like this is achieved with a single query (processed on the server?). I think the real benefits are the most typical use cases when you add/remove fields which in most cases you won't have to do anything in your NoSQL data store. This normally will require scripted logic as part of your deployment which could potentially require downtime. http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment11http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment11Fri, 23 Apr 2010 01:10:51 GMTI must say, that's really ugly. I have to name every field (twice) in order to update a single field. It's an API issue, but it means I would really try to avoid modifying my document format or doing bulk updates. I thought that NoSQL was about making it easy to change your schema, but this post makes it clear that SQL's better at that. Or am I just misunderstanding the examples? http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment10http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment10Fri, 23 Apr 2010 00:38:53 GMTI'm glad that I'm not the only person who thinks that data migration is a pain - I was beginning to think I was suffering this task in silence. *Database Schema migration* (although we automatically generate the required DDL) is the *only* reason why we have downtime when we upgrade our systems at mflow. To mitigate these problems, I've thrown away the normalization book on RDBMS design and we keep most of our Key Value Objects as stored as Blobs. Although this is transparent to the model as they are exposed as a strongly-typed IList, IDictionary, etc, where even our LINQ provider provides partial support for them where ever possible. I've found the benefits to be numerous, one of which is that we can modify the type and not have to do a schema migration only runtime updates if required. Because of this pain, 'schema flexibility' is something I cared very deeply about when developing the C# Redis client, so much so that I decided to build my own text-serialization format specifically catered for serializing C# POCO classes. Performance and resilience to schema changes is it's top priority where as long as it doesn't lose data it will de/serialize. i.e. you can serialize a custom POCO class and read it back as string Dictionary (and as long as the dictionary keys matches the POCO properties - vice versa). As a benefit of controlling the implementation, I use 'generic optimizations' to remove all runtime reflection which gives me a 5.3x increase over .NET's BCL JsonDataContractSerializer. Anyway for anyone that's interested it's ideal for blobbing any .NET POCO and comes in a dependency-free .dll that's open source under the liberal BSD licence at: [code.google.com/p/servicestack/wiki/TypeSerializer](http://code.google.com/p/servicestack/wiki/TypeSerializer) http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment9http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment9Fri, 23 Apr 2010 00:37:33 GMTfschwiet, You can certainly do that, sure. But I am showing a more common case of a point in time upgrade. http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment8http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment8Thu, 22 Apr 2010 19:08:35 GMTThanks for this blog series. It seems all these migration scenarios require putting the application on hold while the database is being updated. Are there solutions that would not require downtime? I was thinking the documents could be versioned so things with an old schema could be recognized/converted on an as needed basis. I have no experience with document databases though. Do you have any thoughts or experience with this approach, or other no-downtime approaches? http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment7http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment7Thu, 22 Apr 2010 17:50:09 GMTMF, Deletes/Create are handled the usual way. Rafal, 1) Based on how you submit it. In my current perf tests for Raven, I am test on a 7 GB and ~ 2.5 million documents. Doing about 2 million inserts, then about >1 million updates in about 1 hour and 15 minutes. http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment6http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment6Thu, 22 Apr 2010 17:03:41 GMThmm. interesting. I hadn't even gotten to thinking about migrations. http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment5http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment5Thu, 22 Apr 2010 16:00:36 GMT.... anyway, could you estimate how much time it takes to update million docs in Raven? http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment4http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment4Thu, 22 Apr 2010 10:34:27 GMT1. How do you tell if it's an update query and not a select? 2. Changing db schema in RDBM is a nightmare if your db is big enough. It puts a table lock on your data and the update can take hours or days if the server concludes it has to move all data due to record size change. The application is inaccessible during that time and practically you can't do anything but wait and hope everything's ok. And in case of a fokup transaction rollback can take twice as much time :) NoSQL is a clear winner here. http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment3http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment3Thu, 22 Apr 2010 10:32:25 GMTBy that i meant creating and deleting documents. But after thinking about it some more, you probably don't end up creating/deleting documents very often. http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment2http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment2Thu, 22 Apr 2010 10:08:05 GMTThat answers updates, but i am interested in how you do creates and deletes. http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment1http://ayende.com/4468/that-no-sql-thing-document-database-migrations#comment1Thu, 22 Apr 2010 10:06:38 GMT