5 ms·
Our Multi-Tenancy Journey with Postgres Schemas and Apartment
- Walkman 10y agoIf you are interested in the topic of multi-tenancy, this Microsoft article is the best I ever found: https://msdn.microsoft.com/en-us/library/aa479086.aspx https://msdn.microsoft.com/en-us/library/aa479086.aspx
- why-el 10y agoI completely agree. My first professional job was dealing with multi-tenancy, and this article I had printed and sit on my desk for months. Extremely well written.
- infogulch 10y agoIf you go with the separate-database model, how do you manage database deployments and syncing it with application deployments?
- Walkman 10y agoExample 1: You can stop all instances and wait for sycn/migration to complete, and start all instances. 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.
- jacobkg 10y agoEveryone's experience is of course their own, but I wanted to provide a counterpoint from my experience over the past 5 years. At NationBuilder we do schema-based multi-tenancy with Postgres. We have over 30,000 schemas spread across over a dozen databases. One of our databases has more than 5,000 customer schemas on it (the average is about 2,500). Nevertheless we are able to run a Rails application, execute migrations, generate new schemas using the Schema.rb file, add and remove indexes, and many of the other items mentioned in the article. 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)
- arethuza 10y agoI had no idea what NationBuilder does (definitely not in the target market) - but having had a look at your website I can see why your customers could well be rather security conscious!
- barrkel 10y agoWe do SaaS for the financial industry, and we started out creating a separate virtual network with multiple VMs for each customer. Router firewalling, iptable lockdown, IP whitelisting to customer IP range. Encrypted disks (backed by SAN) for database storage with different keys per customer. Etc. 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.
- chrismorgan 10y agoHow about row-level security? As I understand it—without having actually used it—it should be able to get the same level of isolation as schemas (so long as you stay away from incrementing, user-exposed IDs), simply making all the other tenants’ data invisible.
- 15155 10y agoRLS doesn't allow for one very important administrative feature: 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.
- tener 10y agoPerhaps I'm missing something, but to me the obvious way to serve multiple clients is to simply create multiple isolated deployments, including a separate database server for every client. Handling this on the application level feels like unnecessary complexity. Any good reasons to go with the approach described in the blog post?
- arethuza 10y agoWhat if you want to support a very large number of relatively small tenants - having a separate server for each one of those could well be overkill.
- geocar 10y agoQuerying across multiple schemas can be useful, for example if: • 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.
- eternalban 10y ago> including a separate database server for every client .. .. 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.
- zwily 10y agoWe use a similar scheme. The biggest driver for us is being able to move tenants around between databases to balance load as our tenants grow.
- jacobkg 10y ago+1 We are continuously rebalancing customers between database servers. An added benefit is that doing a pg_dump/pg_restore removes all accumulated database bloat which decreases size on disk and improves performance
- abishekk92 10y agoHow long does it usually take to do such restructuring? Also, are you able to do this with minimal downtime? I have similar usecase, however, the export and re-import usually takes quite a long time.
- zwily 10y agoWe use bucardo to move a schema to a different host with no downtime.
- Walkman 10y agoIn my experience there is no point developing multi-tenant software nowadays, because operating large number of isolated environments is a solved problem and really cheap nowadays. You can buy VPS-es for less then 5$ today, you can automatically create them, deploy the instances easily. You can scale either horizontally or vertically (Docker can help both). It's not much more work, but much more secure and more scalable and almost as cheap as a multi-tenant solution would be.
- cakes 10y agoMulti-tenancy solves some problems around maintenance and upgrade paths that are hugely beneficial when you are managing multiple deployments. If you have even more than just 1 deployment of a thing and scale to 2 (or N), you now also have to manage the software level of those N containers (without multi-tenancy) vs. just 1 deployment for everywhere.
- mason55 10y ago> If you have even more than just 1 deployment of a thing and scale to 2 (or N), you now also have to manage the software level of those N containers (without multi-tenancy) vs. just 1 deployment for everywhere. 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).
- cakes 10y agoEntirely agree. Sometimes it is a feature (and sometimes a curse). Even uptime is a good point for why you couldn't "upgrade everyone" together. 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.
- 10y ago
- Todd 10y agoOne advantage of storing multiple schemas in one database is that a connection can be shared (assuming a shared authentication strategy). This is because, as mentioned in the article, the switch to one schema vs. another happens after the connection. This can make it easier to pool connections and to share servers across tenants. A tenant_id approach gives the same benefit without the isolation, but a separate database (even on the same server) doesn't. Edit: typo
- stcredzero 10y agoI don’t know exactly how to represent that in Big O, but I think it would look something like O(WTFxN). If WTF is a constant, then O(WTFxN) == O(kN) == O(N)
- vog 10y agoI don't think the author meant "WTF" to be a constant. It was defined to be whatever complexity index creation has. 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.
- ibejoeb 10y agoI'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.