But if the idea is to isolate accounts form each other, the different schemas would be available to different DB users. You would have to re-authenticate to get access to the other DB.
The same query run across all databases should either return 1 query (for the valid tenant) and empty set for all other databases, OR it should return the same result set regardless.
I just realized what I am proposing, a hidden out of band column that is effectively the "database id" for that row.
With schemas, it's definitely possible to use the same generator for ids across schemas (at least, I'm 90% sure it is in Postgres), but you'll probably end up fighting against ORM libraries to get it to work properly (Rails for instance makes a LOT of assumptions about how the id column works), and you aren't technically guaranteed uniqueness since you'll still have distinct PK columns.
Exactly.
(It's not actually simpler when query execution over 100s of millions of rows is a perf bottleneck, and each tenant has several billion rows in the main tables. Then you're grateful you can schlep them around, and keep small tenants fast, etc. Even now, Postgres would still be a dubious choice due to the unpredictability of its query planner, though I use it for all my hobby projects.)
Rails migrations work reasonably well with apartment gem. Never had a problem with inconsistent database migrations. Sometimes a migration will fail for a tenant, but ActiveRecord migrations records that, you fix the migration, and reapply, a no-op where it's already done.
We don't use a single mysqld for every tenant mind, it's not like migrating tenants is completely serialized.