Hi! Most migration tools I have seen simply run the SQL needed to perform the update directly. This causes trouble during deploys as there will inevitably be some downtime between applying the migration and rolling out the application changes which work with the new schema.
Say for example you want to rename a column for a running application. With a regular migration tool, you can't just run the migration as the old application deployment still expects the old column name, changing it breaks the application. Instead you might do something like this:
1. Bring down the old application instances
2. Apply the migration
3. Deploy new application instances which can work with the new schema
All of this is downtime which might not be too bad if deployments are quick and the migration doesn't take much time. For more complex migrations that take a longer time, like changing data in a bunch of rows, this downtime can become significant. The way I've seen that handled is that developers have to manually perform several deployments, for example:
1. Add temporary column to table with no data. Update application to fill it for new rows. Deploy.
2. Write batch job to backfill old rows. Deploy. Wait for job to finish.
3. Update application to use new column. Deploy.
4. Remove old column. Deploy.
This is a lot of manual work which Reshape aims to automate, hopefully reducing the burden on developers and the risk for human errors.
Sounds neat! We currently have to deploy 2-3 releases to just have 1 logical migration and it's pretty error-prone and complicates our release schedule. We basically gave up trying to rename columns at this point because it's too much effort for little worth, but it confuses the hell out of new devs when a column name in a DB doesn't match the name in the code.
Your article mentions that it allows to avoid locking tables during migrations and it's something we haven't solved yet either - in one of the recent migrations that touched a very fat table we had to write a custom migration which applied changes in batches to avoid locking the entire table under one fat transaction.
Our DB is sharded: we first apply migrations on thousands of DBs and only then deploy code. Such migrations can take up to an hour to finish before code is deployed, so we have to make sure our migrations are forward-compatible because old code can see new schemas for a prolonged time. Will Reshape work well for this mechanism? Sometimes we have to run custom migrations which call code, I guess Reshape can't replace it because it's strictly DB-based?
Unfortunately, we use MySQL, not PostreSQL, so I wonder, if it's portable to other DBs.
Your situation sounds just like what I have in mind for Reshape to fix! The backfills performed by Reshape are automatically done in batches to avoid locking.
As you said, Reshape can't call code outside the database as it relies on transactional guarantees for the migrations. Postgres has quite powerful support for procedural languages though so even though Reshape only supports SQL at the moment, there are opportunities to extend that in the future.
Reshape is currently Postgres only and uses a bunch of its features: schemas, updatable views, triggers, procedural functions, transactions and more. I'm sure equivalent things exist in MySQL for Reshape to work with that as well but I'm focusing on Postgres at the moment.
For MySQL we use github's tool gh-ost https://github.com/github/gh-ost
There is a few tech restrictions, but if your database meets that it works great.
I migrated huge tables with absolutely no impact on production
Postgres has something called updatable views so INSERTs, UPDATEs, DELETEs can be made against views just as if they were actual tables. For the example in the post, they work differently depending on if the insert is made against the new or old schema:
- For old schema (i.e. full_name is set): full_name is split in two, the first part is assigned to first_name and the second to last_name. This is controlled by the "up" setting in the migration.
- For new schema (i.e. first_name and last_name is set): full_name is set to first_name and last_name concatenated with a space. This is controlled by the "down" setting in the migration.
I haven't used that, yet. But this has me confused:
"An automatically updatable view may contain a mix of updatable and non-updatable columns. A column is updatable if it is a simple reference to an updatable column of the underlying base relation; otherwise the column is read-only, and an error will be raised if an INSERT or UPDATE statement attempts to assign a value to it."
Isn't splitting full_name into two columns a complex reference (for lack of a better word)?
Actually, no! That's because from the perspective of the view, it's simply directly referencing a table and some columns, it doesn't handle any of the updating.
What Reshape does is that it sets up triggers for the underlying table. When an insert is made, the trigger runs and updates the other columns directly on the table, which has no effect on the view.
IMHO this is the opposite to the ideal approach. The current schema version should contain tables and the old schema versions should contain views. Using the current form of Reshape is asking for maintainability and performance problems down the road as its approach doesn't let you easily drop old schemas and change indexes as new needs emerge.
The old schemas will be automatically dropped when the migration is completed. So there will ever only be either one schema (current migration) or two during a deploy.
I'm not sure what you mean by maintainability and performance. It's only during a migration that Reshape will add triggers and temporary tables and such. Once a migration is completed, the tables will look just like what you expect, no Reshape stuff. The only thing Reshape adds in between migrations are the schema and some views, nothing should build up over time.
Wow! I love how elegant and powerful your strategy is. There is a lot of potential here and your tool might turn PostgreSQL into an even easier choice for new projects. Congratulations!
Is there any migration scenario that you anticipate your view strategy won't be able to handle? Maybe add a "Limitations" section? Again..congrats. I am impressed and I hope someday this becomes part of PostgreSQL native feature set.
That's a great question. I don't anticipate the view part being very limiting, Postgres seems to have great support for updatable views which means they should be able to transparently take the place of tables in most situations. My previous posts's comments here on HN have some details on the limitations of views: https://news.ycombinator.com/item?id=27531934.
The biggest limitations will probably come down to what changes can actually be automated. There might just be some migrations that require a human touch. I don't know what those will be yet but I might add some kind of escape hatch to write your own migrations at your own risk
I should probably add a limitations section, good idea! There are a ton of limitations now given that Reshape is still experimental. Foreign keys, for example, probably don't work well as I currently just ignore them.
Thanks again! I don't know any database which has nice schema migrations as a built-in feature but if you ask me, they should!
> I don't know what those will be yet but I might add some kind of escape hatch to write your own migrations at your own risk
We have migrated a few fields from varchar to integer. How would your solution deal with this? And of course in some cases there will be data that requires manual handling.
Another one is adding foreign keys where the existing data does not conform to the foreign key constraint.
For the cases that require manual handling, that is a bit tricky. I'm not sure Reshape would be able to automate that in any meaningful way so the best thing might just be to fall back to a standard procedure of making the changes in two separate migrations, where the manual changes are done in between.
> Another one is adding foreign keys where the existing data does not conform to the foreign key constraint.
I have given this some thought before as I wanted to add a migration which can add new foreign keys. I think it can be done by writing some migrations first which update the existing data to conform to the constraint, for example adding missing values. This can be done with `alter_column` right now and there will be more comprehensive migrations for data changes in the future.
> There is actually an example in the README on how to change a column from TEXT to INTEGER
I'm not very familiar with PostgreSQL, how do you handle the casts during inserts/updates?
> For the cases that require manual handling, that is a bit tricky.
Yeah in our case we just blocked the schema change and complained. Our support team would then take a look, possibly escalating to development. Not often such a change is done though.
> for example adding missing values
In our case we just delete the offending values if the constraint has "on cascade delete", which most do. We do take a backup of the database before doing schema changes though.
> I'm not very familiar with PostgreSQL, how do you handle the casts during inserts/updates?
In the example, you can see "up" and "down" settings on the migration. These are SQL expressions which specify how to convert between the old and new schema, in this case casting from TEXT to INTEGER and back. So in this case we have:
up = "CAST(reference AS TEXT)"
down = "CAST(reference AS INTEGER)"
which is just a standard Postgres function for casting.
> In our case we just delete the offending values if the constraint has "on cascade delete", which most do.
Interesting! I might add some options to a future `add_foreign_key` migration which can achieve behavior like this automatically.
This is very interesting, thanks for sharing. I had been noodling on the concept of zero downtime schema changes in postgres as well, and recently started https://github.com/shayonj/pg-online-schema-change. Its inspired by pg_repack and pt-online-schema-change (mysql).
That's really cool, thanks for sharing! I've also considered using shadow tables but haven't needed them yet. Might change in the future. I really like how your solution allows one to user regular DDL statements and not some DSL.
I've used a similar custom technique in the past, but only been able to deal with it from a read perspective. As a result I had to split schemas into read and write schemas which turned out wasn't too much off an issue since it was a data warehouse.
Read schemas were composed of mostly read objects: views, functions, procedures, external tables (backed by S3) and in some cases tables that were filled with static data (CSV/JSON/TSV from source-control ingested/copied into table). Views, functions, etc... all pointed out to a write schema that was managed traditionally (Flyway, CLI/Docker, etc).
If the read schema changed, I (CI/CD) would build out a new schema with the changes, leaving the existing schema unchanged. Every read schema was immutable and idempotent. In fact I would build a docker image with all of the DDLs and specific deployment tool (specific DB CLI version), publish and tag it. Worked perfectly for CI/CD (or manually if all else went wrong).
There were two options of making the new schema "live":
1) Swap old and new schema (not all DBs have this functionality). Swap back if something goes wrong.
2) Schemas are treated like artifacts with versions. It's up to the applications to decide which version they need to use, or matches their read service contracts. Swapping back to previous version goes hand in hand with the application release/rollback. Great for blue/green. Old schema would get dropped after a certain amount of time and sign-off.
This worked great, but having to split read and write schemas (even for data warehouses) and having two different database migration techniques eventually becomes problematic.
I really like your idea/approach of using a new schema for your new table, and adding triggers to your old table until you complete the migration. Do you have a way to rollback (without having to create a new migration that undoes the current migration)?
The only idea that I've come close to having about solving the write problem is by encapsulating write activities in a procedure that dual-writes to both old and new schemas/tables (hardly original). Problem with this is that for blue/green to continue working, it has to ensure that dual-write works in both schemas for a while (old-->new, new-->old) and there's the potential for split-brain. Moreover, adding dual-write capabilities to existing procedures means that the schema is no longer immutable. The whole process becomes more complex overall.
That's a really nice solution, I especially like the concept of versioning the read-only schema.
There is a way to rollback after you have started a migration with `reshape abort`. This stops the migration which is currently in progress and reverts to the old state with no data loss. Basically as if you had never run `reshape migrate` at all. I think this is also pretty convenient during development where you can continuously run `abort` and then `migrate` again as you iterate on a new migration.
Abort doesn't work if you have already completed the migration using `reshape complete`. In that case, all the changes have already been applied so I don't think it makes sense to have a command to perform a rollback, that could also cause downtime! In that case, it's probably a better idea to create a new migration manually to undo the changes and apply that the normal way.
I've had some issues in the past with queries on top of views not properly using indexes in postgres. I wish I'd saved some examples but I'm now quite cautious about putting a lot of views between my queries and my tables (though the examples I had were not simple views, but views that joined several tables together). Are the views only used during the migration or are they used all the time?
They are used all the time. If you ever find some examples of this, I'd be really interested to see them. Reshape only uses simple, updatable views [0] which hopefully means it won't struggle with finding indices.
Important:
Note: Reshape is experimental and should not be used in production. It can (and probably will) destroy your data and break your application.
This looks really nice and I'm certainly going to try it out soon!
One thing that is not that clear to me, what the vision is on how to use Reshape once it is production ready. Is it meant to be used exclusively to handle you db schema (e.g. replacing alembic, sqitch, etc. completely) or alongside such tools to handle difficult migrations?
35 comments
[ 0.24 ms ] story [ 103 ms ] threadSay for example you want to rename a column for a running application. With a regular migration tool, you can't just run the migration as the old application deployment still expects the old column name, changing it breaks the application. Instead you might do something like this:
1. Bring down the old application instances
2. Apply the migration
3. Deploy new application instances which can work with the new schema
All of this is downtime which might not be too bad if deployments are quick and the migration doesn't take much time. For more complex migrations that take a longer time, like changing data in a bunch of rows, this downtime can become significant. The way I've seen that handled is that developers have to manually perform several deployments, for example:
1. Add temporary column to table with no data. Update application to fill it for new rows. Deploy.
2. Write batch job to backfill old rows. Deploy. Wait for job to finish.
3. Update application to use new column. Deploy.
4. Remove old column. Deploy.
This is a lot of manual work which Reshape aims to automate, hopefully reducing the burden on developers and the risk for human errors.
Your article mentions that it allows to avoid locking tables during migrations and it's something we haven't solved yet either - in one of the recent migrations that touched a very fat table we had to write a custom migration which applied changes in batches to avoid locking the entire table under one fat transaction.
Our DB is sharded: we first apply migrations on thousands of DBs and only then deploy code. Such migrations can take up to an hour to finish before code is deployed, so we have to make sure our migrations are forward-compatible because old code can see new schemas for a prolonged time. Will Reshape work well for this mechanism? Sometimes we have to run custom migrations which call code, I guess Reshape can't replace it because it's strictly DB-based?
Unfortunately, we use MySQL, not PostreSQL, so I wonder, if it's portable to other DBs.
As you said, Reshape can't call code outside the database as it relies on transactional guarantees for the migrations. Postgres has quite powerful support for procedural languages though so even though Reshape only supports SQL at the moment, there are opportunities to extend that in the future.
Reshape is currently Postgres only and uses a bunch of its features: schemas, updatable views, triggers, procedural functions, transactions and more. I'm sure equivalent things exist in MySQL for Reshape to work with that as well but I'm focusing on Postgres at the moment.
I migrated huge tables with absolutely no impact on production
- For old schema (i.e. full_name is set): full_name is split in two, the first part is assigned to first_name and the second to last_name. This is controlled by the "up" setting in the migration.
- For new schema (i.e. first_name and last_name is set): full_name is set to first_name and last_name concatenated with a space. This is controlled by the "down" setting in the migration.
"An automatically updatable view may contain a mix of updatable and non-updatable columns. A column is updatable if it is a simple reference to an updatable column of the underlying base relation; otherwise the column is read-only, and an error will be raised if an INSERT or UPDATE statement attempts to assign a value to it."
Isn't splitting full_name into two columns a complex reference (for lack of a better word)?
What Reshape does is that it sets up triggers for the underlying table. When an insert is made, the trigger runs and updates the other columns directly on the table, which has no effect on the view.
I'm not sure what you mean by maintainability and performance. It's only during a migration that Reshape will add triggers and temporary tables and such. Once a migration is completed, the tables will look just like what you expect, no Reshape stuff. The only thing Reshape adds in between migrations are the schema and some views, nothing should build up over time.
The biggest limitations will probably come down to what changes can actually be automated. There might just be some migrations that require a human touch. I don't know what those will be yet but I might add some kind of escape hatch to write your own migrations at your own risk
I should probably add a limitations section, good idea! There are a ton of limitations now given that Reshape is still experimental. Foreign keys, for example, probably don't work well as I currently just ignore them.
Thanks again! I don't know any database which has nice schema migrations as a built-in feature but if you ask me, they should!
We have migrated a few fields from varchar to integer. How would your solution deal with this? And of course in some cases there will be data that requires manual handling.
Another one is adding foreign keys where the existing data does not conform to the foreign key constraint.
There is actually an example in the README on how to change a column from TEXT to INTEGER, the technique would be the same the other way around: https://github.com/fabianlindfors/reshape#alter-column
For the cases that require manual handling, that is a bit tricky. I'm not sure Reshape would be able to automate that in any meaningful way so the best thing might just be to fall back to a standard procedure of making the changes in two separate migrations, where the manual changes are done in between.
> Another one is adding foreign keys where the existing data does not conform to the foreign key constraint.
I have given this some thought before as I wanted to add a migration which can add new foreign keys. I think it can be done by writing some migrations first which update the existing data to conform to the constraint, for example adding missing values. This can be done with `alter_column` right now and there will be more comprehensive migrations for data changes in the future.
I'm not very familiar with PostgreSQL, how do you handle the casts during inserts/updates?
> For the cases that require manual handling, that is a bit tricky.
Yeah in our case we just blocked the schema change and complained. Our support team would then take a look, possibly escalating to development. Not often such a change is done though.
> for example adding missing values
In our case we just delete the offending values if the constraint has "on cascade delete", which most do. We do take a backup of the database before doing schema changes though.
In the example, you can see "up" and "down" settings on the migration. These are SQL expressions which specify how to convert between the old and new schema, in this case casting from TEXT to INTEGER and back. So in this case we have:
up = "CAST(reference AS TEXT)" down = "CAST(reference AS INTEGER)"
which is just a standard Postgres function for casting.
> In our case we just delete the offending values if the constraint has "on cascade delete", which most do.
Interesting! I might add some options to a future `add_foreign_key` migration which can achieve behavior like this automatically.
Right, I get how that would work with a read-only view, I just didn't know you could do updates against such a view. Nifty!
I will give this a spin! Kudos
Let me know if you run into any bugs :)
Read schemas were composed of mostly read objects: views, functions, procedures, external tables (backed by S3) and in some cases tables that were filled with static data (CSV/JSON/TSV from source-control ingested/copied into table). Views, functions, etc... all pointed out to a write schema that was managed traditionally (Flyway, CLI/Docker, etc).
If the read schema changed, I (CI/CD) would build out a new schema with the changes, leaving the existing schema unchanged. Every read schema was immutable and idempotent. In fact I would build a docker image with all of the DDLs and specific deployment tool (specific DB CLI version), publish and tag it. Worked perfectly for CI/CD (or manually if all else went wrong).
There were two options of making the new schema "live":
1) Swap old and new schema (not all DBs have this functionality). Swap back if something goes wrong. 2) Schemas are treated like artifacts with versions. It's up to the applications to decide which version they need to use, or matches their read service contracts. Swapping back to previous version goes hand in hand with the application release/rollback. Great for blue/green. Old schema would get dropped after a certain amount of time and sign-off.
This worked great, but having to split read and write schemas (even for data warehouses) and having two different database migration techniques eventually becomes problematic.
I really like your idea/approach of using a new schema for your new table, and adding triggers to your old table until you complete the migration. Do you have a way to rollback (without having to create a new migration that undoes the current migration)?
The only idea that I've come close to having about solving the write problem is by encapsulating write activities in a procedure that dual-writes to both old and new schemas/tables (hardly original). Problem with this is that for blue/green to continue working, it has to ensure that dual-write works in both schemas for a while (old-->new, new-->old) and there's the potential for split-brain. Moreover, adding dual-write capabilities to existing procedures means that the schema is no longer immutable. The whole process becomes more complex overall.
There is a way to rollback after you have started a migration with `reshape abort`. This stops the migration which is currently in progress and reverts to the old state with no data loss. Basically as if you had never run `reshape migrate` at all. I think this is also pretty convenient during development where you can continuously run `abort` and then `migrate` again as you iterate on a new migration.
Abort doesn't work if you have already completed the migration using `reshape complete`. In that case, all the changes have already been applied so I don't think it makes sense to have a command to perform a rollback, that could also cause downtime! In that case, it's probably a better idea to create a new migration manually to undo the changes and apply that the normal way.
[0] https://www.postgresql.org/docs/current/sql-createview.html
I was using sqitch which worked well, but was thinking along the same lines as the author here, "can't this be automated?".
It's really nice to see both a confirmation of my approach and someone willing to explore the solution space.
One thing that is not that clear to me, what the vision is on how to use Reshape once it is production ready. Is it meant to be used exclusively to handle you db schema (e.g. replacing alembic, sqitch, etc. completely) or alongside such tools to handle difficult migrations?
It's designed to be a full tool to handle all schema changes, replacing tools such as the ones you mentioned :)