Actually we're on postgres... It just makes me nervous when the migration is taking upwards of 10 minutes to complete since some of our tables have millions of rows and lots of concurrent read/writes are happening.
If the site isn't taken down during an update, some parts of it will always be broken during a schema migration since our ORM expects each table corresponding to a data model to be in sync after a code update. I guess you could always version your data models and handle the pre-upgrade and post-upgrade cases, but that seems like a lot of work in our situation.
I find this interesting because for a previous project, we had to manage separate database instances for each client (regulations). Even though we had a database management tool that allowed us to push schema changes out to the various DBs automatically, it would be better to not have to push those changes out at all.
Almost all major oracle operations can be done online ... Index rebuilds, table reorgs etc. Depends how much hardware you have how much it affects things ...
True in this case, but not always true, the issue often isn't upgrading the schema, it's that there can be no single schema that can hold the data because it varies on a per row basis. No RDBMS can deal with this well, no matter how mature, it's just not what they're designed for. This is trivial for an OODBMS which just stores raw objects, no matter their shape.
Comments
It's really nice for not having to bring down the site for schema upgrades.
All the other popular databases let you modify the schema online. This feature has been taken for granted for over a decade.
Actually we're on postgres... It just makes me nervous when the migration is taking upwards of 10 minutes to complete since some of our tables have millions of rows and lots of concurrent read/writes are happening.
I don't think executing long-running DDL with concurrent read/writes is that bad, especially if the DDL is additive and doesn't lock the whole table.
Concurrency - isn't that what a DB is supposed to do well?
If the site isn't taken down during an update, some parts of it will always be broken during a schema migration since our ORM expects each table corresponding to a data model to be in sync after a code update. I guess you could always version your data models and handle the pre-upgrade and post-upgrade cases, but that seems like a lot of work in our situation.
I find this interesting because for a previous project, we had to manage separate database instances for each client (regulations). Even though we had a database management tool that allowed us to push schema changes out to the various DBs automatically, it would be better to not have to push those changes out at all.
Without locking tables?
Yes.
Indeed. This is not a RDBMS problem. Its a MySQL problem and the correct solution is to use a mature DB.
Even in Oracle, we've had to schedule downtime for a single column schema change and population for 45 minutes. I would call Oracle "mature."
Note: I wasn't involved in doing the update, so it's possible there was a better way to do it. The table is used by about 50 different applications.
Almost all major oracle operations can be done online ... Index rebuilds, table reorgs etc. Depends how much hardware you have how much it affects things ...
True in this case, but not always true, the issue often isn't upgrading the schema, it's that there can be no single schema that can hold the data because it varies on a per row basis. No RDBMS can deal with this well, no matter how mature, it's just not what they're designed for. This is trivial for an OODBMS which just stores raw objects, no matter their shape.