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.