Re: strategies for segregating client data when using PostgreSQL in a web app - Mailing list pgsql-general

From Chris Angelico
Subject Re: strategies for segregating client data when using PostgreSQL in a web app
Date
Msg-id CAPTjJmo2RgP=FEkUs7kQxnoxPsv7NyF7W5s7gb1tcUERO8xb9w@mail.gmail.com
Whole thread Raw
In response to strategies for segregating client data when using PostgreSQL in a web app  (Menelaos PerdikeasSemantix <mperdikeas.semantix@gmail.com>)
List pgsql-general
On Sat, Aug 4, 2012 at 6:05 AM, Menelaos PerdikeasSemantix
<mperdikeas.semantix@gmail.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

pgsql-general by date:

Previous
From: Scott Marlowe
Date:
Subject: Re: How to don't update sequence on rollback of a transaction
Next
From: Mike Christensen
Date:
Subject: Re: Another question about Range types