4 ms·

Help me understand the value of undoable migrations. I've always operated under "the only path is forward" and you release a new version that fixes the issue

Help me understand the value of undoable migrations. I've always operated under "the only path is forward" and you release a new version that fixes the issue or create a new migration that does some kind of partial rollback if necessary. Once a migration is live, naively rolling things back seems like you're asking for problems. I also only perform migrations as part of app init, not separately.

contravariant 3y ago
I don't think 'undoable' is the clearest description, the crux is this: > Keep two versions of the schema (previous and next) accessible at the same time during the whole migration process This has some obvious advantages. Like you said you can't easily roll back once a migration is fully live, but it helps a lot if you can cancel a migration once it turns out it doesn't work.
- tudorg 3y ago
  Yes, that's exactly it. I generally agree with "always move forward" but if rolling back is as easy as dropping the "new" view, that makes it a lot less scary.
- candiddevmike 3y ago
  Isn't that what transactions are for?
  - contravariant 3y ago
    That would be hard to coordinate if your application is even slightly complicated.
    - candiddevmike 3y ago
      Not really... - Start migration, create transaction and locks - Do migration things - Do some SELECTs, INSERTS, or w/e in your code to validate the schema matches your model. - End transaction or rollback if failure None of this is free, but it's by no means hard IMO. I do something similar in Go: https://github.com/candiddev/shared/blob/main/go/postgresql/postgresql.go#L299 https://github.com/candiddev/shared/blob/main/go/postgresql/...
      - ttfkam 3y ago
        > Start migration, create transaction and locks If you do that on a table or two with millions of rows, you've effectively locked your database from all other access, even SELECTs. ALTER statements typically grab exclusive locks, blocking both writes and reads. Chain a couple together and you're looking at high CPU, queued queries, and an unresponsive database for hours. Once you realize what's happening and kill the migration in a panic, it may take tens of minutes or more for the DB to revert back to its initial state, much to everyone's impatient horror. In simple programs and databases, everything is possible and quick. Complex programs and large databases disabuse you of your naïveté with a quickness. I love Postgres's transactional DDL, but as the article's mention of renaming columns highlights, one transaction simply isn't enough for the job.
        candiddevmike 3y ago
        The locks I was referring to were more around advisory locks to prevent multiple migrations from running. For the locks you mention, all migrations create some kind of table lock typically, it's up to the dev to make them as small as necessary. I don't follow your strawman.
        bananapub 3y ago
        > I don't follow your strawman. how large are the DB migrations you've done? your experience is seemingly completely different to mine.
        ttfkam 3y ago
        The locks I was referring to were a direct result of many (most?) ALTER statements. Multiple clients trying to migrate isn't even in this equation. Just one client migrating can ruin your day with a single ALTER statement given a large enough database. What's the biggest table you've ever had to modify? Adding a column with a default is easy. What about deleting a column from a table with 10s of millions of rows? Renaming a column? Changing a column's type? Adding a CHECK constraint? Adding a NOT NULL? Adding a foreign key constraint that someone accidentally removed in the previous migration while data keeps flowing? Those concerns aren't about your explicit advisory locks. Not by a long shot. https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES https://www.postgresql.org/docs/current/explicit-locking.htm...
  - vanviegen 3y ago
    That wouldn't allow you to partially roll out your new code base (depending on the altered schema), nor to easily revert such a roll out.
Nullabillity 3y ago
During development I'll often keep rolling the same change back and forth (with minor changes), rather than recreate the database from scratch/a backup each time.
exekias 3y ago
I believe this is one of the reasons why migrations become scary in many cases. If something goes wrong "the only path is forward". Also, rolling out new versions of the application means either breaking the previous versions (with some instances still running) or doing the migration in several steps. We believe there is a better way, they way pgroll works, you can start a migration, and keep the old & new schemas working for as long as you need to rollout your app. If the new version of the app/schema doesn't behave as you were expecting, you only need to rollback the commit and undo the migration. pgroll guarantees that the previous version is still working during the whole process. There is a graph in the readme depicting this concept: https://github.com/xataio/pgroll/blob/main/docs/img/migration-flow%402x.png https://github.com/xataio/pgroll/blob/main/docs/img/migratio...
- candiddevmike 3y ago
  That's great that pgroll does this, but the heavy lifting for supporting this comes at a huge cost on the application side, IMO.
  - surjection 3y ago
    Do you mean the extra configuration required to make applications use the correct version of the database schema, or something else?
    - candiddevmike 3y ago
      Yea, keeping your application consistent with two different schema versions. And I'm not saying from a blue/green standpoint, from whatever pgroll does instead so when the rollback happens you don't lose data.
      - matsemann 3y ago
        Eh, isn't this making it easier on the application side? Today, when I make a change in my app that needs a migration, I need multiple steps to make sure I don't break old instances of the app still running. With this it looks like one can avoid all that?
bityard 3y ago
If your organization requires change management (as many are contractually obligated to), then you don't have much of a choice. Every change needs to be tested, every change needs a way to fully and completely roll back the change, and the rollback has to be tested. Additionally, the people executing the change are not necessarily those who have developed the change. They need two big buttons: Do and Undo. If the change fails or breaks something, they hit the Undo button and tell the developers about it and by the way, here are the logs, please go reproduce it on the test system and try again. I know this is not "devops," but it's still how a lot of high-availability software deployments work, particularly when hampered by bureaucratically-imposed processes. Finally, database schema changes are a fine way to irreversibly munge your data if you are not careful. (This goes beyond SQL.) If that happens, there is no such thing as a path forward, the only way to save the ship is to restore from your most recent backup.
nobleach 3y ago
A couple of places I've worked lived like this. We used the free version of Flyway with Spring Boot and Quarkus. We got by really well "rolling forward". My next gig used Sequalize and MySQL. Aside from hating those technologies, I hated that false sense of security baked into "oh well, we can always roll back"
- cptMayhem 3y ago
  > I hated that false sense of security baked into "oh well, we can always roll back" That's why I like pgroll's approach, in that there isn't really a "rollback procedure" that might make things worse during emergencies, but rather old and new schemas that remain working simultaneously until the migration is marked as complete and there are no clients using the old schema. "Rolling back" is actually cancelling the new schema migration and falling back to the previous version that's been working at all times, thus minimizing the risk.

contravariant 3y ago

I don't think 'undoable' is the clearest description, the crux is this: > Keep two versions of the schema (previous and next) accessible at the same time during the whole migration process This has some obvious advantages. Like you said you can't easily roll back once a migration is fully live, but it helps a lot if you can cancel a migration once it turns out it doesn't work.

tudorg 3y ago
Yes, that's exactly it. I generally agree with "always move forward" but if rolling back is as easy as dropping the "new" view, that makes it a lot less scary.
candiddevmike 3y ago
Isn't that what transactions are for?
- contravariant 3y ago
  That would be hard to coordinate if your application is even slightly complicated.
  - candiddevmike 3y ago
    Not really... - Start migration, create transaction and locks - Do migration things - Do some SELECTs, INSERTS, or w/e in your code to validate the schema matches your model. - End transaction or rollback if failure None of this is free, but it's by no means hard IMO. I do something similar in Go: https://github.com/candiddev/shared/blob/main/go/postgresql/postgresql.go#L299 https://github.com/candiddev/shared/blob/main/go/postgresql/...
    - ttfkam 3y ago
      > Start migration, create transaction and locks If you do that on a table or two with millions of rows, you've effectively locked your database from all other access, even SELECTs. ALTER statements typically grab exclusive locks, blocking both writes and reads. Chain a couple together and you're looking at high CPU, queued queries, and an unresponsive database for hours. Once you realize what's happening and kill the migration in a panic, it may take tens of minutes or more for the DB to revert back to its initial state, much to everyone's impatient horror. In simple programs and databases, everything is possible and quick. Complex programs and large databases disabuse you of your naïveté with a quickness. I love Postgres's transactional DDL, but as the article's mention of renaming columns highlights, one transaction simply isn't enough for the job.
      - candiddevmike 3y ago
        The locks I was referring to were more around advisory locks to prevent multiple migrations from running. For the locks you mention, all migrations create some kind of table lock typically, it's up to the dev to make them as small as necessary. I don't follow your strawman.
        bananapub 3y ago
        > I don't follow your strawman. how large are the DB migrations you've done? your experience is seemingly completely different to mine.
        ttfkam 3y ago
        The locks I was referring to were a direct result of many (most?) ALTER statements. Multiple clients trying to migrate isn't even in this equation. Just one client migrating can ruin your day with a single ALTER statement given a large enough database. What's the biggest table you've ever had to modify? Adding a column with a default is easy. What about deleting a column from a table with 10s of millions of rows? Renaming a column? Changing a column's type? Adding a CHECK constraint? Adding a NOT NULL? Adding a foreign key constraint that someone accidentally removed in the previous migration while data keeps flowing? Those concerns aren't about your explicit advisory locks. Not by a long shot. https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES https://www.postgresql.org/docs/current/explicit-locking.htm...
- vanviegen 3y ago
  That wouldn't allow you to partially roll out your new code base (depending on the altered schema), nor to easily revert such a roll out.

Nullabillity 3y ago

During development I'll often keep rolling the same change back and forth (with minor changes), rather than recreate the database from scratch/a backup each time.

exekias 3y ago

I believe this is one of the reasons why migrations become scary in many cases. If something goes wrong "the only path is forward". Also, rolling out new versions of the application means either breaking the previous versions (with some instances still running) or doing the migration in several steps. We believe there is a better way, they way pgroll works, you can start a migration, and keep the old & new schemas working for as long as you need to rollout your app. If the new version of the app/schema doesn't behave as you were expecting, you only need to rollback the commit and undo the migration. pgroll guarantees that the previous version is still working during the whole process. There is a graph in the readme depicting this concept: https://github.com/xataio/pgroll/blob/main/docs/img/migration-flow%402x.png https://github.com/xataio/pgroll/blob/main/docs/img/migratio...

candiddevmike 3y ago
That's great that pgroll does this, but the heavy lifting for supporting this comes at a huge cost on the application side, IMO.
- surjection 3y ago
  Do you mean the extra configuration required to make applications use the correct version of the database schema, or something else?
  - candiddevmike 3y ago
    Yea, keeping your application consistent with two different schema versions. And I'm not saying from a blue/green standpoint, from whatever pgroll does instead so when the rollback happens you don't lose data.
    - matsemann 3y ago
      Eh, isn't this making it easier on the application side? Today, when I make a change in my app that needs a migration, I need multiple steps to make sure I don't break old instances of the app still running. With this it looks like one can avoid all that?

bityard 3y ago

If your organization requires change management (as many are contractually obligated to), then you don't have much of a choice. Every change needs to be tested, every change needs a way to fully and completely roll back the change, and the rollback has to be tested. Additionally, the people executing the change are not necessarily those who have developed the change. They need two big buttons: Do and Undo. If the change fails or breaks something, they hit the Undo button and tell the developers about it and by the way, here are the logs, please go reproduce it on the test system and try again. I know this is not "devops," but it's still how a lot of high-availability software deployments work, particularly when hampered by bureaucratically-imposed processes. Finally, database schema changes are a fine way to irreversibly munge your data if you are not careful. (This goes beyond SQL.) If that happens, there is no such thing as a path forward, the only way to save the ship is to restore from your most recent backup.

nobleach 3y ago

A couple of places I've worked lived like this. We used the free version of Flyway with Spring Boot and Quarkus. We got by really well "rolling forward". My next gig used Sequalize and MySQL. Aside from hating those technologies, I hated that false sense of security baked into "oh well, we can always roll back"

cptMayhem 3y ago
> I hated that false sense of security baked into "oh well, we can always roll back" That's why I like pgroll's approach, in that there isn't really a "rollback procedure" that might make things worse during emergencies, but rather old and new schemas that remain working simultaneously until the migration is marked as complete and there are no clients using the old schema. "Rolling back" is actually cancelling the new schema migration and falling back to the previous version that's been working at all times, thus minimizing the risk.