3 ms·

This is very cool! Schema migrations have always been tough and fraught with peril. That being said, I've always found that doing the migrations within the ap

This is very cool! Schema migrations have always been tough and fraught with peril. That being said, I've always found that doing the migrations within the app is safer, because rollbacks are tied to the code. For example to change a constraint, we add a new column with the new constraint, and then change the code to read from the new column and old column, take the new value if it exists, otherwise use the old value, perform the operation, then write the new value to the new column and old column. We do this for a while and then do a background migration of the data at the same time, slow enough to not overload the database. At some point the new column is fully populated, and then we can put a new version of the code that only reads the new column. Then you check your stats to make sure there are no reads from the old column, and then you can delete the old one whenever you want, which is a very fast operation. Then you get your space back when you do your vacuum (protip: make sure you have solid vacuuming schedules!). What are some use cases where you'd want to migrate the schema without also migrating the code at the same time?

hosh 3y ago
I wasn't able to see the blog article (because it 404 by the time I am looking at this). I'm considering introducing this to the eng team I am a part of, because multiple teams and multiple projects often touch the same db. Anyone know how well this works with very large datasets? The backfill sounds like it would take a while to do. Does this Go binary need to be continuously running, or does it keep track of migration state in the database?
- tudorg 3y ago
  > Anyone know how well this works with very large datasets? The backfill sounds like it would take a while to do. It can take a long time, yes. It's somehow similar in that regard with, for example, gh-ost for Mysql that also does backfills. The advantage of Postgres here, is that backfill is required for fewer migration types, and pgroll only does backfills when needed. > Does this Go binary need to be continuously running, or does it keep track of migration state in the database? The latter, you only run the Go binary when doing schema changes.
  - Yeroc 3y ago
    How can you properly plan for eg. disk storage requirements etc. Does the tool calculate that upfront via some sort of dry-run mode? For companies with larger datasets this would be a rather important consideration. Also, those backfills will generate a lot of network traffic in clustered environments.
    - exekias 3y ago
      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.
skrebbel 3y ago
To my reading that’s exactly what this is intended for. You do “pgroll start”, let it run, and then when that’s done you deploy your new code. Then when you’re confident the new code is fine, you do “pgroll complete”. If at any time you realize you got it wrong, you rollback the code and then you do “pgroll rollback” and it’s af if nothing happened (but data changes that went through the new code and schema are still around, if the change you made allows).
- jedberg 3y ago
  Right, but the difference is that this is done on the backend with no app awareness. Doing it in the app ties the app logic to the database schema. Using pgroll would allow the database and app to get out of sync.
  - surjection 3y ago
    Allowing the database and the application to be out of sync (to +1/-1 versions) is really the point of pgroll though. pgroll presents two versions of the database schema, to be used by the current and vNext versions of the app while syncing data between the two. An old version of the app can continue to access the old version of the schema until such time as all instances of the application are gracefully shut down. At the same time the new versions of the app can be deployed and run against the new schema.
    - jedberg 3y ago
      But then how do avoid errors introduced due to the lack of app awareness? For example the old app can keep writing data with the old schema, whereas the new version makes assumptions based on the new schema, which could be broken by the new data in the old schema that don't follow the same assumptions. Seems like a dangerous way to introduce very hard to find data inconsistencies.
      - ris 3y ago
        > For example the old app can keep writing data with the old schema, whereas the new version makes assumptions based on the new schema To do safe migrations it's a good idea to avoid rolling out versions that start making assumptions about the new schema until the migration is done and dusted. This does of course start to create weird territory where migrations cease to become trivially "stackable" if you want to retain the whole zero-downtime thing, but this is true for a lot of "graceful update" patterns in computing (in fact I've not seen it solved properly before).
monocularvision 3y ago
When performing a rollout of new code across multiple machines, you can expect to have both your old code and new code running at the same time. That’s why you might want to support both the old and new schema at the same time.

hosh 3y ago

I wasn't able to see the blog article (because it 404 by the time I am looking at this). I'm considering introducing this to the eng team I am a part of, because multiple teams and multiple projects often touch the same db. Anyone know how well this works with very large datasets? The backfill sounds like it would take a while to do. Does this Go binary need to be continuously running, or does it keep track of migration state in the database?

tudorg 3y ago
> Anyone know how well this works with very large datasets? The backfill sounds like it would take a while to do. It can take a long time, yes. It's somehow similar in that regard with, for example, gh-ost for Mysql that also does backfills. The advantage of Postgres here, is that backfill is required for fewer migration types, and pgroll only does backfills when needed. > Does this Go binary need to be continuously running, or does it keep track of migration state in the database? The latter, you only run the Go binary when doing schema changes.
- Yeroc 3y ago
  How can you properly plan for eg. disk storage requirements etc. Does the tool calculate that upfront via some sort of dry-run mode? For companies with larger datasets this would be a rather important consideration. Also, those backfills will generate a lot of network traffic in clustered environments.
  - exekias 3y ago
    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.

skrebbel 3y ago

To my reading that’s exactly what this is intended for. You do “pgroll start”, let it run, and then when that’s done you deploy your new code. Then when you’re confident the new code is fine, you do “pgroll complete”. If at any time you realize you got it wrong, you rollback the code and then you do “pgroll rollback” and it’s af if nothing happened (but data changes that went through the new code and schema are still around, if the change you made allows).

jedberg 3y ago
Right, but the difference is that this is done on the backend with no app awareness. Doing it in the app ties the app logic to the database schema. Using pgroll would allow the database and app to get out of sync.
- surjection 3y ago
  Allowing the database and the application to be out of sync (to +1/-1 versions) is really the point of pgroll though. pgroll presents two versions of the database schema, to be used by the current and vNext versions of the app while syncing data between the two. An old version of the app can continue to access the old version of the schema until such time as all instances of the application are gracefully shut down. At the same time the new versions of the app can be deployed and run against the new schema.
  - jedberg 3y ago
    But then how do avoid errors introduced due to the lack of app awareness? For example the old app can keep writing data with the old schema, whereas the new version makes assumptions based on the new schema, which could be broken by the new data in the old schema that don't follow the same assumptions. Seems like a dangerous way to introduce very hard to find data inconsistencies.
    - ris 3y ago
      > For example the old app can keep writing data with the old schema, whereas the new version makes assumptions based on the new schema To do safe migrations it's a good idea to avoid rolling out versions that start making assumptions about the new schema until the migration is done and dusted. This does of course start to create weird territory where migrations cease to become trivially "stackable" if you want to retain the whole zero-downtime thing, but this is true for a lot of "graceful update" patterns in computing (in fact I've not seen it solved properly before).

monocularvision 3y ago

When performing a rollout of new code across multiple machines, you can expect to have both your old code and new code running at the same time. That’s why you might want to support both the old and new schema at the same time.