๐Ÿ‘คksec๐Ÿ•‘2y๐Ÿ”ผ328๐Ÿ—จ๏ธ149

(Replying to PARENT post)

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

๐Ÿ‘คdboreham๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

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.

๐Ÿ‘คcandiddevmike๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

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.

[0] https://github.com/fabianlindfors/reshape

๐Ÿ‘คfabianlindfors๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

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`.

๐Ÿ‘คmenthe๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

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.

[0] https://github.com/quantumdb/quantumdb

๐Ÿ‘คmichaeldejong๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

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 :-)
๐Ÿ‘คGRBurst๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

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?

๐Ÿ‘คjedberg๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

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.

๐Ÿ‘คmenthe๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

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.

๐Ÿ‘คaeyes๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

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.
๐Ÿ‘คcpursley๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

"Undoable" in this case means "possible to undo", not irreversible!
๐Ÿ‘คpierat๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

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.

๐Ÿ‘คris๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

There 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.

๐Ÿ‘คnojvek๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

Cool, 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?

๐Ÿ‘คtryithard๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

Using this thread to ask; Is there some simple sql based migration tool for psql?
๐Ÿ‘คkakoni๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

hey HN, repo is here: https://github.com/xataio/pgroll

Would love to hear your thoughts!

๐Ÿ‘คtudorg๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

Thank 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?
๐Ÿ‘คjmccarthy๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

the 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
๐Ÿ‘คAlready__Taken๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

How do people typically do migrations in production anyway - what tools are used? what are the best practices?
๐Ÿ‘คps256๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

In one of the diagrams: "Rename column lastname", did they mean "add column lastname"?
๐Ÿ‘คjonny_eh๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

Why does a migration tool need to be subscription based? One time fee should be enough.
๐Ÿ‘คsidcool๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

If I'm using alembic for schema migrations already, how do I make use of this?
๐Ÿ‘คtibanne๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

Any chance of doing something similar with sqlite?
๐Ÿ‘คcanadiantim๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

Are there any plans for an abstraction over JSON?
๐Ÿ‘คmoltar๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

How would I fit this into an alembic workflow?
๐Ÿ‘คtlarkworthy๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

if you're in rails, then in my experience you just add `safe-pg-migrations` gem and call it a day :D
๐Ÿ‘คjakswa๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

Is this ready for prod use?
๐Ÿ‘คnerdyadventurer๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

I 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.

๐Ÿ‘ค1ba9115454๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0

(Replying to PARENT post)

This looks amazing, thank you!
๐Ÿ‘คtotallywrong๐Ÿ•‘2y๐Ÿ”ผ0๐Ÿ—จ๏ธ0