9 ms·
Pgroll: zero-downtime, reversible schema migrations for Postgres
- tudorg 3y agohey HN, repo is here: https://github.com/xataio/pgroll https://github.com/xataio/pgroll Would love to hear your thoughts!
- aschleck 3y agoCool stuff! Do you have any thoughts about how this compares to https://github.com/fabianlindfors/reshape https://github.com/fabianlindfors/reshape?
- tudorg 3y agoGreat question! Reshape was definitely a source of inspiration, and in fact, our first PoC version was based on it. 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).
- gvkhna 3y agoGreat to see more innovation in this space! How does this compare to? https://github.com/shayonj/pg-osc https://github.com/shayonj/pg-osc
- exekias 3y agoHi there, I'm one of the pgroll authors :) 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.
- brycethornton 3y agoDoes pgroll have any process to address table bloat after the migration? One of the (many) nice things about pg-osc is that it results in a fresh new table without bloat.
- surjection 3y agoAnother pgroll author here :) 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.
- pritambaral 3y ago> ... 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.
- brycethornton 3y agoThanks for the reply. My question was specifically about the MVCC feature that creates new rows for updates like this. If you're backfilling data into a new column then you'll likely end up creating new rows for the entire table and the space for the old rows will be marked for re-use via auto-vacuuming. Anyway, bloat like this is a big pain for me when make migrations on huge tables. It doesn't sound like this type of bloat cleanup is a goal for pgroll. Regardless, it's always great to have more options in this space. Thanks for your work!
- 3y ago
- nwhnwh 3y agoIs it possible to make it work using SQL only in the future? Also, what about if the user can just maintain one schema file (no migrations), and the lib figures out the change and applies it?
- surjection 3y agoHi, one of the authors of pgroll here. 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.
- aseering 3y agoThanks for releasing this tool! I actually interpreted the question differently: Rather than manipulating in SQL, would you consider exposing it as something like a stored procedure? Could still take in JSON to describe the schema change, and would presumably execute multiple transactions under the hood. But this would mean I can invoke a migration from my existing code rather than needing something out-of-band, I can use PG’s existing authn/authz in a simple way, etc.
- gorkish 3y ago> Also, what about if the user can just maintain one schema file (no migrations), and the lib figures out the change and applies it? 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.
- potamic 3y agoThe link to the introductory blog post here appears to be broken https://xata.io/blog/pgroll-schema-migrations-postgres https://xata.io/blog/pgroll-schema-migrations-postgres
- aeyes 3y agoThis looks very nice indeed but I see a few possible problems which I have seen with pg_repack which might apply to this approach as well: 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.
- exekias 3y ago> This looks very nice indeed but I see a few possible problems which I have seen with pg_repack which might apply to this approach as well: 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.
- pierat 3y ago"Undoable" in this case means "possible to undo", not irreversible!
- canadiantim 3y agoSeems like very unfortunate wording then as irreversible is exactly what I thought
- jandrese 3y agoThanks, I was wondering why they were advertising the process to be impossible. A better term would have been "reversible".
- murkt 3y agoYeah, needs a dash there. Undo-able.
- bitslayer 3y agoThat is a funny word. I am a big proponent of in-word dashes, which in this case could help clarify. It is "undo-able", not "un-doable".
- wccrawford 3y agoIt wouldn't mean "irreversible" regardless. It would mean "not possible to do" (ie impossible) or "possible to undo".
- systems 3y agoyes, horrible choice of word, i read it to mean one way schema migration (but zero downtime so maybe worth the risk) i think he should use the obvious word if this is what it means: reversible
- exekias 3y agoThank you for noticing this! We are looking into changing the wording :)
- hamilyon2 3y ago"With easy rollback", because rollback is well understood
- fabianlindfors 3y agoCongrats to the lovely Xata team on another great launch! I'm the creator of Reshape [0], a similar tool which inspired pgroll, and seeing the concept taken even further is really exciting. [0] https://github.com/fabianlindfors/reshape https://github.com/fabianlindfors/reshape
- tibanne 3y agoIf I'm using alembic for schema migrations already, how do I make use of this?
- surjection 3y agoWe are looking to build integrations with other tools but for now isn't recommended to use pgroll alongside another migration tool. 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.
- candiddevmike 3y agoHelp me understand the value of undoable migrations. I've always operated under "the only path is forward" and you release a new version that fixes the issue or create a new migration that does some kind of partial rollback if necessary. Once a migration is live, naively rolling things back seems like you're asking for problems. I also only perform migrations as part of app init, not separately.
- contravariant 3y agoI don't think 'undoable' is the clearest description, the crux is this: > 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.
- tudorg 3y agoYes, that's exactly it. I generally agree with "always move forward" but if rolling back is as easy as dropping the "new" view, that makes it a lot less scary.
- candiddevmike 3y agoIsn't that what transactions are for?
- contravariant 3y agoThat would be hard to coordinate if your application is even slightly complicated.
- candiddevmike 3y agoNot really... - 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/postgresql.go#L299 https://github.com/candiddev/shared/blob/main/go/postgresql/...
- sidcool 3y agoWhy does a migration tool need to be subscription based? One time fee should be enough.
- exekias 3y agoHi, one of the authors here! The tool is open source and doesn't require any subscription :)
- happytoexplain 3y agoTo add context for others: 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.
- tudorg 3y ago> 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.
- cpursley 3y agoWhat I'd love to see is state-based migrations similar to what Prisma offers - but that can handle, views, functions, and complex logic that references other things - and have it be smart enough to change those as well. Or at least walk you through any dependent changes. I'd pay for that.
- t1mmen 3y agoI’ve looked everywhere for this in NodeJS & adjacent stacks; almost all migration tools seem to focus on tables, columns and rows. None seem to deal with views, functions, triggers. 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?
- peter_l_downs 3y agoI believe Migra can generate those changes for you via diffing, not sure how well it handles dependent views/functions/etc
- t1mmen 3y agoThanks! I’ll give it a look (their docs are offline atm) 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.
- peter_l_downs 3y agoThe migra maintainer has abandoned it for greener pastures. If you’re interested in sponsoring an alternative, send me an email. My pgmigrate tool has all the groundwork necessary to make this possible but I have held off implementing this because I am not personally interested in using it.
- jonny_eh 3y agoIn one of the diagrams: "Rename column lastname", did they mean "add column lastname"?
- tudorg 3y agoAh yes, good catch! Fixing..
- jedberg 3y agoThis 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 agoI 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.
- menthe 3y agoZero-downtime, undoable, schema migrations for Postgres... But definite downtime and undone blog post... > 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.
- hosh 3y agoI did find this: https://github.com/xataio/pgroll https://github.com/xataio/pgroll
- alexf_19 3y agoThis should be back up now: https://xata.io/blog/pgroll-schema-migrations-postgres https://xata.io/blog/pgroll-schema-migrations-postgres That's what we get for trying to fix some text ;-)
- tudorg 3y agoSorry about it, small glitch with our blog as we tried to fix something in the title and accidentally broke the metadata for it :)
- csmattryder 3y agoWhen can we expect blogroll to be released??
- alexf_19 3y agoWell played, well played.
- dboreham 3y agoFor those curious, as I was, how this works, beyond the details in the readme and blog post, note that "schema" in this context is both a loose term (we changed the schema, old schema, new schema) AND a concrete thing in PostgreSQL[0]. It's helpful to know that pgroll implements the first one (let's change the schema) using the second one (make Schema objects in PG [1]). The magic is in creating different views in different Schemas that map appropriately to underlying tables (in a third Schema). 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 https://www.postgresql.org/docs/current/ddl-schemas.html [1] https://github.com/xataio/pgroll/blob/main/pkg/roll/execute.go#L64 https://github.com/xataio/pgroll/blob/main/pkg/roll/execute.... [3] https://stackoverflow.com/a/67525360 https://stackoverflow.com/a/67525360
- surjection 3y agoYou're right. I wish schema wasn't such an overloaded term :) 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-SCHEMAS-PATH https://www.postgresql.org/docs/current/ddl-schemas.html#DDL... [1] - https://github.com/xataio/pgroll/blob/main/docs/README.md#client-applications https://github.com/xataio/pgroll/blob/main/docs/README.md#cl...
- dkubb 3y agoI mentally change schema to namespace when thinking about the postgresql feature.
- guffins 3y agoYou’re not alone. That’s also how PostgreSQL itself thinks about schemas! https://www.postgresql.org/docs/current/catalog-pg-namespace.html https://www.postgresql.org/docs/current/catalog-pg-namespace...
- jmccarthy 3y agoThank you for the project! I did a brief scan for an explanation of the PG 14+ version constraint. Which 14-specific features are you relying on?
- exekias 3y agoWe use a few statements that are not supported by previous versions. For instance `CREATE OR REPLACE TRIGGER`. Supporting previous versions would be possible, but we went for the most recent ones to reduce complexity.
- ps256 3y agoHow do people typically do migrations in production anyway - what tools are used? what are the best practices?
- meowtimemania 3y agoAt my job we do database migrations 1 week and then deploy code the next. That way if any problems happen in either, it’s easier to roll back.
- lazyant 3y agoFlyway and Liquibase are typical migration tools. Best practice is to have your new code backwards-compatible with the old schema so you can do blue-green deployments.
- peter_l_downs 3y agoFlyway is generally the most popular. Django/alembic in the python world. 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! https://github.com/peterldowns/pgmigrate https://github.com/peterldowns/pgmigrate
- claytonjy 3y agoI used sqitch in a past job and loved it, but I had to basically implement what pgroll does automatically in order to ensure smooth migrations, and I hit plenty of issues along the way. Learned a lot, but pgroll looks much friendlier.
- Already__Taken 3y agothe 3 schemas is new to me and a cool idea but, how about a migration that removes a column into a few linking tables. what does that look like? I've changed note table colmun into a note-type and it's attached ID so you can add a note to anything. initially with notes from the original table colmun. how does that undo
- exekias 3y agoI didn't fully understand the change that you are explaining, but in general having old & new schemas working relies on the migration definition having the proper `up` & `down` functions defined. These are postgres functions so you can fit any logic in them to ensure that a column deleted in the new schema gets properly backfilled to the old one. 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.
- menthe 3y agoBeen reading the code.. very tidy. However the Complete step (e.g. in op_set_notnull.go) renames the temporary column name to the proper column name on the underlying table.. but while the docs describe the view on the new schema getting updated to refer to the now renamed underlying column, I do not seem to find the step where it happens? Also, shouldn't those two steps be in a transaction to ensure no failed queries in between - otherwise that's enough to be qualified as downtime ihmo? Quite dubious to see that `createView` is only called once, on `Start`, and that there doesn't seem to be locks or transactions. 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`.
- surjection 3y agoThere is no code to do this because it's actually a nice feature of postgres - if the underlying column is renamed, the pgroll views that depend on that column are updated automatically as part of the same transaction.
- menthe 3y agoVery cool, thank you!
- kakoni 3y agoUsing this thread to ask; Is there some simple sql based migration tool for psql?
- a8m 3y agoCheck out: https://github.com/ariga/atlas https://github.com/ariga/atlas. It’s like Terraform for databases. (I'm one of the authors of this project).
- nextaccountic 3y agoForr postgres, how does the schema diffing aspect compare to migra? https://github.com/djrobstep/migra 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 https://github.com/bikeshedder/tusker and https://github.com/blainehansen/postgres_migrator https://github.com/blainehansen/postgres_migrator), issues are piling up but development seem to be slowing down
- deleted 3y ago[deleted]
- rotemtam 3y agoHey, 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 https://discord.gg/zZ6sWVg6NT
- 1ba9115454 3y agoI didn't look too deep at this as soon as it said JSON. I was gone. I've been using dbmate which uses SQL and works really well.
- GRBurst 3y agoSo during migration both schemas are valid if I understood correctly?! It would be awesome if "during migration" could be lifted to a point where it is possible to keep both schemas (old and new) for as long as I want and do migrations/transformation of incoming request (like queries) on the fly. Then I could map my different api version to different schemas and these on the fly transformation would be able to take care of the rest in many scenarios :-)
- chrisweekly 3y agoI had the same thought; eager to see if anyone can explain why this wouldn't work, or (better) how they're already doing this today.
- exekias 3y agoThis is actually the case, old and new schemas are available and working until you complete the migration, and you can run this step whenever you want. 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.
- GRBurst 3y agoso if I want to sunset my api version X in 1 year for whatever reason and I am able to support an old schema X, which api X maps to, for that time period without any hassle (and not only during migration), this would be a much bigger feature / USP for me then everything else mentioned. I am really curious to look deeper into this :-)
- michaeldejong 3y agoVery cool! Congratulations to the authors on the release! I'm the author of a similar (zero-downtime migration) tool for PG called QuantumDB[0]. It was the first (to my knowledge at least) tool to support foreign keys, by creating table copies (keeping those in sync using triggers), and exposing multiple schemas through a custom database driver. I never got to production-ready version unfortunately, but I'm happy this one did. I'm seeing a lot of familiar concepts, and it looks well thought out. [0] https://github.com/quantumdb/quantumdb https://github.com/quantumdb/quantumdb
- jakswa 3y agoif you're in rails, then in my experience you just add `safe-pg-migrations` gem and call it a day :D
- tryithard 3y agoCool, We use liquibase I wonder how this compares to it? 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?
- exekias 3y agoI don't know much about Liquidbase, but I believe it doesn't support accessing both the old and the new schema versions at the same time? (I could be wrong here) 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/backfill.go https://github.com/xataio/pgroll/blob/main/pkg/migrations/ba...
- canadiantim 3y agoAny chance of doing something similar with sqlite?
- tlarkworthy 3y agoHow would I fit this into an alembic workflow?
- moltar 3y agoAre there any plans for an abstraction over JSON?
- ris 3y agoThis does look awesome, though I think I'd need a lot of trust in it before I went down this route. It seems to take a pretty drastic approach and it's unclear how complex a schema it would be able to handle. e.g. how well would its use of triggers to synchronize fields work with a schema that itself uses triggers in various ways? I can imagine some weird interactions that could take place. I'd also be a bit nervous that any of these tricks may themselves cause (perhaps temporary) performance problems.
- nojvek 3y agoThere is no magic bullet to database migration unless your app is deployed with the database itself and can atomically switch to new version as a DDL change is committed, or you take down the whole ship in maintenance mode and bring it back up once app + DB is migrated. 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.
- totallywrong 3y agoThis looks amazing, thank you!
- nerdyadventurer 3y agoIs this ready for prod use?