PostgreSQL Rising

I've seen it happen multiple times. The reasons vary, but it generally has to do with scaling up the system into more modularized components and using data stores better suited for particular sub-problems that the "big honking database" was used previously.

To be clear, I'm not really suggesting you avoid using fancy features in data stores like Redis or Solr. The relational database, with history as a guide, tends to be the first data store a system starts with, and ends up having everything thrown into it even if it is not the right tool for the job. Over time, chunks of data end up being moved out of the relational monster into other data stores. If your relational database choice is tightly coupled with your implementation, then you'll have a harder time moving things like your search engine into Solr, your queue into RabbitMQ, your counters into Redis, and so on. And yes, you'll also have a hard time moving to a different relational database, which I've also seen happen (on a massive, massive scale actually.)

If you stick to using an ORM that provides few leaks in its abstraction, write non-clever SQL, model things relationally using the standard types, generally speaking moving parts of a system out of a relational database into different data stores goes from being nearly impossible to being merely difficult.

Typical fanboy bluster. What's dishonest about the criticism, be specific? What you really mean to say is that most MySQL users use it because it is there and take it as it has been configured for them. MySQL is the storage platform for people who do not know about databases and probably only use it because some blogging product requires them to.

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.

That looks great actually. I've recently switched to PG and use pgAdmin III, but find it too confusing with tables being hidden so deep in the tree (or maybe that's "pg way" of doing things with tables not being centric?)

Are you suggesting that there isn't "serious commercial support" available for MySQL? Oracle, Percona, SkySQL, and a number of other businesses provide high quality support for MySQL installations.