I’m generally pro SQL-as-interface, but this is just wrong.

Not only are there all sorts of bizarre constraints imposed by databases on migration behavior that application code can’t express (for example, how can I implement a transaction-plus-double-write pattern to migrate to use a new table because the locks taken to add an index to the old table require unacceptably long downtime? There are probably some SQL engines out there that can do this with views, but most people solve it on the client side for good reason), but there are plenty of changes that you just plain can’t do without a uniform service layer in front of your database. Note that “uniform service layer” doesn’t necessarily mean “networked service layer”, this can be in-process if you can prevent people from bypassing your querying functions and going directly to the DB.

> Note that “uniform service layer” doesn’t necessarily mean “networked service layer”, this can be in-process if you can prevent people from bypassing your querying functions and going directly to the DB.

You can take it one step further and implement the “uniform service layer” in the database itself - using stored procedures and views.

This has downsides, like strong coupling with the specific DBMS, and difficulty of development in a comparatively primitive SQL dialect, but protects the database from “naughty” clients and can have tremendous performance advantages in some cases.

As the sibling comment mentioned, this is a solved problem. MySQL and MariaDB take a very brief lock on the table at the very end of index creation that you will not notice, I promise. Postgres does the same if you use the CONCURRENTLY option for index builds.

If for some reason you do need to migrate data to a new table, triggers.

If somehow these still don’t solve your problem, ProxySQL or the equivalent (you are running some kind of connection pooler, right?) can rewrite queries on the fly to do whatever you want.

Either triggers or create index concurrently? Do most people solve that on the client side? Doesn't e.g. percona use triggers?