10 ms·

Zero-downtime schema migrations in Postgres using Reshape

177 points by fabianlindfors 5y ago

jfbaro 5y ago
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!
- fabianlindfors 5y ago
  Thank you!
chrisjc 5y ago
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.
- fabianlindfors 5y ago
  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.
jeremyjh 5y ago
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?
- fabianlindfors 5y ago
  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. [0] https://www.postgresql.org/docs/current/sql-createview.html https://www.postgresql.org/docs/current/sql-createview.html
sidcool 5y ago
Important: Note: Reshape is experimental and should not be used in production. It can (and probably will) destroy your data and break your application.
- fabianlindfors 5y ago
  Yes, good note! I highly appreciate anybody willing to experiment with Reshape to help make it production ready :)
claytonjy 5y ago
This is so cool; wish I had this a few years ago when I implemented most of these features by hand on a per-migration basis! 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.
- fabianlindfors 5y ago
  I'm glad you like it! I hope you'll have some project in the future where Reshape can be useful.
smoe 5y ago
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?
- fabianlindfors 5y ago
  Let me know your experience when you do try it! Eager to iron out bugs. It's designed to be a full tool to handle all schema changes, replacing tools such as the ones you mentioned :)
ekzhu 5y ago
I am curious what is issue with existing migration tools? There are so many tools out there. What is the problem they are not solving?
- fabianlindfors 5y ago
  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.
  - kgeist 5y ago
    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.
tobias_irmer 5y ago
How do INSERTs work if you are migrating from `full_name` to `first_name` - `last_name`?
- fabianlindfors 5y ago
  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.
  - tobias_irmer 5y ago
    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)?
    - fabianlindfors 5y ago
      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.
aargh_aargh 5y ago
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.
- fabianlindfors 5y ago
  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.
jfbaro 5y ago
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.
- fabianlindfors 5y ago
  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 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!
  - magicalhippo 5y ago
    > 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.
    - fabianlindfors 5y ago
      > We have migrated a few fields from varchar to integer. How would your solution deal with this 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 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.
yakkomajuri 5y ago
Interesting, will give this a look. Have been digging into zero downtime migrations quite a bit lately.
- fabianlindfors 5y ago
  It's a very interesting topic! If you have time to test it out, I'd appreciate hearing about any bugs you find or feedback you might have :)
deleted 5y ago
[deleted]
shayonj 5y ago
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 https://github.com/shayonj/pg-online-schema-change. Its inspired by pg_repack and pt-online-schema-change (mysql). I will give this a spin! Kudos
- fabianlindfors 5y ago
  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. Let me know if you run into any bugs :)

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!

fabianlindfors 5y ago
Thank you!

chrisjc 5y ago

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.

fabianlindfors 5y ago
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.

jeremyjh 5y ago

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?

fabianlindfors 5y ago
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. [0] https://www.postgresql.org/docs/current/sql-createview.html https://www.postgresql.org/docs/current/sql-createview.html

sidcool 5y ago

Important: Note: Reshape is experimental and should not be used in production. It can (and probably will) destroy your data and break your application.

fabianlindfors 5y ago
Yes, good note! I highly appreciate anybody willing to experiment with Reshape to help make it production ready :)

claytonjy 5y ago

This is so cool; wish I had this a few years ago when I implemented most of these features by hand on a per-migration basis! 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.

fabianlindfors 5y ago
I'm glad you like it! I hope you'll have some project in the future where Reshape can be useful.

smoe 5y ago

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?

fabianlindfors 5y ago
Let me know your experience when you do try it! Eager to iron out bugs. It's designed to be a full tool to handle all schema changes, replacing tools such as the ones you mentioned :)

ekzhu 5y ago

I am curious what is issue with existing migration tools? There are so many tools out there. What is the problem they are not solving?

fabianlindfors 5y ago
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.
- kgeist 5y ago
  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.

tobias_irmer 5y ago

How do INSERTs work if you are migrating from `full_name` to `first_name` - `last_name`?

fabianlindfors 5y ago
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.
- tobias_irmer 5y ago
  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)?
  - fabianlindfors 5y ago
    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.

aargh_aargh 5y ago

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.

fabianlindfors 5y ago
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.

jfbaro 5y ago

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.

fabianlindfors 5y ago
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 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!
- magicalhippo 5y ago
  > 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.
  - fabianlindfors 5y ago
    > We have migrated a few fields from varchar to integer. How would your solution deal with this 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 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.

yakkomajuri 5y ago

Interesting, will give this a look. Have been digging into zero downtime migrations quite a bit lately.

fabianlindfors 5y ago
It's a very interesting topic! If you have time to test it out, I'd appreciate hearing about any bugs you find or feedback you might have :)

deleted 5y ago

[deleted]

shayonj 5y ago

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 https://github.com/shayonj/pg-online-schema-change. Its inspired by pg_repack and pt-online-schema-change (mysql). I will give this a spin! Kudos

fabianlindfors 5y ago
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. Let me know if you run into any bugs :)