Get the top HN stories in your inbox every day.
dboreham
surjection
You'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... [1] - https://github.com/xataio/pgroll/blob/main/docs/README.md#cl...
dkubb
I mentally change schema to namespace when thinking about the postgresql feature.
guffins
You’re not alone. That’s also how PostgreSQL itself thinks about schemas! https://www.postgresql.org/docs/current/catalog-pg-namespace...
fabianlindfors
You got it right! I wrote a blog post a few years back about how this technique works for anyone curious: https://fabianlindfors.se/blog/schema-migrations-in-postgres...
candiddevmike
Help 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.
exekias
I believe this is one of the reasons why migrations become scary in many cases. If something goes wrong "the only path is forward". Also, rolling out new versions of the application means either breaking the previous versions (with some instances still running) or doing the migration in several steps.
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...
dot5xdev
> 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.
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?
indigo945
Just from my understanding from having read the linked website: yes, you do.
"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.
undefined
pcthrowaway
I recently had to do a migration on a timescale hypertable where a "schema" was migrated for a table which had jsonb columns containing arrays of arrays of numbers to a new table containing the same data as two-dimensional postgres arrays of numeric[][] data (better storage characteristics)
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?
exekias
To do this with pgroll I would use an alter_column migration, changing the type: https://github.com/xataio/pgroll/tree/main/docs#change-type, this would:
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!
claytonjy
This is almost exactly how I did a similar migration, also in Timescale. I used PL/pgSQL and sqitch, did you use a migration tool?
mst
Apologies for the off-topic-ness, but no matter where I've tried putting the mouse focus on the post, Up/Down don't work to scroll (but PgUp/PgDown are fine).
(I very much appreciate the effort to provide tooling that puts all these things together, btw)
exekias
Thanks for reporting! we will look into it
candiddevmike
That's great that pgroll does this, but the heavy lifting for supporting this comes at a huge cost on the application side, IMO.
surjection
Do you mean the extra configuration required to make applications use the correct version of the database schema, or something else?
contravariant
I 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
Yes, 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
Isn't that what transactions are for?
vanviegen
That wouldn't allow you to partially roll out your new code base (depending on the altered schema), nor to easily revert such a roll out.
contravariant
That would be hard to coordinate if your application is even slightly complicated.
Nullabillity
During development I'll often keep rolling the same change back and forth (with minor changes), rather than recreate the database from scratch/a backup each time.
bityard
If your organization requires change management (as many are contractually obligated to), then you don't have much of a choice. Every change needs to be tested, every change needs a way to fully and completely roll back the change, and the rollback has to be tested.
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.
nobleach
A couple of places I've worked lived like this. We used the free version of Flyway with Spring Boot and Quarkus. We got by really well "rolling forward". My next gig used Sequalize and MySQL. Aside from hating those technologies, I hated that false sense of security baked into "oh well, we can always roll back"
cptMayhem
> I hated that false sense of security baked into "oh well, we can always roll back"
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.
fabianlindfors
Congrats 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.
menthe
Been 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
There 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
Very cool, thank you!
michaeldejong
Very 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.
GRBurst
So 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 :-)
exekias
This 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
so 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 :-)
chrisweekly
I 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.
jedberg
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?
skrebbel
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
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
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.
hosh
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
> 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
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.
monocularvision
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.
menthe
Zero-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.
alexf_19
This should be back up now: https://xata.io/blog/pgroll-schema-migrations-postgres
That's what we get for trying to fix some text ;-)
hosh
I did find this: https://github.com/xataio/pgroll
tudorg
Sorry about it, small glitch with our blog as we tried to fix something in the title and accidentally broke the metadata for it :)
aeyes
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:
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
> 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.
cpursley
What 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.
evanelias
For things like stored procs, triggers, and views, there's a lot of vendor-specific (e.g. Postgres vs MySQL vs SQL Server) edge cases in syntax, introspection, and operational best practices. That's true of tables too of course, but at least the introspection part tends to be fully functional for tables in all major database systems. For other object types, introspection can be half-baked and things can get painful in general. It's much harder to design a generic declarative tool which works across multiple DBs without making sacrifices in expressiveness, safety, and ergonomics.
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...
tianzhou
One of Bytebase authors here, we use "state-based" and "declarative" https://www.bytebase.com/blog/database-version-control-state... interchangeably
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.
evanelias
I was referring to blog posts, for example [1], in which you admittedly use both terms in the text, but you focus more on "state-based" for example in the headline and image. You also erroneously conflate declarative migrations with somehow involving `mysqldump` for some reason? This is what I mean when I say posts like this feel like they're designed to make declarative migrations seem strange and foreign.
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...
cpursley
I’m 100% in on Postgres but what you describe sounds awesome.
peter_l_downs
If you’re seriously willing to pay money for this, send me an email — I’m considering implementing this in my pgmigrate tool but not sure if it’s worth the development time. I have all the groundwork done, probably achievable in about 10 hours of concerted effort.
t1mmen
I’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
I believe Migra can generate those changes for you via diffing, not sure how well it handles dependent views/functions/etc
t1mmen
Thanks! 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.
Guillaume86
SSDT for mssql can do it, I have my schema as SQL files in the repo, the tooling can diff and migrate between schema versions.
pierat
"Undoable" in this case means "possible to undo", not irreversible!
wccrawford
It wouldn't mean "irreversible" regardless. It would mean "not possible to do" (ie impossible) or "possible to undo".
bitslayer
That 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".
murkt
Yeah, needs a dash there. Undo-able.
exekias
Thank you for noticing this! We are looking into changing the wording :)
jandrese
Thanks, I was wondering why they were advertising the process to be impossible. A better term would have been "reversible".
canadiantim
Seems like very unfortunate wording then as irreversible is exactly what I thought
hamilyon2
"With easy rollback", because rollback is well understood
systems
yes, 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
ris
This 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.
Get the top HN stories in your inbox every day.
For 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 [1] https://github.com/xataio/pgroll/blob/main/pkg/roll/execute.... [3] https://stackoverflow.com/a/67525360