5 ms·

I actually have implemented a multi-tenant application based on Postgres 3 years ago and we have been using it in production ever since (for paying customers th

I actually have implemented a multi-tenant application based on Postgres 3 years ago and we have been using it in production ever since (for paying customers that is). As such I have some experience in this field and I'm actually in the process of moving (back) to a model where each customer has its own database (and application process). For context: I have a relatively low number of tenants (tens) which in turn each have mid-hundreds to many thousands of active users (the largest tenant has just about 10k active users). Unless you think you will have many hundreds or thousands of tenants (!= users) any time soon, I'd advise you to stick with one db per tenant. The reasons for backing out of the use of schemas for multi-tenancy are for me: * on a smaller scale (with regards to number of tenants, not users) it does not really offer any significant benefits in resource usage over one db per tenant, which makes the following reasons significant: * lack of isolation - unless you use a separate database user with carefully set permissions for each customer it is possible to access each schema from a single compromised application process. If you do actually use a separate db user for each customer, one of the benefits of using schemas as opposed to separate db's disappear (you can no longer share db connections/db backends across requests from different end-users so the number of concurrent database connections increases). * complexity - multi-schema use is usually not supported by default in common web frameworks and related tools. I use Django and had to create a custom db driver (and leverage thread-local storage) to get any sort of transparent support for multi-schema use (and especially in this case you want transparency for your developers - otherwise risk of isolation failure increases to the same level as with the 'one db/tenant_id in table' approach - see previous point). Also backup/restore becomes more complicated. And Django's management tools (the manage.py script) had to be taught a new trick. And South, the schema migration tool had to be ... You get it I guess. It works - sure, but took quite a bit of work to get right and there is always the risk that future updates to any standard packages used break it again. I'd rather not have it. * one schema per tenant implies also one application process for all (or at least multiple) tenants. This again makes it more complicated to keep things secure and you'll run into some other issues as well depending on the requirements for your application. One of those issues we've run into (not related to the database) is that we're in the position that we can definitively benefit from a 'one source code base for all' but still have to be able to customize our application per tenant with respect to the specific wording of certain aspects in the user interface and that in multiple languages. In the current setup (one application process for all) it meant that we could not use Django's native i18n machinery. Building an alternative (with proper version control in this case) was not as much fun as you may think and basically a mistake. In the setup we're migrating to we can leverage the native i18n features of Django (and git) for this job. So where we're going now is: * one virtualenv per tenant, with a dedicated Django project for each tenant (the Django project does not contain the actual application code, only settings and if the need arises customized .mo/.po files.) * one application process per tenant (I use uwsgi with emperor mode for this) * one database per tenant (hosted on a Postgresql master/slave pair) The resulting setup is easier to maintain and reason about than the current setup and my testing so for shows that resource-wise it does not make a significant difference. There is of course a bit more disk usage and a bit more memory usage. That is mostly because of the extra copies of the application code I need to keep around on disk and in memory - but that is all together a relatively minor bit of the total disk and memory usage so percentage wise it does not really make a difference (at least for us - is probably very situation dependent). Something I'd been worried about was the number of concurrent database connections. But after some testing that fear turned out to be unnecessary as even under very high load the number remained within manageable bounds. EDIT: formatting.

pestaa 14y ago
Considering the low number of tenants in your application, I believe automation was not an important requirement for you (if at all), but how would you go about creating virtualenvs on-the-fly?
- jvdongen 14y ago
  We actually do automate as much as possible (small team, lots to do). We've a Fabric script that sets up a complete new environment for a customer/tenant in one go. That includes creating and populating the virtualenv, creating a new database + database user etc. If the command is finished the customer basically is able to log in. And that script is actually only about a 200 lines of code or so. The nice thing of Fabric is that it makes automating something hardly more work than doing something in a one-off fashion.
mekoka 14y ago
During my research prior to starting development, I read two very interesting articles from the msdn library [1] [2]. My take from them was that there's a spectrum of possible architectures for multi-tenancy and within that spectrum, there isn't exactly any wrong or right decision. Quoting the second article, The distinction between shared data and isolated data isn't binary. Instead, it's more of a continuum, with many variations that are possible between the two extremes "Isolated - Separated DB - Separate Schema - Shared Schema - Shared" There are advantages and drawbacks to choosing a strategy over another, but one should not necessarily aim for some Holy Grail. The article goes on saying Where your application should fall along this continuum depends on your business, architectural, and operational needs, and on customer considerations [...] all of these considerations are interrelated to some degree. Regarding framework support, this was not my primary concern. We're building one app, so we might as well just build the whole thing from scratch. If we expected to reuse this code for multiple projects, I might have given it a second thought, but it's a single app with its own idiosyncrasies. I didn't want the constraints of following some "agreed upon" approach, at the cost of having to bend the natural alignment that the architecture would follow to stay faithful to what's expected of the application. Like I said, I could've chosen to do things from scratch, but I settled for what seems to me a much more beneficial alternative, a micro-framework and a specialized database library (Flask, SQLAlchemy). So far things have been running pretty smooth. Regarding complexity, I concur with you that I've had to write a couple of scripts to automate certain aspects of schema management, but honestly, it wasn't that bad (SQLAlchemy helps a great deal with this stuff). The alternative would be to create scripts to manage different application instances. So there's work either way. One of my main concerns was to keep development simple. The approach that you're considering was very appealing in that regard, as I would basically be building a single-tenant app and replicate it for each new tenant. But from my readings, I also learned that there are some performance and scalability disadvantages to running one application instance (and db) per tenant, at the scale that I'm aiming. When I found out about Postgresql schemas, it offered a very good compromise. I set up an app with the previously mentioned tools, spent some time reading on how to operate with schemas, tested a few queries, had some misses that taught me more on the peculiarities of this setup. But within a few hours of trial and error, everything worked as advertised. I never had to create a driver or anything like that. Upon connection start I just stored a connection on a thread-local object, started a transaction and switched schema. I then did what I had to do in my request and on the way out I released the connection. That was all on day 1. The code that manages the multi-schema access to the db is probably 5 to 10 short lines in 2 functions. The rest of the development is oblivious to the multi-tenancy aspect of the app. Regarding security, I won't claim to know what you know on the matter, but the scenario in which an attacker gains access to other schemas with a compromised process seems a bit far fetched to me. I'd have to read up a bit more on the topic to understand how that exploit might work. PostgreSQL has some interesting authentication protocols and I imagine that in combination with the OS' own auth protocol there are ways to prevent, or at least make things more difficult. [1] Architecture Strategy For Catching The Long Tail http://msdn.microsoft.com/en-us/library/aa479069.aspx http://msdn.microsoft.com/en-us/library/aa479069.aspx [2] Multi-Tenant Data Architecture http://msdn.microsoft.com/en-us/library/aa479086.aspx http://msdn.microsoft.com/en-us/library/aa479086.aspx

pestaa 14y ago

Considering the low number of tenants in your application, I believe automation was not an important requirement for you (if at all), but how would you go about creating virtualenvs on-the-fly?

jvdongen 14y ago
We actually do automate as much as possible (small team, lots to do). We've a Fabric script that sets up a complete new environment for a customer/tenant in one go. That includes creating and populating the virtualenv, creating a new database + database user etc. If the command is finished the customer basically is able to log in. And that script is actually only about a 200 lines of code or so. The nice thing of Fabric is that it makes automating something hardly more work than doing something in a one-off fashion.

mekoka 14y ago

During my research prior to starting development, I read two very interesting articles from the msdn library [1] [2]. My take from them was that there's a spectrum of possible architectures for multi-tenancy and within that spectrum, there isn't exactly any wrong or right decision. Quoting the second article, The distinction between shared data and isolated data isn't binary. Instead, it's more of a continuum, with many variations that are possible between the two extremes "Isolated - Separated DB - Separate Schema - Shared Schema - Shared" There are advantages and drawbacks to choosing a strategy over another, but one should not necessarily aim for some Holy Grail. The article goes on saying Where your application should fall along this continuum depends on your business, architectural, and operational needs, and on customer considerations [...] all of these considerations are interrelated to some degree. Regarding framework support, this was not my primary concern. We're building one app, so we might as well just build the whole thing from scratch. If we expected to reuse this code for multiple projects, I might have given it a second thought, but it's a single app with its own idiosyncrasies. I didn't want the constraints of following some "agreed upon" approach, at the cost of having to bend the natural alignment that the architecture would follow to stay faithful to what's expected of the application. Like I said, I could've chosen to do things from scratch, but I settled for what seems to me a much more beneficial alternative, a micro-framework and a specialized database library (Flask, SQLAlchemy). So far things have been running pretty smooth. Regarding complexity, I concur with you that I've had to write a couple of scripts to automate certain aspects of schema management, but honestly, it wasn't that bad (SQLAlchemy helps a great deal with this stuff). The alternative would be to create scripts to manage different application instances. So there's work either way. One of my main concerns was to keep development simple. The approach that you're considering was very appealing in that regard, as I would basically be building a single-tenant app and replicate it for each new tenant. But from my readings, I also learned that there are some performance and scalability disadvantages to running one application instance (and db) per tenant, at the scale that I'm aiming. When I found out about Postgresql schemas, it offered a very good compromise. I set up an app with the previously mentioned tools, spent some time reading on how to operate with schemas, tested a few queries, had some misses that taught me more on the peculiarities of this setup. But within a few hours of trial and error, everything worked as advertised. I never had to create a driver or anything like that. Upon connection start I just stored a connection on a thread-local object, started a transaction and switched schema. I then did what I had to do in my request and on the way out I released the connection. That was all on day 1. The code that manages the multi-schema access to the db is probably 5 to 10 short lines in 2 functions. The rest of the development is oblivious to the multi-tenancy aspect of the app. Regarding security, I won't claim to know what you know on the matter, but the scenario in which an attacker gains access to other schemas with a compromised process seems a bit far fetched to me. I'd have to read up a bit more on the topic to understand how that exploit might work. PostgreSQL has some interesting authentication protocols and I imagine that in combination with the OS' own auth protocol there are ways to prevent, or at least make things more difficult. [1] Architecture Strategy For Catching The Long Tail http://msdn.microsoft.com/en-us/library/aa479069.aspx http://msdn.microsoft.com/en-us/library/aa479069.aspx [2] Multi-Tenant Data Architecture http://msdn.microsoft.com/en-us/library/aa479086.aspx http://msdn.microsoft.com/en-us/library/aa479086.aspx