3 ms·

I always see people asking about this. But why? Are you making schema changes that frequently?

by jjeaff 5y ago

handrous 5y ago
Schema changes are pretty much the riskiest damn thing you can do with a database, so it's natural people would be extremely interested in being able to confine as much of that as possible to transactions, even if changes are rare (which, arguably, they're rare in no small part because they're so risky).
simonw 5y ago
It's my opinion that schema changes should be cheap, fast and unexciting. Unfortunately, in my experience once you are operating at scale they are expensive, slow and fraught with peril. The result is that engineering teams often chose to build poorly designed schemas and take on technical debt rather than make a schema change to a core table. This is a terrible anti-pattern! I'm massively in favour of anything that makes schema changes cheaper and more boring. I really enjoy working with the Django schema migration system for this reason - though it won't help you as much if you need to make schema changes to older versions of MySQL (I've not tried schema changes against 8.0 yet) since you'll likely still need to use GitHub ghost or Percona pt-online-schema-change.
- spullara 5y ago
  Before Twitter migrated off their MySQL user database there were many columns that had been repurposed for exactly this reason.
  - simonw 5y ago
    I've been contemplating adding a JSON column to all of my tables just to provide somewhere that covers those cases - still controlled with source-code level validation and with the expectation that any column which needs to be used for filtering rows gets promoted to "real column" status. I'd much rather use a robust, performant real schema migrations tool though.
- gfody 5y ago
  yeah treating every schema change as a db migration might be our industry's worst anti-pattern. transactional ddl allows you to do stuff like: begin tran ..refactor data, drop/create tables.. ..create backwards-compatible views.. commit ..against a live database without risking consistency
  - jjeaff 5y ago
    I would have very few cases where that works for me. Because I almost always need the database layer to continue to work for a time with both the old code and the new code update. Deployments usually roll out to pods a few at a time so changing the schema of a table suddenly will immediately throw errors all over the place.
    - gfody 5y ago
      > Because I almost always need the database layer to continue to work for a time with both the old code and the new code update. that's what the backwards-compatible views are for
- iamaperson 5y ago
  https://www.planetscale.com/blog/non-blocking-schema-changes https://www.planetscale.com/blog/non-blocking-schema-changes disclaimer: I work at PlanetScale
- lclarkmichalek 5y ago
  https://github.com/facebookincubator/OnlineSchemaChange https://github.com/facebookincubator/OnlineSchemaChange
yxhuvud 5y ago
Yes? Also, I want them to not be able to break halfway.
hans_castorp 5y ago
> But why? Are you making schema changes that frequently? Because it makes the migrations much more robust and way easier to develop & test. If something goes wrong in a migration script, everything is rolled back. You edit the part that caused the error and simply run the whole script again. And despite of thorough testing, there is always the possibility that it fails when applied in production. You want that migration to be completely successful or not at all. Think e.g. splitting a table into two (which also includes DML) or moving columns around while creating new foreign keys. You might not need to put the whole migration into a single transaction, but at least combine some steps into one - but this is only possible if the DDL statements are transactional.
pmarreck 5y ago
Have you never written a migration in development that broke halfway through and required you to manually repair it? Because I used to have to do that all the time seemingly before I finally got to pick my toolchain and I chose Postgres

I always see people asking about this. But why? Are you making schema changes that frequently?

by jjeaff 5y ago

I always see people asking about this. But why? Are you making schema changes that frequently?

Schema changes are pretty much the riskiest damn thing you can do with a database, so it's natural people would be extremely interested in being able to confine as much of that as possible to transactions, even if changes are rare (which, arguably, they're rare in no small part because they're so risky).

simonw 5y ago

It's my opinion that schema changes should be cheap, fast and unexciting. Unfortunately, in my experience once you are operating at scale they are expensive, slow and fraught with peril. The result is that engineering teams often chose to build poorly designed schemas and take on technical debt rather than make a schema change to a core table. This is a terrible anti-pattern! I'm massively in favour of anything that makes schema changes cheaper and more boring. I really enjoy working with the Django schema migration system for this reason - though it won't help you as much if you need to make schema changes to older versions of MySQL (I've not tried schema changes against 8.0 yet) since you'll likely still need to use GitHub ghost or Percona pt-online-schema-change.

spullara 5y ago
Before Twitter migrated off their MySQL user database there were many columns that had been repurposed for exactly this reason.
- simonw 5y ago
  I've been contemplating adding a JSON column to all of my tables just to provide somewhere that covers those cases - still controlled with source-code level validation and with the expectation that any column which needs to be used for filtering rows gets promoted to "real column" status. I'd much rather use a robust, performant real schema migrations tool though.
gfody 5y ago
yeah treating every schema change as a db migration might be our industry's worst anti-pattern. transactional ddl allows you to do stuff like: begin tran ..refactor data, drop/create tables.. ..create backwards-compatible views.. commit ..against a live database without risking consistency
- jjeaff 5y ago
  I would have very few cases where that works for me. Because I almost always need the database layer to continue to work for a time with both the old code and the new code update. Deployments usually roll out to pods a few at a time so changing the schema of a table suddenly will immediately throw errors all over the place.
  - gfody 5y ago
    > Because I almost always need the database layer to continue to work for a time with both the old code and the new code update. that's what the backwards-compatible views are for
iamaperson 5y ago
https://www.planetscale.com/blog/non-blocking-schema-changes https://www.planetscale.com/blog/non-blocking-schema-changes disclaimer: I work at PlanetScale
lclarkmichalek 5y ago
https://github.com/facebookincubator/OnlineSchemaChange https://github.com/facebookincubator/OnlineSchemaChange

yxhuvud 5y ago

Yes? Also, I want them to not be able to break halfway.

hans_castorp 5y ago

> But why? Are you making schema changes that frequently? Because it makes the migrations much more robust and way easier to develop & test. If something goes wrong in a migration script, everything is rolled back. You edit the part that caused the error and simply run the whole script again. And despite of thorough testing, there is always the possibility that it fails when applied in production. You want that migration to be completely successful or not at all. Think e.g. splitting a table into two (which also includes DML) or moving columns around while creating new foreign keys. You might not need to put the whole migration into a single transaction, but at least combine some steps into one - but this is only possible if the DDL statements are transactional.

pmarreck 5y ago

Have you never written a migration in development that broke halfway through and required you to manually repair it? Because I used to have to do that all the time seemingly before I finally got to pick my toolchain and I chose Postgres