I think many people were burned by MySQL InnoDB, which modifies (or modified; I'm still on quite an old version) schemas by making a new table and copying the data, and don't realise that all databases don't do that.

InnoDB tables have online, non-copying (for most operations) DDL in 5.6. Also, TokuDB has had online, non-copying DDL (column add and drop, add index) since TokuDB 5.0.

With something without a schema, data migrations are almost impossible to do correctly.

And sorry but it is complete nonsense to suggest that data migrations are almost impossible on schema less systems. I've done countless myself and people are doing it every day.

*: transactionally, for certain flavours of relational databases.

This has not been my experience. Sure, you occasionally need code to do a migration. But at least 90% of the time, you are adding to or refactoring your existing data model. In which case, pure SQL is entirely sufficient, unless you've been doing untoward things with your schema.

If you have an ORM for your schema-less database, you get roughly the same thing, but with weaker guarantees.

Correct data migration requires knowledge of the schema of the data.

If the database is small, then sure, schema changes are harmless. But if a database is in production with data shuttling in and out of it and has many records, then ALTERs are non-trivial.

And I don't think an ALTER can survive in a transaction, i.e. it is not something that can be rolled back. It can be undone via another ALTER that is it's opposite, but it cannot be rolled back the way INSERT and UPDATE can.

It can in databases that use transactional DDL (and, really, doing so is part of the relational model, since part of that model is that metadata describing the structure of data is itself stored in relations and -- whatever the syntactic sugar layered over top of it -- can itself be manipulated with the same tools that regular data is manipulated with.)

And since Morphia (https://github.com/mongodb/morphia) is similar to JPA it is trivial to port your existing code to MongoDB. Which then leaves you with the same experience as an SQL database except with a lot of benefits (single binary, easy to query/administer/scale, geo spatial features etc).

I suspect that it would be somewhat harder to find static+NoSQL combinations than dynamic+SQL, since it's easier to use a dynamic language over an SQL database (types in SQL don't match 1:1 those in languages anyway, so queries are traversed by column index in both static and dynamic languages; this means that SQL's "static typing" won't affect your code) than a static language over a NoSQL database (suddenly you start to lose guarantees about what goes in every field, and checks must be performed).

Someone who is comfortable with strong guarantees in the database may be more willing to use a dynamically-typed language for the application, and (less commonly) vice-versa.

Consider this long open high priority enhancement to MongoDB: https://jira.mongodb.org/browse/SERVER-863

Without a fixed schema, the schema must be stored with each item. Every "row" not only has the data but the metadata as well, including field names.

If you have a lot of repeatable data this can consume very significant amounts of space (regularly on the order of 4x comparing PG rows to BSON docs). This isn't just a disk space issue, this means less caches and more pages to scan when doing any operation. Compression only improves the disk space issue while adding CPU overhead.

Relational DB's like PostgreSQL having a fixed schema need only store it one time, each row has minimal overhead (about 23 bytes in PG) to store only the unique data.

Perhaps Mongo could optimize behind the scenes, detecting a repeating schema and making a fixed schema data structure for it similar to how the V8 javascript engine creates C++ classes for javascripts objects so its not a key-value lookup everywhere limiting performance. I haven't seen any NoSql db attempt that, I don't think any even attempt simple string interning as suggest in Mongos 863 issue.

PG gives me a choice I can go nearly schemaless with the JSON type or I can create a fixed schema when that makes sense, with many optimized data types to choose from. PG having array support give you a nice middle ground between the two as well.

So again why do I need a second data model that does nothing but get in my way ?

In many document stores, you have to write manual map/reduce jobs telling the system how to get the data you want.

Yes. The migrations might become expensive (though that varies between databases - some take exclusive locks for a long time, others don't), but you can save a lot of time and money when querying the data.

In a reasonably normalized schema, there's next to nothing that you can't extract with the help of SQL and whatever you throw at it, databases have become very good at planning the optimal way to query for the data you've requested.

If all your data is in huge blobs, querying for arbitrary conditions and aggregating parts of those blobs is complicated and requires manual planning of every query you may come up with.

If your data is well normalized, whatever SQL you throw at it, the database will know how to answer. No specific retrieval code needed.

As such I would argue that a strict schema and a relational database will save you a lot of time as the requirements of your application change.

2.) The document based approach doesn't say anything about the degree of data normalization - you can have your data as normalized or denormalized as you want, just as in relational databases. Each row can correspond to one document, the containing table usually corresponds to a 'type' property on the document. Following this approach, building views / queries is about as powerful as it can be in a RDBMS, the trade-off is rather between strictness in forbidding/allowing slow algorithms vs. ease of implementing a first working version.

Yes, but I already have one of those in the form of a static type system. I dislike database schema for the same reason I dislike XML schema: it tends to enforce only the most trivial constraints, and means you duplicate part of your validation (because you still want validation in code for the things you can't check in the schema). It's just another moving part that can be a potential source of failures.

a. Schema changes are considered DDL statements and hence can't be simply rolled back via transaction in most databases (as far as I know).

b. If you have millions or more rows and you need to introduce a schema change, it can get painful to do so and can result in app downtime. It is a well documented problem with some well documented work arounds.

Put simply: MySQL was written by and for people whose time isn't valuable. I.e. the assumption is that it's cheaper to throw low-grade people and brainpower at workarounds (OpEx) than to pay to have the problems avoided in the first place (CapEx). There's a reason people shell out the money for big-iron databases: the math can be easy when you factor in the costs saved (both the direct costs and the opportunity costs).

In the database of my choice (Postgres. Since 7.1), DDL is transactional and can be rolled back.

>b. If you have millions or more rows and you need to introduce a schema change, it can get painful to do so and can result in app downtime.

In many databases and for many schema changes the workarounds are trivial (like adding columns without a default value).

Companies such as Percona also provide open-source tools which allow for online, non-locking schema changes for Postgres, and I assume commercial database engines have options as well.