exekias

I don't know much about Liquidbase, but I believe it doesn't support accessing both the old and the new schema versions at the same time? (I could be wrong here)

Backfilling happens in batches, we use the PK of the table to update all rows, a trigger is also installed so any new insert/update executes the backfill mechanism to update any new column.

More details can be found here: https://github.com/xataio/pgroll/blob/main/pkg/migrations/ba...

To do this with pgroll I would use an alter_column migration, changing the type: https://github.com/xataio/pgroll/tree/main/docs#change-type, this would:

1) Create a new column with the desired type (numeric[][] in your case) 2) Backfill it from the original one, executing the up function to do the casting and any required transformation 3) Install a trigger to execute the up function for every new insert/update happening in the old schema version 4) After complete, remove the old column, as it's no longer needed in the new version of the schema

Backfills are executed in batches, you can check how that works here: https://github.com/xataio/pgroll/blob/main/pkg/migrations/ba...

I don't think any of us has tested pgroll against timescaledb but I would love to know about the results if anyone does!

Yes, this is the case as of today. It's important to get the up/down functions right.

The good thing is that this will probably be detected during the start phase, as data would fail to be backfilled.

We are thinking of ideas to improve this in the longer term, for example: * Having a dry-run mode where you check that the data present in the DB will stay valid after applying the up function * Optionally allow for this to happen, but "quarantine" the affected rows so they are no longer available in the new schema.

This is a good point, I believe we can look into trying to estimate storage needs or timings before a migration. It definitely looks like a nice to have.

For migrations altering columns (for instance adding a constraint), data gets upgraded/downgraded between old and new versions trough the up/down functions. These are defined by the migration. They work like this:

- For rows inserted/updated through the old version of the schema, the up function gets executed, copying the resulting value into the new column - For rows inserted/updated through the new version, the down function gets executed, copying the resulting value into the old column

For instance, you can test that up function works before releasing the new app relying on it, just by checking that the values present in the new schema are correct.

Thanks for reporting! we will look into it

^ this is exactly how it works :)

That is what this project is trying to achieve. By allowing your client apps to access both the old and the new schema at the same time, you can have the old instances of your application working while the new ones (using the new schema) get deployed.

They can work in parallel for a while until you complete the rollout and call the migration as done.

This is actually the case, old and new schemas are available and working until you complete the migration, and you can run this step whenever you want.

The aim is not to deal with conditional logic in the app dealing with both schemas, but having an old version of the app linked to the old schema and the new one using the other.

I didn't fully understand the change that you are explaining, but in general having old & new schemas working relies on the migration definition having the proper `up` & `down` functions defined. These are postgres functions so you can fit any logic in them to ensure that a column deleted in the new schema gets properly backfilled to the old one.

I wonder if there will be cases where this gets too complex or otherwise hits any limits. It may be possible that for those cases a raw SQL migration is required.

We use a few statements that are not supported by previous versions. For instance `CREATE OR REPLACE TRIGGER`.

Supporting previous versions would be possible, but we went for the most recent ones to reduce complexity.

Hi, one of the authors here!

The tool is open source and doesn't require any subscription :)

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/migratio...

Thank you for noticing this! We are looking into changing the wording :)

Hi there, I'm one of the pgroll authors :)

I could be mistaken here, but I believe that pg-osc and pgroll use similar approaches to ensuring no locking or how backfilling happens.

While pg-osc uses a shadow table and switches to it at the end of the process, pgroll creates shadow columns within the existing table and leverages views to expose old and new versions of the schema at the same time. Having both versions available means you can deploy the new version of the client app in parallel to the old one, and perform an instant rollback if needed.

> This looks very nice indeed but I see a few possible problems which I have seen with pg_repack which might apply to this approach as well:

Thank you for your input! I'm one of the pgroll authors :)

> You can't change table names unless you take a lock. How exactly do you switch the original table to be a view pointing to the original table? The docs don't go into detail how this is done exactly, I'll check the code later.

pgroll only performs operations requiring a short lock, like renaming a table. It sets a lock timeout for these operations (500ms by default), to ensure we avoid lock contention if other operations are taking place. We plan to add an automatic retry mechanism for these timeouts so there is no need for manual intervention.

One cool thing about views is that they will automatically get updated when you rename a table/column, so the view keeps working after the rename.

> It looks like the tool maintains two copies of the table but how exactly this copy process is done isn't explained. A potential issue is that you need to have disk space and I/O capacity available to support this. > The copy table + trigger approach might not work for databases of significant size. For example I have seen instances with >50k qps on a table where it is not possible to run pg_repack because it never catches up and it also doesn't ever manage to take the lock which is needed to switch to the new table. This can be simulated with overlapping long running queries.

pgroll doesn't really copy full tables, but individual columns when needed (for instance when there is a constraint change). It is true that I/O can become an issue, backfilling is batched but the system should have enough capacity for it to happen. There are some opportunities to monitor I/O and throttle backfilling based on it.