Open Stack

Excerpts from Robert Collins's message of 2016-09-01 20:45:22 +1200:
> On 31 August 2016 at 01:57, Clint Byrum <clint at fewbar.com> wrote:
> >
> >
> > It's simple, these are the holy SQL schema commandments:
> >
> > Don't delete columns, ignore them.
> > Don't change columns, create new ones.
> > When you create a column, give it a default that makes sense.
> 
> I'm sure you're aware of this but I think its worth clarifying for non
> DBAish folk: non-NULL values can change a DDL statements execution
> time from O(1) to O(N) depending on the DB in use. E.g. for Postgres
> DDL requires an exclusive table lock, and adding a column with any
> non-NULL value (including constants) requires calculating a new value
> for every row, vs just updating the metadata - see
> https://www.postgresql.org/docs/9.5/static/sql-altertable.html
> """
> When a column is added with ADD COLUMN, all existing rows in the table
> are initialized with the column's default value (NULL if no DEFAULT
> clause is specified). If there is no DEFAULT clause, this is merely a
> metadata change and does not require any immediate update of the
> table's data; the added NULL values are supplied on readout, instead.
> """
> 

InnoDB (via MySQL) has no such restrictions for online DDL:

https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid

Basically what the link above says is that anything except these
operations can be done without locking up the table:

- Fulltext index creation
- Change column data type
- Convert or specify column character sets

Specifically, defaults are only ever stored in the rows if they're
changed. The current default is kept in the table definition, so the
rows end up with NULL physically unless the default is changed. An alter
that does a default change is just like a big update to set the current
NULL's to the old default.

> > Do not add new foreign key constraints.
> 
> What's the reason for this - if it's to avoid exclusive locks, I'd
> note that the other rules above don't avoid exclusive locks - again,
> DB specific, and for better or worse we are now testing on multiple DB
> engines via 3rd party testing.
> 
> https://dev.launchpad.net/Database/LivePatching has some info from our
> experience doing online and very fast offline patches in Launchpad.
> 

The reason is to avoid the old code running into new restrictions. If
you add a FK constraint to an existing table, old code will insert into
it and fail because it doesn't add the FK rows needed.