Why no rolling-back completed migrations?

[wpcarro]

I've been using alembic for database migrations for ~1y now, but I'm considering switching to pgroll because I need to migrate O(100M) rows with minimal downtime.

Is there a reason pgroll doesn't support rolling-back completed migrations? I can upgrade and downgrade using alembic. AFAICT my options are:

• Use alembic and pgroll
• Use alembic to write the O(100M) row migration using the same concepts that pgroll uses (i.e. expand/contract, triggers)

I would love to fully switch over to pgroll, but being able to downgrade would make me more comfortable.

[andrew-farries]

Hi @wpcarro 👋

pgroll supports rolling back migrations in the 'active migration period' ie, the time between pgroll start and pgroll complete. There is no limit to how long a migration can remain in this state, only that the migration must be completed before another can be started.

Once the migration is completed, the database schema is contracted to leave the final desired state of the schema. This is a lossy operation; for example if you have altered a column to add a check constraint with a migration that involves a data migration, like this:

{
  "name": "22_add_check_constraint",
  "operations": [
    {
      "alter_column": {
        "table": "posts",
        "column": "title",
        "check": {
          "name": "title_length",
          "constraint": "length(title) > 3"
        },
        "up": "(SELECT CASE WHEN length(title) <= 3 THEN LPAD(title, 4, '-') ELSE title END)",
        "down": "title"
      }
    }
  ]
}

Then during the active migration period the database schema has an additional _pgroll_new_title field which contains the migrated values from the title field. Rolling back from this state is straightforward - remove the _pgroll_new_title field. The existing title field remains unchanged. Once the migration is completed however, the old title field is dropped and the new one is renamed to title. There is no way back from this operation - the old title field is gone forever.

In your case, is it possible that you can keep the migration active (ie started but not completed) for as long as you need to verify the correctness of the migration?