These kinds of rule-based migration safety checks are simple, but hardly complete.
The problem is that some migration safety depends on the state of the database, which isn’t represented in the DDL statement alone. For example, altering a column type is either a no-op or an exclusive locked table rewrite depending on the original type of the column.
There are other footguns that can happen if the column you’re altering is a foreign key, where multiple tables can be locked.
I went down a rabbit hole a few years ago and built a system[1] to introspect a given migration against a live schema, and actually let Postgres tell you what it’s doing[2].
It would be great to have better built-in support for this (EXPLAIN for DDL statements?), but this direction feels safer and more accurate than static rulesets.
Safety also depends on the size/activity of a table being altered (i.e rewriting an empty table is fine). Having an accurate representation of the locks and actions performed by the database lets you integrate with production metrics to actually determine real-world safety across a fleet of databases, rather than guessing.
I would go further than this and argue that most bugs during database migrations happen because of mismatched application behavior with the action of the migration, not because the DDL was wrong. E.g. removing something that was still being relied on by the application, or starting to backfill data to a new column before the application is fully writing it. The most insidious version of this is where one application server doesn't have it's code updated (or comes back from the dead, etc) and causes the problem.
At a previous job what I did to prevent that was to have a special DB table that would signal what capabilities the database has, and the code would read that table and compare to its own requirements. If a capability required by the database was not present in the code (e.g. code not updated for a new feature) the code would refuse to make any writes to the DB and error all incoming requests. Likewise if a capability required by the code was missing from the database (e.g. code deployed too soon and database migration not run yet) it again would refuse requests. Before setting a feature to required in the DB and preforming the migration with feature flags, we could check all known application servers were reporting compatibility with the new feature (if any were down or not reporting at the time, they will be blocked in the next step - prioritizing safety over liveness)
I came here to say this too. Most bugs I run into are when databases schema versions interact with multiple software versions. If you are a low availability service, you can just take down the service and update the schema atomically, but 90% of the time you actually need to write backwards compatible migrations and forwards compatible code and coordinate the rollout accordingly.
My rule of thumb is no more than two distinct software versions can share a database at the same time. This effectively rules out database sharing between services. That way you push the problem to an API layer, which is better equipped to handle maintaining compatibility between many client versions.
There is really nothing specific to API layer (I am assuming you mean REST API layer) in building backwards- and forwards-compatibility compared to databases. If anything, it is less powerful.
It is pretty easy to do with databases as well, you just need to adopt the right mindset.
For instance, if you think having an "api/vX" of an endpoint is acceptable, then it must also be to create a duplicate table/relation — you'll have exactly the same challenges in maintaining consistency between the two, though RDBMS offer quite a bit of tooling built-in.
The difference between a rest api (or gRPC or whatever) and a database schema is the degree to which clients of either are coupled to the data model. With a rest API, you have a degree of freedom to change the data model without breaking clients. Sure, you could implement that decoupling via views (for reads) and stored procedures (for writes) within a DBMS. This is generally clunky though, in my experience
API consumers are similarly tied to the exact API contract — what you need to do to maintain backwards compatibility is introduce a different view into it (which is why I bring up /api/vX as the common pattern).
There are even richer ways to change the DB model with triggers, for instance. Really, it's only clunky because we are not used to doing it (or do not know how), but there is no technical or conceptual limitation compared to API evolution.
I believe there are patterns that always work, but might not be optimal for all circumstances.
Eg. you could have a mirror table that you keep in sync with triggers without any constraints or foreign keys, do the migration on it, and then switch them around when ready.
orf · · focus · HN ↗
The problem is that some migration safety depends on the state of the database, which isn’t represented in the DDL statement alone. For example, altering a column type is either a no-op or an exclusive locked table rewrite depending on the original type of the column.
There are other footguns that can happen if the column you’re altering is a foreign key, where multiple tables can be locked.
I went down a rabbit hole a few years ago and built a system[1] to introspect a given migration against a live schema, and actually let Postgres tell you what it’s doing[2].
It would be great to have better built-in support for this (EXPLAIN for DDL statements?), but this direction feels safer and more accurate than static rulesets.
Safety also depends on the size/activity of a table being altered (i.e rewriting an empty table is fine). Having an accurate representation of the locks and actions performed by the database lets you integrate with production metrics to actually determine real-world safety across a fleet of databases, rather than guessing.
1. <a href="https://github.com/orf/locksmith" rel="nofollow">https://github.com/orf/locksmith
2. <a href="https://github.com/orf/locksmith/blob/f8798c6ee92bfae10d416c49aae33bc4afaaabf1/crates/locksmith/src/oracle.rs#L34" rel="nofollow">https://github.com/orf/locksmith/blob/f8798c6ee92bfae10d416c...
grogers · · focus · HN ↗
At a previous job what I did to prevent that was to have a special DB table that would signal what capabilities the database has, and the code would read that table and compare to its own requirements. If a capability required by the database was not present in the code (e.g. code not updated for a new feature) the code would refuse to make any writes to the DB and error all incoming requests. Likewise if a capability required by the code was missing from the database (e.g. code deployed too soon and database migration not run yet) it again would refuse requests. Before setting a feature to required in the DB and preforming the migration with feature flags, we could check all known application servers were reporting compatibility with the new feature (if any were down or not reporting at the time, they will be blocked in the next step - prioritizing safety over liveness)
catlifeonmars · · focus · HN ↗
My rule of thumb is no more than two distinct software versions can share a database at the same time. This effectively rules out database sharing between services. That way you push the problem to an API layer, which is better equipped to handle maintaining compatibility between many client versions.
necovek · · focus · HN ↗
It is pretty easy to do with databases as well, you just need to adopt the right mindset.
For instance, if you think having an "api/vX" of an endpoint is acceptable, then it must also be to create a duplicate table/relation — you'll have exactly the same challenges in maintaining consistency between the two, though RDBMS offer quite a bit of tooling built-in.
catlifeonmars · · focus · HN ↗
necovek · · focus · HN ↗
There are even richer ways to change the DB model with triggers, for instance. Really, it's only clunky because we are not used to doing it (or do not know how), but there is no technical or conceptual limitation compared to API evolution.
necovek · · focus · HN ↗
Eg. you could have a mirror table that you keep in sync with triggers without any constraints or foreign keys, do the migration on it, and then switch them around when ready.