I'd like to offer an alternative option to those currently confronting this topic. Use a database that explicitly supports this kind of thing, such as SQL Server RLS or Oracle RAS/VPD. (I'll note that PostgreSQL can do very similar things rather easily, too, but it might provide less of a benefit under larger loads. I'll write that up separately if there's interest.)

These feature work by with multitenant tables (e.g., tenant_id everywhere) by allowing the application to initialize a predicate that is automatically applied to every query executed.

For example, app executes "select * from orders where quantity > 10" for tenant 10 and the database resolves it to "select * from orders where tenant_id = 10 and quantity > 10".

With this configuration, you get both the isolation afforded by schemas and the uniformity (thing DRY) if the data definitions. You're protected from leaky queries and you can leverage the performance features.

Here are a few considerations:

Recognizing that, to the database, objects schema1.sometable and schema2.sometable are different, it becomes clear that large schema-based tenancy is quite a bit more work for the database. Each statement must be compiled and executed separately, so if "select foo from bar" is one plan, then for n schemas, you have n plans for the same thing. Those plans must exist in memory. In the nationbuilder case in this thread, where the average number of schemas per node is 2,500 that's a crazy amount of additional overhead. Plan will be evicted, and you're going to be burning CPU recompiling statements and regenerating plans.

Furthermore, you're going to be facing connection management issues and the potential leakiness there. Improper application of connection pooling can just as easily expose cross-schema data as cross-row data. In large system, just the sheer number of connections may become burdensome, so connection sharing will be important in reducing degradation from setup/teardown. Most vendors offer fancy connection striping for smartly routing queries to appropriately initialized application sessions.

If you're growing and you need to cope with a lot of data, you can now begin to employ more advanced database features. For example, all vendors offer some kind of data partitioning and parallel execution. This is going to let you focus more on the physical structure of the data and arrange it in ways that naturally support the kinds of queries you need to perform.

Likewise, it will simplify unloading a loading to analytical systems. It's pretty easy to extract this month's orders from one orders table (even if that table is physically all over the place and managed by some fancy storage management feature), copy the file, and load it into Vertica. You're going to get into building some novel system to do it for dynamic, distributed schemas.

Following that, it might be economically compelling. License cost might be less than engineering cost. It probably will be. Some of the features I've mentioned are separately licensed under some vendor models, and that's an additional expense, but it's also one that can be deferred until needed. It's really worth investigating whether you should be spending this much engineering effort non-line-of-business matters, because it very well could cost much more than implementing an existing solution.