I'm probably really ignorant asking this, but how do you "pause" schema migrations period. And even if you did, how do you ensure a consistent experience for your users if your db is broken? Some sort of application logic to deal with inconsistencies? That seems really expensive (from a development work perspective).
With gh-ost migrations are performed on a copy of the table. This, combined with the way data is copied to this table mean:
- you can pause just by suspending the copy,
- changes are invisible until the end, when tables are swapped.
The first point depends on the mechanism used to keep up with changes to the original table. You can’t fully pause migrations on pt-online-schema-change for example, as it leverages triggers for that part.
From my phone so sorry if too brief, gh-ost’s docs are great and would tell the whole story.
It's all described in the readme, but generally online schema change tools work by creating a new table, copying the data from the old table over, somehow keeping track of new writes to the old table, and then syncing those over. At the end the tables are swapped. With gh-ost you can pause the writes to the new table.
Not OP but I’m familiar with the topic and run similar tooling on large clusters. By pause he probably means prevent it from starting on more databases and let whatever is inflight finish.
For the second point, correct, your application needs to handle both schemas during transition. When that’s done, you can rip out the unneeded logic from your application.
> your application needs to handle both schemas during transition.
How is this typically done? Have a version number in the db? Have the app examine the schema with every transaction? Have the app assume old/new schema optimistically, and if that fails rollback and try with alt schema? Something else?
