matc

> The application can move straight to supporting the new structure and entirely forget that the old one existed.

Not always. Changing the schema can break an application, in particular when the database supports multiple applications.

> It's simple data and some dangling records aren't going to hurt anyone.

It depends on the application. In healthcare people literally die due to some dangling inconsistent records.

The problem in anticipating and working around these inconsistencies is that the workaround is added ad-hoc in code, rather than through a model defining the data change. You need a model http://chronicdb.com/preparing_schema_changes

The fact that ALTER TABLE locks on most databases is not the primary reason changing a schema is hard. The primary reason is that a schema change breaks the application [1].

Migrations are programs, but your existing live application is also a program. Changing the schema breaks your live application to prepare for the new version of the application.

[1] - http://chronicdb.com/blogs/change_is_not_the_enemy

Moving databases between clusters is no easy feat. Can you share more details on the man-hours it took to oversee and verify the migration?

I see you mention several days for the data to be copied, but how about the time to put your solution together and verify it? Also, did recomputing checksums take less time between passes, proportional to the size of the remaining changes?

We didn't find much published material about the best way to do this,

You may want to have a look at our live migration solution: http://chronicdb.com

It goes beyond checksums: it guarantees the data move is atomic and data consistent.

You can easily drop the oldest month every 1st using a delete statement.

Queries can be fast with indexes without adding complexity.

If you are up for SQL, the downtime issue is solvable with ChronicDB pain-free.

1. Thank you for the link on views being updateable with some (natural) restrictions (http://news.ycombinator.com/item?id=3406952). Given this feature, SQL offers the primitives needed. Even if this feature is missing from various databases at the moment, it should be available eventually.

But updateable views are still not what you want! See below.

2. What ChronicDB adds in terms of backward compatibility is, effectively, automatic creation and management of these views for every schema change.

Rather than manually write compatibility views and rewrite/review an application to ensure it always uses these views, programmers instead access plain tables, not views. In fact ChronicDB creates no views at all!

You want to issue SQL queries that don't break. You don't want to manage views to do so. It's a subtle, yet major point. No database to date offers such automated versioning.

ChronicDB of course adds other features we wanted to have from our database (reversibility, data versioning, zero-downtime relocation) again in the spirit of saving the programmer/DBA from doing anything manually.

You are right, this aspect is not new. More like half-baked.

I'm not sure what you mean by bidirectional. If this suggests having a separate copy for the old data and the new data then, in ChronicDB at least, no they are not bidirectional. Which is what one wants ideally: data consistency.

If you mean that updates on the old version are immediately reflected in the new version, then yes.

Basically, there's a single version of the truth, while old and new relations can have different names, for all DML operations.

If you change the schema then the old version of the app can break. For example, if two tables are merged together.

If one suggests old data and new data be stored separately then the door to data inconsistency opens.

Views are the reflection of the light at the end of the tunnel, but solve less than half the problem to date. Views work on SELECT, but not on UPDATE/INSERT/DELETE.

Also, what makes you say that database as an integration layer should always be avoided?

This is what you are missing: > but that is always true

You may have an old form you can't change.

For example:

- 10 apps connected to the same database. Changing the model breaks 10 apps. And you may not have access to change the code to any of those apps.

- In healthcare, when either the database or app go down people literally die.

This isn't as easy as it gets: no database vendor has a solution for this to date.

(Edit: formatting)

Schema-free != handling data model changes transparently.

We are actually working on this at ChronicDB http://chronicdb.com

On 6ren's rigidity argument, relations and SQL are still not enough to guarantee you'll always be able to get the data you need; notably, when the data model changes. Backward compatibility is important and we might have they only solution that guarantees it.

Agreed on backwards compatible, but for application semantics; not types of schema changes.

There's no reason to not be allowed to both add and remove a column at the same time, or to merge and split whole tables. In your example, these kinds of changes would not be possible.

There's also no reason to not be able to run old and new code at the same time, or to revert a schema change.

With ChronicDB we reduced schema changes to:

  $ chd change -f upgrade_map mydb

Schemaless databases don't solve this, just as an instantaneous ALTER TABLE won't solve this.

Why not?

There is no efficient safety interlock system when modifying data.

Even if confident your test passed, what happens when you later realize it hadn't, and at what cost?

The denormalization argument is misguided.

Denormalization is a performance consideration, and now even happens transparently at the storage layer in some RDBMs. So restructuring a 'live' database should be done for semantic reasons: because the workflow changes; because the app needs to change.

Why isn't the CAN-SPAM act enforced, say in the US?

Would it be impossible to make email spam illegal?

Why was it possible to make SMS spam, but not email, illegal?

There are email gateways to all major phone providers, so one can send, rather than receive, text messages without cost.