pgroll

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-r...

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

An example would make this more concrete. This migration[0] adds a CHECK constraint to a column. When the migration is started, a new column with the constraint is created and values from the old column are backfilled using the 'up' SQL from the migration. The 'up' SQL rewrites values that don't meet the constraint so that they do. The same 'up' SQL is used 'on the fly' as data is written to the old schema by applications - the 'up' SQL is used (as part of a trigger) to copy data into the new column, rewriting as necessary to ensure the constraint on the new column is met. As the sibling comment makes clear, it is currently the migration author's responsibility to ensure that the 'up' SQL really does rewrite values so that they meet the constraint. [0] - https://github.com/xataio/pgroll/blob/main/examples/22_add_c...

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!

You're right. I wish schema wasn't such an overloaded term :) In order to access either the old or new version of the schema, applications should configure the Postgres `search_path`[0] which determines which schema and hence which views of the underlying tables they see. This is touched on in the documentation here[1], but could do with further expansion. [0] - https://www.postgresql.org/docs/current/ddl-schemas.html#DDL... [1] - https://github.com/xataio/pgroll/blob/main/docs/README.md#cl...

For those curious, as I was, how this works, beyond the details in the readme and blog post, note that "schema" in this context is both a loose term (we changed the schema, old schema, new schema) AND a concrete thing in PostgreSQL[0]. It's helpful to know that pgroll implements the first one (let's change the schema) using the second one (make Schema objects in PG [1]). The magic is in creating different views in different Schemas that map appropriately to underlying tables (in a third Schema). Presumably (didn't see this mentioned in the docs yet) the trick is that you re-deploy the app with a new connection string referencing the new schema (as in [3]), while an old app deployment can keep referencing the old schema with its connection string. Hopefully I got that right. [0] https://www.postgresql.org/docs/current/ddl-schemas.html [1]

I did find this: https://github.com/xataio/pgroll

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

hey HN, repo is here: https://github.com/xataio/pgroll Would love to hear your thoughts!

This is a fantastic article! It shows that even simple migrations (like adding or removing a column) can be quite tricky to deploy in concert with the application deployement. We (at Xata) have tried for a while to come up with a generic schema migration system for PostgreSQL that makes this easier. We ended up using views and temporary columns in such a way that we can provide both the "old" and the "new" schema simultaneously. Up/down triggers convert newly inserted data from old to new and the other way around. This also has the advantage the it can do rollbacks instantly by just dropping the "new" view. We were just planning to announce this as an open source project this week, but actually it is already public, so if you are curious: https://github.com/xataio/pgroll