Re: strategies for segregating client data when using PostgreSQL in a web app

On Sat, Aug 4, 2012 at 6:05 AM, Menelaos PerdikeasSemantix
<mperdikeas(dot)semantix(at)gmail(dot)com> wrote:
> [1] use just one database and schema and logically segregate companies data
> by having all tables have a client_id column as part of their primary key.
> [2] use multiple database (in the same server instance) and only the public
> schema in each of them for the customer's data.
> [3] use one database and multiple schemas to separate the different
> customer's data.
>
> (the [2] and [3] in particular seem practically indistinguishable to me).

At my work, we started with a MySQL setup involving one schema (what
MySQL calls a "database") for global data and one for each customer's.
After moving to Postgres, we solidified that as schemas in a database,
and since then have moved quite a bit of data into the global-data
schema - so we're in a hybrid of [1] and [3].

> What are the trade-offs in terms of:
>
> [1] enforcing security and access separation

If your customers aren't able to write SQL themselves, this is easy in
any scheme. Otherwise, [2] and [3] become far easier (but [1] is still
possible, it just requires some careful work with views).

> [2] administering the database and responding to inquiries like "please
> reset my company's data to the image of yesterday cause we messed up some
> tables" or "we are taking our business elsewhere, can we please have a dump
> of our data?" or "we would like a weekly DVD with our data".

Ouch. This is only possible, AT ALL, on condition that nobody's data
affects anybody else's. But assuming that, I'd recommend [2] or [3].

> [3] backup / restore and partitioning

Definitely [3], you can back the whole lot up easily.

> [4] potential for connection pooling at the Application Server.

Never done this, can't speak to it.

On the whole, I would advise option 3. You get good separation, and
you can do schema upgrades with short outages for each customer rather
than a long outage for everyone. Though this could work the other way
too - it might be more convenient to go for option 1, guaranteeing
that every customer's tables are following the same structure.

ChrisA