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.

> this is a solved problem

It really isn't. The addition of an index was an example, and a relatively straightforward/simple one, of general schema migration pain. And even index additions aren't a solved problem: CONCURRENTLY (postgres only) and other affordances don't work in every case, as their documentation abundantly illustrates--what about overlapping indices, foreign key "inherited"/implicit indexes, UNIQUE constraints, and so on? Even in postgres with a DDL expert on hand, things get hairy fast.

In MySQL/maria, that "very brief lock" at the end of index creation can turn out to be anything but, if the table is high-write-volume, since InnoDB locks aren't FIFO--at least, not in the way people expect them to be.

And again, that's a simple case for which mitigations exist! More complex schema migrations hit immense amounts of trouble at even medium sizes of data and query volumes.

Online-schema-change tools that use triggers/cutovers are extremely limited: not only do they take locks to set up and manage their triggers/replication systems internally (meaning that they can cause severe disruption, or fail to migrate, in the face of continuous $thousands/sec of writes to a table), but their underlying mechanism is also often one of duplicating a table entirely, bulk-copying data from old to new, using triggers to stream changes during the migration, and then cut over. That mechanism is flawed for large tables (2x the data might not fit), flawed for high write volumes (2x the update operations is an unacceptable latency cost in plenty of circumstances), and just plain risky (that final cutover/drop/rename to point things at the new table is and probably will always be extremely lock-ful).

To be clear,the availability of advanced database facilities like CREATE INDEX ... CONCURRENTLY, triggers, and online-schema-change tools are all good things! My point is that this is very, very far from a solved or painless problem space--one that starts to become quite painful at even moderate scale.

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