6 ms·

> 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. If I delete a "last_n

> 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. If I delete a "last_name" column, apply the migration, and then decide I shouldn't have deleted users' last names. Do I get that data back?

deleted 3y ago
[deleted]
indigo945 3y ago
Just from my understanding from having read the linked website: yes, you do. "Applying the migration" doesn't actually do anything to the table, it just creates a new schema containing views over the old one, where the view for the table whose column you deleted hides the column. You can then try if your app still works when using accessing this schema instead of the old one. If you're happy, you can "complete" the migration, at which point only the table structure actually gets altered in a non-reversible way.
- exekias 3y ago
  ^ this is exactly how it works :)
  - javaunsafe2019 3y ago
    But if it works like that aren’t there schema migration paths that are changing the actual content of a column and are then not undoable?
    - pcthrowaway 3y ago
      Also, if the data isn't deleted couldn't this lead to database bloat?
      - surjection 3y ago
        The bloat incurred by the extra column is certainly present while the migration is in progress (ie after it's been started with `pgroll start` but before running `pgroll complete`). Once the migration is completed any extra columns are dropped.
    - surjection 3y ago
      Any pgroll operations[0] that require a change to an existing column, such as adding a constraint, will create a new copy of the column and backfill it using 'up' SQL defined in the migration and apply the change to that new column. There are no operations that will modify the data of an existing column in-place, as this would violate the invariant that the old schema must remain usable alongside the new one. [0] - https://github.com/xataio/pgroll/tree/main/docs#operations-reference https://github.com/xataio/pgroll/tree/main/docs#operations-r...
      - darkwater 3y ago
        Maybe this is explained somewhere in the docs but I'm lazy: how does it cope with possible performance issue in highly trafficked tables? Can you somehow control the backfill speed if it's taking up too much I/O?
        surjection 3y ago
        There's nothing about this in the docs :) Backfills are done in fixed size batches to avoid taking long-lived row locks on many rows but there is nothing in place to control the overall rate of backfilling. This would certainly be a nice feature to add soon though.

deleted 3y ago

[deleted]

indigo945 3y ago

Just from my understanding from having read the linked website: yes, you do. "Applying the migration" doesn't actually do anything to the table, it just creates a new schema containing views over the old one, where the view for the table whose column you deleted hides the column. You can then try if your app still works when using accessing this schema instead of the old one. If you're happy, you can "complete" the migration, at which point only the table structure actually gets altered in a non-reversible way.

exekias 3y ago
^ this is exactly how it works :)
- javaunsafe2019 3y ago
  But if it works like that aren’t there schema migration paths that are changing the actual content of a column and are then not undoable?
  - pcthrowaway 3y ago
    Also, if the data isn't deleted couldn't this lead to database bloat?
    - surjection 3y ago
      The bloat incurred by the extra column is certainly present while the migration is in progress (ie after it's been started with `pgroll start` but before running `pgroll complete`). Once the migration is completed any extra columns are dropped.
  - surjection 3y ago
    Any pgroll operations[0] that require a change to an existing column, such as adding a constraint, will create a new copy of the column and backfill it using 'up' SQL defined in the migration and apply the change to that new column. There are no operations that will modify the data of an existing column in-place, as this would violate the invariant that the old schema must remain usable alongside the new one. [0] - https://github.com/xataio/pgroll/tree/main/docs#operations-reference https://github.com/xataio/pgroll/tree/main/docs#operations-r...
    - darkwater 3y ago
      Maybe this is explained somewhere in the docs but I'm lazy: how does it cope with possible performance issue in highly trafficked tables? Can you somehow control the backfill speed if it's taking up too much I/O?
      - surjection 3y ago
        There's nothing about this in the docs :) Backfills are done in fixed size batches to avoid taking long-lived row locks on many rows but there is nothing in place to control the overall rate of backfilling. This would certainly be a nice feature to add soon though.