Open Stack

Excerpts from Mike Bayer's message of 2016-08-30 14:56:15 -0400:
> 
> On 08/30/2016 09:57 AM, Clint Byrum wrote:
> >>
> >
> > As someone else brought up, this is an unnecessarily bleak view of how database
> > migrations work.
> 
> We aren't talking about database migrations.  We are talking about 
> *online* database migrations, where we would like both the *old* and 
> *new* versions of the code, talking to the database at the same time.
> 
> 
> If I write code that does this:
> 
> 
>      SELECT foo, bar FROM table
> 
> then I do a migration that replaces "bar" with some new table, the new 
> SQL is:
> 
>      SELECT table.foo, othertable.bar FROM table JOIN othertable ON 
> table.id == othertable.foo_id
> 
> Those two SQL statements are incompatible.  The "new" version of the 
> code must expect and maintain the old "bar" column for the benefit of 
> the "old" version of the code still reading and writing to it.   To me, 
> this seems to contradict your suggestion "don't delete columns, ignore 
> them".  We can't ignore "bar" above.
> 

It's hard to think about what you're saying without concrete examples,
but I'll try.

As I said, don't remove columns, ignore them. Of course, you can't ignore
them on writes, they still exist. If you have a new relationship for that
data, then yes, you still have to write to the old columns and tables so
that older versions of the code can find the data it needs.

Your join needs to be a left join, so that you get the data from the old
table when it was written by old code.

> >
> > Following these commandments, one can run schema changes at any time. A
> > new schema should be completely ignorable by older code, because their
> > columns keep working, and no new requirements are introduced. New code
> > can deal with defaulted new columns gracefully.
> 
> You need to specify how new code deals with the above two totally 
> different SQL statements "gracefully", except that it has to accommodate 
> for both versions of the schema at the same time.   This may be 
> "graceful" in operator land but in developer land, there is no easy 
> solution for this.  Unless there is, and nobody has shown it to me yet:
> 

Correct, it is harder for development. Since the database server has all
of the potential for the worst problems, being a stateful service, then
I believe moving complexity _out_ of it, is generally an operational
win, at the expense of some development effort. The development effort,
however, is mostly on the front of the pipeline where timelines can be
longer. Operations typically is operating under SLA's and with
requirements to move slowly in defense of peoples' data and performance
of the system. So I suggest that paying costs in dev, vs. at the
database is usually the highest value choice.

This is of course not the case if timelines are short for development as
well, but I can't really answer the question in that case. For OpenStack,
we nearly always find ourselves with more time to develop, than operators
do to operate.

> > I don't think it's all that ambitious to think we can just use tried and
> > tested schema evolution techniques that work for everyone else.
> 
> People have been asking me for over a year how to do this, and I have no 
> easy answer, I'm glad that you do.  I would like to see some examples of 
> these techniques.
> 
> If you can show me the SQL access code that deals with the above change, 
> that would help a lot.
> 

So schema changes fall into several categories. But basically, the only
one that is hard, is a relationship change. Basically, a new PK. Here's
an example:

Book.isbn was the PK, but we want to have a record per edition, so the
new primary key is (isbn, edition).

Solution: Maintain two tables. You have created an entirely new object!

CREATE TABLE book (
  isbn varchar(30) not null primary key,
  description text,
)

CREATE TABLE book_editions (
  isbn varchar(30) not null,
  edition int not null,
  description text,
  primary key (isbn, edition),
)

And now on read, your new code has to do this:

SELECT b.isbn,
       COALESCE(be.edition, 0) AS edition,
       COALESCE(be.description, b.description) AS description
FROM book b
     LEFT OUTER JOIN book_editions be
     ON b.isbn = be.isbn
WHERE b.isbn = 'fooisbn'

And now, if a book has only ever been written by old code, you get one
record with a 0 edition. And if it were written by the new system, the
new system would need to go ahead and duplicate the book description into
the old table for as long as we have code that might expect it.

Most other things are simpler and have quite obvious solutions.

> If the answer is, "oh well just don't do a schema change like that", 
> then we're basically saying we aren't really changing our schemas 
> anymore except for totally new features that otherwise aren't accessed 
> by the older version of the code.  That's fine.   It's not what people 
> coming to me are saying, though.
> 

I mean, yes and no. We should pay some respect to operators who have to
deal with our desire for the schema to be "Right". If it scales well,
maintains integrity, and is 98% clear and well formed, then that 2%
where we store the "project id" redundantly in the "tenant_id" column
for a few releases, that isn't really a bother to me.