PostgreSQL Rising

Up until very recently Twitter was still using stock MySQL 5.0.45 (which I believe is from 2007) nearly everywhere in production. The notion that MySQL requires a highly patched version with a world class development team to maintain is a myth. Facebooks patches are made public and are often incorporated into MariaDB and/or Percona forks. Oracle usually goes their own way on implementing changes from the community.

Source: http://tinyurl.com/7adp6fl (PDF)

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 [2] Multi-Tenant Data Architecture http://msdn.microsoft.com/en-us/library/aa479086.aspx

Thanks a ton. That is really helpful. I will see if changing to fixed width is feasible when string length is above a threshold.

better names would have been PostSQL or PostDB not Postgres

With the ability to define columns which store composite types, the line between a custom data type in C and a composite type plus casts is blurring.

Back in the 7.x line at one point I discovered that you could do something like:

create table foo (...);

create table bar (myfoo foo);

And this table would be write-only, and indeed the backend would crash when reading from it. That's the only really interesting bug I ever found in PostgreSQL. However, I made the case that it would be nice to be able to treat tuples as full-fledged data types and now by at least 8.1, that was supported.

So now I can:

create type foo as (...); -- works with create table too

create function foo_to_text .....;

create function text_to_foo ....;

create cast......

create table bar (my_foo foo);

insert into bar (my_foo) values ('my.foo.as.text'::text);

So the point is that composite types can now be used to blur the lines quite a bit between traditional custom types and the roles that composite types are traditionally used for. You can go a long way prototyping what is useful with composite types, and then later do that right in C.