Open Stack

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.
"""

> 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.

-Rob