On 31 August 2016 at 01:57, Clint Byrum <cl...@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 __________________________________________________________________________ OpenStack Development Mailing List (not for usage questions) Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
