Our Multi-Tenancy Journey with Postgres Schemas and Apartment
influitive.io
influitive.io
Example 2: (which is much better IMO) deploy the new version, but keep the older versions running. Sync/migrate one-by-one and start the new version when ready.
Has it been challenging? Absolutely. But it definitely is possible. Furthermore we have not found a better way to ensure the privacy of our customer data (separate schemas is the closest we can get to guaranteeing that a badly written join won't expose data to the wrong customer).
PS - I applaud the authors of the original article for publishing their schema management code as the open source Apartment gem (which I wish had existed when we were starting out)
So we had to scale up our deployment process to multiple concurrent migrations per customer pretty much from day one.
The downside of this is that customers need to be big to afford the constant overhead of physically renting the machinery to serve them, administer them etc. So we're moving towards multi-tenancy from the other angle, aiming at smaller firms with less stringent data security requirements.
Handling this on the application level feels like unnecessary complexity.
Any good reasons to go with the approach described in the blog post?
• You want easy administrative controls for impersonating a customer
• You want to run business queries across multiple customers
• You integrate authentication with your database, so you don't need a separate service to tell your application "which" database endpoints to use with your username.
• You want to simplify sysadmin job of managing a single database instead of thousands of databases.
And so on. Sometimes these things are worth giving up the "simple" isolation.
.. is actually a kind of "[h]andling this on the application level", with "this" being the Postgres' process management. Each database gets its own daemon and it is this per-database process that is responsible for launching a peer process for each connection.
There are likely legitimate use cases for multiple PG databases per node, but multi-tenancy is not one of them.
Edit: typo
Data migration/export
Schemas allow an admin to excise an exact backup of a user's data without any other users, RLS is a little less flexible in this regard.
If WTF is a constant, then O(WTFxN) == O(kN) == O(N)
However, I still disagree with "O(WTF*N)", because "WTF" almost certainly depends of the number of rows in the table (large tables take longer to index than small tables), and that number becomes lower in the multi-tenancy approach, because the individual tables are smaller.
Sometimes this is a feature. If you're doing managed enterprise SaaS then you frequently need to have different customers running on different releases for whatever reason (maybe one customer has a difficult data migration that has to take place before you can upgrade, maybe another customer has something custom that needs to be upgraded in sync).
At the same point if I were hosting something for a group of parties (at my own expense/will/etc.) and I want everyone to just be at version X.Y.Z - having to upgrade each deployment itself is definitely a pain.
Not all software needs to be multi-tenant but it also isn't something that is no longer needed or without thought.
Sometimes multi-tenancy is a simpler solution because no instance fleet management code needs to be written. With everything there are downsides to multi-tenancy too so you have to weigh the pros and cons for the specific project.
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.