Pgroll: zero-downtime, reversible schema migrations for Postgres
xata.io
xata.io
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] https://github.com/xataio/pgroll/blob/main/pkg/roll/execute.... [3] https://stackoverflow.com/a/67525360
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...
I also only perform migrations as part of app init, not separately.
> Keep two versions of the schema (previous and next) accessible at the same time during the whole migration process
This has some obvious advantages. Like you said you can't easily roll back once a migration is fully live, but it helps a lot if you can cancel a migration once it turns out it doesn't work.
- Start migration, create transaction and locks
- Do migration things
- Do some SELECTs, INSERTS, or w/e in your code to validate the schema matches your model.
- End transaction or rollback if failure
None of this is free, but it's by no means hard IMO. I do something similar in Go: https://github.com/candiddev/shared/blob/main/go/postgresql/...
If you do that on a table or two with millions of rows, you've effectively locked your database from all other access, even SELECTs.
ALTER statements typically grab exclusive locks, blocking both writes and reads. Chain a couple together and you're looking at high CPU, queued queries, and an unresponsive database for hours.
Once you realize what's happening and kill the migration in a panic, it may take tens of minutes or more for the DB to revert back to its initial state, much to everyone's impatient horror.
In simple programs and databases, everything is possible and quick. Complex programs and large databases disabuse you of your naïveté with a quickness.
I love Postgres's transactional DDL, but as the article's mention of renaming columns highlights, one transaction simply isn't enough for the job.
how large are the DB migrations you've done? your experience is seemingly completely different to mine.
What's the biggest table you've ever had to modify?
Adding a column with a default is easy. What about deleting a column from a table with 10s of millions of rows? Renaming a column? Changing a column's type? Adding a CHECK constraint? Adding a NOT NULL? Adding a foreign key constraint that someone accidentally removed in the previous migration while data keeps flowing?
Those concerns aren't about your explicit advisory locks. Not by a long shot.
https://www.postgresql.org/docs/current/explicit-locking.htm...
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...
They can work in parallel for a while until you complete the rollout and call the migration as done.
pgroll (or rather the database triggers that it creates along with the up and down SQL defined in the migration) does the work to ensure that data written by the old applications is visible to the new and vice-versa.
A rollback in pgroll then only requires dropping the schema that contains the new version of the views on the underlying tables and any new versions of columns that were created to support them.
If I delete a "last_name" column, apply the migration, and then decide I shouldn't have deleted users' last names. Do I get that data back?
"Applying the migration" doesn't actually do anything to the table, it just creates a new schema containing views over the old one, where the view for the table whose column you deleted hides the column. You can then try if your app still works when using accessing this schema instead of the old one. If you're happy, you can "complete" the migration, at which point only the table structure actually gets altered in a non-reversible way.
Once the migration is completed any extra columns are dropped.
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...
Backfills are done in fixed size batches to avoid taking long-lived row locks on many rows but there is nothing in place to control the overall rate of backfilling.
This would certainly be a nice feature to add soon though.
(I very much appreciate the effort to provide tooling that puts all these things together, btw)
Our workflow was something like:
1) Create the new hypertable
2) Create after insert trigger on first table to insert transformed data from first table into second table, and delete from first table (this ensured applications can continue running using first schema/table, without any new data being added to first table after migration)
3) Iterate over first table in time-bucketed batches using a plpgsql block to move chunks of data from first table to second table.
Would pgroll enable a similar workflow? I guess I'm curious if the way pgroll works would similarly create a trigger to allow apps to work with the initial schema as a stopgap... I guess pgroll would perform the whole migration as a series of column updates on a single table, but I'm unclear on whether it attempts to migrate all data in one step (potentially locking the table for longer periods?) while also allowing applications using the old schema to continue working so there is no downtime as changes are rolled out.
Has pgroll been tested with timescaledb at all?
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!
Backfills are done in fixed size batches to avoid long lived row locks, but the operation can still be expensive in terms of time and potentially I/O. Options to control the rate of backfilling could be a useful addition here but they aren't present yet.
Additionally, the people executing the change are not necessarily those who have developed the change. They need two big buttons: Do and Undo. If the change fails or breaks something, they hit the Undo button and tell the developers about it and by the way, here are the logs, please go reproduce it on the test system and try again.
I know this is not "devops," but it's still how a lot of high-availability software deployments work, particularly when hampered by bureaucratically-imposed processes.
Finally, database schema changes are a fine way to irreversibly munge your data if you are not careful. (This goes beyond SQL.) If that happens, there is no such thing as a path forward, the only way to save the ship is to restore from your most recent backup.
That's why I like pgroll's approach, in that there isn't really a "rollback procedure" that might make things worse during emergencies, but rather old and new schemas that remain working simultaneously until the migration is marked as complete and there are no clients using the old schema. "Rolling back" is actually cancelling the new schema migration and falling back to the previous version that's been working at all times, thus minimizing the risk.
Unless obviously the view has magic to use either column name based on what's available on the underlying, but I did not see that either on `createView`.
The aim is not to deal with conditional logic in the app dealing with both schemas, but having an old version of the app linked to the old schema and the new one using the other.
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?
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?
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.
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).
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.
Seems like a dangerous way to introduce very hard to find data inconsistencies.
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).
Or make the app do the migration as part of its normal operation, so you have total control over it. :)
I think we're saying the same thing, I'm just suggesting it's safer to do it in the app itself, because then app changes and database changes are tied together instead of divorced from each other.
If you only have one or two people working on the code that interacts with the table that is being changed, both methods are pretty much the same. But if you have a large team or want to be able to rollback better, then doing it all in the app code feels like the better way to go for me.
- For rows inserted/updated through the old version of the schema, the up function gets executed, copying the resulting value into the new column - For rows inserted/updated through the new version, the down function gets executed, copying the resulting value into the old column
For instance, you can test that up function works before releasing the new app relying on it, just by checking that the values present in the new schema are correct.
The good thing is that this will probably be detected during the start phase, as data would fail to be backfilled.
We are thinking of ideas to improve this in the longer term, for example: * Having a dry-run mode where you check that the data present in the DB will stay valid after applying the up function * Optionally allow for this to happen, but "quarantine" the affected rows so they are no longer available in the new schema.
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...
May I suggest spelling this out more explicitly in the docs? As far as I can tell it’s only mentioned by example halfway down the tutorial. To me it seems like one of pgroll’s true killer features, worth more coverage :-)
> Page not found
> We're sorry, but the page you requested could not be found. This could be because the page has been moved or deleted. We're tracking these errors so we can fix them in the future.
That's what we get for trying to fix some text ;-)
You can't change table names unless you take a lock. How exactly do you switch the original table to be a view pointing to the original table? The docs don't go into detail how this is done exactly, I'll check the code later.
It looks like the tool maintains two copies of the table but how exactly this copy process is done isn't explained. A potential issue is that you need to have disk space and I/O capacity available to support this.
The copy table + trigger approach might not work for databases of significant size. For example I have seen instances with >50k qps on a table where it is not possible to run pg_repack because it never catches up and it also doesn't ever manage to take the lock which is needed to switch to the new table. This can be simulated with overlapping long running queries.
Thank you for your input! I'm one of the pgroll authors :)
> You can't change table names unless you take a lock. How exactly do you switch the original table to be a view pointing to the original table? The docs don't go into detail how this is done exactly, I'll check the code later.
pgroll only performs operations requiring a short lock, like renaming a table. It sets a lock timeout for these operations (500ms by default), to ensure we avoid lock contention if other operations are taking place. We plan to add an automatic retry mechanism for these timeouts so there is no need for manual intervention.
One cool thing about views is that they will automatically get updated when you rename a table/column, so the view keeps working after the rename.
> It looks like the tool maintains two copies of the table but how exactly this copy process is done isn't explained. A potential issue is that you need to have disk space and I/O capacity available to support this. > The copy table + trigger approach might not work for databases of significant size. For example I have seen instances with >50k qps on a table where it is not possible to run pg_repack because it never catches up and it also doesn't ever manage to take the lock which is needed to switch to the new table. This can be simulated with overlapping long running queries.
pgroll doesn't really copy full tables, but individual columns when needed (for instance when there is a constraint change). It is true that I/O can become an issue, backfilling is batched but the system should have enough capacity for it to happen. There are some opportunities to monitor I/O and throttle backfilling based on it.
I only got back into Postgres this year, after almost a decade away from SQL. It’s kind of bizarre to me that the migration tooling is still at the stage where a 1 line change to eg a Postgres function requires a the whole function to be dropped and re-created?
I understand this is needed at the db level, but surely a “definition” that generates the final migration is doable; it would make such a huge difference in code reviews and to understand how a function/etc changed over time.
Am I just looking in the wrong place? Does this exist? If not, how come? Is it really that hard to do?
DrizzleKit and several others do this for table changes, but nothing I’ve found (possibly excluding Flyway and other Java options) do views/functions/etc.
I’m not too keen on sponsoring an alternative when the dev doesn’t particularly care about the feature — it didn’t work out last I tried something similar. But, perhaps there’s another path: Supabase.
They are in (imo) desperate need of better migration tooling, and they sponsor several open source projects that boost their over-all offering.
AFAIK, they haven’t done much in the migration space yet (aside from their alpha db branching feature), so I expect they’ll co-opt an open source solution at some point soon. May be worth pinging them? Seems it could be a win-win there.
So most likely you're going to want a Postgres-specific tool for this, but I'm not sure one exists yet that handles everything you're looking for here.
I'm the author of a product called Skeema which does handle all this (tables, procs/funcs, views, triggers) for MySQL and MariaDB, and in my opinion this is an area where MySQL/MariaDB's relative simplicity -- in things like e.g. lack of transactional DDL -- actually makes this problem easier to solve there. For example Skeema explicitly doesn't handle data migrations because you can't atomically combine schema changes and data changes in MySQL/MariaDB in the first place.
btw when describing/searching for this, I always say "declarative" and never "state-based". "Declarative" is consistent with terminology used by other infra-as-code such as Terraform and Kubernetes. The main places I see calling it "state-based" are marketing blog posts from commercial schema management tools using an imperative migration approach (Liquibase, Bytebase, etc). To me it feels like they say "state-based" in order to make the declarative competition seem more strange/foreign...
Our initial launch does use "state-based", but we later change it to "declarative". You can check the product here https://demo.bytebase.com/project/blog-102#gitops
Of course, it's always a bit confusing to have multiple ways to reference the same thing. So be it.
I realize this post is two years old, and you're understandably not going to mention a competing product like mine. But it feels like a disingenuous strawman argument to claim that declarative schema management requires `mysqldump`, considering that Skeema (my product) and SQLDef (another declarative tool) were both released in 2016 and are both widely used.
[1] https://www.bytebase.com/blog/database-version-control-state...
>> it feels like a disingenuous strawman argument to claim that declarative schema management requires `mysqldump`
The article explicitly said it's a hybrid approach and never intended to claim this way. Otherwise, it would be an obvious mis-statement.
Yes, and I have not disputed this at any point here.
> The article explicitly said it's a hybrid approach and never intended to claim this way. Otherwise, it would be an obvious mis-statement.
Your blog post said "State-based approach stores the desired end state of the entire schema in the code repository. For MySQL, it means to store the schema dump created by mysqldump." I think that is indeed an obvious mis-statement. It would give a casual unfamiliar reader the impression that declarative schema management somehow involves/requires mysqldump.
i think he should use the obvious word if this is what it means: reversible
I'd also be a bit nervous that any of these tricks may themselves cause (perhaps temporary) performance problems.
The only way to do it without downtime is to make the app forward compatible before making DB changes, and make it backward compatible before undoing the DB changes.
There will always be a short period where the app version and DB version will be out of sync.
Also, how do you handle the back filling on columns, how you make sure you don't miss any data before dropping the old column?
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...
(I'm one of the authors of this project).
https://github.com/djrobstep/migra
I'm asking because, although migra is excellent and there are multiple migrations tools based on it (at least https://github.com/bikeshedder/tusker and https://github.com/blainehansen/postgres_migrator), issues are piling up but development seem to be slowing down
I'm @a8m's co-founder and also a maintainer of Atlas. I don't have an exact feature comparision but Atlas (and esp the Postgres driver) are heavily maintained with advanced features like views and materialized views (functions and procedures coming up, followed by triggers and more).
What features specifically are you missing in migra? Perhaps we can prioritize them. Also feel free to join our Discord https://discord.gg/zZ6sWVg6NT
Would love to hear your thoughts!
We decided to start a new project for a couple of reasons. First, we preferred it to have it in Go, so we can integrated it easier in Xata. And we wanted to push it further, based on our experience, to also deal with constraints (with Reshape constraints are shared between versions).
I could be mistaken here, but I believe that pg-osc and pgroll use similar approaches to ensuring no locking or how backfilling happens.
While pg-osc uses a shadow table and switches to it at the end of the process, pgroll creates shadow columns within the existing table and leverages views to expose old and new versions of the schema at the same time. Having both versions available means you can deploy the new version of the client app in parallel to the old one, and perform an instant rollback if needed.
I'm not very familiar with pg-osc, but migrations with pgroll are a two phase process - an 'in progress' phase, during which both old and new versions of the schema are accessible to client applications, and a 'complete' phase after which only the latest version of the schema is available.
To support the 'in progress' phase, some migrations (such as adding a constraint) require creating a new column and backfilling data into it. Triggers are also created to keep both old and new columns in sync. So during this phase there is 'bloat' in the table in the sense that this extra column and the triggers are present.
Once completed however, the old version of this column is dropped from the table along with any triggers so there there is no bloat left behind after the migration is done.
This is only true after all rows are rewritten after the old column is dropped. In standard, unmodified Postgres, DROP COLUMN does not rewrite existing tuples.
Also, what about if the user can just maintain one schema file (no migrations), and the lib figures out the change and applies it?
Migrations are JSON format as opposed to pure SQL for at least a couple of reasons:
1. The need to define up and down SQL scripts that are run to backfill a new column with values from an old column (eg when adding a constraint).
2. Each of the supported operation types is careful to sequence operations in such a way to avoid taking long-lived locks (eg, initially creating constraints as NOT VALID). A pure SQL solution would push this kind of responsibility onto migration authors.
A state-based approach to infer migrations based on schema diffs is out of scope for pgroll for now but could be something to consider in future.
Because that only solves the DDL issues and not the DML. It is still useful though.
I use a schema comparison tool that does exactly this to assist in building my migration and rollback plans, but when simply comparing two schemas there is no way to tell the difference (for example) between a column rename and a drop column/add column. The tooling provides a great scaffold and saves a ton of time.
Supporting previous versions would be possible, but we went for the most recent ones to reduce complexity.
I wonder if there will be cases where this gets too complex or otherwise hits any limits. It may be possible that for those cases a raw SQL migration is required.
Best practice is to have your new code backwards-compatible with the old schema so you can do blue-green deployments.
I think most migration tools are missing a lot of features that would make them safer and easier to use. Modern teams merge a lot and deploy frequently, but most migration tools are built for a db admin manually running commands.
I wrote a migration cli tool (and golang library) that I think is more suited to modern workflows, if you check it out I’d appreciate any feedback!
The tool is open source and doesn't require any subscription :)
Reading the homepage as a total DB noob, all signs point to this product being a CLI tool. However, when you get to the bottom, you see that it is subscription-based with a free tier. Reading the feature list ("availability zones", "storage", etc), it sounds like this is in fact a service with a CLI frontend? But again, this is a layman perspective, and some clarity from the authors (or other commenters) might help.
Update: I think I see what happened. Xata is a serverless DB service, and this is a tool they wrote that can be used independently of their service (I assume). The subscription options presented to the user look like they are related to this CLI tool, but they are in fact for the broader Xeta service.
Yes, that's right. Xata is a Postgres-based service and we're working on exposing the Postgres DB directly and unrestricted to our users. As part of this, we're also open-sourcing parts of the platform. We'll have more such open source projects soon.
This is not exactly the case with pgroll, as its approach is different from what we do today in Xata, but we'll be incorporating pgroll in Xata soon.
To try out pgroll on a database with an existing schema (whether created by hand or by another migration tool), you should be able to have pgroll infer the schema when you run your first migration.
You could try this out in a staging/test environment by following the docs to create your first migration with pgroll. The resulting schema will then contain views for all your existing tables that were created with alembic. Subsequent migrations could then be created with pgroll.
It would be great to try this out and get some feedback on how easy it is to make this switch; it may be that the schema inference is incomplete in some way.
I've been using dbmate which uses SQL and works really well.