The operational relational schema paradigm | Hacker News Reader

The operational relational schema paradigm

planetscale.com

28 pointskiyanwang7 comments

I wish relational databases would offer One Simple Trick that I think would improve migrations immensely:

Every table should—by default—be a view of the actual underlying table. In other words, a table is a shim for the real table. This way, when it's time to perform migrations, the shim to the underlying table could change independently of the actual migration, since the interface to the table would be preserved for all applications.

I know there are overhead concerns for this shim-view idea, but I'm sure many of them can be cleverly elided under many conditions.

This is a conversation our team had for a while. We are still on the fence about it because the physical schema is very stable at this point. There haven't been any major issues with migrations in years.

But, this is one of those things like planting a tree. If the entire business depends directly on the physical tables, getting a major change in could instantly become a nightmare.

This is precisely what I did for my automated zero-downtime migration tool and it works pretty well: https://github.com/fabianlindfors/reshape. At least for Postgres, simple views like these have almost no overhead as queries are simply rewritten for the underlying table.

Very cool project! Thanks for sharing

Too much here to deconstruct in a comment, but some choice responses:

* How does the author suggest non-blocking DDL is actioned? You'd have to snapshot your entire table using e.g. a similar method to snapshot isolation at the row level to allow concurrent access. Accessing the table, while in transition, will lead to inconsistent output data sets.

* If your migration is 'days-long', then you are doing something wrong. Try creating a new table (instant) then ETL'ing your data over from the original, dropping and renaming. It will be much faster AND it addresses accessibility on the table (no table lock).

* You can schedule migrations if using a tool like Flyway by doing this in code. If you insist on using a database product, you could use (for MSSQL) SQL Agent, or cron for anything on Linux (run a SP), etc.

* It is already possible to revert a migration if you are using Oracle, using Oracle Flashback. For other DBs, you can still revert a migration if you are using your deployment tool properly i.e. redeploy an earlier version of your schema and drop all other objects. You'll have to deal with your data, of course.

This would have been a more interesting post if a possible solution to all the points was posited.

Author here. Thank you for your thoughts! Some comments:

> How does the author suggest non-blocking DDL is actioned?

Online DDL is done in MySQL using one of the 3rd party tools, such as pt-online-schema-change, gh-ost, or now via Vitess. Running online DDL is an industry standard in MySQL since about 2009. We have recently integrated online DDL natively to Vitess, see https://vitess.io/docs/14.0/user-guides/schema-changes/manag... and https://docs.planetscale.com/concepts/nonblocking-schema-cha...

> Try creating a new table (instant) then ETL'ing your data over from the original, dropping and renaming

Alas, in an online service you can't afford such downtime. Online DDL allows you to change the structure of your table even as your app continues to interact with the database.

> It is already possible to revert a migration if you are using Oracle, using Oracle Flashback.

What I call "revert" is something else and more powerful. Once you have migrated your schema, your app keeps writing to the database. Oracle flashback means going back in time, losing that new data. In my definition of Revert, you can restore your original schema while at the same time fully retain your data, including the data accumulated since the migration. Please see https://planetscale.com/blog/its-fine-rewind-revert-a-migrat...

> This would have been a more interesting post if a possible solution to all the points was posited.

That's fair! We plan a followup post. I actually intentionally did not include any solutions in this post as I wanted to first make a statement, an observation of the state of the relational database today. At Vitess and at PlanetScale, we have indeed tackled all those points, to be discussed in the followup post.

programming in time is hard

(especially with migrations, because migrations take time)

this feels too high-level -- different migration operations can afford different guarantees (esp in the multi-node case). In particular this doesn't talk about data migrations, where you might need to lock 2 tables + join them to generate a third. not all migrations are pure-sql either, they may include app code

my favorite article about this is benchling's https://benchling.engineering/move-fast-and-migrate-things-h... because it talks about pre + post deploy migrations

Reply on news.ycombinator.com