Zero-downtime schema migrations in Postgres using Reshape
fabianlindfors.se
fabianlindfors.se
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.
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!
[0] https://www.postgresql.org/docs/current/sql-createview.html
I will give this a spin! Kudos
Let me know if you run into any bugs :)
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.
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.
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 :)
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.
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.
I migrated huge tables with absolutely no impact on production
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 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.